Not really. There are good technical reasons for why most MVCC database engines intentionally avoid using transaction ids with enough bits such that they will never plausibly wraparound. More on that in a moment.

Empirically, you need at least a 64-bit transaction id to practically mitigate the possibility of wraparound; even MVCC systems with 48-bit transaction ids have wrapped around in real systems. Consequently, most databases like PostgreSQL are designed under the assumption that wraparound will happen and provide mechanisms for smoothly managing that in the hands of someone that knows what they are doing. No matter how much automatic safety you build into the database, there are performance optimization knobs that enable an incompetent or unskilled DBA to effectively disable or render ineffective the transaction wraparound management mechanisms. This is clearly explained in database documentation.

So back to why transaction id wraparound exists as a thing:

In an MVCC database, each logical record you store usually has multiple transaction ids associated with it that are stored with the record. Because most relational database records are typically quite small, the transaction id overhead in the heap row headers can be a significant percentage of the total storage cost. If you store transactional information in your indexes as well (some do, some don't) the relative space consumption is even worse.

This has an enormous performance impact. A typical row header in an OLTP database is 20-30 bytes. In cases where implementors have shaved a couple bytes off the row header size with heroic efforts, it has generated a 5-10% performance boost across the board due to the more compact representation. The difference between a 32-bit transaction id and a 64-bit transaction id is at least 8 bytes! Yes, you could use 64-bit transaction ids but at the cost of a database that is much more bloated -- and therefore slower -- than the 32-bit version. Since there are good technical solutions to transaction id wraparound, it would be silly to make everyone suffer from poor performance to keep a few poor DBAs from shooting themselves in the foot.

That is not to say you should never use a 64-bit transaction id in a database engine design -- I have, in fact -- but that you should understand the technical tradeoffs and implications.

Is there a reason that you couldn't store the transaction IDs in the row headers in a more compact form - for example using the full 64 bits for txmin, but storing txmax as a 24 bit delta from txmin?

Some Googling reveals that they've discussed 64-bit TXID since 2005, but resisted due to performance and complexity concerns.

The two biggest discussion threads w/Tom Lane seem to be:

2005 http://postgresql.nabble.com/Re-PATCHES-lastval-td2213404.ht...

2012 http://postgresql.nabble.com/PostgreSQL-64-Bit-XIDs-Transact...

Oops, got that first link wrong, sorry.

2005 http://postgresql.nabble.com/A-couple-of-patches-for-Postgre...

From their article:

"Additionally we had been running with far too much delay in vacuuming which meant lower load on the system, but more idle time in maintenance."

So, vacuum wasn't given the resources to keep up with their load, and it's not clear if they were supplementing with manual vacuums during quiet times. Nor was it clear when they started reacting, as PostgreSQL (also outlined in their documentation link), will start squawking about wraparound well before it shuts down, or the monitoring they had in place for whether or not autovacuum/vacuum was keeping up with the workload, or the number of locks their application was taking (locks can block autovacuum).

Adjusting the autovacuum settings by table will give you finer control over the postgresql.conf parameters to better match the workload for specific tables, as well.

Partitioning would have also helped make the actively written part of the table smaller, and older data could be vacuumed with VACUUM FREEZE, or deleted later. There are extensions to help make that easier.

https://github.com/keithf4/pg_partman

Josh Berkus of PgExperts has some great advice here:

https://dba.stackexchange.com/questions/21068/aggressive-aut...

So does Jim Nasby, here:

http://bluetreble.com/2014/10/postgres-mvcc-and-a-look-at-va...

Regarding monitoring, check_postgres.pl would give them an idea that their vacuuming settings needed adjustment sooner, or their application locking needed to be adjusted to not block autovacuum.

https://bucardo.org/check_postgres/check_postgres.pl.html#tx...

https://bucardo.org/check_postgres/check_postgres.pl.html#la...

https://bucardo.org/check_postgres/check_postgres.pl.html#lo...

It's easy to mention things after the fact, though, and it's good they got things up and running again for their customers.

   PostgreSQL ... will start squawking about wraparound
   ...
   or the monitoring they had in place

If only there was a tool or service they could use that could monitor log files for signs of problems. (I couldn't resist being a little snarky, the situation seems so perfect.)

All kidding aside, kudos to Sentry for being so candid publicly about their problems. We've seen so many companies avoid providing any technical information. About the best we'll hear in some of these is that the problem was "not terrorism related".

David from Sentry here.

We actually knew about the problem with delay and had been working to improve it. We were a couple days away from failing over to the new hardware (safely) and unfortunately we didn't have any early warnings in the logs. I haven't yet looked at why.

You need to monitor how close you are to a wraparound. https://bucardo.org/check_postgres/check_postgres.pl.html#tx... is one way you could do that.

Can you share what you had for your autovacuum_vacuum_cost_delay, and your approximate rate of transactions?

We had at it 50ms on the previous setup, though I wish we I knew why that value was used. Likely it was a default with the Chef cookbook we forked off of, or we read something that convinced us at the time it was a good idea.

Happens. Glad you're back up and running, and best of luck with your new setup. =)

To an extent, you wouldn't want the transaction ID to be any bigger either because of the clog - which postgres needs to determine row visibility.

That's the 'clog' (commit log), not the xlog (WAL, for durability).

Bah, you're right, I've edited my post. You'd think after working with PostgreSQL for years I'd have my terminology straight.

Exactly my thought, I wonder if anyone can shed some light on this matter.

Shame you're being downvoted. 2 billion is not that big a number and I found it surprising since other databases are using GUID's and UUID's and similar to deal with that problem. There was an unfortunate lack of explanation for that in the article.