Postgresql – Why RDS PostgreSQL `MaximumUsedTransactionIDs` not decreasing even after data base seems correctly `VACUUM FREEZE`’d

amazon-rdspostgresql

My MaximumUsedTransactionIDs seems to keep growing indefinitely, but I have
these evidences hinting me it maybe should not be happening that way:

$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

my_main_data_base=> SELECT datname FROM pg_database;
     datname
-------------------
 template0
 rdsadmin
 template1
 postgres
 my_main_data_base
 another_data_base
(6 rows)

my_main_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC 
LIMIT 5;
VACUUM
       relname        | xid_age
----------------------+---------
 pg_toast_1150794     |   63707
 a_user_table         |   63707
 another_user_table   |   63707
 pg_toast_1223948     |   63707
 pg_toast_1223998     |   63707
(5 rows)

my_main_data_base=> \q
$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
another_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.5.8, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

another_data_base=> VACUUM FREEZE; SELECT relname, age(relfrozenxid) AS xid_ag
e FROM pg_class JOIN pg_stat_all_tables USING (relname) ORDER BY xid_age DESC 
LIMIT 5;
VACUUM
      relname       | xid_age
--------------------+---------
 pg_toast_2964      |   12350
 pg_toast_3592      |   12350
 pg_toast_2396      |   12350
 pg_db_role_setting |   12350
 pg_authid          |   12350
(5 rows)

another_data_base=> \q
$ _

$MY_USER_LAND_RDS_SUPER_USER is what AWS RDS provides me as a superuser.
It is a super user from the RDS point of view, but it is not a superuser
from the PostgreSQL point of view.


I would expect now to have MaximumUsedTransactionIDs decreased, but it does
not. It is still at about ~80.000.000.

I have planned that after it reaches 100.000.000 I'll fork a development
version of the RDS instance where I'll make it so AWS boots the autovacuum
process at it.

Questions

  1. What is happening with MaximumUsedTransactionIDs? Why it does not shrink?

  2. Will autovacuum take care of MaximumUsedTransactionIDs once booted?

  3. Is autovacuum irrelevant in my scenario because I run manually unqualified
    VACUUM FREEZEs from time to time on all my user land databases?

  4. Should I just ignore MaximumUsedTransactionIDs?

  5. Can I somehow connect to database rdsadmin to SELECT age(relfrozenxid)
    there?

EDIT, fulfilling your first request, adding…

$ PGPASSWORD=$MY_USER_LAND_RDS_SUPER_USER_PASSWORD psql -h $MY_RDS_INSTANCE -d
my_main_data_base -U $MY_USER_LAND_RDS_SUPER_USER
psql (9.6.6, server 9.5.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 
256, compression: off)
Type "help" for help.

postgresql=> SELECT datname, datfrozenxid FROM pg_database;
     datname       | datfrozenxid
-------------------+--------------
 template0         |         1822
 rdsadmin          |         1822
 template1         |         1822
 postgres          |         1822
 my_main_data_base |     76628948
 another_data_base |     76681257
(6 rows)

postgresql=> \q
$ _

Best Answer

Activate autovacuum in the parameter group and when it hits the configured level (min 100.000.000) it'll start and MaximumUsedTransactionIDs will go down drastically.