Re: Avoid Wraparound Failures - Mailing list pgsql-admin

I have read all of you carefully, and I have read the simulation of the failure and subsequent recovery. Very interesting.

With all the information, it seems clear that autovacuum must be well adjusted for this process to be able to maintenance the tables in good condition and include the execution of vacuum as maintenance routines.

I have done a test and... to my amazement, vacuum does not reduce the age of the relfrozenxid.

The test is very simple.

Create new database and connect to it:

postgres=# create database test_xid;
CREATE DATABASE
postgres=#
postgres=# \c test_xid
You are now connected to database "test_xid" as user "todopostgresql".

Check the current transaction:

test_xid=# select txid_current();
 txid_current
--------------
          983
(1 row)

Create a table and insert 10 million tuples:

test_xid=# create table t1 (id serial);
CREATE TABLE
test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000

The frozen transaction on table t1 is 984 and has a distance of 3 from txid, which is currently 987. So far so good.

test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |   3

test_xid=# select txid_current();
 txid_current
--------------
          987

"Dirty" table t1:

test_xid=# update t1 set id = id + 1;
UPDATE 10000000
test_xid=#
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |   5
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
          989
(1 row)

If I do a vacuum now, the 10 million "dirty" tuples will be marked as reusable.

test_xid=# vacuum t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |   7
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
          991
(1 row)

If I now insert another 10 million tuples they should occupy the space of already past ¿frozen by vacuum? transactions:

test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |   9
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
          993
(1 row)

But no. I don't see the improvement. Try again with vacuum analyze:

test_xid=# update t1 set id = id + 1;
UPDATE 20000000
test_xid=#
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |  12
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
          996
(1 row)

test_xid=# vacuum ANALYZE t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |  14
(1 row)

test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |  15
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
          999
(1 row)

Either. Do a vacuum of the entire database:

test_xid=# vacuum ;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
          984 |  17

Neither. Try vacuum freeze:

test_xid=# vacuum FREEZE t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where relname = 't1';
 relfrozenxid | age
--------------+-----
         1001 |   0
(1 row)

test_xid=# select txid_current();
 txid_current
--------------
         1001
(1 row)

Yes now. Sure, because age is the difference between txid and relfrozenxid and "vacuum freexe" freezes all transactions before txid.

But we have agreed that vacuum freeze has disadvantages and that vacuum should be enough.

Why does vacuum seem to do nothing? Is my test wrong?

I need to understand.. and be able to continue with my life XD