Limiting transactions by making use of snapshot too old

VACUUM does a good job and it will reclaim free space as needed. However, when can VACUUM actually clean out rows and turn them into free space? The rule is this: if a row cannot be seen by anybody anymore, it can be reclaimed. In reality, this means that everything that is no longer seen, even by the oldest active transaction, can be considered to be really dead.

This also implies that really long transactions can postpone cleanup for quite some time. The logical consequence is table bloat. Tables will grow beyond proportion, and performance will tend to go downhill. Fortunately, starting with PostgreSQL 9.6, the database has a nice feature that allows the administrator to intelligently limit the duration of a transaction. Oracle administrators will be familiar with the snapshot too old error. Since PostgreSQL 9.6, this error message is also available. However, it is more of a feature than an unintended side effect of bad configuration (which it actually is in Oracle).

To limit the lifetime of snapshots, you can make use of a setting in PostgreSQL's config file, postgresql.conf, which has all of the configuration parameters that are needed for this:

old_snapshot_threshold = -1 
         # 1min-60d; -1 disables; 0 is immediate 

If this variable is set, transactions will fail after a certain amount of time. Note that this setting is on an instance level and that it cannot be set inside a session. By limiting the age of a transaction, the risk of insanely long transactions will decrease drastically.