Understanding Excess Space in PostgreSQL Tables

Hey, if you've ever noticed your PostgreSQL tables ballooning in size without a good reason, you're likely dealing with what's known as table bloat. This happens when outdated data rows—created from updates or deletions—stick around because the VACUUM process hasn't swept them up yet, leading to bloated data files.

For VACUUM to properly clean house and free up that space, it needs assurance that no active transaction could ever need those old rows. If something's holding onto an ancient transaction, the cleanup gets halted right there.

In this guide, I'll walk you through four common reasons this buildup occurs, show you signs to spot them, and share straightforward ways to sort them out.

  • Transactions that drag on forever
  • Prepared transactions left hanging without a commit
  • Standby server queries when hot_standby_feedback is turned on
  • Delays in logical replication

Setup for Testing

  • PostgreSQL 19dev (commit hash: 34740b90bc123d645a3a71231b765b778bdcf049)

Dealing with Transactions That Linger

One of the most straightforward issues I've seen is when a transaction just hangs around too long, whether it's busy or just sitting idle. This blocks VACUUM from clearing out the remnants of updates or deletes that happened after it began, since that transaction might still want to peek at the original data versions.

Getting Things Ready

Fire up Terminal 1, kick off a transaction, and grab its ID.

Terminal 1:
=# BEGIN;
BEGIN
=*# SELECT txid_current();
 txid_current
--------------
          782
(1 row)

Then, in Terminal 2, wipe some data and trigger a VACUUM.

Terminal 2:
=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 782, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 15 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

When you see something like tuples: 0 removed, 100 remain, 100 are dead but not yet removable, it means those stale rows are still lurking and haven't been cleared.

Pinpointing the Issue

Look for a note like removable cutoff: 782, which was 2 XIDs old when operation ended—that's pointing fingers at transaction ID 782 as the blocker. Dive into the pg_stat_activity view to investigate.

Terminal 2:
=# SELECT * FROM pg_stat_activity WHERE backend_xid = 782;
-[ RECORD 1 ]----+------------------------------
datid            | 5
datname          | postgres
pid              | 94076
leader_pid       | [NULL]
usesysid         | 10
usename          | shinya
application_name | psql
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 13:41:30.049678+09
xact_start       | 2026-01-19 13:54:38.856466+09
query_start      | 2026-01-19 13:54:43.501664+09
state_change     | 2026-01-19 13:54:43.50271+09
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 782
backend_xmin     | [NULL]
query_id         | [NULL]
query            | SELECT txid_current();
backend_type     | client backend

Turns out, the process with PID 94076 is chilling in an "idle in transaction" mode, which is why VACUUM couldn't do its job.

Fixing It Up

Shut down that backend process using PID 94076.

Terminal 2:
=# SELECT pg_terminate_backend(94076);
 pg_terminate_backend
----------------------
 t
(1 row)

=# VACUUM (VERBOSE) t;
INFO:  00000: vacuuming "postgres.public.t"
LOCATION:  heap_vacuum_rel, vacuumlazy.c:848
INFO:  00000: table "t": truncated 1 to 0 pages
LOCATION:  lazy_truncate_heap, vacuumlazy.c:3401
INFO:  00000: finished vacuuming "postgres.public.t": index scans: 0
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 792, which was 1 XIDs old when operation ended
new relfrozenxid: 792, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 7.796 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
LOCATION:  heap_vacuum_rel, vacuumlazy.c:1199
VACUUM

Now, a message like tuples: 100 removed, 0 remain, 0 are dead but not yet removable shows everything's been tidied up nicely.

Quick tip: Double-check it's okay to kill that process before you pull the plug.

Handling Prepared Transactions That Aren't Committed

Sometimes this pops up when applications...[продолжение статьи]