PostgreSQL High Performance Cookbook

Control bloat using transaction age

In this recipe, we will be discussing how to control the generation of dead tuples using the snapshot threshold setting.

Getting ready

In earlier versions of PostgreSQL, the old version of a tuple could be visible to the snapshot until the transaction was completed. Once the tuple was not visible to any of the active transactions, then it would be removed logically by the autovacuum process. Also, we cannot limit the age of a transaction snapshot as we can in other database management systems. If we can restrict the age of a transaction, then we can prevent generating multiple versions of the tuples by throwing the snapshot too old error. This means that, if a transaction holds a set of tuples that were modified ...