Running a PostgreSQL 11 instance with a table of more than two billion rows.

I get the following output when I query for the remaining transactions before a transaction identifier wraparound happens:

Query source:

   oid::regclass::text AS table,
   age(relfrozenxid) AS xid_age, 
   mxid_age(relminmxid) AS mxid_age, 
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid), 
(SELECT setting::int
        FROM    pg_settings
        WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)  
) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;


-[ RECORD 1 ]---------------+------------------------------
table                       | tablename 
xid_age                     | 2146483650
mxid_age                    | 0
tx_before_wraparound_vacuum | -1946483650
size                        | 1583 GB
last_autovacuum             | 2019-08-18 05:03:56.64232+00

Why does it return a negative number for tx_before_wraparound_vacuum?

Does this mean that there have been 1946483650 transactions more than allowed since the last wraparound protection was run?


1 Answer 1


That just means that your query is bogus.

age(xid) compares the age to the current transaction ID, so it is useless to call it with a multixact ID.

Moreover, running that query will return nonsense for anything except for regular tables (relkind = 'r').

You could try something like

SELECT cc.next_multixact_id::text::bigint - t.relminmxid::text::bigint
FROM pg_class AS t
   CROSS JOIN pg_control_checkpoint() AS cc
WHERE t.relkind = 'r';

to see how old the oldest multixact is.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.