- Mastering PostgreSQL 9.6
- Hans Jurgen Schonig
- 239字
- 2021-07-09 19:57:14
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.
- Internet接入·網(wǎng)絡(luò)安全
- 玩轉(zhuǎn)智能機(jī)器人程小奔
- 傳感器技術(shù)應(yīng)用
- 可編程序控制器應(yīng)用實(shí)訓(xùn)(三菱機(jī)型)
- 大數(shù)據(jù)驅(qū)動(dòng)的設(shè)備健康預(yù)測及維護(hù)決策優(yōu)化
- 工業(yè)機(jī)器人維護(hù)與保養(yǎng)
- Mastering ServiceNow Scripting
- 液壓機(jī)智能故障診斷方法集成技術(shù)
- Unity Multiplayer Games
- 電腦故障排除與維護(hù)終極技巧金典
- 西門子S7-1200/1500 PLC從入門到精通
- 新一代人工智能與語音識(shí)別
- 網(wǎng)絡(luò)規(guī)劃與設(shè)計(jì)
- 網(wǎng)絡(luò)設(shè)備規(guī)劃、配置與管理大全(Cisco版)
- Hands-On Data Analysis with Scala