Why Your Postgres Table Keeps Growing Even Though You're Deleting Rows

A while back I noticed something odd. A table with a modest number of rows was taking far more disk space than it should. SELECT count(*) said one thing, pg_relation_size said another.

I had deleted and updated plenty of rows, so where was the space going? The answer is MVCC, and once you see it, a lot of Postgres behavior starts to make sense.

Let's reproduce it locally and watch it happen.

The Short Version

In Postgres, an UPDATE does not change a row in place, and a DELETE does not remove it. Both just leave the old version of the row behind. Those leftover versions are called dead tuples, and they keep taking up space until something cleans them up.

That "something" is VACUUM. But even VACUUM doesn't do exactly what most people expect.

Why Postgres Works This Way

Postgres uses MVCC (Multi-Version Concurrency Control) so that readers never block writers and writers never block readers.

If a transaction is reading a row while another one updates it, the reader needs to keep seeing the old version. So Postgres keeps both versions: the old one for anyone who still needs it, and a new one for everyone else.

Every row version carries two hidden columns, xmin (the transaction that created it) and xmax (the transaction that deleted or replaced it). Postgres uses these to decide which version each transaction is allowed to see.

Let's Reproduce It

Start with a simple table:

CREATE TABLE bloat_demo (
    id  int PRIMARY KEY,
    val text
);

-- Turn off autovacuum for this table so we can see the raw behavior
ALTER TABLE bloat_demo SET (autovacuum_enabled = false);

INSERT INTO bloat_demo
SELECT g, md5(g::text)
FROM generate_series(1, 100000) AS g;

Check the size:

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size,
       count(*) AS live_rows
FROM bloat_demo;

On my machine (running Postgres 17 in docker) this showed 6672 kB for 100,000 rows. Your number may differ slightly by version and settings.

Step 1: Watch a row move

SELECT ctid, xmin, xmax, id FROM bloat_demo WHERE id = 1;

UPDATE bloat_demo SET val = 'changed' WHERE id = 1;

SELECT ctid, xmin, xmax, id FROM bloat_demo WHERE id = 1;

The ctid (the physical location of the row) changes after the update. The row didn't get modified in place. Postgres wrote a brand new version somewhere else and left the old one behind.

In my run, the ctid went from (0,1) to (833,41). The table was about 834 pages at that point, so the new version landed on the very last page. Postgres appended it at the end of the file instead of rewriting the row where it was. The xmin also changed (742 to 754), because a different transaction created this version.

Step 2: Update everything, a few times

UPDATE bloat_demo SET val = md5(val);
UPDATE bloat_demo SET val = md5(val);
UPDATE bloat_demo SET val = md5(val);

Now check again:

SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size,
       count(*) AS live_rows
FROM bloat_demo;

Still 100,000 live rows, but the table went from 6672 kB to 26 MB, about 4x bigger. Every pass left a full copy of dead rows behind.

You can see them in the stats:

SELECT relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'bloat_demo';

(These stats can lag by a moment, so if n_dead_tup looks low, wait a few seconds and run it again.)

My run showed n_dead_tup = 299962 next to 100,000 live rows: roughly three full copies of the table, one per update pass. It isn't exactly 300,000 because these numbers are estimates.

What VACUUM Actually Does

VACUUM bloat_demo;

Run the size query again. Here is the part that surprises most people: the file usually does not shrink.

VACUUM marks the space taken by dead tuples as reusable inside the table. New inserts and updates will fill that space instead of extending the file. But the file on disk stays the same size, because Postgres only gives space back to the OS in one narrow case: when completely empty pages sit at the very end of the table.

So VACUUM stops the table from growing further. It does not make it smaller.

Here is the proof from my run. After VACUUM the size was still 26 MB. Then I updated every row once more:

UPDATE bloat_demo SET val = md5(val);
SELECT pg_size_pretty(pg_relation_size('bloat_demo'));

It stayed at 26 MB. The new row versions went into the space vacuum had freed, instead of extending the file. That is what VACUUM is for: it keeps the table from growing, it doesn't shrink it.

Getting the space back

If you really need to shrink the file, you have two main options:

• VACUUM FULL rewrites the whole table into a new file. It works, but it takes an ACCESS EXCLUSIVE lock, which blocks reads and writes for the whole duration. Not something you want to run casually on a busy production table.
• pg_repack does a similar rewrite with much shorter locks. It is an extension you have to install, but it is the usual choice for live systems.

I ran VACUUM FULL on the demo table and the size dropped from 26 MB back to 6672 kB, exactly where it started.

The Gotcha: Long-Running Transactions

Here is the one that causes real production pain. VACUUM can only remove a dead tuple if no transaction could still need to see it.

If someone leaves a transaction open for hours (a forgotten BEGIN in a psql session, or an app stuck in idle in transaction), vacuum has to keep every row version that transaction might still read. Dead tuples pile up and vacuum runs but cleans almost nothing.

Find the offenders:

SELECT pid, state, xact_start, now() - xact_start AS open_for, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

If the oldest transaction on the list has been open for hours, that is probably your bloat problem.

Here is what this looked like in my test. In a second psql session I opened a transaction and left it open:

BEGIN;
SELECT txid_current();   -- returned 761

Back in the first session I updated every row and ran a verbose vacuum:

UPDATE bloat_demo SET val = md5(val);
VACUUM VERBOSE bloat_demo;

The relevant lines of the output:

tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable
removable cutoff: 761

Vacuum ran fine and removed nothing. The 100,000 old row versions have to stay, because transaction 761 might still need to see them. The cutoff of 761 is the ID of that open transaction. A second VACUUM VERBOSE gave the same result. Ending the transaction with COMMIT or ROLLBACK lifts the block.

After I ran ROLLBACK in the second session, the same command gave a different result:

tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable
removable cutoff: 763

Same table, same VACUUM. This time it removed all 100,000 dead versions, and the cutoff moved from 761 to 763 now that the old transaction was gone. The only thing that changed was the open transaction.

One detail: a bare BEGIN with nothing inside it doesn't hold vacuum back. What does is the transaction's ID or snapshot. I ran txid_current() inside mine, which assigns it an ID.

How to Check Your Own Database

A quick look at which tables have the most dead tuples:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

A high dead_pct together with an old last_autovacuum means autovacuum is not keeping up (or is being blocked, see above).

This is an estimate based on statistics, not an exact measurement. For precise numbers, the pgstattuple extension can scan a table and report real bloat.

What I Took Away From This

• UPDATE and DELETE never free space immediately. They create dead tuples.
• VACUUM makes that space reusable, but it doesn't shrink the file.
• VACUUM FULL shrinks it, at the cost of blocking the table.
• Long-running transactions silently stop vacuum from doing its job.
• Tables with heavy update traffic are the ones to watch.

The next time a table looks bigger than its data should justify, start with n_dead_tup and pg_stat_activity before touching anything else.

Have you run into table bloat in production? I'd like to hear how you found it.

Comments

[Abhinav Sharma]

The count vs pg_relation_size mismatch is the cleanest way to make MVCC click - the rows are gone for readers but not for the disk. The part that still catches teams is that plain VACUUM returns space to the table, not to the OS, so the file stays big until VACUUM FULL or pg_repack.