【Postgre】DB(スキーマ/テーブル)が最後に更新された日時を調べる方法(AnalyzeやVacuumとは？)

概要

Postgreにおいて、あるDBスキーマあるいはテーブルが一番最後に更新された日時を調べる方法を紹介します。

実施方法

PostgreSQLには、pg_stat_user_tablesというシステムビューが用意されており、テーブルの統計情報を提供しています。このビューを使用して、テーブルの最後の更新日時を調べることができます。

以下のような形でSQLを実施します。
pg_stat_user_tablesとは、PostgreSQL内部の統計情報を持つシステムカタログビューの一つ。
relnameには該当のテーブルを入れてあげます。

SELECT relname, last_autoanalyze, last_autovacuum, last_analyze, last_vacuum
FROM pg_stat_user_tables
WHERE relname = 'sample';

結果、以下の結果になりました。

Schema全体で実施したい場合は以下を実行します。

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

これで各テーブルにおいて、最終的に更新された日時がわかります。

自動解析や自動バキュームとは？

上記では、last_autoanalyzeやlast_autovacuumなどのカラムにそれぞれテーブルに対して実行された最後の自動解析や自動VACUUMなどの日時が記録されています。

では、自動解析や自動バキュームとはなんなのか？

PostgreSQL VACUUM and ANALYZE Best Practice Tips

上記、英語サイトですが詳細に書かれています。

PostgreSQLには、自動解析と自動バキュームという2つの機能があり、どちらもデータベースのパフォーマンスを維持するために重要なものです。

• 自動解析：PostgreSQLがテーブルの統計情報（statistics)を自動的に更新するプロセス
  • 統計情報とは：テーブル内のデータの分布や値の範囲などを示すもので、クエリプランナーがクエリの最適な実行計画を決定する際に使用される(=ベストなクエリプランのために使われる、ってことですね)
  • テーブル内のデータが一定の変更量を超えた場合にトリガーされる

An “analyze” operation does what its name says – it analyzes the contents of a database’s tables and collects statistics about the distribution of values in each column of every table. PostgreSQL query engine uses these statistics to find the best query plan. As rows are inserted, deleted, and updated in a database, the column statistics also change. ANALYZE – either run manually by the DBA or automatically by PostgreSQL after an autovacuum – ensures the statistics are up-to-date.

• 自動バキューム：データベース内の不要なデータや行を削除し、データベースの容量を最適化するプロセス
  • データベース内の行の更新や削除により、不要なDead Rows（dead tuples）が残ることがある。これは、PostgreSQLがMVCC（Multi-Version Concurrency Control）と呼ばれる仕組みを使用しており、削除された行も実際には物理的に削除されずに残るため。データベースが不必要に肥大化し、パフォーマンスが低下する可能性があるため、自動バキュームによってクリーンアップを行う。
  • PostgreSQLは古い行を物理的にテーブルから削除するのではなく、それに"marker"のようなものを付けてクエリがその行を返さないようにしている
  • トランザクションIDの整理も行う。長期間にわたって実行されるトランザクションが増えるとデータベースのパフォーマンスに影響を及ぼす可能性があるため、不要な古いトランザクションIDに関連する情報を削除してデータベースの効率を向上させる。
  • データベース内で一定のしきい値を超えた場合にトリガーされる

A vacuum is used for recovering space occupied by “dead tuples” in a table. A dead tuple is created when a record is either deleted or updated (a delete followed by an insert). PostgreSQL doesn’t physically remove the old row from the table but puts a “marker” on it so that queries don’t return that row. When a vacuum process runs, the space occupied by these dead tuples is marked reusable by other tuples.

具体的にそれぞれがkick offされる閾値を調べる方法もあります。
先ほどのサイトには、ベストプラクティスとして「Fine-tune Autovacuum Threshold」とあるので、閾値をカスタマイズすることも大事です。

ANALYZEの閾値情報を取得するSQLクエリ

SELECT name, setting
FROM pg_settings
WHERE name = 'autovacuum_analyze_scale_factor'
   OR name = 'autovacuum_analyze_threshold';

→テーブルの行数の10%を超える場合　OR　行数が50行を超える場合

自動バキュームの閾値情報を取得するSQLクエリ

SELECT name, setting
FROM pg_settings
WHERE name = 'autovacuum_vacuum_scale_factor'
   OR name = 'autovacuum_vacuum_threshold';

→テーブルの行数の20%を超える場合　OR　行数が50行を超える場合