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…
A database table can grow in size even after rows have been deleted, due to a feature called Multi-Version Concurrency Control (MVCC) in PostgreSQL. When a row is updated or deleted, PostgreSQL keeps the old version of the row around, known as a dead tuple. These dead tuples take up space until something cleans them up, which is the job of the VACUUM command.
However, VACUUM only marks the space as reusable, it doesn't shrink the file on disk unless the table is completely empty. This can lead to unexpected growth in table size. To reclaim disk space, you can use VACUUM FULL, which rewrites the entire table into a new file and locks the table for the duration, or use pg_repack for a less intrusive approach.
Long-running transactions can also prevent VACUUM from removing dead tuples, causing them to pile up and consume more space.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
