PostgreSQL テーブルパーティショニング

はじめに (Introduction)

この実験 (lab) では、PostgreSQL でテーブルパーティショニングを実装する方法を学びます。目標は、大きなテーブルをより小さく、管理しやすい部分に分割し、クエリのパフォーマンスを向上させ、データ管理を簡素化することです。

まず、sale_date 列で範囲パーティション化された sales という名前のテーブルを設定します。これには、メインの sales テーブルを作成し、さまざまな日付範囲のパーティションを定義することが含まれます。次に、sales テーブルにデータを挿入します。データは自動的に正しいパーティションにルーティングされ、パーティション化されたテーブル全体に対してクエリを実行します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL postgresql(("PostgreSQL")) -.-> postgresql/PostgreSQLGroup(["PostgreSQL"]) postgresql/PostgreSQLGroup -.-> postgresql/db_setup("Create New Database") postgresql/PostgreSQLGroup -.-> postgresql/table_init("Create Basic Table") postgresql/PostgreSQLGroup -.-> postgresql/row_add("Insert One Row") postgresql/PostgreSQLGroup -.-> postgresql/data_all("Select All Data") postgresql/PostgreSQLGroup -.-> postgresql/data_where("Filter With WHERE") subgraph Lab Skills postgresql/db_setup -.-> lab-550963{{"PostgreSQL テーブルパーティショニング"}} postgresql/table_init -.-> lab-550963{{"PostgreSQL テーブルパーティショニング"}} postgresql/row_add -.-> lab-550963{{"PostgreSQL テーブルパーティショニング"}} postgresql/data_all -.-> lab-550963{{"PostgreSQL テーブルパーティショニング"}} postgresql/data_where -.-> lab-550963{{"PostgreSQL テーブルパーティショニング"}} end

`sales` テーブルと初期パーティションの作成 (Create the `sales` Table and Initial Partitions)

このステップでは、メインの sales テーブルと、2023 年の初期パーティションを作成します。パーティショニングは、大きなテーブルをより小さく、管理しやすい部分に分割し、クエリのパフォーマンスを向上させます。

まず、postgres ユーザーとして PostgreSQL データベースに接続します。ターミナルを開き、次のコマンドを使用します。

sudo -u postgres psql

これで、PostgreSQL のインタラクティブターミナルに入ります。

次に、sales テーブルを作成します。このテーブルには、販売日、製品 ID、販売額などの販売データが格納されます。

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

このコマンドは、sales テーブルを作成し、sale_date 列で範囲（range）によってパーティション化されることを指定します。

次に、2023 年のパーティションを作成し、年を四半期に分割します。

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');

これらのコマンドは、sales テーブルに対して 4 つのパーティションを作成し、それぞれが 2023 年の四半期をカバーします。

テーブルとパーティションが正常に作成されたことを確認するには、すべてのテーブルをリストします。

\dt

出力には、sales テーブルとそのパーティション (sales_2023_q1、sales_2023_q2、sales_2023_q3、sales_2023_q4) が表示されるはずです。

2024 年のパーティションの定義 (Define Partitions for 2024)

sales テーブルと 2023 年のパーティションを作成したので、2024 年のパーティションを追加しましょう。これにより、年間全体の販売データを確実に保存できます。

前のステップから、PostgreSQL のインタラクティブターミナルにまだいるはずです。そうでない場合は、次を使用して再度接続します。

sudo -u postgres psql

2024 年の sales テーブルのパーティションを作成します。四半期ごとのパーティション分割スキームに従います。

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');

これらのコマンドは、sales テーブルに対して 4 つの新しいパーティションを作成し、2024 年の各四半期をカバーします。

新しいパーティションが正常に作成されたことを確認するには、すべてのテーブルを再度リストします。

\dt

出力には、sales テーブル、2023 年のパーティション、および新しい 2024 年のパーティションが表示されるはずです。

`sales` テーブルへのデータの挿入 (Insert Data into the `sales` Table)

このステップでは、サンプルデータを sales テーブルに挿入します。PostgreSQL は、sale_date に基づいて、データを正しいパーティションに自動的にルーティングします。

PostgreSQL のインタラクティブターミナルにまだいるはずです。そうでない場合は、次を使用して再度接続します。

sudo -u postgres psql

次のサンプルデータを sales テーブルに挿入します。

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 ステートメントは、2023 年と 2024 年のさまざまな日付の販売レコードを追加します。PostgreSQL は、sale_date に基づいて、各レコードを適切なパーティションに自動的にルーティングします。

データが正しく挿入されたことを確認するには、sales テーブルをクエリして、レコードの総数をカウントします。

SELECT COUNT(*) FROM sales;

出力には、合計 16 件のレコードが表示されるはずです。

パーティション分割された `sales` テーブルのクエリ (Query the Partitioned `sales` Table)

このステップでは、パーティション分割された sales テーブルをクエリしてデータを取得します。テーブル全体を単一のテーブルであるかのようにクエリでき、PostgreSQL はクエリを最適化して、関連するパーティションのみにアクセスします。

PostgreSQL のインタラクティブターミナルにまだいるはずです。そうでない場合は、次を使用して再度接続します。

sudo -u postgres psql

sales テーブル全体からすべての販売レコードを取得するには、次の SELECT ステートメントを使用します。

SELECT * FROM sales;

このクエリは、sales テーブルのすべてのパーティションからすべてのデータを返します。

WHERE 句を使用してデータをフィルタリングすることもできます。たとえば、2023 年のすべての販売レコードを取得するには、次のようにします。

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

PostgreSQL は、パーティションキー (sale_date) を使用して、アクセスするパーティションを決定し、クエリのパフォーマンスを向上させます。

別のクエリを試して、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;

このクエリは、2024 年内の各 product_id の sale_amount の合計を計算します。

最後に、PostgreSQL のインタラクティブターミナルを終了します。

\q

まとめ (Summary)

この実験（Lab）では、PostgreSQL でテーブルパーティショニングを実装する方法を学びました。sales テーブルを作成し、日付範囲でパーティション分割し、2023 年と 2024 年のパーティションを定義し、テーブルにデータを挿入し、パーティション分割されたテーブルをクエリしました。パーティショニングは、クエリのパフォーマンスを大幅に向上させ、大規模なテーブルのデータ管理を簡素化できます。