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 some time ago, then that transaction should not progress further. In PostgreSQL 9.6, we can achieve this by configuring the old_snapshot_threshold parameter.
How to do it...
To demonstration this feature, let us execute the following query and then...
