When PostgreSQL vacuum won’t remove dead rows from a Table ?

What is VACUUM in PostgreSQL ?

In PostgreSQL, whenever rows in a table deleted, The existing row or tuple is marked as dead ( will not be physically removed) and during an update, it marks corresponding exiting tuple as dead and inserts a new tuple so in PostgreSQL UPDATE operations = DELETE + INSERT. These dead tuples consumes unnecessary storage and eventually you have a bloated PostgreSQL database, This is a serious issue to solve for a PostgreSQL DBA. VACUUM reclaims in the storage occupied by dead tuples, Please keep this noted that reclaimed storage space is never given back to the resident operating system rather they are just defragmented within the same database page and so storage for reuse to future data inserts in the same table. Does the pain stops here ? No, It doesn’t. Bloat seriously affect the PostgreSQL query performance, In PostgreSQL tables and indexes are stored as array of fixed-size pages ( usually 8KB in size). Whenever a query requests for rows, the PostgreSQL instance loads these pages into the memory and dead rows causes expensive disk I/O during data loading.

How to monitor autovacuum has processed bloated tables ?

If you are suspecting bloated tables in your PostgreSQL infrastructure, The  first thing to check is vacuum has processed those bloated table. We use following script to collect last processed vacuum data:

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
    / (n_live_tup
       * current_setting('autovacuum_vacuum_scale_factor')::float8
          + current_setting('autovacuum_vacuum_threshold')::float8)
     DESC
LIMIT 10;

But sometimes you can see vacuum is run recently and still didn’t free the dead tuples:

 schemaname |    relname   | n_live_tup | n_dead_tup |   last_autovacuum
-----------+--------------+------------+------------+---------------------
 ad_ops_dB  | ad_clicks    |      96100 |      96100 | 2020-04-18 16:33:47
 pg_catalog | pg_attribute |         11 |        259 |
 pg_catalog | pg_amop      |        193 |        81  |
 pg_catalog | pg_class     |         61 |         29 |
 pg_catalog | pg_type      |         39 |         14 |
 pg_catalog | pg_index     |          8 |         21 |
 pg_catalog | pg_depend    |       7349 |        962 |
 pg_catalog | pg_trigger   |          6 |         37 |
 pg_catalog | pg_proc      |        469 |         87 |
 pg_catalog | pg_shdepend  |         18 |         11 |
(10 rows)

So it’s evident now that sometimes PostgreSQL vacuum is not removing dead rows.

When PostgreSQL Vacuum won’t remove the dead rows ?

Transactions in PostgreSQL are identified with xid  ( transaction or “xact” ID), PostgreSQL will assign a transaction xid only if it starts modifying the data because it’s only from that point where other process need to start tracking it changes, These are not applicable for read only transactions.

We have  copied  below the PostgreSQL data structure ( from proc.c ) that handle transactions:

typedef struct PGXACT
{
    TransactionId xid;   /* id of top-level transaction currently being
                          * executed by this proc, if running and XID
                          * is assigned; else InvalidTransactionId */

    TransactionId xmin;  /* minimal running XID as it was when we were
                          * starting our xact, excluding LAZY VACUUM:
                          * vacuum must not remove tuples deleted by
                          * xid >= xmin ! */

    ...
} PGXACT;

PostgreSQL Vacuum removes only the dead rows that are not in use anymore. A tuple is considered not needed when transaction ID of the deleting transaction is older than oldest transaction which is still active in the PostgreSQL database. The vacuum processes calculate the minimum boundary of data that they need to retain by tracking the minimum of xmins of all active transactions.

The following are three situations which holds back xmin horizon in a PostgreSQL infrastructure:

1. Long running transactions

You can find the details of long running queries and their respective xmin values form the query copied below:

SELECT pid, datname, usename, state, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;

P.S. If you think those transactions are no longer required, Please use pg_terminate_backend()  to terminate PostgreSQL sessions blocking Vacuum processes

2. Abandoned replication slots

In PostgreSQL a replication slot is a data structure to control PostgreSQL from deleting the data that are still required by a standby server to catch-up with the primary database instance. If ever the replication to a  standby server / slave is delayed or slave PostgreSQL instance goes down for longer duration then replication slot will prevent vacuum from deleting old records / rows. To monitor replication slots and their relative xmin value please use the query below

SELECT slot_name, slot_type, database, xmin
FROM pg_replication_slots
ORDER BY age(xmin) DESC;

P.S. – To drop replication slots that are no longer needed, please use the function pg_drop_replication_slot( )

3. Orphaned prepared transactions

In a two-phase commit PostgreSQL database infrastructure, a distributed transaction is first prepared using PREPARE statement and later committed with COMMIT PREPARED statement. To monitor all prepared transactions and their respective xmin value please run the query below:

SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;

P.S. – We recommend ROLLBACK PREPARED SQL statement to remove prepared transactions

Conclusion

PostgreSQL Autovacuum addresses table bloating efficiently but there are situations where vacuum does’t work as expected so we strongly recommend to regularly check on how vacuum is processing the bloated tables

Reference links

• https://www.citusdata.com/blog/2016/11/04/autovacuum-not-the-enemy/ 
• https://www.postgresql.org/docs/11/sql-vacuum.html 
• https://devcenter.heroku.com/articles/postgresql-concurrency 
• https://github.com/pgexperts/pgx_scripts/tree/master/bloat 
• https://minervadb.com/index.php/2020/04/27/how-postgresql-autovacuum-works/
• https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/