はじめに
この実験 (Lab) では、PostgreSQL でテーブルパーティショニングを実装する方法を学びます。目標は、大きなテーブルをより小さく管理しやすい断片に分割することです。これにより、クエリのパフォーマンスが大幅に向上し、バックアップやアーカイブなどのデータ管理タスクが簡素化されます。
まず、パーティショニング用に設計されたメインの「親」テーブルを作成します。次に、特定の期間のデータを保持する複数の「子」テーブル、つまりパーティションを定義します。最後に、親テーブルにデータを挿入し、PostgreSQL が自動的に正しいパーティションにルーティングする様子を観察します。また、パーティション化されたテーブルをクエリする方法と、PostgreSQL が関連するパーティションのみにアクセスすることでこれらのクエリを最適化する方法も学びます。
親パーティションテーブルの作成
このステップでは、パーティションの親テーブルとして機能する sales テーブルを作成します。このテーブルは、すべてのパーティションの構造を定義しますが、それ自体はデータを格納しません。
まず、PostgreSQL データベースに接続する必要があります。ターミナルを開き、以下のコマンドを使用して postgres ユーザーとして psql 対話型シェルを開始します。
sudo -u postgres psql
これで、postgres=# のような PostgreSQL プロンプトが表示されるはずです。この実験 (Lab) での以降のすべての SQL コマンドは、このプロンプトから実行されます。
次に、sales テーブルを作成します。このテーブルは sale_date 列で範囲 (RANGE) によってパーティション化されます。
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE NOT NULL,
product_id INTEGER,
sale_amount DECIMAL(10, 2),
PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);
このコマンドの内訳を見てみましょう。
CREATE TABLE sales (...): 販売データ用の列を定義します。PRIMARY KEY (sale_id, sale_date): パーティション化されたテーブルでは、主キーにはパーティション化列 (sale_date) を含める必要があります。PARTITION BY RANGE (sale_date): これが重要な部分です。sale_date列に対してRANGEメソッドを使用してこのテーブルがパーティション化されていることを宣言します。
コマンドを実行すると、CREATE TABLE の確認メッセージが表示されるはずです。
テーブルが作成されたことを確認するには、psql で \d コマンドを使用してテーブル構造を記述できます。
\d sales
出力にはテーブルの列が表示され、一番下には「Partitioned table」であることが確認され、「Partition key」がリストされます。
Table "public.sales"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+------------------------------------------------
sale_id | integer | | not null | nextval('sales_sale_id_seq'::regclass)
sale_date | date | | not null |
product_id | integer | | |
sale_amount | numeric(10,2) | | |
Partition key: RANGE (sale_date)
Indexes:
"sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0
「Number of partitions」が 0 であることに注意してください。実際のパーティションは次のステップで作成します。
日付範囲のパーティションを定義する
親テーブルである sales テーブルが作成されたので、実際にデータを格納するパーティションを作成する必要があります。各パーティションは特定の期間のデータを保持します。このステップでは、2023 年と 2024 年の四半期ごとのパーティションを作成します。
引き続き psql 対話型ターミナルを使用してください。
まず、2023 年の 4 つのパーティションを作成します。各コマンドは、sales のパーティションとして新しいテーブルを定義し、それがカバーする日付範囲を指定します。
CREATE TABLE sales_2023_q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE sales_2023_q2 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE sales_2023_q3 PARTITION OF sales
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE sales_2023_q4 PARTITION OF sales
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');
FOR VALUES FROM ... TO ... 句は、各パーティションの範囲を定義します。下限は含まれ、上限は含まれません。例えば、sales_2023_q1 は sale_date が 2023-01-01 から 2023-04-01 の直前までのレコードを格納します。
次に、同じ四半期ごとのスキームを使用して、2024 年のパーティションを作成します。
CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE sales_2024_q2 PARTITION OF sales
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE sales_2024_q3 PARTITION OF sales
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE sales_2024_q4 PARTITION OF sales
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
各 CREATE TABLE コマンドを実行すると、確認メッセージが表示されます。
すべてのパーティションが作成されたことを確認するには、データベースのテーブルを再度一覧表示できます。
\dt
出力には、親テーブル sales と、作成した 8 つのパーティション（sales_2023_q1、sales_2023_q2 など）が表示されるはずです。
データの挿入とルーティング
このステップでは、サンプルデータを挿入します。パーティショニングの主な機能は、データを直接親テーブル (sales) に挿入すると、PostgreSQL がパーティションキー (sale_date) の値に基づいて各行を正しいパーティションに自動的にルーティングすることです。
引き続き psql 対話型ターミナルを使用してください。
以下の INSERT ステートメントを実行して、2023 年と 2024 年にまたがる 16 件のサンプル販売レコードを追加します。
INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);
コマンドが完了すると、INSERT 0 16 という出力が表示され、16 行が正常に挿入されたことを示します。
データが正しくルーティングされたことを確認するには、個々のパーティションをクエリできます。例えば、2023 年の第 1 四半期のレコード数をチェックしてみましょう。
SELECT COUNT(*) FROM sales_2023_q1;
出力は次のようになります。
count
-------
2
(1 row)
次に、2024 年の第 4 四半期のレコード数を確認します。
SELECT COUNT(*) FROM sales_2024_q4;
出力も 2 になるはずです。これにより、PostgreSQL がデータを正しい基盤となるパーティションテーブルに配置したことが確認できます。
データのクエリとパフォーマンス分析
最終ステップとして、パーティション化された sales テーブルをクエリします。パーティショニングの主な利点である「パーティションプルーニング（partition pruning）」とは、PostgreSQL のクエリオプティマイザが、データセット全体をスキャンするのではなく、必要なパーティションのみをスキャンするのに十分賢いということです。
引き続き psql 対話型ターミナルを使用してください。
まず、2023 年の第 1 四半期のすべての販売データを取得するクエリを実行します。
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
この日付範囲に含まれる 2 つのレコードが表示されます。PostgreSQL がこれをどのように最適化するかを確認するには、クエリの実行計画を示す EXPLAIN コマンドを使用できます。
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
出力は次のようになります。
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on sales_2023_q1 sales (cost=0.00..31.75 rows=7 width=28)
Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)
Seq Scan on sales_2023_q1 という行に注目してください。これは、PostgreSQL が sales_2023_q1 パーティションのみをスキャンし、他の 7 つを無視したことを証明しており、大規模なデータセットではクエリが大幅に高速化されます。
次に、2024 年の各製品の合計販売金額を計算する、より複雑なクエリを実行します。
SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;
このクエリは、結果を計算するために 2024 年の 4 つのパーティションのみを効率的にスキャンします。出力には、製品 ID 109 から 116 までの各製品の合計販売額が表示されます。
最後に、PostgreSQL 対話型ターミナルを終了するには、次のように入力します。
\q
通常のシェルプロンプトに戻ります。
まとめ
この実験では、PostgreSQL におけるテーブルパーティショニングの基本を学びました。日付範囲でパーティション化された親テーブルの作成、異なる期間に対する特定のパーティションの定義、そして正しいパーティションに自動的にルーティングされたデータの挿入に成功しました。最も重要なのは、EXPLAIN コマンドを使用してパーティションプルーニングが実際に機能していることを確認し、パーティショニングがいかにデータベースがデータの一部のみをスキャンできるようにすることでクエリパフォーマンスを大幅に向上させるかを示したことです。これは、大規模なデータセットを効率的に管理するための強力なテクニックです。
