ablog

不器用で落着きのない技術者のメモ

Aurora PostgreSQL で vacuum truncate を試す

AWS

課題

PostgreSQL 11 までは Master で vacuum （fullではない）中のテーブルに Slave でクエリすると以下のエラーが発生し、セッションが切断されることがある（参考: RDS PostgreSQL での VACUUM とリードレプリカでの SELECT のコンフリクト - ablog）
- “User query might have needed to see row versions that must be removed.
- “User was holding a relation lock for too long.

原因

VACUUM の最後にテーブルの末尾の空き領域を切り詰める際にとる排他ロック獲得時に、Slave でコンフリクト（ページレベルのロック）したクエリでエラーになるため

解決策

create table の storage オプションに vacuum_truncate オプションをつける
手動 vacuum は truncate オプションをつける（PostgreSQL 12 以降）

vacuum (truncate off) test1;

Aurora PostgreSQL は 11.6*1 でも対応している模様

Aurora PostgreSQL release 3.1.3
（中略）
Aurora PostgreSQL now supports the PostgreSQL vacuum_truncate storage parameter to manage vacuum truncation for specific tables. Set this storage parameter to false for a table to prevent the VACUUM SQL command from truncating the table's trailing empty pages.
Amazon Aurora PostgreSQL releases and engine versions - Amazon Aurora

12未満は "vacuum (truncate off) test1" は対応していないが、"create table test1 (col1 int) with (vacuum_truncate=off)" は対応している模様

# 11.6
postgres=>  select AURORA_VERSION();
 aurora_version
----------------
 3.1.3
(1 row)

postgres=> create table test1 (col1 int) with (vacuum_truncate=off);
CREATE TABLE

# 11.7
writer=> select AURORA_VERSION();
 aurora_version
----------------
 3.2.3
(1 row)

writer=> create table test1 (col1 int) with (vacuum_truncate=off);
CREATE TABLE

参考

VACUUM と CREATE TABLE に末尾の空のページを切り捨てしないオプションが追加されました。 (Takayuki Tsunakawa) (12)
このために VACUUM文にオプション TRUNCATE が追加されました。同様にテーブルのストレージオプションに vacuum_truncate とtoast.vacuum_truncate が追加されました。これらのオプションを使用すると、VACUUM処理のロックを軽減できますが、オペレーティングシステムにディスク領域を戻すことができなくなります。
PostgreSQL 12 に関する技術情報

*1:Aurora PostgreSQL release 3.1.3（PosgreSQL 11.6）