This article summarizes information I have about this problem. I know that this is not all but I did not find such a summarization anywhere therefore I place it here. Maybe it could help to someone.
- Transaction ID has 32 bits. It means that after ID 2147483648 will be transaction ID reseted.
- There are 2 special IDs: 1 and 2
- ID 1 = BootstrapXID
- ID 2 = FrozenXID
- FrozenXID is special ID
- FrozenXID exists for database (pg_database) and every table (pg_class)
- Freeze is done by “vacuum freeze” command
- Freeze makes this:
- Takes biggest transaction ID (on table, on database), saves it into system catalog table (for table in pg_class, for database in pg_database)
- All XID are then replaced by “2”
- In overrun of XID new XIDs starts with 3
- Database has these parameters for vacuum and autovacuum:
| name | setting | unit | short_desc |
| autovacuum | off | Starts the autovacuum subprocess. | |
| autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples. | |
| autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates or deletes prior to analyze. | |
| autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound. | |
| autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes. | |
| autovacuum_naptime | 60 | s | Time to sleep between autovacuum runs. |
| autovacuum_vacuum_cost_delay | 20 | ms | Vacuum cost delay in milliseconds, for autovacuum. |
| autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum. | |
| autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. | |
| autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum. | |
| log_autovacuum_min_duration | -1 | ms | Sets the minimum execution time above which autovacuum actions will be logged. |
| vacuum_cost_delay | 0 | ms | Vacuum cost delay in milliseconds. |
| vacuum_cost_limit | 200 | Vacuum cost amount available before napping. | |
| vacuum_cost_page_dirty | 20 | Vacuum cost for a page dirtied by vacuum. | |
| vacuum_cost_page_hit | 1 | Vacuum cost for a page found in the buffer cache. | |
| vacuum_cost_page_miss | 10 | Vacuum cost for a page not found in the buffer cache. | |
| vacuum_freeze_min_age | 30000000 | Minimum age at which VACUUM should freeze a table row. | |
| vacuum_freeze_table_age | 150000000 | Age at which VACUUM should scan whole table to freeze tuples. |
- If transaction ID overrun is imminent then autovacuum on table is triggered automatically even if autovacuum is switched off. This is PostgreSQL safety mechanism to prevent data loss which could be caused by this trans ID overrun.
- Basically it makes “vacuum freeze”. Which means that the biggest existing XID is stored in special column pg_class.relfrozenXID and this XID and all lower XIDs in particular table are replaced with special value “2”.XID=2 is “frozen XID”. The same happens if we run “vacuum freeze” on table manually.
- This can happen even if danger of total overrun of XID is not eminent, there are parameters in postgresql.conf for vacuum freeze on table.
- If XID overrun is eminent then all XIDs on database can be frozen the same way. In that case frozen XID for database is stored in column pg_database.datfrozenXID and all XIDs on database are replaced with value “2”. The same happens if we run “vacuum freeze” on database manually.
- After XID overrun new XID starts with value “3”. This way old XIDs are thanks to freeze always all older so data loss cannot happen.
- This automatic safety autovacuum process can be triggered unexpectedly any time which can cause problems with responses and also lock the table. It could also explain for example some highly “illogical deadlocks” – table was locked by autovacuum process.
- This mechanism was introduced in PG 7.2 and it is the same still in version 9.3.