PostgreSQLはバージョン10で宣言的パーティショニングを導入し、面倒な継承ベースのパーティショニングモデルを置き換えました。クエリプランナーは、クエリの述語をパーティションの境界と比較することによって静的パーティションプルーニングを計画段階で実行しますが、どのパーティションを除外できるかを判断するためには、式が計画時に定数として評価可能である必要があります。
コアアーキテクチャの制約は、now()やcurrent_timestampのようなSTABLE関数が計画時に評価されないことです。なぜなら、これらの関数の結果は計画と実行、あるいはクエリの実行中に異なる可能性があるからです。したがって、プランナーはこれらの関数を含む述語をブラックボックスとして扱い、特定のパーティションに一致する行が含まれないことを証明できず、すべてのパーティションをスキャンせざるを得なくなります。
解決策は、述語をIMMUTABLE関数またはリテラル定数を使用するように書き換えるか、PostgreSQL 11以降で利用可能なランタイムパーティションプルーニングに依存することです。enable_partition_pruningをonに設定することで、エグゼキュータは実行時にパーティションの境界に対してSTABLE関数の結果を評価し、初回の計画段階を経た後に無関係なパーティションを動的にスキップします。
ある金融分析会社は、テクスチャデータのテラバイトを管理するために、execution_time列で日次範囲を持つTIMESTAMPTZで取引テーブルをパーティショニングしました。アナリストは頻繁に**WHERE execution_time >= now() - interval '1 hour'**で最近の活動をクエリしましたが、これらのクエリはすべての365日次のパーティションをスキャンすることで壊滅的なパフォーマンス低下を観察しました。
最初に考慮されたアプローチは、アプリケーション層がタイムスタンプ境界を計算し、それをリテラル定数として注入することでした。これにより、即座の静的プルーニングが可能になり、クエリ時間が45秒から80ミリ秒に減少しました。しかし、これは容易に変更できないサードパーティのBIツールに埋め込まれた既存のSQLクエリを壊しました。
二番目のアプローチは、現在の日付に基づいた固定タイムスタンプを返すカスタムの不変関数を作成することでした。これは、データベーストランザクションが真夜中の境界を越えてオープンのままである場合に、不正確な結果をもたらす可能性があるため却下されました。これは、トランザクション内でnow()が提供するSTABLE契約を侵害することになります。このような侵害は、プランナーが古いパーティション範囲をキャッシュした場合に、静かに不正なクエリ結果を引き起こす可能性があります。
選択された解決策は、PostgreSQL 12のランタイムパーティションプルーニング機能を活用しました。データベース管理者はenable_partition_pruningが有効であることを確認し、SQL文字列に連結されるのではなく、パラメーターとして時間境界を渡す準備文を使用するようにアプリケーションをリファクタリングしました。これにより、エグゼキュータは実行時に実際のパラメータ値を使用してパーティションを動的にプルーニングし、レガシー報告ツールでのSQLテキスト生成の変更なしにほぼ最適なパフォーマンスを達成しました。
継承テーブルに対するPostgreSQLの制約除外メカニズムは、ネイティブパーティションプルーニングとどのように異なり、前者が明示的なGUC設定を必要とし、後者が必要ではないのはなぜですか？
制約除外は、宣言的パーティショニングの前に使用されたパーティショニング方法で、子テーブルに対するCHECK制約を利用して、テーブルが関連する行を含まないことを証明します。計画中に数百のテーブルが存在する場合、これらの制約をすべての継承テーブルに対して評価することは高コストなため、これはconstraint_exclusionパラメータによって制御されており、デフォルトではpartition（継承を介してクエリする際のみチェック）となっています。ネイティブパーティションプルーニングは、パーティション階層を直接理解するプランナーの専門的なデータ構造を使用するため、より迅速で常に有効であり、正しい機能のためにGUC調整を必要としません。
パーティションキーを変更して行をパーティション間で移動する場合、なぜPostgreSQLは内部的にDELETEとINSERTを実行し、インプレース更新を行わないのか、またこれがどのようなトリガーの影響を与えるのか？
各パーティションは、別々の物理ストレージを持つ独立したヒープ関係であるため、パーティションキーを変更すると、タプルをあるファイルから別のファイルに移動する必要があります。PostgreSQLは、この移行をソースパーティションから行を削除し、別のパーティションに挿入することによって実装しています。つまり、行レベルのBEFORE DELETE、AFTER DELETE、BEFORE INSERT、AFTER INSERTトリガーは、単一のUPDATE操作のように見える間にすべて発火します。さらに、論理レプリケーションはこれを2つの別々のWALエントリ（削除と挿入）としてストリームし、レプリカIDが正しく構成されていない場合には、サブスクライバで衝突を引き起こす可能性があります。
DEFAULTパーティションを持つテーブルに新しいパーティションをアタッチする際にどのような特定のロックと検証オーバーヘッドが発生し、どのようにしてDEFAULTパーティションのフルスキャンを回避できますか？**
DEFAULTパーティションを含む範囲またはリストパーティションテーブルに新しいパーティションをアタッチする際、PostgreSQLは、新しいより具体的なパーティションに属すべき行が存在しないことを確認するために、全体のDEFAULTパーティションをスキャンする必要があります。この検証スキャンは、パーティショニングされたテーブルにACCESS EXCLUSIVEロックを取得し、大きなデフォルトパーティションに対しては数時間かかることがあります。これを回避するためには、新しいパーティションをアタッチする前にDEFAULTパーティションを切り離し、その後に矛盾する行が存在しないことを確認してからDEFAULTパーティションを再アタッチするか、あるいはCREATE TABLE ... PARTITION OFを使用して新しい空のパーティションを作成し、特定のパーティション範囲をフィルタリングするINSERT ... SELECTを使用してデータを移行し、デフォルトパーティションの内容の検証スキャンを回避する必要があります。