Friday, September 28, 2012

Freezing Your Tuples Off, Part 1

Given that it's currently around 50F, damp and foggy in San Francisco (so much for Indian Summer), my thoughts turn to freezing.   Specifically, freezing transaction IDs.  There's three settings associated with this behavior in postgresql.conf, which I'll be discussing below:
  •     autovacuum_freeze_max_age
  •     vacuum_freeze_min_age
  •     vacuum_freeze_table_age
At the least, you'll need to understand freezing and what these options do.  Many users will also want to change them away from the default values.

The Need To Freeze

Every write transaction or standalone statement in PostgreSQL gets assigned a unique transaction ID (or XID).  This XID determines the precedence visibility, determining which other concurrent users can see row changes associated with the transaction.  Read-only transactions do not get assigned an XID.  These XIDs get stamped on each row in storage, in the form of xmin and xmax, denoting the minimum and maximum concurrent transactions which can "see" the row.  By putting these in the row headers, we decentralize transaction conflict resolution, which supports PostgreSQL's very high concurrency capabilities.

However, there's a problem with XIDs: they're a four-byte integer.  This means that after 2.1 billion transactions, they need to wrap around.  And by the time the XID counter wraps around, the old XIDs need to be gone from all of the old row headers.  This means removing the XID from xmin and xmax and replacing it with the special RelFrozenXID value, indicating a transaction which is so old everyone can see it.

Now, 2.1 billion transactions seems like a lot, but it's not really.  If you're doing just 100 write transactions per second (average), you'll go through that in about 10 months.  This means that we need to be constantly cleaning old data pages of old XIDs to be ready for wraparound.  And, indeed, PostgreSQL is prepared to do this, through the autovacuum facility.


The first autovacuum setting is autovacuum_freeze_max_age, which is an absolute ceiling on how long autovacuum will let you go before it kicks in and starts exhaustively vacuum freezing the old XIDs on your tables with old rows.  Hitting this threshold isn't a great thing, because it can generate lots of IO at unexpected times, and freeze autovacuums aren't cancellable (if you cancel them, they come right back).

You can see how close you are to autovacuum freeze anywhere with this query:

select max(age(datfrozenxid)) from pg_database;

However, you don't generally really care about that; what you care about is "how close am I to forced autovacuum freeze on a large table?"  Here's that:

SELECT relname, age(relfrozenxid) as xid_age,
    pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 20;

More specifically, that says "give me the top 20 tables over 1GB, sorted by the age of their oldest XID".  Results look something like this:

        relname         |  xid_age  | table_size
 postgres_log           | 199785216 | 12 GB
 statements             |   4551790 | 1271 MB
 normal_statement_times |        31 | 12 GB

Now, that above is trouble brewing.  We see that postgres_log is at an XID age of 199 million, which is awfully close to the default autovacuum_freeze_max_age of 200 million.  And having autovac decide to read and rewrite an entire 12GB table, possibly at peak usage times, can cause unexpected poor application performance.

One thing we can do is raise autovacuum_freeze_max_age.  The default, 200m, is very conservative; it's only 10% of our shut-down-the-database threshold.  On high-transaction-volume databases, I generally raise it to 1 billion, which is still only the 50% mark.

However, that's just putting off the problem.  What we should do is manually freeze the table at at time which is convenient for the application, i.e. during a low activity period or a scheduled downtime.  Then we run:

VACUUM FREEZE postgres_log;

Even better, we'd like to prevent ourselves from getting that close to the threshold in the first place.  How do we do that?  Well, there's some other tuning parameters. 

To be continued in Part 2 ...


  1. Thanks for the article. Good stuff as usual. One note, I think the parameter is autovacuum_freeze_max_age not autovacuum_max_freeze_age as listed below the query output.

  2. Thanks for this article, it was very useful to us. I wonder if you have any hints on how to set autovacuum_multixact_freeze_max_age in Postgresql 9.3? The default is double autovacuum_freeze_max_age. If I set autovacuum_freeze_max_age to 1 billion, should I set the multixact to 2 billion? That seems quite high to me.

  3. Couldn't postgres reserve a special XID that is never available for normal transactions but that indicates that any transaction can see it because it is so old? Then instead of constantly having to freeze old XIDs each time the XID is going to wrap, vacuum can just set it to the special XID and never touch it again unless something really changes.

  4. Thanks Josh for your excellent article. It helped us understand why our production environment was experiencing sporadic slowdowns. We had large tables of warehoused data that would be frozen at inopportune times. Some of these tables exceeded 20GB in size.

    We are nearing complete database transaction wraparound and I'm a bit concerned about this. We are using aggressive autovacuum settings (FREEZE at 200 million transactions) so hopefully when wraparound occurs most of the work will have been done already.