官术网_书友最值得收藏!

Making use of snapshot too old

VACUUM is doing a good job and it will reclaim free space as needed. But 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 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 PostgreSQL 9.6 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. But 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.conf:

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 it cannot be set inside a session. By limiting the size of a transaction, the risk of insanely long transactions will decrease drastically.

主站蜘蛛池模板: 嫩江县| 沛县| 青川县| 通州区| 兴和县| 文山县| 山阴县| 利津县| 永新县| 界首市| 阿拉善左旗| 东阳市| 镇安县| 波密县| 县级市| 南溪县| 临颍县| 乌兰浩特市| 定结县| 敖汉旗| 屏东市| 黄冈市| 阜新| 沙洋县| 宣武区| 咸宁市| 瑞丽市| 苏尼特右旗| 白玉县| 新余市| 饶阳县| 阿克陶县| 常熟市| 平湖市| 虎林市| 张家界市| 灵武市| 商都县| 西青区| 石城县| 宝应县|