PostgreSQL 14+ における宣言的テーブルパーティショニングの理解と実装
PostgreSQL 14以降のネイティブな宣言的パーティショニング機能について詳しく解説します。レンジ、リスト、ハッシュの各パーティショニングタイプを説明し、パーティションテーブルの作成と管理に関する実践的なSQL例を提供します。パーティションプルーニングと効率的なメンテナンス戦略を活用して、クエリパフォーマンスを最適化し、大規模データセットのデータ管理を簡素化する方法を学びます。
PostgreSQL 14+ における宣言的テーブルパーティショニングの理解と実装
PostgreSQLのパーティショニングは、1つのテーブルに対するクエリ、バキューム、アーカイブ、削除が扱いにくくなった場合に検討する価値があります。よくある例は、1日に数百万行を受信し、ほとんどの場合時間範囲でクエリされるイベントテーブルです。パーティショニングがなければ、優れたインデックスがあっても、メンテナンスにコストがかかり、古いデータの削除が困難なテーブルになる可能性があります。
宣言的パーティショニングを使用すると、1つの論理テーブルが行をパーティションと呼ばれるより小さな物理テーブルにルーティングできます。PostgreSQL 10でネイティブ構文が導入され、以降のバージョンでは計画、プルーニング、インデックス、メンテナンスの動作が改善されました。PostgreSQL 14以降は十分に成熟しており、多くのチームがトリガーベースの継承スキームなしでパーティショニングを使用できますが、それでも慎重な設計が求められます。不適切なパーティションキーは、システムを高速化することなく複雑にする可能性があります。
宣言的テーブルパーティショニングとは？
宣言的パーティショニングは、定義された一連のルールに基づいて、単一の論理テーブル（親またはパーティションテーブル）を複数の物理テーブル（子またはパーティションテーブル）に分割できるデータベース機能です。各パーティションには、親テーブルのデータのサブセットが保持されます。パーティションキーによって、行がどのパーティションに属するかが決まります。
宣言的パーティショニングの主な利点は次のとおりです。
- クエリパフォーマンスの向上: パーティションキーでフィルタリングするクエリは、一致する行を含まないパーティションをプルーニングできるため、高速化できます。
- データ管理の容易化: 古いデータの削除やアーカイブなどの操作は、単一の大きなテーブルに対して大規模な
DELETE操作を実行するよりも、個々のパーティションをデタッチまたはドロップする方がはるかに効率的に実行できます。 - メンテナンスの簡素化: インデックス作成とバキュームをパーティションごとに管理できるため、テーブル全体への影響を軽減できます。
- より小さなメンテナンス単位: パーティションレベルのインデックス、デタッチ操作、ターゲットを絞ったバキューム作業により、ルーチンメンテナンスの影響範囲を減らすことができます。
宣言的パーティショニングのタイプ
PostgreSQLは、宣言的パーティショニングにいくつかの方法をサポートしており、それぞれ異なるデータ分散パターンに適しています。
1. レンジパーティショニング
レンジパーティショニングは、特定の列（日付、数値など）の連続した値の範囲に基づいてデータを分割します。
ユースケース: ログ、イベントデータ、売上記録などの時系列データに最適で、特定の日付または数値範囲内のデータを頻繁にクエリする場合に適しています。
例: sale_date列でsalesテーブルをパーティショニングします。
レンジパーティションテーブルの作成
まず、パーティショニング方法とキーを指定して親テーブルを作成します。
CREATE TABLE sales (
sale_id SERIAL,
product_name VARCHAR(100),
sale_amount NUMERIC(10, 2),
sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);
次に、個々のパーティションを作成します。各パーティションは、含まれる範囲を指定するFOR VALUES句で定義されます。
-- 2023年1月の売上パーティション。
-- 上限は排他的なので、1月31日を含みます。
CREATE TABLE sales_2023_01
PARTITION OF sales ()
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
-- 2023年2月の売上パーティション
CREATE TABLE sales_2023_02
PARTITION OF sales ()
FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
-- 2023年3月の売上パーティション
CREATE TABLE sales_2023_03
PARTITION OF sales ()
FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
ヒント: 範囲を定義するときは、連続しており、すべての可能な値をカバーしていることを確認してください。範囲が重複しないようにしてください。TOの値は排他的です。
2. リストパーティショニング
リストパーティショニングは、列の個別の値のリストに基づいてデータを分割します。
ユースケース: 地理的領域、ステータスコード、製品カテゴリなど、固定された既知の値のセットを持つ列に適しています。
例: region列でordersテーブルをパーティショニングします。
リストパーティションテーブルの作成
PARTITION BY LISTを使用して親テーブルを定義します。
CREATE TABLE orders (
order_id SERIAL,
customer_name VARCHAR(100),
order_total NUMERIC(10, 2),
region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);
特定のリージョンのパーティションを作成します。
-- 'North America'の注文パーティション
CREATE TABLE orders_north_america
PARTITION OF orders ()
FOR VALUES IN ('North America');
-- 'Europe'の注文パーティション
CREATE TABLE orders_europe
PARTITION OF orders ()
FOR VALUES IN ('Europe');
-- 'Asia'の注文パーティション
CREATE TABLE orders_asia
PARTITION OF orders ()
FOR VALUES IN ('Asia');
重要: 既存のパーティションのINリストに一致しないregionの値を挿入し、DEFAULTパーティションがない場合、挿入は失敗します。DEFAULTパーティションを作成して、他のすべての値をキャッチできます。
デフォルトパーティションの作成
-- 明示的にリストされていないリージョンのデフォルトパーティション
CREATE TABLE orders_other
PARTITION OF orders ()
DEFAULT;
3. ハッシュパーティショニング
ハッシュパーティショニングは、パーティションキーのハッシュ値に基づいて、データを複数のパーティションに分散します。
ユースケース: 大量のデータがあり、明確な範囲やリストベースの分散なしにパーティション全体に均等に分散したい場合に役立ちます。負荷分散に適しています。
例: user_idでusersテーブルをパーティショニングします。
ハッシュパーティションテーブルの作成
PARTITION BY HASHを使用して親テーブルを定義し、パーティション数を指定します。
CREATE TABLE users (
user_id BIGSERIAL,
username VARCHAR(50) NOT NULL,
email VARCHAR(100)
)
PARTITION BY HASH (user_id);
PostgreSQLは、パーティションを明示的に指定しない場合、自動的にパーティションを作成しますが、特にパーティションの数と名前を制御したい場合は、明示的に作成することをお勧めします。
明示的なハッシュパーティションの作成
-- 4つのハッシュパーティションを作成
CREATE TABLE users_p0
PARTITION OF users
FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE users_p1
PARTITION OF users
FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE users_p2
PARTITION OF users
FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE users_p3
PARTITION OF users
FOR VALUES WITH (modulus 4, remainder 3);
注: ハッシュパーティショニングを使用する場合、modulus（パーティションの総数）とremainder（このパーティションがどのパーティションであるか）を指定する必要があります。
宣言的パーティショニングの実装: ベストプラクティス
- 適切なパーティションキーを選択する: パーティションキーは、最も頻繁なクエリフィルターとデータ管理操作に合わせる必要があります。適切なキーはパフォーマンスを大幅に向上させます。
- パーティションの数を検討する: パーティションが少なすぎると十分なメリットが得られない可能性があり、多すぎるとオーバーヘッドが増加する可能性があります。管理性とパフォーマンスのバランスが取れた数を目指してください。レンジパーティショニングの場合は、データの成長率と保持ポリシーを考慮してください。
- 自動化に
pg_partmanを使用する: 特に時系列データのレンジパーティショニングでは、pg_partmanなどの拡張機能の使用を検討してください。新しいパーティションの作成と古いパーティションのアーカイブ/削除を自動化し、手作業を大幅に削減します。 - 戦略的にインデックスを作成する: インデックスはパーティションごとに物理的に保存されます。親にインデックスを作成すると、一致するパーティションインデックスが作成されますが、各パーティションに同じインデックスパターンが必要かどうかを確認する必要があります。
- パーティションプルーニング:
WHERE句にパーティションキーを含めることで、クエリがパーティションプルーニングを活用できるようにします。EXPLAINコマンドを使用すると、プルーニングが発生しているかどうかを確認できます。 DEFAULTパーティション: リストパーティショニングの場合、新しい値が予期せず表示された場合に挿入エラーを回避するために、DEFAULTパーティションが重要です。- 一意制約: パーティションテーブルの一意制約または主キーには、通常、すべてのパーティションキー列を含める必要があります。これにより、多くの初めての設計者が驚くことがよくあります。
- データ型: パーティションキーのデータ型が適切であり、親テーブルと子テーブル間で一貫していることを確認します。
パーティションの管理
パーティションのアタッチとデタッチ
パーティションはCREATE TABLE ... PARTITION OF ...を介して直接作成されますが、既存のテーブルをパーティションとしてデタッチおよびアタッチすることもできます。これは、データの移行や大規模なデータセットの管理に役立ちます。
パーティションのデタッチ: デタッチすると、パーティションはデータを保持したまま通常のテーブルになります。
-- sales_2023_01パーティションをデタッチ
ALTER TABLE sales DETACH PARTITION sales_2023_01;
テーブルをパーティションとしてアタッチ: 親のスキーマに準拠し、パーティション境界に適合するデータを持つ通常のテーブルをアタッチできます。
-- sales_2022_12がsalesと同じ列を持つ通常のテーブルで、
-- 2022年12月の行のみを含むと仮定します。
ALTER TABLE sales ATTACH PARTITION sales_2022_12
FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');
大きなテーブルをアタッチする前に、最初に一致するCHECK制約を追加します。PostgreSQLはその制約を使用して、行がパーティション境界に適合することを証明するためにテーブル全体をスキャンすることを回避できます。
パーティションのドロップ
パーティションのドロップは高速な操作であり、パーティションテーブルのみを削除し、その中のデータは削除しません（明示的に指定しない限り）。これはDELETEよりもはるかに高速です。
-- パーティションをドロップするには、子テーブルを単純に削除します
DROP TABLE sales_2023_01;
例: パーティションプルーニングによるクエリパフォーマンスの向上
前述のように、sale_dateでパーティショニングされたsalesテーブルを考えます。
パーティションプルーニングなしのクエリ（パーティショニングされていないテーブルでの仮定）:
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';
salesが巨大でパーティショニングされていないテーブルの場合、このクエリはテーブル全体をスキャンします。ただし、宣言的パーティショニングを使用すると、次のようになります。
-- このクエリはsales_2023_01パーティションのみをスキャンします
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';
PostgreSQLのクエリプランナーは、sale_dateがパーティションキーであり、指定された範囲がsales_2023_01パーティションに完全に収まることを認識します。したがって、そのパーティションのみをスキャンし、I/Oを大幅に削減してパフォーマンスを向上させます。
これを確認するには、EXPLAINを使用します。
EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';
出力には、関連するパーティションのみが表示されるか、PostgreSQLのバージョンと計画の形状によっては、削除されたサブプランが表示される場合があります。重要な兆候は、無関係なパーティションがスキャンされないことです。
実践的な設計チェックリスト
運用上のメリットを明確に説明できる場合にのみ、パーティショニングを導入してください。「テーブルが大きい」というだけでは十分ではありません。適切にインデックスが作成されたポイントルックアップを備えた大きなテーブルは、問題ない場合があります。パーティショニングは、ほとんどのクエリにパーティションキーが含まれている場合、古いデータが定期的にアーカイブまたは削除される場合、または1つの巨大なテーブルのメンテナンスがすでに問題を引き起こしている場合に、より意味があります。
時系列テーブルの場合は、クエリと保持パターンに一致するパーティションサイズを選択します。日次パーティションは、非常に高い取り込み率と短い保持期間に役立ちます。月次パーティションは、中程度のイベント量に対して管理が容易な場合がよくあります。小さなパーティションが多すぎると、計画が遅くなり、メンテナンスが煩雑になる可能性があります。一方、巨大なパーティションが少なすぎると、元の問題が解決されない可能性があります。
出荷前に挿入を計画します。行が遅れて到着する可能性がある場合は、それらを受信できるように古いパーティションを十分に長く利用可能にしておきます。パーティションキーに予期しない値が含まれる可能性がある場合は、DEFAULTパーティションを作成して監視します。デフォルトパーティションはセーフティネットであり、忘れられたデータが何ヶ月も静かに蓄積される場所であってはなりません。
最後に、実際のクエリ形状でテストします。パーティションプルーニングは、WHERE句がsale_date >= '2023-01-01' AND sale_date < '2023-02-01'のようにパーティションキーを明確に公開する場合に最も効果的に機能します。キーを関数でラップすると、プルーニングが難しくなります。
-- プルーニングにあまり適さない
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';
-- プランナーにとって簡単
WHERE sale_date >= DATE '2023-01-01'
AND sale_date < DATE '2023-02-01';
宣言的パーティショニングは、クエリツールであると同時にメンテナンスツールでもあります。適切に使用すれば、古いデータを安価に削除し、ホットデータをスキャンしやすくします。軽率に使用すると、テーブル、インデックス、およびエッジケースが増えます。アクセスパターンから始め、そのパターンからパーティションキーを選択し、設計を完了する前に計画を確認します。
既存の大きなテーブルの場合、ピークトラフィック時にリスクの高い一発の変換を計画しないでください。一般的な移行パスは、新しいパーティションテーブルを作成し、データをチャンクでコピーし、アプリケーションロジックまたは注意深くテストされたトリガーを通じて新しい書き込みを流し続け、短いメンテナンスウィンドウ中に名前を交換することです。正確なアプローチは、書き込み量とダウンタイムの許容度によって異なりますが、原則は同じです。コピーを証明し、制約を証明し、本番環境に触れる前にカットオーバーをリハーサルします。