🐘

PostgreSQLのテーブル肥大化を引き起こす4つの原因とその対処方法

2026/01/20に公開

2026/05/24

PostgreSQL

tech

PostgreSQLのテーブル肥大化を引き起こす4つの原因とその対処方法

テーブルの肥大化とは？

PostgreSQLにおけるテーブルの肥大化とは、UPDATEやDELETEによって発生した「デッドタプル」がVACUUMで回収されずに残り続け、データファイルが不必要に膨らんでしまう現象を指します。

VACUUMがデッドタプルを回収するには、そのタプルが「実行中のどのトランザクションからも参照される可能性がない」と保証されなければなりません。何らかの理由で古いトランザクションが残り続けると、VACUUMのゴミ掃除はそこで止まってしまいます。

テーブル肥大化につながる以下の4つの原因について、どのような現象か、どのように原因を特定するか、どのように対処するかを説明します。

ロングトランザクションの滞留
未コミットの準備済みトランザクション
hot_standby_feedback有効時のスタンバイでの参照クエリ
論理レプリケーションの遅延

調査環境

PostgreSQL 19dev (34740b90bc123d645a3a71231b765b778bdcf049)

ロングトランザクションの滞留

これが一番馴染み深い原因ではないでしょうか。ActiveでもIdleでも、ロングトランザクションがあるとその開始以降にUPDATEやDELETEで発生したデッドタプルはVACUUMで回収できません。ロングトランザクションが更新前のタプルを読む可能性があるためです。

事前準備

Terminal 1でトランザクションを開始し、トランザクションIDを払い出しておきます。

Terminal 1

=# BEGIN;
BEGIN
=*# SELECT txid_current();
 txid_current 
--------------
          782
(1 row)

別のTerminal 2でデータを削除し、VACUUMを実行します。

Terminal 2

=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 782, which was 2 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 15 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 0 removed, 100 remain, 100 are dead but not yet removableというログから、デッドタプルが回収されずに残っていることがわかります。

原因の特定

removable cutoff: 782, which was 2 XIDs old when operation endedというログからトランザクションID782が怪しいことがわかるので、pg_stat_activityビューを確認します。

Terminal 2

=# SELECT * FROM pg_stat_activity WHERE backend_xid = 782;
-[ RECORD 1 ]----+------------------------------
datid            | 5
datname          | postgres
pid              | 94076
leader_pid       | [NULL]
usesysid         | 10
usename          | shinya
application_name | psql
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 13:41:30.049678+09
xact_start       | 2026-01-19 13:54:38.856466+09
query_start      | 2026-01-19 13:54:43.501664+09
state_change     | 2026-01-19 13:54:43.50271+09
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 782
backend_xmin     | [NULL]
query_id         | [NULL]
query            | SELECT txid_current();
backend_type     | client backend

PID94076のバックエンドプロセスがidle in transactionであるため、VACUUMがデッドタプルを回収できなかったとわかりました。

対処方法

PID94076のバックエンドプロセスを終了させます。

Terminal 2

=# SELECT pg_terminate_backend(94076);
 pg_terminate_backend 
----------------------
 t
(1 row)

=# VACUUM (VERBOSE) t;
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 792, which was 1 XIDs old when operation ended
new relfrozenxid: 792, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 7.796 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 100 removed, 0 remain, 0 are dead but not yet removableというログから、デッドタプルがすべて回収されたことがわかります。

未コミットの準備済みトランザクション

アプリケーションや分散データベースミドルウェアが2PC（Two-Phase Commit）を利用している場合に原因となり得ます。未コミットの準備済みトランザクションは、セッションが切断されてもデータベース内に残り続け、VACUUMによるデッドタプル回収を妨げます。

事前準備

準備済みトランザクションを使用するために、max_prepared_transactionsを1以上に設定し、PostgreSQLを再起動します。

postgresql.conf

max_prepared_transactions = 10

トランザクションを開始し、2PC用に準備します。

=# BEGIN;
BEGIN
=*# PREPARE TRANSACTION 'foobar';
PREPARE TRANSACTION

データを削除し、VACUUMを実行します。

=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 796, which was 2 XIDs old when operation ended
new relfrozenxid: 793, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 14.974 MB/s, avg write rate: 14.974 MB/s
buffer usage: 13 hits, 4 reads, 4 dirtied
WAL usage: 4 records, 4 full page images, 25678 bytes, 25412 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 0 removed, 100 remain, 100 are dead but not yet removableというログから、デッドタプルが回収されずに残っていることがわかります。

原因の特定

removable cutoff: 796, which was 2 XIDs old when operation endedというログからトランザクションID796が怪しいことがわかるので、pg_prepared_xactsビューを確認します。

=# SELECT * FROM pg_prepared_xacts;
 transaction |  gid   |           prepared            | owner  | database 
-------------+--------+-------------------------------+--------+----------
         796 | foobar | 2026-01-19 14:49:30.089288+09 | shinya | postgres
(1 row)

グローバルトランザクションIDがfoobarの準備済みトランザクションが原因で、VACUUMがデッドタプルを回収できなかったとわかりました。

対処方法

グローバルトランザクションIDがfoobarの準備済みトランザクションを、COMMIT PREPAREDでコミットするか、ROLLBACK PREPAREDでロールバックします。

=# COMMIT PREPARED 'foobar';
COMMIT PREPARED
=# VACUUM (VERBOSE) t;
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 798, which was 1 XIDs old when operation ended
new relfrozenxid: 798, which is 5 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 9.778 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 100 removed, 0 remain, 0 are dead but not yet removableというログから、デッドタプルがすべて回収されたことがわかります。

`hot_standby_feedback`有効時のスタンバイでの参照クエリ

ストリーミングレプリケーション構成でhot_standby_feedbackを有効化している場合、スタンバイでの参照クエリがVACUUMによるデッドタプル回収を妨げ続けます。そもそもhot_standby_feedbackは、VACUUMによるデッドタプル削除を防ぎ、スタンバイのクエリとのコンフリクトを回避するためのパラメータだからです。

hot_standby_feedbackがスタンバイから報告したxminの記録先は2通りあります。レプリケーションスロットを使わなければ、xminはプライマリ側のwalsenderプロセス自身に記録されます。レプリケーションスロットを使う場合は、xminはレプリケーションスロットに記録されます。前者は接続が切れればwalsenderの終了とともに消えますが、後者はスロットが永続オブジェクトのため残り続けます。この違いが、後述の2ケースの分かれ目になります。

レプリケーションスロットを使わない場合

事前準備

ストリーミングレプリケーション環境を構築し、スタンバイでhot_standby_feedbackを有効化し、wal_receiver_status_intervalを小さくして、スタンバイからプライマリへの報告頻度を高めます。

postgresql.conf（スタンバイ）

hot_standby_feedback = on
wal_receiver_status_interval = 1s

スタンバイでトランザクションを開始し、クエリを実行します。

スタンバイ

=# BEGIN;
BEGIN
=*# SELECT pg_sleep(300);
 pg_sleep 
----------
 
(1 row)

プライマリでデータを削除し、VACUUMを実行します。

プライマリ

=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 831, which was 1 XIDs old when operation ended
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
buffer usage: 12 hits, 0 reads, 0 dirtied
WAL usage: 0 records, 0 full page images, 0 bytes, 0 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 0 removed, 100 remain, 100 are dead but not yet removableというログから、デッドタプルが回収されずに残っていることがわかります。

原因の特定

removable cutoff: 831, which was 1 XIDs old when operation endedというログからトランザクションID831が怪しいことがわかるので、pg_stat_replicationビューを確認します。

プライマリ

=# SELECT * FROM pg_stat_replication WHERE backend_xmin = 831;
-[ RECORD 1 ]----+------------------------------
pid              | 171632
usesysid         | 10
usename          | shinya
application_name | walreceiver
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 16:32:24.33927+09
backend_xmin     | 831
state            | streaming
sent_lsn         | 0/03081178
write_lsn        | 0/03081178
flush_lsn        | 0/03081178
replay_lsn       | 0/03081178
write_lag        | [NULL]
flush_lag        | [NULL]
replay_lag       | [NULL]
sync_priority    | 0
sync_state       | async
reply_time       | 2026-01-19 17:29:08.063083+09

確かにトランザクションID831がhot_standby_feedbackにより報告されていることがわかりました。

次に、スタンバイでpg_stat_activityビューを確認します。

スタンバイ

=# SELECT * FROM pg_stat_activity WHERE backend_xmin = 831;
-[ RECORD 1 ]----+------------------------------
datid            | 5
datname          | postgres
pid              | 197146
leader_pid       | [NULL]
usesysid         | 10
usename          | shinya
application_name | psql
client_addr      | [NULL]
client_hostname  | [NULL]
client_port      | -1
backend_start    | 2026-01-19 17:25:19.931946+09
xact_start       | 2026-01-19 17:28:33.640921+09
query_start      | 2026-01-19 17:28:36.617061+09
state_change     | 2026-01-19 17:28:36.617067+09
wait_event_type  | Timeout
wait_event       | PgSleep
state            | active
backend_xid      | [NULL]
backend_xmin     | 831
query_id         | [NULL]
query            | SELECT pg_sleep(300);
backend_type     | client backend

スタンバイのPID197146のバックエンドプロセスが実行するSELECT pg_sleep(300)というクエリが原因だとわかりました。

対処方法

スタンバイのバックエンドプロセス（PID197146）が実行するクエリを終了させます。

スタンバイ

=# SELECT pg_cancel_backend(197146);
 pg_cancel_backend 
-------------------
 t
(1 row)

プライマリ

=# VACUUM (VERBOSE) t;
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 792, which was 1 XIDs old when operation ended
new relfrozenxid: 792, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 7.796 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 8 records, 6 full page images, 34103 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 100 removed, 0 remain, 0 are dead but not yet removableというログから、デッドタプルがすべて回収されたことがわかります。

レプリケーションスロットを使う場合

レプリケーションスロットを使っている場合、hot_standby_feedbackが報告したxminの記録先は永続オブジェクトであるレプリケーションスロットに移ります。レプリケーションスロットはスタンバイが停止・撤去されても残るため、active = f（非アクティブ）のままxminを握り続け、VACUUMを恒久的に阻害します。レプリケーションスロットを使わない場合が接続断とともに自己回復するのに対し、こちらは明示的に対処しない限り解消しません。

事前準備

前項と同じhot_standby_feedback構成に加えて、プライマリでレプリケーションスロットを作成します。

プライマリ

=# SELECT pg_create_physical_replication_slot('standby_slot');
 pg_create_physical_replication_slot 
-------------------------------------
 (standby_slot,)
(1 row)

スタンバイがこのレプリケーションスロットを使うように設定します。

postgresql.conf（スタンバイ）

primary_slot_name = 'standby_slot'
hot_standby_feedback = on
wal_receiver_status_interval = 1s

前項と同様にスタンバイで長めのトランザクションを実行してxminを報告させます。レプリケーションスロット使用時は、これがpg_replication_slotsビューのxmin列に記録されます。

スタンバイ

=# BEGIN;
=*# SELECT pg_sleep(300);

ここがポイントで、この状態のままスタンバイを停止します。

スタンバイ

$ pg_ctl stop

スタンバイが消えてもレプリケーションスロットは残り、最後に報告されたxminを握ったままになります。プライマリでデータを削除し、VACUUMを実行します。

プライマリ

=# DELETE FROM t;
DELETE 100
=# VACUUM (VERBOSE) t;
pages: 0 removed, 1 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 100 remain, 100 are dead but not yet removable
removable cutoff: 707, which was 1 XIDs old when operation ended
new relfrozenxid: 705, which is 1 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 45.687 MB/s
buffer usage: 14 hits, 0 reads, 3 dirtied
WAL usage: 4 records, 3 full page images, 24981 bytes, 24576 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 0 removed, 100 remain, 100 are dead but not yet removableというログから、デッドタプルが回収されずに残っていることがわかります。

原因の特定

レプリケーションスロットを使わない場合はpg_stat_replicationのbackend_xminから犯人を特定できました。しかし、いまはスタンバイからの接続が無いため、pg_stat_activityやpg_stat_replicationを見ても該当するbackend_xminは見つかりません。これがレプリケーションスロットを使う場合の見落としやすい点です。残っているレプリケーションスロットのxminをpg_replication_slotsで確認します。

プライマリ

=# SELECT * FROM pg_replication_slots;
-[ RECORD 1 ]--------+------------------------------
slot_name            | standby_slot
plugin               | [NULL]
slot_type            | physical
datoid               | [NULL]
database             | [NULL]
temporary            | f
active               | f
active_pid           | [NULL]
xmin                 | 707
catalog_xmin         | [NULL]
restart_lsn          | 0/0306E2F8
confirmed_flush_lsn  | [NULL]
wal_status           | reserved
safe_wal_size        | [NULL]
two_phase            | f
two_phase_at         | [NULL]
inactive_since       | 2026-01-19 17:30:09.279242+09
conflicting          | [NULL]
invalidation_reason  | [NULL]
failover             | f
synced               | f
slotsync_skip_reason | [NULL]

着目点は次のとおりです。

active = f: いま誰もこのスロットを使っていない
xmin = 707: 物理レプリケーションスロットが握る最古のxmin
inactive_since: いつから未使用かの目安

なお、レプリケーションスロットにはWALを保持し続ける副作用もあります。スロットはrestart_lsn以降のWALをキープするため、スタンバイ不在のまま放置するとpg_walが増え続け、最終的にディスクを圧迫します。

対処方法

この物理レプリケーションスロットは使用されていないので削除します。

プライマリ

=# SELECT pg_drop_replication_slot('standby_slot');
 pg_drop_replication_slot 
--------------------------
 
(1 row)

=# VACUUM (VERBOSE) t;
pages: 1 removed, 0 remain, 1 scanned (100.00% of total), 0 eagerly scanned
tuples: 100 removed, 0 remain, 0 are dead but not yet removable
removable cutoff: 708, which was 1 XIDs old when operation ended
new relfrozenxid: 708, which is 3 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 11.629 MB/s
buffer usage: 20 hits, 0 reads, 6 dirtied
WAL usage: 7 records, 6 full page images, 34052 bytes, 33628 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 100 removed, 0 remain, 0 are dead but not yet removableというログから、デッドタプルがすべて回収されたことがわかります。

論理レプリケーションの遅延

論理レプリケーションでは、WALを読み取りデコードしたうえでサブスクライバーに送信します。そのデコード処理では、対象のWALレコードが生成された「過去の時点」のシステムカタログ状態が必要になることがポイントです。

例えば、過去にカラム削除が行われたテーブルに対するWALをデコードする場合、現在のテーブル定義ではなく「カラム削除前」の定義情報を参照しなければ正しくデコードできません。

そのため、論理レプリケーションスロットがそのWALを処理し終えるまで、その時点で必要だったシステムカタログのデッドタプルを保持し続けなければなりません。

事前準備

論理レプリケーションスロットを作成します。

=# SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding');
 pg_create_logical_replication_slot 
------------------------------------
 (test_slot,0/017BBC00)
(1 row)

既存のテーブルを削除し、pg_classカタログに対してVACUUMを実行します。

=# DROP TABLE t;
DROP TABLE
=# VACUUM (VERBOSE) pg_class;
pages: 0 removed, 14 remain, 14 scanned (100.00% of total), 0 eagerly scanned
tuples: 0 removed, 418 remain, 1 are dead but not yet removable
removable cutoff: 780, which was 1 XIDs old when operation ended
new relfrozenxid: 779, which is 19 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 0.000 MB/s, avg write rate: 16.421 MB/s
buffer usage: 45 hits, 0 reads, 4 dirtied
WAL usage: 4 records, 4 full page images, 32658 bytes, 32392 full page image bytes, 0 buffers full
memory usage: dead item storage 0.02 MB accumulated across 0 resets (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 0 removed, 418 remain, 1 are dead but not yet removableというログから、デッドタプルが回収されずに残っていることがわかります。

原因の特定

removable cutoff: 780, which was 1 XIDs old when operation endedというログからトランザクションID780が怪しいことがわかるので、pg_replication_slotsビューを確認します。

=# SELECT * FROM pg_replication_slots WHERE catalog_xmin = 780;
-[ RECORD 1 ]--------+------------------------------
slot_name            | test_slot
plugin               | test_decoding
slot_type            | logical
datoid               | 5
database             | postgres
temporary            | f
active               | f
active_pid           | [NULL]
xmin                 | [NULL]
catalog_xmin         | 780
restart_lsn          | 0/017BBBC8
confirmed_flush_lsn  | 0/017BBC00
wal_status           | reserved
safe_wal_size        | [NULL]
two_phase            | f
two_phase_at         | [NULL]
inactive_since       | 2026-01-19 20:09:07.152103+09
conflicting          | f
invalidation_reason  | [NULL]
failover             | f
synced               | f
slotsync_skip_reason | [NULL]

この論理レプリケーションスロットが必要とする、システムカタログに影響する最古のトランザクションIDが780であることがわかりました。

対処方法

この論理レプリケーションスロットは使用されていないので削除します。

=# SELECT pg_drop_replication_slot('test_slot');
 pg_drop_replication_slot 
--------------------------
 
(1 row)

=# VACUUM (VERBOSE) pg_class;
pages: 0 removed, 14 remain, 14 scanned (100.00% of total), 0 eagerly scanned
tuples: 1 removed, 417 remain, 0 are dead but not yet removable
removable cutoff: 781, which was 0 XIDs old when operation ended
new relfrozenxid: 781, which is 2 XIDs ahead of previous value
frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
visibility map: 1 pages set all-visible, 1 pages set all-frozen (0 were all-visible)
index scan needed: 1 pages from table (7.14% of total) had 1 dead item identifiers removed
index "pg_class_oid_index": pages: 4 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "pg_class_relname_nsp_index": pages: 6 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "pg_class_tblspc_relfilenode_index": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 7.032 MB/s, avg write rate: 63.288 MB/s
buffer usage: 62 hits, 1 reads, 9 dirtied
WAL usage: 9 records, 9 full page images, 55417 bytes, 54864 full page image bytes, 0 buffers full
memory usage: dead item storage 0.03 MB accumulated across 1 reset (limit 64.00 MB each)
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM

tuples: 1 removed, 417 remain, 0 are dead but not yet removableというログからデッドタプルがすべて回収されたことがわかります。

コミュニティでの議論状況

以上で原因とその対処について説明しました。

意図的にこのような状況を作り出し対処したので、そこまで難しい問題には見えなかったかもしれませんが、実際に解決するにはPostgreSQL運用の知識が相当必要ですし、複数のビューを確認する必要があるので結構大変です。また、テーブルが肥大化しているだけにとどまらず、トランザクションID周回問題の危機が迫っているような場合に、冷静にこの手順を実行するのはオペミスもありそうですし、できれば避けたいところです。

現在、PostgreSQLコミュニティでデッドタプルが回収できなかった理由をVACUUMのログに出力する機能を提案中です。まだコミットされそうにはありませんが、コミットされたらこのブログ記事に追記しようと思っています。

過去にはデッドタプルが回収できなかった理由をビューに表示する機能についての議論もありましたが、現状はまだ実装されていないので、こちらも対応していきたいと思っています。

まとめ

PostgreSQLにおけるテーブル肥大化の原因と対処法について説明しました。

余談ですが、PostgreSQLやAIに関する話をPodcastで話しているので、ご興味のある方は聞いてみてください！

GitHubで編集を提案

PostgreSQLのテーブル肥大化を引き起こす4つの原因とその対処方法

テーブルの肥大化とは？

調査環境

ロングトランザクションの滞留

事前準備

原因の特定

対処方法

未コミットの準備済みトランザクション

事前準備

原因の特定

対処方法

hot_standby_feedback有効時のスタンバイでの参照クエリ

レプリケーションスロットを使わない場合

事前準備

原因の特定

対処方法

レプリケーションスロットを使う場合

事前準備

原因の特定

対処方法

論理レプリケーションの遅延

事前準備

原因の特定

対処方法

コミュニティでの議論状況

まとめ

Discussion

`hot_standby_feedback`有効時のスタンバイでの参照クエリ