Postgres: "vacuum" command does not clean up dead tuples
Use VACUUM (VERBOSE)
to get detailed statistics of what it is doing and why.
There are three reasons why dead tuples cannot be removed:
There is a long running transaction that has not been closed. You can find the bad boys with
SELECT pid, datname, usename, state, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY age(backend_xmin) DESC;
You can get rid of a transaction with
pg_cancel_backend()
orpg_terminate_backend()
.There are prepared transactions which have not been commited. You can find them with
SELECT gid, prepared, owner, database, transaction FROM pg_prepared_xacts ORDER BY age(transaction) DESC;
User
COMMIT PREPARED
orROLLBACK PREPARED
to close them.There are replication slots which are not used. Find them with
SELECT slot_name, slot_type, database, xmin FROM pg_replication_slots ORDER BY age(xmin) DESC;
Use
pg_drop_replication_slot()
to delete an unused replication slot.