会社情報
採用情報
お問い合わせ
Article

PostgreSQLで10TBのデータを扱って学んだチューニングの極意

高田晃太郎
PostgreSQLで10TBのデータを扱って学んだチューニングの極意

大容量データを扱うRDBMS運用は近年明らかに増加しており、PostgreSQLの採用も加速している。主要クラウドのベンチマークや公開事例を俯瞰しても、I/Oコストと復旧時間（RTO/RPO）の最適化がビジネスのレイテンシSLOを左右するという点で一致している²。10TB級PostgreSQLの運用では、WAL（書き込みログ）とチェックポイント、Autovacuum、統計情報の解像度、接続設計の4点に注力するのが定石だ。本稿では、PostgreSQL 15系、NVMe SSD＋XFS、PgBouncer併用といった一般的な構成を例に、環境依存であることを前提にしつつ、再現性の高い設定例と観測の要点を整理する。

10TB運用の現実と最大のボトルネックはどこにあるか

10TBの壁は、単にストレージ容量の問題ではない。WAL（書き込みのジャーナリング）、チェックポイントの発火条件、ストレージI/O帯域、Autovacuum（不要行の掃除）の捕捉速度、そして統計情報の精度が複雑に絡む²³。初期状態のチェックポイント間隔が短いと、スパイク的なfsyncバーストがアプリケーションのp95レイテンシを押し上げがちだ。WALを圧縮し、チェックポイントの分散とバックグラウンド書き込みを太くするだけで、CPU使用率の山とI/Oキューの滞留が目に見えて滑らかになることが多い²³。以下は効果の出やすい第一段階のpostgresql.conf例である。

# WAL/チェックポイント：バーストを避けるための分散
wal_compression = on
max_wal_size = '64GB'
min_wal_size = '8GB'
checkpoint_timeout = '20min'
checkpoint_completion_target = 0.9
wal_buffers = '256MB'
effective_io_concurrency = 256
maintenance_work_mem = '4GB'

変更の狙いは単純だ。チェックポイントを間延びさせてWALアーカイブを太くし、バックグラウンドでの書き出しを9割まで前倒しする。I/Oの細切れ書き込みを減らすだけでなく、後段のオブジェクトストレージへのWAL配送も均される。一般に、WALの流量を制御し、チェックポイントの仕事量を分散することで、ピーク帯のスループットやp95レイテンシの改善が報告されている²³。WALの流量を制御し、チェックポイントの仕事量を分散する。この原則は10TB級でこそ効く。

Autovacuumと膨張抑制は「速く・広く・賢く」

大容量環境では膨張を「許容する量」ではなく「発生させない速度」で語るべきだ。テーブルごとの更新頻度と行サイズに応じてスケールファクタを小さく取り、コストリミットを十分に与える。さらにHOT更新（同一ページ内の更新でインデックス再書きを避ける）を有効にするためのfillfactor調整が効く⁴。更新頻度の高いファクトテーブルでは次のように設定すると、HOT率の向上が期待できる。

ALTER TABLE fact_events SET (fillfactor = 80);
VACUUM (VERBOSE, ANALYZE) fact_events;
-- 監視例: autovacuumの進捗とbloat感
SELECT relname, n_dead_tup, vacuum_count
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC LIMIT 10;

Autovacuumは速く走らせる。待たせないためにautovacuum_vacuum_cost_limitを数千〜数万へ引き上げ、naptimeを短縮する。処理対象は広く取る。大型テーブルの先頭パーティションだけでなく末尾側も早めに当てる。賢く走らせるために更新密度の高いテーブルに専用パラメータを付与し、他と分離して考えると良い。結果としてインデックスのbloatも抑えられ、vacuum freezeの負債も軽くなる。

クエリ設計と統計情報の解像度を上げる

10TB級で致命的なのは、「賢そうに見えて外す」プランナーの選択だ。カーディナリティ推定の誤差は、全表走査や誤ったネストループとして顕在化し、秒単位の遅延を分単位へ膨らませる。ここで有効なのが拡張統計（CREATE STATISTICS）とdefault_statistics_targetの段階的引き上げである⁵⁶。複数列の相関を統計で持たせることで、結合順序が安定し、パーティションの絞り込みも計画段階で確実になる⁵。以下は拡張統計の一例だ。

-- 複合相関を統計として保持
CREATE STATISTICS stat_events (dependencies, mcv)
  ON user_id, event_type, occurred_at
  FROM fact_events;
ANALYZE fact_events;

-- 基本統計を全体的に強化
ALTER SYSTEM SET default_statistics_target = 500;
SELECT pg_reload_conf();

計画の安定性はEXPLAINで観察する。相関の明示後、選択性の高い条件から最初に当てにいく計画へ収束しやすい。JIT（実行時コンパイル）は長大な集計には効くが、短尺のOLTPでは逆にオーバーヘッドになることがある。jit_above_costを使い、重たさに応じて有効化の閾値を調整すると短いクエリの冷えが改善する。

-- 代表クエリの計画差分を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT user_id, count(*)
  FROM fact_events
 WHERE event_type = 'purchase'
   AND occurred_at >= now() - interval '7 days'
 GROUP BY user_id;

-- 短命クエリのJITを抑制
SET LOCAL jit_above_cost = 100000;

パーティション設計は統計と不可分だ。日次レンジでパーティションを切り、（従来のconstraint exclusionではなく）現行のパーティションプリuningとプランナの整合が取れるようにする。スキャン範囲が連続で、かつ時間順アクセスが多いテーブルにはBRIN（軽量な範囲インデックス）がよく効く⁷。末尾数日分の参照や範囲集計で、スキャンコストが目に見えて下がるケースは少なくない。

-- 日次レンジパーティションとBRIN
CREATE TABLE fact_events (
  id bigserial PRIMARY KEY,
  user_id bigint NOT NULL,
  event_type text NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb
) PARTITION BY RANGE (occurred_at);

-- 子テーブル例（自動化は外部ツールや拡張で）
CREATE TABLE fact_events_2024_09_01
  PARTITION OF fact_events FOR VALUES FROM ('2024-09-01') TO ('2024-09-02');

-- 時系列アクセス向けの軽量インデックス
CREATE INDEX ON fact_events_2024_09_01 USING BRIN (occurred_at) WITH (pages_per_range=64);
CREATE INDEX ON fact_events_2024_09_01 (event_type);

ストレージとOSの地ならしでI/Oの山をなくす

ソフトウェアの設定だけでは限界がある。NVMeのキュー深度、ファイルシステムの書き込みバリア、readahead、カーネルのdirty比率は、WALとチェックポイントの山谷に直結する。XFSの採用や書き込みの安定性を優先したカーネルパラメータの調整で、ピーク時のfsync時間のばらつきが抑えられることが多い⁸。以下は差分が大きかった最低限の設定例である。

# /etc/sysctl.d/99-postgres.conf
vm.swappiness = 1
vm.dirty_background_ratio = 5
vm.dirty_ratio = 10
vm.max_map_count = 262144
kernel.sched_migration_cost_ns = 5000000

# PostgreSQL側
huge_pages = try
wal_sync_method = fdatasync
synchronous_commit = on
random_page_cost = 1.1

ストレージの整合性はバックアップと復旧で真価が出る。RTOを現実的にするには、WALの並列リストアとベースバックアップの帯域確保が鍵となる。pgBackRestやWAL-Gで並列度を上げ、ネットワークスループットを常時監視する。10TB環境では復旧が「うまくいくか」ではなく「いつ終わるか」に変わる。レプリカ初期化の時間も同じ論理だ。

# pgBackRest例: 並列度とスロットリング
pgbackrest --stanza=main --type=full backup --process-max=8
pgbackrest --stanza=main restore --type=time --target="2025-08-01 12:00:00" --process-max=8

# 目標RTOや実効帯域に応じて所要時間を見積もる
# 復旧時間 ≒ (ベースバックアップ + WAL) / 並列帯域

書き込みの安定性は「fsyncの行列」を短くする発想で

チェックポイントの前倒しとdirty比率の抑制で、fsync待ちの行列は短くなる。巨大トランザクションを避け、トランザクション境界で小刻みにコミットするアプリケーション設計も、WALファイルのローテーションを平準化する。synchronous_commitはオンを基本とし、遅延を許容できる非同期処理のみディレイ付きに分離するなど、ビジネス要求に合わせた粒度で整える。

接続とアプリケーションのふるまいを正す

PostgreSQLはプロセス/コネクションモデルだ。接続を増やしても直線的にスループットは伸びない。PgBouncerでトランザクションプーリングを使えば、バックエンドは少数精鋭で回せる⁹。多くの事例で、max_connectionsを抑えてプール側での同時実行を確保する再設計により、文脈切り替えのオーバーヘッドを抑えられる。長時間アイドルやidle in transactionは、statement_timeoutとidle_in_transaction_session_timeoutの二重線で確実に落とす。

# postgresql.conf（アプリ抑制の基本線）
max_connections = 200
shared_buffers = '64GB'
work_mem = '64MB'
statement_timeout = '5s'
idle_in_transaction_session_timeout = '30s'
lock_timeout = '2s'
application_name = 'prod-core'

アプリケーション側では、タイムアウトとリトライ戦略が品質を決める。Pythonのasyncpgでの実装例を示す。タイムアウト、リトライ、タグ付けを最初から織り込み、計測の単位をコードに埋め込んでおくと分析が速い。

import asyncio
import asyncpg
from async_timeout import timeout

DSN = "postgresql://appuser:***@pgbouncer:6432/appdb"

async def fetch_counts(pool):
    async with pool.acquire() as conn:
        await conn.execute("SET application_name = 'svc-api';")
        await conn.execute("SET jit_above_cost = 100000;")
        try:
            async with timeout(3.0):
                return await conn.fetch(
                    """
                    SELECT user_id, count(*) AS c
                      FROM fact_events
                     WHERE event_type = $1
                       AND occurred_at >= now() - interval '7 days'
                     GROUP BY user_id
                     ORDER BY c DESC
                     LIMIT 100
                    """, 'purchase')
        except asyncio.TimeoutError:
            # observability: attach tags for SLO
            raise

async def main():
    pool = await asyncpg.create_pool(DSN, min_size=5, max_size=50, timeout=2.0)
    try:
        rows = await fetch_counts(pool)
        for r in rows:
            pass
    finally:
        await pool.close()

if __name__ == '__main__':
    asyncio.run(main())

Go/pgxを使う場合も同様に、コンテキストに期限をつける。クエリの寿命が上限を越えれば即座にキャンセルし、待ち行列を延ばさない。

package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    cfg, _ := pgxpool.ParseConfig("postgres://appuser:***@pgbouncer:6432/appdb?application_name=svc-api")
    cfg.MaxConns = 50
    pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
    if err != nil { log.Fatal(err) }
    defer pool.Close()

    ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
    defer cancel()

    rows, err := pool.Query(ctx, `SELECT user_id, count(*) FROM fact_events WHERE event_type=$1 AND occurred_at >= now() - interval '7 days' GROUP BY user_id ORDER BY 2 DESC LIMIT 100`, "purchase")
    if err != nil { log.Fatal(err) }
    defer rows.Close()

    for rows.Next() {
        var uid int64
        var c int64
        if err := rows.Scan(&uid, &c); err != nil { log.Fatal(err) }
        _ = fmt.Sprintf("%d:%d", uid, c)
    }
    if rows.Err() != nil { log.Fatal(rows.Err()) }
}

接続プールの背後では、準備済み文のキャッシュとバインド変数の使い分けが効く。計画が安定しないクエリは、強制的にプラン再最適化の余地を残す。逆に、日々同型のクエリは準備済みで固めるとよい。どちらも監視のタグと合わせ、SLO違反の火の手が上がった時に原因特定が速くなる。

スロークエリは「短期的な回避」と「恒久対策」を分けて潰す

実運用では、重いクエリに遭遇した瞬間の回避と、根治のための設計改善を分けるのが現実的だ。前者はインデックスの即時追加や並列実行度の一時的引き上げで凌ぎ、後者は統計の拡張、パーティションの見直し、そしてアプリのアクセスパターン修正で根本から外科的に治す。例えば、日別集計のバッチが多段ネストループを引き起こしている場合、拡張統計とレンジパーティションの再設計で計画が安定し、処理時間が現実的な範囲へ収束することが多い。

成果とビジネスインパクト：速度はコストをも溶かす

技術的な改善は、ビジネスの現実に跳ね返る。ピーク帯のTPSやp95の短縮、SLA内のエラーレート低下、チェックポイント起因のジッタ減少による入札や配信の安定、さらにはインフラのダウンサイジングによるコスト最適化に結びつく可能性がある。これらの効果はワークロードと構成に大きく依存するため、事前に代表的なクエリとバックアップ/復旧経路で計測し、改善幅を見積もるのが健全だ。レプリカ初期化やポイントインタイムリカバリの所要時間も同じ論理で見通しが立つ。数字は「結果」ではなく「設計の意思決定を支える材料」として扱う。

最後に：原則はシンプル、実装は具体的に

10TBのPostgreSQLは難しく見えるが、原則は揺らがない。WALとチェックポイントで書き込みの山をならし、Autovacuumで膨張を先回りし、統計の解像度でプランを外さず、接続を秩序立てる。これらを現場の実装として具体化するための断片を、ここに置いた。あなたの現場ではどこから始めるのが最も費用対効果が高いだろうか。まずは代表的な重いクエリを一つ選び、EXPLAINとタイムアウトを携えて観察してほしい。小さな改善がレイテンシの尾を短くし、SLOを守り、ビジネスの速度を上げる。今日の一手が、来月のコスト構造を変えていく。

参考文献

  1. 公開事例・ベンダーブログ・ベンチマークの横断レビュー（2024–2025年、公開情報の範囲での一般傾向の整理）。
  2. EnterpriseDB. Tuning max_wal_size in PostgreSQL. https://www.enterprisedb.com/blog/tuning-maxwalsize-postgresql#:~:text=One%20of%20the%20biggest%20surprises,performance%20increase%20after%20tuning%20max_wal_size
  3. EnterpriseDB. Tuning shared_buffers and wal_buffers. https://www.enterprisedb.com/blog/tuning-sharedbuffers-and-walbuffers#:~:text=wal_buffers%3D64MB%20doubled%20performance%20as%20compared,of%20concurrent%20activity%2C%20higher%20values
  4. Crunchy Data. Postgres performance boost: HOT updates and fillfactor. https://www.crunchydata.com/blog/postgres-performance-boost-hot-updates-and-fill-factor#:~:text=For%20example%2C%20consider%20a%20table,lead%20to%20situations%20where%20an
  5. CYBERTEC. PostgreSQL CREATE STATISTICS: Advanced query optimization. https://www.cybertec-postgresql.com/en/postgresql-create-statistics-advanced-query-optimization/#:~:text=CREATE%20STATISTICS%3A%20Solving%20warehousing%20problems
  6. postgresqlco.nf. default_statistics_target parameter documentation. https://postgresqlco.nf/doc/en/param/default_statistics_target/#:~:text=Sets%20the%20default%20statistics%20target,stats
  7. Crunchy Data. PostgreSQL BRIN indexes: big data performance with minimal storage. https://www.crunchydata.com/blog/postgresql-brin-indexes-big-data-performance-with-minimal-storage#:~:text=Many%20applications%20today%20record%20data,lookups%2C%20analytical%20queries%2C%20and%20more
  8. Percona. Tune Linux kernel parameters for PostgreSQL optimization. https://www.percona.com/blog/tune-linux-kernel-parameters-for-postgresql-optimization/#:~:text=hardware,in%20most%20cases
  9. EnterpriseDB. Challenges of setting max_connections and why you should use a connection pooler. https://www.enterprisedb.com/postgres-tutorials/challenges-setting-maxconnections-and-why-you-should-use-connection-pooler#:~:text=It%20becomes%20apparent%20that%20while,to%20actually%20connect%20to%20pgbouncer