MVCC and VACUUM: you deleted the rows, the table didn't shrink
In PostgreSQL an UPDATE writes a new row and a DELETE erases nothing. A look under the hood at dead tuples, bloat, autovacuum, and how one forgotten transaction bloats the whole database.
The problem: a million rows deleted, zero bytes freed
Picture this: your events table holds several million rows, half of them stale. You write a nightly Celery task that DELETEs the old ones. Next morning you check pg_relation_size() and the table hasn't shrunk by a single byte. Or another scenario: every payment updates a status in orders, the row count barely moves, yet the table and its indexes keep growing week after week and plain SELECTs get slower.
This isn't a bug. It's a direct consequence of how PostgreSQL implements MVCC and how its janitor, VACUUM, works. Let's look under the hood: row versions, xmin/xmax, dead tuples, bloat, autovacuum tuning, and why one forgotten transaction can hurt the whole database.
Why the naive mental model breaks
Most people picture the database like this: UPDATE changes a row in place, DELETE erases it from disk, and the space is freed immediately. If that were true, a reader would have to wait on a lock while a writer modified a row, and vice versa.
PostgreSQL took a different route: readers don't block writers and writers don't block readers. The price is that one logical row can have several physical versions on disk at the same time. Each transaction uses its snapshot to decide which version it sees. And old versions that nobody needs anymore don't disappear on their own; something has to clean them up.
MVCC from the inside: xmin, xmax and tuple versions
Every physical row (a tuple) carries hidden fields in its header. The important ones:
xmin: the ID (XID) of the transaction that created this version (INSERT or UPDATE);xmax: the ID of the transaction that deleted or updated it (0means untouched; the field is also used for row locks);ctid: the physical address of the version,(page number, position on page).
You can select these system columns with a plain SELECT:
CREATE TABLE accounts (
id bigint PRIMARY KEY,
balance numeric(14, 2) NOT NULL
);
INSERT INTO accounts VALUES (1, 100.00);
SELECT ctid, xmin, xmax, * FROM accounts;
-- ctid | xmin | xmax | id | balance
-- (0,1) | 755 | 0 | 1 | 100.00
UPDATE accounts SET balance = 150.00 WHERE id = 1;
SELECT ctid, xmin, xmax, * FROM accounts;
-- ctid | xmin | xmax | id | balance
-- (0,2) | 756 | 0 | 1 | 150.00
Notice that ctid moved from (0,1) to (0,2). The UPDATE didn't modify the row in place: it wrote a new version and stamped its own XID into the old version's xmax. In other words, UPDATE is really INSERT + DELETE. Your XIDs will differ; the idea is the same.
The old version is still sitting on the page. The pageinspect extension shows it (superuser required):
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('accounts', 0));
-- lp | t_xmin | t_xmax | t_ctid
-- 1 | 755 | 756 | (0,2)
-- 2 | 756 | 0 | (0,2)
The first version has t_xmax = 756 and its t_ctid points to the new one. DELETE works the same way minus the new version: it fills in xmax, and that's it. The data stays on disk.
The simplified visibility rule: a version is visible to you if its xmin transaction committed and is "in the past" for your snapshot, and its xmax is empty or belongs to a transaction your snapshot can't see (not yet committed, rolled back, or started after the snapshot).
Dead tuples and bloat
Once the xmax transaction has committed and no open transaction can still see that version, it becomes a dead tuple. It occupies space, fills pages, gets read by every Seq Scan anyway, and leaves index entries pointing at it.
When dead tuples pile up and a table or index becomes much larger than its useful data, that's bloat. It's easy to reproduce:
CREATE TABLE events AS
SELECT g AS id, now() AS created_at, 'pending'::text AS status
FROM generate_series(1, 1000000) AS g;
SELECT pg_size_pretty(pg_relation_size('events')); -- A
UPDATE events SET status = 'done';
SELECT pg_size_pretty(pg_relation_size('events')); -- ~2 x A
VACUUM events;
SELECT pg_size_pretty(pg_relation_size('events')); -- still ~2 x A
UPDATE events SET status = 'archived';
SELECT pg_size_pretty(pg_relation_size('events')); -- roughly the same: freed space reused
What happened? The first UPDATE wrote a new version of every row. The old pages were full, so the new versions landed on new pages and the table nearly doubled. VACUUM removed the dead tuples, but the file didn't shrink: the freed space goes into the free space map (FSM) and is reused by later INSERTs and UPDATEs. The last step proves it: the second UPDATE adds almost nothing. Measure the numbers yourself; they depend on your PostgreSQL version and row width.
Plain VACUUM doesn't give space back to the operating system; it marks it for reuse inside the table. Only completely empty pages at the end of the file get truncated.
To actually return space you need VACUUM FULL, which rewrites the table into a new file but holds an ACCESS EXCLUSIVE lock the whole time: no reads, no writes. In production people usually reach for the pg_repack extension, which also rewrites the table but only takes brief locks.
VACUUM does two more important jobs: it updates the visibility map (needed for efficient index-only scans) and it freezes old versions, more on that below.
HOT updates: the cheaper kind
If an UPDATE doesn't touch any indexed column and there's room on the same page, PostgreSQL performs a HOT (heap-only tuple) update: the new version goes on the same page and no new index entries are added. PostgreSQL can also partially clean such chains while reading the page, without waiting for VACUUM (page pruning). That's why lowering fillfactor (say, to 90) on frequently updated tables and avoiding unnecessary indexes pays off: an index on status makes every update of that column non-HOT.
When autovacuum kicks in
You don't have to run VACUUM by hand; the autovacuum daemon does it. Every autovacuum_naptime it walks the databases and vacuums a table when:
n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples
The key settings and their defaults:
| Parameter | Default | What it does |
|---|---|---|
autovacuum_vacuum_threshold |
50 | base dead-tuple threshold |
autovacuum_vacuum_scale_factor |
0.2 | fraction of table size added to the threshold |
autovacuum_vacuum_insert_threshold |
1000 | for insert-only tables (PG 13+) |
autovacuum_analyze_scale_factor |
0.1 | when ANALYZE runs |
autovacuum_naptime |
1min | pause between checks |
autovacuum_max_workers |
3 | concurrent workers |
autovacuum_vacuum_cost_delay |
2ms | throttling to protect disk I/O |
autovacuum_freeze_max_age |
200 million | forced anti-wraparound vacuum threshold |
The trouble is scale_factor = 0.2. On a 10-million-row table autovacuum starts only after roughly 2 million dead tuples. For large, busy tables that's far too late. The fix is to leave the global settings alone and tune that specific table. In Django a migration is the natural place:
# orders/migrations/0013_order_autovacuum.py
from django.db import migrations
class Migration(migrations.Migration):
dependencies = [("orders", "0012_order_psp_id")]
operations = [
migrations.RunSQL(
sql="""
ALTER TABLE orders_order SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 1000,
autovacuum_analyze_scale_factor = 0.02,
fillfactor = 90
);
""",
reverse_sql="""
ALTER TABLE orders_order RESET (
autovacuum_vacuum_scale_factor,
autovacuum_vacuum_threshold,
autovacuum_analyze_scale_factor,
fillfactor
);
""",
),
]
fillfactor only affects newly written pages; existing pages are redistributed only when the table is rewritten (VACUUM FULL or pg_repack). Also note that autovacuum is deliberately throttled by cost-based delay. If it can't keep up with the rate of dead tuples on a big table, you'll need to raise autovacuum_vacuum_cost_limit.
Long transactions: the enemy of the whole database
VACUUM can remove a dead tuple only if no transaction can see it. PostgreSQL decides this using the xmin horizon: the oldest still-running transaction or snapshot. If a session opened a transaction an hour ago and forgot to close it, the dead tuples created during that hour in every table of the database cannot be removed. Autovacuum keeps running, burning CPU and I/O, but removes nothing; VACUUM VERBOSE reports those rows as "dead but not yet removable".
In a Django project it often looks like this:
# orders/tasks.py — WRONG: the transaction stays open during every HTTP call
import requests
from celery import shared_task
from django.db import transaction
from .models import Order
PSP_URL = "https://psp.example.com/status/{}"
@shared_task
def sync_orders():
with transaction.atomic():
for order in Order.objects.filter(status="pending"):
resp = requests.get(PSP_URL.format(order.psp_id), timeout=10)
order.status = resp.json()["status"]
order.save(update_fields=["status"])
The task calls an external API for every order, and the whole loop sits inside a single transaction.atomic(). If the API slows down, the transaction stays open for minutes. The right approach is to move network calls out of the transaction and wrap only the writes in a short one:
# orders/tasks.py — FIXED: network outside, short transaction only for writes
import requests
from celery import shared_task
from django.db import transaction
from .models import Order
PSP_URL = "https://psp.example.com/status/{}"
@shared_task
def sync_orders(batch_size=200):
# one short SELECT in autocommit mode
pending = list(
Order.objects.filter(status="pending")
.order_by("id")
.values_list("id", "psp_id")[:batch_size]
)
# slow network calls, no transaction open
results = {}
for order_id, psp_id in pending:
resp = requests.get(PSP_URL.format(psp_id), timeout=10)
resp.raise_for_status()
results[order_id] = resp.json()["status"]
# short transaction just for the writes
with transaction.atomic():
for order_id, status in results.items():
Order.objects.filter(id=order_id, status="pending").update(status=status)
ATOMIC_REQUESTS = True carries the same risk: a slow external call inside a view keeps the transaction open for the entire request.
How to find who's holding the horizon:
SELECT pid,
usename,
state,
now() - xact_start AS xact_age,
backend_xid,
backend_xmin,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 10;
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;
SELECT gid, prepared, owner
FROM pg_prepared_xacts;
The prime suspects are sessions in idle in transaction with an old backend_xmin or backend_xid. Also on the list: unused replication slots, forgotten prepared transactions, and long analytical queries on a replica with hot_standby_feedback = on. They all hold the horizon back.
As a safety net, set timeouts in your Django settings:
# settings.py
DATABASES = {
"default": {
"ENGINE": "django.db.backends.postgresql",
"NAME": "shop",
"USER": "shop",
"HOST": "127.0.0.1",
"PORT": "5432",
"OPTIONS": {
"options": (
"-c idle_in_transaction_session_timeout=60s "
"-c statement_timeout=30s"
),
},
}
}
If you connect through PgBouncer in transaction mode, startup options may never reach the server; in that case set the values at the role level: ALTER ROLE shop SET idle_in_transaction_session_timeout = '60s';.
Big UPDATEs and DELETEs in batches
Updating an entire table in one query (Event.objects.update(status="done")) means one long transaction and millions of dead tuples at once. Split such backfills into batches:
# events/backfill.py
import time
from django.db import connection, transaction
from .models import Event
def backfill_status(batch_size=5_000, vacuum_every=20):
last_id = 0
batches = 0
while True:
with transaction.atomic():
ids = list(
Event.objects.filter(id__gt=last_id, status="pending")
.order_by("id")
.values_list("id", flat=True)[:batch_size]
)
if not ids:
break
Event.objects.filter(id__in=ids).update(status="done")
last_id = ids[-1]
batches += 1
if batches % vacuum_every == 0:
# VACUUM cannot run inside a transaction block; here we are in autocommit
with connection.cursor() as cursor:
cursor.execute("VACUUM (ANALYZE) events_event")
time.sleep(0.1) # let replicas and autovacuum breathe
Batching doesn't reduce the total number of dead tuples, but each transaction stays short, the horizon keeps moving, and the space VACUUM frees between batches gets reused by the next ones. VACUUM can't run inside a transaction block, which is why it's called outside atomic(), in autocommit mode.
Monitoring: pg_stat_user_tables
Per-table statistics live in the pg_stat_user_tables view:
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,
n_tup_upd,
n_tup_hot_upd,
last_autovacuum,
autovacuum_count,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
What to watch for:
n_dead_tupkeeps growing whilelast_autovacuumis old: autovacuum can't keep up, or the horizon is pinned;- a low
n_tup_hot_upd / n_tup_updratio: updates touch indexed columns or pages have no free room; autovacuum_countclimbs fast butn_dead_tupdoesn't drop: almost always a long transaction or a slot.
These numbers are estimates from the statistics system. For exact bloat there's the pgstattuple extension: SELECT * FROM pgstattuple('events'); gives precise dead-tuple and free-space percentages, but reads the whole table, so be careful on big ones.
Transaction ID wraparound, briefly
An XID is a 32-bit number. About 4 billion values are used in a circle, and for any transaction roughly 2 billion are "the past" and the rest "the future". If old rows were never frozen, one day they would look as if a "future" transaction created them and become invisible. To prevent that, VACUUM marks sufficiently old versions as frozen.
When a table's relfrozenxid age exceeds autovacuum_freeze_max_age (200 million by default), a forced anti-wraparound vacuum runs even if autovacuum is disabled. Since PostgreSQL 14, reaching vacuum_failsafe_age (1.6 billion by default) makes VACUUM drop its cost throttling and do the essential work faster. If even that isn't enough, PostgreSQL first emits warnings and then, to protect your data, stops assigning new XIDs, so writes stop. This usually happens where long transactions and forgotten slots blocked cleanup for a long time.
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;
SELECT c.oid::regclass AS table_name,
age(c.relfrozenxid) AS xid_age
FROM pg_class c
WHERE c.relkind IN ('r', 'm', 't')
ORDER BY xid_age DESC
LIMIT 10;
Feed these values into your monitoring and alert when they climb well past autovacuum_freeze_max_age.
Production pitfalls
- Running VACUUM FULL "to tidy up". It locks the table completely. In production
pg_repackis safer, andREINDEX CONCURRENTLYhandles index bloat. - Queue tables. Outbox, task and event tables see rows inserted and deleted quickly. Give them aggressive autovacuum settings; at large volumes, partition them and
DETACH/DROPold partitions instead ofDELETE. - A hot row. If a single counter row is updated many times per second, its dead versions accumulate. HOT and pruning help, but an open long transaction defeats them too.
autovacuum_enabled = false. Easy to set and forget. Bloat grows without bound, and wraparound protection will still launch a forced vacuum at the worst possible moment.- Shells and debugging. An
atomic()block inmanage.py shellor Jupyter, a transaction paused atbreakpoint(): a ready-madeidle in transactionsession. - Forgetting ANALYZE after a big DELETE. Planner statistics go stale;
VACUUM (ANALYZE)does both. - Index bloat. Freed space in a B-tree page is only reused for new keys in the same key range. Track index size separately from the table (
pg_relation_size('index_name')).
Checklist
- Lower
autovacuum_vacuum_scale_factorper table for large, busy tables. - Keep network calls, file uploads and slow computations outside
transaction.atomic(). - Set
idle_in_transaction_session_timeoutandstatement_timeout. - Monitor the oldest
xact_startandbackend_xmininpg_stat_activity, pluspg_replication_slots. - Watch
n_dead_tup,last_autovacuumand the HOT ratio inpg_stat_user_tables. - Batch backfills and mass deletes.
- Alert on
age(datfrozenxid). - Use
pg_repackinstead ofVACUUM FULL.
Takeaway
In PostgreSQL UPDATE writes a new row version and DELETE just sets xmax; VACUUM removes the old versions and makes the space reusable, but doesn't give it back to the disk. Autovacuum is usually enough, but large tables need tuning, and it's powerless against a long open transaction. Keep transactions short and watch pg_stat_user_tables and pg_stat_activity; those two habits prevent most bloat problems.
// comments
Loading comments...