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:

  1. 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() or pg_terminate_backend().

  2. 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 or ROLLBACK PREPARED to close them.

  3. 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.