PostgreSQLのMVCC(Multi-Version Concurrency Control：多版型同時実行制御)と運用に必要なこと

PostgreSQLは、更新処理を行った際に更新前のデータをデータファイル内に保存していく仕組み(MVCC：Multi-Version Concurrency Control：多版型同時実行制御)をとっています。これは、あるデータに対して複数のユーザからアクセスがあった場合に、参照処理と更新処理を同時に行えるようにするためのものです。そのため、データファイル内のデータを整備せずに運用を続けると更新によって使用されなくなったデータ(不要領域)が増えていき、実データよりファイルサイズが大きくなります。

このデータファイルサイズの肥大化によってパフォーマンスが悪化するため、不要領域を定期的に整備する必要があります。また、SQLの実行を効率的に行うために使用するデータ(統計情報)も、定期的に更新して最新にしておくことが推奨されてます。

PostgreSQLの不要領域や統計情報を整備するSQLコマンドには、主にVACUUM、ANALYZE、REINDEX、CLUSTERが存在します。テーブル名を省略した場合はデータベース内のすべてのテーブルが対象となります。

VACUUM

テーブルのインデックスを含む不要領域を回収するコマンドです。不要領域の回収とは、不要な領域を判別できるようにマークして、その領域を再利用できるようにすることです。バキュームの実行は、対象のテーブルやデータベースに対して所有者権限が必要です。

書式

VACUUM [オプション] [テーブル名[, テーブル名...]];

主なオプション

オプション	説明
FULL	不要領域の回収を行い、回収した不要領域をOS上から削除するコマンド実行中はテーブルに排他ロックをかける
ANALYZE	不要領域の回収を行い、統計情報の収集・更新を行う

vacuumdbコマンド

VACUUMコマンドはDBに接続した状態で実行しますが、OSのコマンドプロンプトからvacuumdbコマンドを実行することでも、不要領域の回収を行うことができます。
書式は以下の通りです。

書式

vacuumdb [接続オプション] [オプション] [-t テーブル名] [データベース名]

主な接続オプション

オプションの設定がない場合は環境変数が使用されますが、環境変数の設定がない場合はデフォルトの設定になります。

接続オプション	説明	環境変数	デフォルト
-U ユーザ名	接続時のデータベースユーザ名を指定します。	PGUSER	OSユーザ名
-h ホスト名またはIPアドレス	接続先のホスト名またはIPアドレスを指定します。	PGHOST	UNIXドメインによる接続
-d データベース名	接続先のデータベース名を指定します。	PGDATABASE	データベースユーザ名
-p ポート名	接続先のポート番号を指定します。デフォルトは5432です。	PGPORT	5432

主なオプション

オプション	説明
--all -a	全てのデータベースを不要領域の回収対象とする
--full -f	不要領域の回収を行い、回収した不要領域をOS上から削除するコマンド実行中はテーブルに排他ロックをかける
--analyze -z	不要領域の回収を行い、統計情報の収集・更新を行う
--analyze-only -Z	不要領域の回収はせずに、統計情報の収集・更新のみを行う

VACUUM, ANALYZEの実行状況の確認

各テーブルのVACUUM、ANALYZEの実行状況は、pg_stat_all_tablesで確認することが出来ます。

以下のSQLでカレントデータベースのテーブルのVACUUM, ANALYZE, 自動VACUUM, 自動ANALYZEの実施日時を確認することが出来ます

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;

実施例

実施例 VACUUM

VACUUM 実施前の状況を確認する

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze 
----------+-------------+-----------------+--------------+------------------
 customer |             |                 |              | 
 orders   |             |                 |              | 
 prod     |             |                 |              | 
(3 rows)

customerテーブルに10000件のデータを登録

testdb=# \copy customer from /var/tmp/customer_testdata.csv csv
COPY 10000

データ登録後の実施状況を確認する。autovacuumとautoanalyzeが実行された事が確認できました。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze        
----------+-------------+-------------------------------+--------------+-------------------------------
 customer |             | 2024-07-17 23:40:26.897729+09 |              | 2024-07-17 23:40:27.001538+09
 orders   |             |                               |              | 
 prod     |             |                               |              | 
(3 rows)

cutomerテーブルから5000件のデータを削除

testdb=# delete from customer where customer_id > 5000;
DELETE 5000

データ削除後の実施状況を確認する。autovacuumとautoanalyzeが実行された事が確認できました。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze        
----------+-------------+-------------------------------+--------------+-------------------------------
 customer |             | 2024-07-17 23:49:27.043054+09 |              | 2024-07-17 23:49:27.095428+09
 orders   |             |                               |              | 
 prod     |             |                               |              | 
(3 rows)

VACUUMを実施し、実施状況を確認

testdb=# VACUUM;
VACUUM
testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        | last_analyze |       last_autoanalyze        
----------+-------------------------------+-------------------------------+--------------+-------------------------------
 customer | 2024-07-17 23:50:43.073534+09 | 2024-07-17 23:49:27.043054+09 |              | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-17 23:50:43.068885+09 |                               |              | 
 prod     | 2024-07-17 23:50:43.069456+09 |                               |              | 
(3 rows)

VACUUM ANALYZE を実施し、実施状況を確認

testdb=# testdb=# VACUUM ANALYZE;
VACUUM
testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-17 23:53:39.479408+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-17 23:53:39.538092+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-17 23:53:39.473431+09 |                               | 2024-07-17 23:53:39.475318+09 | 
 prod     | 2024-07-17 23:53:39.475813+09 |                               | 2024-07-17 23:53:39.477332+09 | 
(3 rows)

実施例 vacuumdb コマンド

vacuumdbコマンド実施前の状況を確認する

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-17 23:53:39.479408+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-17 23:53:39.538092+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-17 23:53:39.473431+09 |                               | 2024-07-17 23:53:39.475318+09 | 
 prod     | 2024-07-17 23:53:39.475813+09 |                               | 2024-07-17 23:53:39.477332+09 | 
(3 rows)

vacuumdb コマンド実施

$ vacuumdb testdb 
vacuumdb: vacuuming database "testdb"
$

vacuumdbコマンド実施後の状況を確認する。last_vacuumの日時が更新されているのがわかります。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:21:46.166344+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-17 23:53:39.538092+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:21:46.352298+09 |                               | 2024-07-17 23:53:39.475318+09 | 
 prod     | 2024-07-18 07:21:46.372862+09 |                               | 2024-07-17 23:53:39.477332+09 | 
(3 rows)

vacuumdb --analyze を実施

$ vacuumdb testdb --analyze
vacuumdb: vacuuming database "testdb"
$

vacuumdb --analyze コマンド実施後の状況を確認する。last_vacuum, last_analyze の日時が更新されているのがわかります

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:26:49.423341+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:26:49.555199+09 | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:26:49.567775+09 | 
(3 rows)

vacuumdb --analyze-only コマンドを実施

$ vacuumdb testdb --analyze-only
vacuumdb: vacuuming database "testdb"
$

vacuumdb --analyze-only コマンド実施後の状況を確認する。last_analyze の日時だけ、更新されているのがわかります

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:30:15.330137+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:30:15.426612+09 | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:30:15.43133+09  | 
(3 rows)

ANALYZE

統計情報を収集して更新するコマンドです。統計情報と実際のデータの差異が大きくなってしまうと、SQLの実行計画を適切に作成できなくなります。

書式

ANALYZE [テーブル名[, テーブル名...]];

実施例

ANALYZE コマンド実施前の状況を確認する

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:24.348501+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:24.278999+09 | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:36:24.280179+09 | 
(3 rows)

ANALYZE 実施後、実施状況を確認する。last_analyzeの日時が更新されているの確認できます

testdb=# ANALYZE;
ANALYZE
testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:36:51.828194+09 | 
(3 rows)

REINDEX

インデックスを作り直し、インデックス内に生じた空領域を解消するコマンドです。

書式

REINDEX [オプション];

主なオプション

オプション	説明
INDEX インデックス名	指定したインデックスを作り直します。
TABLE テーブル名	指定したテーブル内の全てのインデックスを作り直します。

実施例

指定した customer_idx を作り直します

testdb=# REINDEX INDEX customer_idx;
REINDEX

CLUSTER

インデックスを使用して、テーブルのデータを物理的に並び替えるコマンドです。これにより、データの検索処理が効率化されます。

書式

CLUSTER テーブル名 [USING インデックス名];

実施例

customer_idx インデックスをクラスタに指定する

testdb=# CLUSTER customer using customer_idx;
CLUSTER

\d+ サブコマンドでcustomerテーブルを確認。customer_idx が CLUSTER になっていることがわかります

testdb=# \d+ customer;
                                            Table "public.customer"
    Column     |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
---------------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 customer_id   | integer |           |          |         | plain    |             |              | 
 customer_name | text    |           |          |         | extended |             |              | 
Indexes:
    "customer_idx" btree (customer_id) CLUSTER
Access method: heap

自動VACUUM

PostgreSQLには自動バキューム(autovacuum)という機能があります。自動バキュームは、自動的に不要領域の回収 (VACUUM) と統計情報の収集・更新 (ANALYZE) を行います。autovacuumパラメー* タで有効(on)と無効(off)を設定でき、バージョン8.3以降ではデフォルトでonになっています。autovacuumパラメータは、PostgreSQLの起動・再起動、または「postgresql.conf」の再読み込みで反映されます。自動バキュームは、大量のデータ更新があったテーブルに対して複数のプロセスで実行されます。

自動VACUUM に関連するパラメータ

設定	型	デフォルト値	内容
autovacuum	boolean	on	サーバがautovacuumランチャデーモンを実行すべきかどうかを管理します。デフォルトでは有効です。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されます。
track_counts	boolean	on	データベースの活動についての統計情報の収集を有効にします。収集される情報を自動バキュームデーモンが必要とするため、このオプションはデフォルトで有効です。スーパーユーザのみがこの設定を変更することができます。
autovacuum_max_workers	integer	3	同時に実行することができるautovacuumプロセスautovacuumランチャ以外）の最大数を指定する。このパラメータは、postgresql.confファイルにあり、サーバ起動時のみで設定可能です。
autovacuum_vacuum_threshold	integer	50	どのテーブルに対してもVACUUMを起動するために必要な、更新もしくは削除されたタプルの最小数を指定します。デフォルトは50タプルです。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されます。
autovacuum_vacuum_insert_threshold	integer	-1	あるテーブルでVACUUMを起動するきっかけとなるのに必要な挿入タプル数を設定します。デフォルトは1000タプルです。 -1が指定されると、自動VACUUMが挿入タプル数に基づいてVACUUM操作を引き起こすことはなくなります。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されます。
autovacuum_analyze_threshold	intger	50	どのテーブルに対してもANALYZEを起動するのに必要な、挿入、更新、もしくは削除されたタプルの最小数を指定します。デフォルトは50タプルです。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されます。この設定はテーブルストレージパラメータの変更により、それぞれのテーブルに対して上書きすることができます。
autovacuum_vacuum_scale_factor	floating point	0.2	VACUUMを起動するか否かを決定するときに、autovacuum_vacuum_thresholdに足し算するテーブル容量の割合を指定します。デフォルトは0.2（テーブルサイズの20%）です。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されますが、テーブルストレージパラメータの変更により、それぞれのテーブルに対して上書きすることができます。
autovacuum_vacuum_insert_scale_factor	floating point	0.2	VACUUMを起動するかどうか決める際のautovacuum_vacuum_insert_thresholdに追加するテーブルサイズの割合を指定します。デフォルトは0.2（テーブルサイズの20%）です。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されますが、テーブルストレージパラメータの変更により、それぞれのテーブルに対して上書きすることができます。
autovacuum_analyze_scale_factor	floating point	0.1	ANALYZEを起動するか否かを決定するときに、autovacuum_analyze_thresholdに足し算するテーブル容量の割合を指定します。デフォルトは0.1（テーブルサイズの10%）です。このパラメータはpostgresql.confファイル、または、サーバのコマンドラインでのみで設定されます。この設定はテーブルストレージパラメータの変更により、それぞれのテーブルに対して上書きされます。

自動VACUUMが実行される条件

自動VACUUMのうち、VACUUMが実行される条件は以下のどちらかを満たしたときとなります

挿入・更新・削除したタプルの数 > autovacuum_vacuum_threshold + テーブルのレコード件数 * autovacuum_vacuum_scale_factor
挿入したタブルの数 > autovacuum_vacuum_insert_threshold + テーブルのレコード件数 * autovacuum_vacuum_insert_scale_factor

PostgreSQLがデフォルトの設定の場合、autovacuum_vacuum_insert_threshold のデフォルト値が-1 のため、以下の挿入・更新・削除したタプルの数の条件の場合だけになります

挿入・更新・削除したタプルの数 > 50 + テーブルのレコード件数 * 0.2

ANALYZEが実行される条件は以下の条件を満たしたとき

挿入・更新・削除したタプルの数 > autovacuum_analyze_threshold + テーブルのレコード件数 * autovacuum_analyze_scale_factor

PostgreSQLがデフォルト設定の場合、以下の条件となります。

挿入・更新・削除したタプルの数 > 50 + テーブルのレコード件数 * 0.1

実施例

自動ANALYZEの確認

実施状況を確認する

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:36:51.828194+09 | 
(3 rows)

prod テーブルに100件のデータを追加

$ psql testdb -f /var/tmp/insert_prod_test.txt 
INSERT 0 1

実施状況を確認します。 prod テーブルの last_autoanalyze に日時が設定されていることがわかります。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:36:51.828194+09 | 2024-08-09 07:35:26.596749+09
(3 rows)

自動VACUUMの確認

実施状況を確認する

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 |                               | 2024-07-18 07:36:51.828194+09 | 2024-08-09 07:35:26.596749+09
(3 rows)

prodテーブルの70件のデータを更新

testdb=# update prod set price = 500 where prod_id > 30;
UPDATE 70

実施状況を確認します。 prod テーブルの last_vacuum に日時が設定されていることがわかります。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 | 2024-08-09 07:51:26.74646+09  | 2024-07-18 07:36:51.828194+09 | 2024-08-09 07:51:26.752692+09
(3 rows)

テーブル単位に自動Vacuumの設定を変更する

自動Vacuumのパラメータは、テーブル単位に変更することが出来ます。ALTER TABLE コマンドを使って、設定することが出来ます。

書式

ALTER TABLE テーブル名 SET ( パラメータ名 = パラメータ値 [, パラメータ2名 = パラメータ2値, ・・・])

パラメータを解除したい場合は、　ALTER TABLE テーブル名 RESET ... コマンドを実施すると解除できます。

書式

ALTER TABLE テーブル名 RESET ( パラメータ名　[, パラメータ2名, ・・・])

自動Vacuumをするかどうかを決めるパラメータは、autovacuum でありますが、ALTER TABLEコマンドで設定する場合は、autovacuum_enabled で行います。

実施例

prod テーブルの自動Vacuumの設定をoffにします。

testdb=# ALTER TABLE prod set ( autovacuum_enabled = off );
ALTER TABLE

\d+ メタコマンドで prod テーブルの定義を確認します。自動Vacuum設定がoffになっていることがわかります

testdb=# \d+ prod
                                            Table "public.prod"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 prod_id   | integer |           |          |         | plain    |             |              | 
 prod_name | text    |           |          |         | extended |             |              | 
 price     | numeric |           |          |         | main     |             |              | 
Access method: heap
Options: autovacuum_enabled=off

実施状況を確認します。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 | 2024-08-09 07:51:26.74646+09  | 2024-07-18 07:36:51.828194+09 | 2024-08-09 07:51:26.752692+09
(3 rows)

prodテーブルの全ての行を更新しました。

testdb=# update prod set price = 100;
UPDATE 110

実施状況を確認します。 prod テーブルの last_autovacuum が更新されていないのがわかります。

testdb=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_all_tables WHERE schemaname = 'public' ORDER BY relname;
 relname  |          last_vacuum          |        last_autovacuum        |         last_analyze          |       last_autoanalyze        
----------+-------------------------------+-------------------------------+-------------------------------+-------------------------------
 customer | 2024-07-18 07:26:49.390169+09 | 2024-07-17 23:49:27.043054+09 | 2024-07-18 07:36:51.884781+09 | 2024-07-17 23:49:27.095428+09
 orders   | 2024-07-18 07:26:49.554874+09 |                               | 2024-07-18 07:36:51.82806+09  | 
 prod     | 2024-07-18 07:26:49.567321+09 | 2024-08-09 07:51:26.74646+09  | 2024-07-18 07:36:51.828194+09 | 2024-08-09 07:51:26.752692+09
(3 rows)

prod テーブルの自動Vacuumの設定をoffにした設定を解除します

testdb=# ALTER TABLE prod RESET ( autovacuum_enabled );
ALTER TABLE

\d+ メタコマンドで prod テーブルの定義を確認します。自動Vacuum設定がoffに設定していたパラメータが解除されていることがわかります。

testdb=# \d+ prod
                                            Table "public.prod"
  Column   |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
-----------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 prod_id   | integer |           |          |         | plain    |             |              | 
 prod_name | text    |           |          |         | extended |             |              | 
 price     | numeric |           |          |         | main     |             |              | 
Access method: heap

参照情報