Posted inDatabase

PostgreSQLにおけるテーブルパーティショニングのガイド：数百万件のレコードを持つテーブルのクエリを高速化する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

はじめに

データと格闘する中で、データテーブルが数十万件から数百万、さらには数十億件に膨れ上がり、頭を悩ませた経験は少なくないでしょう。そのような時、一つ一つのクエリが忍耐の試練のように感じられます。私はかつて、単純な`SELECT`文が数秒、時には数分もかかり、システムを遅延させ、ユーザーからの不満を招くのを目の当たりにしました。

その時、私は`INDEX`の最適化や`QUERY`の書き換えだけでは不十分で、より強力な解決策が必要だと認識しました。そこで見つけた答えが、PostgreSQLにおけるテーブルパーティショニングです。本番環境で半年以上展開・運用した結果、テーブルパーティショニングが極めて効果的な解決策であることを断言できます。これは、膨大なデータテーブルのパフォーマンス向上と管理の簡素化に役立ちます。

この記事では、私がテーブルパーティショニングをどのように適用したかを詳細に共有します。基本的な手順から高度なテクニック、そしてシステムにすぐに適用できる実体験までをご紹介します。

1. クイックスタート：5分でテーブルパーティショニング

前置きは抜きにして、早速実例に入りましょう。システムログのレコードを格納する`audit_logs`というテーブルがあり、このテーブルが驚異的な速度で増大しているとします。これを`created_at`（作成時間）に基づいてパーティショニングしたいと考えています。

ステップ1：親テーブル（パーティション化されたテーブル）の作成

これは直接データを格納しない「親」テーブルであり、子テーブルがどのようにパーティション分割されるかを定義します。

CREATE TABLE audit_logs (
    log_id BIGSERIAL NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

ここでは、`PARTITION BY RANGE (created_at)`を使用して、テーブルが`created_at`列の値の範囲に基づいてパーティション分割されることを指定しています。

ステップ2：子パーティションの作成

各子パーティションは、親テーブルのデータの一部を格納する個別の物理テーブルです。私は通常、データ量に応じて月ごとまたは年ごとにパーティションを作成します。

-- 2023年1月のパーティション
CREATE TABLE audit_logs_2023_01 PARTITION OF audit_logs
    FOR VALUES FROM ('2023-01-01 00:00:00+00') TO ('2023-02-01 00:00:00+00');

-- 2023年2月のパーティション
CREATE TABLE audit_logs_2023_02 PARTITION OF audit_logs
    FOR VALUES FROM ('2023-02-01 00:00:00+00') TO ('2023-03-01 00:00:00+00');

-- そして以降の月も同様に...
CREATE TABLE audit_logs_2023_03 PARTITION OF audit_logs
    FOR VALUES FROM ('2023-03-01 00:00:00+00') TO ('2023-04-01 00:00:00+00');

注意：`FROM`の値の範囲は含まれ、`TO`の値の範囲は含まれません。値の範囲は重複しないようにしてください。

ステップ3：データの挿入

`audit_logs`親テーブルにデータを挿入すると、PostgreSQLは`created_at`列の値に基づいて、そのレコードを正しい子パーティションに自動的にルーティングします。

INSERT INTO audit_logs (action_type, user_id, created_at) VALUES
('LOGIN', 101, '2023-01-15 10:30:00+00'),
('LOGOUT', 101, '2023-01-15 11:00:00+00'),
('UPDATE_PROFILE', 102, '2023-02-01 09:00:00+00'),
('VIEW_REPORT', 103, '2023-02-20 14:00:00+00'),
('DELETE_ITEM', 104, '2023-03-05 16:00:00+00');

もし、既存のすべての子パーティションの範囲外にある`created_at`を持つレコードを挿入した場合、エラーが発生します（`DEFAULT`パーティションがある場合を除く。これについては後述します）。

ステップ4：クエリの実行とパフォーマンスの体感

今では、`created_at`列でフィルタリングしてデータをクエリすると、PostgreSQLは大きなテーブル全体をスキャンする代わりに、必要なデータを含むパーティションのみを賢くスキャンします。

<a href="https://itfromzero.com/ja/database/explain-analyze%e3%81%a7postgresql%e3%82%af%e3%82%a8%e3%83%aa%e3%82%92%e6%9c%80%e9%81%a9%e5%8c%96%e3%81%99%e3%82%8b%ef%bc%9a%e5%ae%9f%e8%a1%8c%e8%a8%88%e7%94%bb%e3%81%ae%e8%aa%ad%e3%81%bf%e6%96%b9.html">EXPLAIN ANALYZE</a> SELECT * FROM audit_logs WHERE created_at BETWEEN '2023-02-01' AND '2023-02-28';

`EXPLAIN ANALYZE`の結果で、PostgreSQLが`audit_logs_2023_02`のみをスキャンし、他のパーティションをプルーニング（剪定）していることがわかります。これにより、クエリが劇的に高速化します。

2. テーブルパーティショニングとは何か、そしてなぜそれが必要なのか？

テーブルパーティショニングの「魔法」を体験した後、その本質についてもう少し深く掘り下げてみましょう。

基本概念

簡単に言えば、テーブルパーティショニングとは、論理テーブル（親テーブル）を、パーティションキーと呼ばれる1つまたは複数の列に基づいて、より小さな複数の物理テーブル（子テーブル）に分割する技術です。PostgreSQLのバージョン10以降では、これがネイティブにサポートされており、以前のバージョンよりもはるかに簡単かつ効果的に行えるようになりました。

テーブルパーティショニングの「実戦的」な利点

私が適用後、経験し、認識した明確な利点は以下の通りです。

  • クエリの大幅な高速化: これが最大の利点です。クエリがパーティションキーに対するフィルタリング条件（例：`WHERE created_at BETWEEN …`）を持つ場合、PostgreSQLは関連するパーティションのみを読み込み、検索対象のデータを含まないパーティションはスキップします。私は数億件のレコードを持つテーブルで、クエリ時間が数十秒から数ミリ秒に短縮されるのを目の当たりにしました。

    -- 例示（EXPLAINの結果は1つのパーティションのみをスキャンすることを示す）
EXPLAIN SELECT * FROM audit_logs WHERE created_at > '2023-03-15' AND created_at < '2023-03-16';
  • より効率的なデータ管理: 古いデータの削除やアーカイブが非常に簡単かつ迅速になります。大きなテーブル全体に対してコストのかかる`DELETE`文を実行する（これにより多くの`VACUUM`オーバーヘッドが発生する可能性があります）代わりに、古いパーティションを`DETACH`（分離）して`DROP`（削除）するだけで済みます。この操作はほぼ瞬時に行われ、システムの運用に影響を与えません。

    -- 2023年1月のパーティションを親テーブルから分離する
ALTER TABLE audit_logs DETACH PARTITION audit_logs_2023_01;

-- 不要になったパーティションを完全に削除する
DROP TABLE audit_logs_2023_01;
  • メンテナンスのパフォーマンス向上: `VACUUM`や`ANALYZE`などのコマンドは、より小さな子パーティションに対してのみ実行すればよいため、完了が速くなり、システムの負荷が軽減されます。これは、トランザクション量の多い本番環境では特に重要です。
  • 競合の削減 (Contention): データが複数の子テーブルに分散されるため、単一のテーブル上でのロック競合が発生する可能性が減少し、システムがよりスムーズに動作します。

一般的なパーティションの種類

PostgreSQLは3つの主要なパーティションタイプをサポートしており、それぞれ異なるデータ型とクエリに適しています。

  • RANGE Partitioning（範囲によるパーティション分割）: 最も一般的です。パーティションキーの値の範囲に基づいてデータが分割されます。通常、時間列（`created_at`、`log_date`）や増加傾向のあるID列（`transaction_id`）に利用されます。

    -- 年ごとのパーティション例
CREATE TABLE sales (
    sale_id BIGSERIAL,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
  • LIST Partitioning（リストによるパーティション分割）: パーティションキーの個別かつ具体的な値に基づいてデータが分割されます。通常、値の数が固定されており、簡単に定義できる列（`region`、`status`、`category`）に利用されます。

    -- 地域ごとのパーティション例
CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(100),
    region VARCHAR(50)
) PARTITION BY LIST (region);

CREATE TABLE users_north PARTITION OF users
    FOR VALUES IN ('North', 'Northeast');

CREATE TABLE users_south PARTITION OF users
    FOR VALUES IN ('South', 'Southeast');
  • HASH Partitioning（ハッシュによるパーティション分割）: パーティションキーのハッシュ値に基づいてデータが分割され、パーティション間でデータを均等に分散することを目的とします。このタイプは管理がより難しいためあまり一般的ではなく、RANGEやLISTでデータを均等に分割する方法がない場合にのみ使用されます。

3. 高度なトピック：パーティションの管理と最適化

初期のパーティション作成はほんの始まりに過ぎません。システムをスムーズに、そして長期的に効率的に運用するためには、より高度な管理および最適化戦略が必要です。

新しいパーティションの自動作成

本番環境では、毎月手動で各パーティションを作成することはできません。私は通常、cronジョブで定期的に実行される小さなスクリプト、またはトリガー/イベントスケジューラを備えたPL/pgSQL関数を使用して、将来のパーティションを作成します（例：3〜6ヶ月先まで作成）。

以下は、私がよく使用する月次パーティション作成用のシンプルなBashスクリプトです。

#!/bin/bash

DB_NAME="your_database"
DB_USER="your_user"
TABLE_NAME="audit_logs"

# 現在の月と年を取得
CURRENT_DATE=$(date +%Y-%m-01)

# 今後2ヶ月分のパーティションを作成
for i in $(seq 0 1); do
    PARTITION_DATE=$(date -d "$CURRENT_DATE + $i months" +%Y-%m-01)
    NEXT_PARTITION_DATE=$(date -d "$CURRENT_DATE + $((i+1)) months" +%Y-%m-01)
    PARTITION_NAME="${TABLE_NAME}_$(date -d "$PARTITION_DATE" +%Y_%m)"

    echo "Checking for partition ${PARTITION_NAME}..."

    # 作成する前にパーティションが存在するかどうかを確認
    psql -U "$DB_USER" -d "$DB_NAME" -tAc "SELECT 1 FROM pg_class WHERE relname='${PARTITION_NAME}'" | grep -q 1
    if [ $? -eq 0 ]; then
        echo "Partition ${PARTITION_NAME} already exists. Skipping."
    else
        echo "Creating partition ${PARTITION_NAME}..."
        psql -U "$DB_USER" -d "$DB_NAME" <<-EOSQL
            CREATE TABLE ${PARTITION_NAME} PARTITION OF ${TABLE_NAME}
            FOR VALUES FROM ('${PARTITION_DATE} 00:00:00+00') TO ('${NEXT_PARTITION_DATE} 00:00:00+00');
EOSQL
        echo "Partition ${PARTITION_NAME} created."
    fi
done

このスクリプトをファイル（例：`create_partitions.sh`）に保存し、実行権限を与え（`chmod +x create_partitions.sh`）、cronジョブに設定して毎月実行してください。

デフォルトパーティション（DEFAULT Partition）

時として、データが定義したどのパーティションとも一致しない場合があります。エラーを避けるために、これらすべての「例外」レコードを格納する`DEFAULT`パーティションを作成できます。

CREATE TABLE audit_logs_default PARTITION OF audit_logs DEFAULT;

警告: `DEFAULT`パーティションにあまりにも多くのデータが流入すると、ホットスポットとなり、パーティショニングの利点が失われる可能性があります。私はこれを一時的な「キャッチオール」としてのみ使用し、常にこのパーティション内のデータをチェックし、処理する手順を設けています。もしここにデータが多すぎる場合、それはパーティショニング戦略を調整する必要があるという兆候です。

「ダウンタイムなし」でのパーティションのデタッチとアタッチ

パーティションを`DETACH`（分離）および`ATTACH`（結合）する機能は、中断なしにデータを管理するための最も強力な機能の1つです。

  • DETACH: 子テーブルを親テーブルから分離します。その子テーブルは独立したテーブルとして存在し続けます。古いデータをアーカイブしたり、迅速に削除したい場合に役立ちます。
  • ATTACH: 独立したテーブル（親テーブルと完全に同じ列構造を持ち、パーティションの範囲外のデータを含まないもの）を、新しい子パーティションとして親テーブルに結合します。これは、大量のデータを個別のパーティションにロードし、それを処理してから親テーブルに結合したい場合に非常に便利です。
    例：別のソースから履歴データを取り込む必要があるとします。親テーブルと同様の構造を持つ新しいテーブルを作成し、そのテーブルにデータをロードしてから、親テーブルに`ATTACH`します。これは、パーティション化されたテーブルに1行ずつ`INSERT`するよりもはるかに高速です。

    -- データを含む一時テーブルを作成する
CREATE TABLE audit_logs_temp (
    log_id BIGSERIAL NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ... audit_logs_tempにデータをロードする ...

-- 一時テーブルをパーティションとして結合する
ALTER TABLE audit_logs ATTACH PARTITION audit_logs_temp
FOR VALUES FROM ('2024-01-01 00:00:00+00') TO ('2024-02-01 00:00:00+00');

パーティションプルーニングと制約排除

これらは、PostgreSQLがパーティション化されたテーブルでのクエリを最適化するために使用する2つの技術です。

  • Partition Pruning（パーティションプルーニング）: PostgreSQL 11以降でデフォルトで有効になっています。クエリが発生すると、PostgreSQLのオプティマイザは`WHERE`条件を分析し、検索対象のデータを含む可能性のあるパーティションのみを選択してスキャンします。他のパーティションは実行計画から完全に除外されます。これがクエリがはるかに高速になる主な理由です。
  • Constraint Exclusion（制約排除）: これはより古い技術（PostgreSQL 10以前でトリガーによる手動パーティショニングによく使用された）ですが、パーティションプルーニングと並行して動作します。各子パーティションの`CHECK`制約に基づいて、それらを除外します。ネイティブパーティショニングでは、`partition pruning`がこのタスクをより効率的に実行します。

`EXPLAIN`を使用することで、`partition pruning`が機能しているかどうかを確認できます。`Partition pruning: …`という行が表示されていれば、機能しています。

4. 実践的なヒントと個人的な経験

パーティショニングを直接扱った経験から、いくつか小さながらも効果的なアドバイスを共有したいと思います。

  • いつ使用すべきか:

    • テーブルサイズが非常に大きい（数千万レコード以上）で、引き続き増大している場合。
    • クエリが特定の日付範囲または値のセットに基づいて頻繁にデータをフィルタリングする場合。
    • 古いデータを頻繁かつ迅速に削除またはアーカイブする必要がある場合。
  • いつ慎重に検討すべきか:

    • テーブルがまだ小さく、パフォーマンスの問題を引き起こすほどではない場合。パーティションを管理するコストが、得られるメリットを上回る可能性があります。
    • クエリがパーティションキーでフィルタリングされることがめったにない場合。その場合、PostgreSQLはすべてのパーティションをスキャンする必要があり、オーバーヘッドのために速度が低下する可能性があります。
  • パーティションキー（Partition Key）を賢く選択する: これが重要な要素です。理想的なパーティションキーは、増加傾向にある列（`created_at`のような時間）や、離散的でめったに変化しない値を持つ列（`region`など）です。値が過度に分散している列や頻繁に変化する列を選択することは避けてください。
  • パーティションサイズ: 神聖な数字はありません。パーティションが小さすぎると、管理オーバーヘッドが発生します（ファイルが多すぎたり、追跡すべき子テーブルが多すぎたり）。パーティションが大きすぎると、パフォーマンス上のメリットが失われます。私は通常、各パーティションのサイズを数GBから数十GB、または数百万から数千万レコードを目指しています。ログテーブルの場合、月ごとのパーティションが良好な選択肢です。
  • データに関する個人的な経験: 作業中に、パーティション化されたテーブルにインポートするために、データを迅速に変換する必要がある場合があります。特に、入力データがCSVで、`JSONB`列に挿入するためにJSON形式が必要な場合などです。そのような場合、私はよくtoolcraft.app/ja/tools/data/csv-to-jsonのコンバーターを使用します。これはブラウザで直接実行されるため、機密データの漏洩を心配する必要がなく、望ましい構造に従ってパーティション化されたテーブルにデータを挿入する際に非常に便利で安全です。
  • パフォーマンスの監視: パーティショニングを展開した後、システムと重要なクエリのパフォーマンスを常に監視してください。`pg_stat_statements`とEXPLAIN ANALYZEは、パーティショニングが期待通りに機能し、メリットをもたらしていることを確認するために不可欠なツールです。

結論

PostgreSQLにおけるテーブルパーティショニングは、本番環境で膨大なデータテーブルを管理するために不可欠な強力な技術です。クエリ速度を驚異的に高速化するだけでなく、データのメンテナンスと管理も簡素化します。データベースを最適化し、システムパフォーマンスを向上させるために、今日からパーティショニングの適用を始めましょう！

Share:
Tags: