Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?

Question

We recently setup netdata to monitor a self-hosted PostgreSQL server.

Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?

What we did

• PostgreSQL is running in v13

• We've run a full-vacuum (vacuumdb --full --all --jobs=1 --verbose --analyze --username=XXXX --password) on all logical databases and only got these errors:

  ERROR: relation "pg_temp_31.temp_lig_plo" ERROR: relation "pg_temp_31.lst_lig_plo"

• We've dropped the schema "pg_temp_31" but still had a similar issue.

• max_prepared_transactions is 0

• SELECT * FROM pg_prepared_xacts is empty on all databases

• We've restarted the DB (so all idle transactions were disconnected) and still observe this increase

Answer 1

According to your comment, this graphic seems to be about transaction ID wraparound.

Check if any of your databases have a problem with that:

SELECT datname, age(datfrozenxid) AS xact_age
FROM pg_database
ORDER BY xact_age DESC;

If that number is not much above 200 million for all databases, you don't need to worry. If the number is significantly higher, connect to that database and see which tables have a problem:

SELECT oid::regclass, age(relfrozenxid) AS xact_age
FROM pg_class
ORDER BY xact_age DESC;

If there are any numbers far above 200 million (or whatever else autovacuum_freeze_max_age is set to), either autovacuum doesn't start on that table, or it is taking too long, or something is blocking its progress.

Try processing those tables manually with

VACUUM (VERBOSE) table_name;

The output should tell you what is going on and, if possible, fix the problem.