Postgres table much bigger than the data in it
Table has maybe 2 million rows and takes 60GB. A row is around 200 bytes. Updates a lot. Is this normal? autovacuum is on.
Your discussion
This browser holds the management token for this post.
Save management token
Keep this private. Anyone with the token can resolve or delete this discussion.
2 replies
That's bloat. Check first whether autovacuum is keeping up:
SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;
If last_autovacuum is old, look for a long-running transaction, idle in transaction is the usual culprit, it stops dead rows being cleaned. Then lower autovacuum_vacuum_scale_factor on that table.
Normal VACUUM frees the space for reuse but doesn't give it back to the OS. VACUUM FULL does and locks the table for the duration. pg_repack if you can't have that.
idle in transaction, 9 days, a reporting tool someone left open. thank you