- 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.
- 大數據戰爭:人工智能時代不能不說的事
- 網上沖浪
- Managing Mission:Critical Domains and DNS
- 分布式多媒體計算機系統
- 基于ARM 32位高速嵌入式微控制器
- C語言寶典
- 高維聚類知識發現關鍵技術研究及應用
- Prometheus監控實戰
- 計算機與信息技術基礎上機指導
- Windows Server 2008 R2活動目錄內幕
- Microsoft System Center Confi guration Manager
- Chef:Powerful Infrastructure Automation
- 嵌入式操作系統原理及應用
- Excel 2010函數與公式速查手冊
- 工業機器人力覺視覺控制高級應用