PostgreSQLは、データをそのまま上書きするのではなく、すべてのUPDATEおよびDELETEに対して新しい行バージョンを作成することでマルチバージョン同時実行制御 (MVCC)を実装しています。この設計はリードロックを排除しますが、ヒープ内に"死んだタプル"が残り、これをVACUUMプロセスが回収しなければなりません。テーブルが高変動ワークロード、例えば保留中のレコードの頻繁なステータス更新やソフトデリートのトグリングのような操作を経験する場合、死んだタプルの蓄積はバキュームを上回り、テーブルの膨張、インデックスの膨張、そしてクエリパフォーマンスの低下を引き起こします。
コアの問題は、VACUUMが死んだタプルを識別するためにテーブル全体をスキャンしなければならず、モノリシックな複数テラバイトのテーブルでは、この操作がI/Oを制約し、トランザクションIDのラップアラウンドの前に完了できない可能性があることです。介入なしでは、テーブルは実際の行数が安定しているにもかかわらず無限に成長し、過剰なストレージを消費し、インデックススキャンを遅くします。
この解決策は、作成日などの時間的または論理的キーに基づくレンジ戦略による宣言的パーティショニングを実装します。テーブルを小さな物理パーティション（例：月次セグメント）に分割することで、バキューム操作はテーブル全体ではなく個々のパーティションで行われます。また、パーティションプルーニングにより、クエリは関連するセグメントのみを_access_でき、古くなったパーティションは分離されてアーカイブされ、バキュームのオーバーヘッドなしで即座にストレージを回収できます。
-- 範囲パーティショニングの親テーブル CREATE TABLE iot_sensor_data ( sensor_id INT NOT NULL, temperature NUMERIC, recorded_at TIMESTAMP NOT NULL, status VARCHAR(20) ) PARTITION BY RANGE (recorded_at); -- 最近の高変動データのための月次パーティション CREATE TABLE iot_sensor_data_2024_06 PARTITION OF iot_sensor_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01'); -- パーティションキーに対するインデックス CREATE INDEX idx_sensor_date ON iot_sensor_data (recorded_at);
あるスマート製造クライアントは、50,000のIoTセンサーからテレメトリを取り込むPostgreSQL 14データベースを運営しており、日々1000万の読み取りを生成し、そのうち30%が24時間以内にステータス更新を必要としていました。sensor_logsテーブルは6ヶ月で2TBに膨張しましたが、AUTOVACUUMは常に行われるステータスフラグの更新から死んだタプルを回収するのに十分に早く処理できませんでした。クエリの待ち時間は30秒に達し、pg_classはテーブルがその論理サイズに対して400%膨張していることを示しました。
三つのアーキテクチャ的解決策が評価されました。最初のアプローチは、AUTOVACUUMの設定を攻撃的に調整し、autovacuum_vacuum_scale_factorを0.02に減少させ、maintenance_work_memを2GBに増加させるものでした。これによりわずかに改善されましたが、プロセスは継続的に2TBのテーブル全体をスキャンする必要があり、実稼働のクエリでひどいI/O競合を引き起こし、夜間のメンテナンスウィンドウ内の完了ができませんでした。第二の戦略は古いデータを除外したパーシャルインデックスを提案しましたが、これによりインデックスの膨張は減少しましたが、テーブル内のヒープの膨張や死んだタプルの蓄積を根本的に解決することはありませんでした。
三つ目の解決策は、recorded_atタイムスタンプに基づくレンジによる宣言的パーティショニングを実装し、各パーティションを約300GBに分割しました。このアプローチは、当月のパーティションに高変動アクティビティを局所化できるため、VACUUMが毎回2TBではなく300GBを処理できることから選択されました。さらに、1年以上古いパーティションはDETACHされ、コールドストレージに移動され、コストのかかるDELETE操作なしでスペースを回収しました。実装後、バキュームの所要時間は8時間から45分に短縮され、トランザクションIDのラップアラウンドリスクは消え、クエリパフォーマンスはパーティションプルーニングを通じて10倍向上しました。
PostgreSQLのHOT（Heap-Only Tuple）メカニズムは、インデックスの膨張をどのように減らし、どの特定の条件下で適用できなくなるのか？
HOT更新は、行が更新されるがインデックスされた列が変更されず、同じ8KBデータページ内に十分な空きスペースが存在する場合に発生します。このシナリオでは、PostgreSQLは新しいタプルをヒープ内の古いタプルにチェーンし、新しいインデックスエントリーを作成せずに、インデックスメンテナンスのオーバーヘッドを大幅に削減します。しかし、更新がインデックスに含まれる任意の列を変更する場合、またはページがfillfactorしきい値未満の空きスペースを欠く場合、HOTは直ちに失敗し、新しいインデックスポインタを書き込み、膨張を引き起こしてしまいます。候補者は、すべてのUPDATE操作がインデックスに等しく影響を及ぼすと仮定することが多く、非インデックスされた列の更新がHOTが成功した場合にどれほど安価であるかを理解しません。
VACUUM、VACUUM FULL、およびCLUSTERのロック動作とテーブルの可用性に関する正確な違いは何ですか？
VACUUMはすべての操作と同時に実行され、死んだタプルを再利用可能なスペースとしてマークしますが、オペレーティングシステムに対してストレージを返却しません。ユーザーデータに対してロックは保持されません。VACUUM FULLはテーブルファイル全体を書き換え、膨張を完全に排除しますがACCESS EXCLUSIVEロックを取得し、全ての読み取りと書き込みをブロックし、その期間は大きなテーブルでは数時間かかることがあります。CLUSTERは、インデックスの順序に合わせてテーブルを物理的に並べ替えますが、これはACCESS EXCLUSIVEロックも必要とし、基本的なスペース回収のためにはVACUUM FULLよりも一般的に遅いですが、将来のクラスタインデックススキャンのためのソート順序を維持します。候補者はしばしばVACUUM FULLを定期的なメンテナンスとして危険に推奨し、これがテーブルの完全な無可用性を引き起こすことを理解していません。
可視性マップはインデックスオンリースキャンのパフォーマンスにどのように影響し、なぜこのアクセス手法に対してバキュームの頻度が重要なのか？
可視性マップは、すべての現在および将来のトランザクションに対して可視なタプルを含むヒープページを追跡するバイナリビットマップです。インデックスオンリースキャンは、ヒープタプルを取得せずにインデックスと可視性マップのみを使用してクエリを満たすことができますが、それはマップがそのページ上のすべての行が可視であることを確認する場合のみです。ページに死んだタプルやコミットされていないトランザクションが含まれている場合、可視性ビットは未設定となり、データベースは個々のヒープタプルを確認する必要があります。頻繁なVACUUMは可視性マップビットを更新し、真のインデックスオンリーアクセスを可能にします。これがなければ、カバリングインデックスを使用するクエリでさえ、タプルの可視性を確認するためにランダムI/Oを引き起こし、最適化の目的を無効にします。