Josh Berkus

If you're looking for a good series of blog posts about xid wraparound in Postgres check out these posts by Josh Berkus:

http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html

http://www.databasesoup.com/2012/10/freezing-your-tuples-off-part-2.html

http://www.databasesoup.com/2012/12/freezing-your-tuples-off-part-3.html

And this more recent one by Robert Haas:

https://rhaas.blogspot.com/2018/01/the-state-of-vacuum.html

As Josh states at the end of the third post the current best practices for dealing with this are really workarounds and as Robert states it requires monitoring and management. Postgres is an amazing piece of software and managing this is doable but IMHO this is one of Postgres' worst warts. It would be awesome if someone could donate some funding to improve this.

The very quick exclusive lock that postgres needs for things that are normally thought of as "free" like `ALTER TABLE ADD COLUMN` without a default can still cause problems on tables that are under extremely heavy contention. If you have hundreds of clients running long transactions against a table, even this fast metadata-only lock can cause an outage due to head-of-line blocking. Our team has had good luck with this lock-polling approach for migrations against very busy tables [1]. The idea is that you combine `lock_timeout` and attempt to `LOCK TABLE IN ACCESS EXCLUSIVE MODE` in a loop prior to running the DDL. Note that this is only safe for migrations that are actually instantaneous once the lock is acquired.

[1] http://www.databasesoup.com/2015/08/lock-polling-script-for-alter-table.html

Sure, there's CASCADE, but as you pointed out it has several drawbacks.

That's why ideally natural keys should be immutable. That has many advantages over surrogate keys.

There's a follow up post (posts) to this presentation: http://www.databasesoup.com/2015/03/primary-keyvil-reprised.html where many problems caused by the abuse of surrogate keys are illustrated.

But the main point is simple: surrogate keys (abuse) is an easy way to data corruption. Not in the sense of durability as in ACID, but in the sense of humans (manually or by bugs in the software) duplicating information that is not enforced to be unique thanks to using non-sense surrogate keys as the uniqueness criteria.

Running more aggressive vacuums that will clean up "old" tuples/tables is good for off-peak times. See Josh Berkus' short three part blog series on postgres' vacuum/freeze:
http://www.databasesoup.com/2012/09/freezing-your-tuples-off-part-1.html

and his python script for doing this:
https://github.com/pgexperts/flexible-freeze

Also, note that long running transactions can prevent cleanup of tuples. Look for old xact_start values of non-idle queries in pg_stat_activity (particularly "idle in transaction" connections) and old entries in pg_prepared_xacts.

Great article. We've just moved a large portion of our async jobs over from Resque to Que because of the huge benefits you get from transactional enqueuing and processing. Performance seems good so far, and if it really becomes an issue, running two queuing systems side by side (one transactional, one high-throughput) seems viable.

We're super cautious about long-running transactions anyway, as they cause a load of other issues (e.g. http://www.databasesoup.com/2013/11/alter-table-and-downtime-part-ii.html - full blog post coming soon!)

Largely because the previous version numbering was sometimes confusing for the opposite reason: Postgres "major" releases were sometimes interpreted as point releases. For example, 9.1 to 9.2 was major version jump. While that was understood by those who already understood the versioning scheme, it was often confusing to those outside the community.

Josh Berkus discusses this here:

http://www.databasesoup.com/2016/05/changing-postgresql-version-numbering.html