Return of the vacuum

In our previous story about Postgres, we had discussed how freezing transaction IDs was required to fight the transaction ID wraparound problem.

However, at the end of the previous post, even through the original materialized view had been destroyed, the overall transaction ID counter was still ticking away.

Search for the rebels

Starting from scratch again, the oldest frozen transaction ID (XID), according to the system table pg_database was identified.

SELECT datname, datfrozenxid, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
   datname     |   datfrozenxid   |    age
---------------+------------------+--------------
 mydb          |   3842253562     |   1232089527
...

With this information, the next step was to search for matching records from pg_class

SELECT relnamespace, relname, relfrozenxid, age(relfrozenxid)
FROM pg_class
WHERE relfrozenxid = 3842253562;
 relnamespace  |     relname    | relfrozenxid |   age
---------------+----------------+--------------+----------
(0 rows)

It looks like there are no exact matches for the XID from pg_database. Maybe there are other lower values?

SELECT relnamespace, relname, relfrozenxid, age(relfrozenxid)
FROM pg_class
WHERE relfrozenxid < 3842253562;
ERROR:  operator does not exist: xid < integer
HINT:  No operator matches the given name and argument type(s).
You might need to add explicit type casts.
-- let's explicitly type cast then
SELECT relnamespace, relname, relfrozenxid, age(relfrozenxid)
FROM pg_class
WHERE relfrozenxid < 3842253562::xid;
ERROR:  cannot cast type integer to xid

We should have realized earlier that XIDs do not have a linear ordering, so it doesn’t make sense to compare these values to determine when they were generated. This was why we had been using age(id) in each of the earlier queries - to measure the number of transactions elapsed till the current transaction's ID.

SELECT relnamespace, relname, relfrozenxid, age(relfrozenxid)
FROM pg_class
WHERE age(relfrozenxid) <= (SELECT age(datfrozenxid)
                            FROM pg_database
                            WHERE datname = 'mydb')
 relnamespace  |     relname    | relfrozenxid |   age
---------------+----------------+--------------+----------
(0 rows)

It seems like the search hasn’t gone anywhere yet — only confirming our suspicion that entries in pg_database were not in sync with the XIDs of the tables (and that in pg_class).

Activating the Death Star

Fast running out of options, we decided to run an explicit VACUUM FREEZE. Using the FREEZE option with VACUUM aggressively freezes XIDs, updating relfrozenxid for each relation to the latest possible XID. However, running the command without specifying a table, makes it run on the entire database. This is expected to cause significant I/O and could cause performance degradation.

To prevent any unexpected surprises from activating the superlaser database-wide vacuum — the command was first tested on a replica. Having this replica also allowed for greater freedom in experimentation and the ability to gauge processing time.

VACUUM FREEZE VERBOSE;
-- an outrageously long time later
SELECT datname, datfrozenxid, age(datfrozenxid)
FROM pg_database
ORDER BY age(datfrozenxid) DESC;
   datname     |   datfrozenxid   |    age
---------------+------------------+-----------
 mydb          |      178183592   |  5727008
...

Finally!

After searching for a fix for many days, the end solution seems to have been a simple VACUUM FREEZE command, run against the entire database.

Note that the user was still the same rds_superuser from the previous post - despite not being a real superuser, the database-wide operation updated pg_database successfully.

Confident that the solution was now at hand, and with an estimate of the time required (and performance impact) for the maintenance operation, we finally applied the operation on the primary database. This achieved the expected result and now the age of datfrozenxid for the database was finally brought down to an acceptable value.

While a VACUUM FULL would have achieved a robust cleanup, we chose to still run VACUUM FREEZE as it does not require any exclusive lock (avoiding any downtime).

Vacuum awakens

Given that many of these problems could be prevented with more regular vacuuming, here are a few Postgres configuration parameters to make the autovacuum processes run aggressively.

• autovacuum_max_workers - number of workers running at any time
• maintenance_work_mem - maximum amount of memory to be used (by each individual worker)
• autovacuum_naptime - minimum delay between autovacuum runs
• autovacuum_cost_limit - acceptable cost when running autovacuum (shared across all workers)
• autovacuum_cost_delay - sleep time when cost limit is hit

While these help autovacuum run more frequently or for a longer duration, the choice of tables to vacuum still remains. For this purpose, other configuration parameters like autovacuum_vacuum_threshold, autovacuum_freeze_max_ageand autovacuum_vacuum_scale_factor can be used. A detailed list can be found at the Postgres documentation.

The entire episode, starting from the initial notification to the panicked searches for a fix to the final solution, proved to be a valuable learning experience. Given the vast number of unknown unknowns out there, I guess the entire process was to be expected as a developer training to master the force at their disposal.