第13回｜スケールと外の世界

1台で足りなくなったときの引き出し（レプリケーション・パーティション・シャーディング）と、RDBMS を選ばないという判断。最終回として、スケール手法とデータストアの選択基準を俯瞰する。

この回のねらい

これまでの12回は、一貫して「PostgreSQL 1台をどう正しく・速く・安全に使い切るか」を扱ってきた。最終回は視野を1段階広げ、「1台では足りなくなったとき、何をどの順で足すか」と「そもそも RDBMS が最適でない仕事をどう見分けるか」を俯瞰する。ここで学ぶ手法（レプリケーション・パーティション・シャーディング・OLTP/OLAP 分離・NoSQL/ベクトルDB）は、いずれも万能薬ではなく、それぞれ固有のコストと引き換えに特定の問題を解く道具である。最重要のメッセージは1つ、「早すぎる分散化を避ける」 ――索引・実行計画・運用という単一ノードの武器を使い切る前に分散へ逃げると、解けたはずの問題を桁違いに難しくしてしまう。

到達目標

レプリケーション・パーティション・シャーディングの違いを説明し、状況に応じて使い分けられる。
ストリーミング（物理）レプリケーションと論理レプリケーションの違い、同期／非同期の違いを説明できる。
宣言的パーティショニングで大きな表を月次分割し、期間指定クエリで partition pruning（不要なパーティションが実行計画から消えること）が効くことを EXPLAIN で確認できる。
パーティションテーブルの主キー・一意制約が、なぜパーティションキー列を含まねばならないかを説明できる。
CAP と結果整合性を「壊れている」ではなく「設計上の選択」として説明できる。
OLTP と OLAP を分離する理由（行指向 vs 列指向、集計を本番で回す害）を説明できる。
NoSQL・ベクトルDB（pgvector）を「いつ選ぶか」を語れる。

前提と準備

第1回で構築した PostgreSQL 16 系のデータベースに、共通スキーマとデータが投入済みであることを前提にする。今回の主役は約1,000万行の events（アクセスログ）である。パーティションの実験は既存の events をそのまま作り替えるため、心配なら別データベースか、events のダンプを取ってから進めるとよい。

ベクトルDBの節では pgvector 拡張を使う。導入済みでなくても本文は読み進められるが、手を動かすなら次で有効化する（拡張のインストール自体は OS のパッケージ管理側の作業になる）。

-- pgvector を使う場合のみ
CREATE EXTENSION IF NOT EXISTS vector;

現在の events の規模を確認しておく。

psql -d postshop -c "SELECT count(*), min(occurred_at), max(occurred_at) FROM events;"

  count    |         min          |         max
-----------+----------------------+----------------------
 10000000  | 2023-01-01 00:00:... | 2024-12-31 23:59:...
(1 row)

この回で新しく出てくる用語

3つのスケール手法は名前が似ていて混同しやすいので、違いを先に一覧で置く。回をまたいで使う語は用語集にもまとめている。

用語	一行での説明
垂直スケール	より強いマシンへ移行して1台の能力を上げること
水平スケール（スケールアウト）	台数を増やして、負荷やデータを複数のサーバに分けること
パーティショニング	論理的には1つの表を、1台の中で複数の子テーブルに分割すること
partition pruning	条件に無関係なパーティションを、実行計画から丸ごと除外すること
レプリケーション	同じデータを複数台のサーバに複製すること
シャーディング	異なるデータを複数台に分割して配置すること
CAP定理	ネットワーク分断時に、一貫性と可用性は同時には満たせないという定理
結果整合性	更新が即座には行き渡らないが、時間が経てば全ノードが同じ値に収束する保証
OLTP / OLAP	小さく多数のトランザクション処理と、大量行を舐める分析処理
`Append`	複数の子テーブルの結果を順に連結する実行計画のノード
`DO $$ ... $$`	関数を定義せずに手続き型のブロックをその場で実行する構文

本編

まず1台を使い切る ― スケールの前提

スケールの話に入る前に、順序を確認する。分散システムは、単一ノードでは決して得られない可用性やスループットを与える代わりに、整合性・運用・デバッグの難しさをまとめて背負い込む。したがって最初の問いは常に「本当に1台では足りないのか」である。多くの「遅い」は分散不足ではなく、次のいずれかで解ける。

索引が足りない／効いていない。適切なインデックスは、全件スキャンを数千分の一に減らす（第7回）。
実行計画が悪い。EXPLAIN (ANALYZE, BUFFERS) で実際のボトルネックを特定する（第8回）。
運用で見えていない。pg_stat_statements で「どのクエリが総時間を食っているか」を定点観測する（第12回）。

これらを使い切ったうえで、なお1台の CPU・メモリ・ディスク I/O が限界なら、まず垂直スケール （より強いマシンへの移行）を検討する。垂直スケールは分散に伴う整合性問題を一切生まないため、コストが許す限り最も安価な選択肢である。以下で扱うのは、1台の中で表を分割するパーティションと、台数を増やして負荷やデータを複数のサーバに分ける水平スケール（スケールアウト。レプリケーションとシャーディングがこれにあたる）で、いずれも垂直スケールでも足りなくなった段階の道具として読む。この順序を飛ばすことが、本章の「つまずき」の筆頭に挙げる早すぎる分散化である。

スケール手法の全体像を、解く問題の軸で整理しておく。

手法	主に解く問題	台数	主なコスト
パーティション	1台の中の巨大な表の管理・スキャン量	1台	設計とキー選定
レプリケーション	読み負荷の分散・可用性（冗長化）	複数台（同じデータ）	結果整合性（レプリケーション遅延）
シャーディング	書き込み負荷・総データ量の分散	複数台（異なるデータ）	クロスシャード JOIN／トランザクション

パーティション ― 1台の中で大きな表を分割する

パーティショニングは、論理的には1つの表を、物理的には複数の子テーブル（パーティション）に分割する仕組みである。台数は増やさない（1台の中の話）。PostgreSQL の宣言的パーティショニングでは、親テーブルを「どの列をキーに、どう分割するか」だけ宣言し、実データは各パーティションに格納される。 events は時系列ログなので、occurred_at を使った**レンジ分割（月次）**が定石である。

既存の表は後からパーティション化できない（ALTER TABLE ... PARTITION BY は存在しない）。そこで「既存表を退避 → パーティション親を作成 → 子を作成 → データを移送」という手順を踏む。

-- 1. 既存の events を退避する
ALTER TABLE events RENAME TO events_old;

-- 2. occurred_at のレンジでパーティション親を定義する
CREATE TABLE events (
  id           bigint      GENERATED BY DEFAULT AS IDENTITY,
  customer_id  bigint      REFERENCES customers(id),
  event_type   text        NOT NULL,
  occurred_at  timestamptz NOT NULL
) PARTITION BY RANGE (occurred_at);

親テーブルにはデータを直接持たせず、月ごとの子パーティションを作る。レンジは下限が含み・上限が含まない（[FROM, TO)）ので、隣接する月を隙間なく・重なりなく並べられる。

CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');   -- 1月ぶん
CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');   -- 2月ぶん
CREATE TABLE events_2024_03 PARTITION OF events
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- ... 以下、必要な月ぶんを作る

24か月ぶんを手書きするのは現実的でない。DO ブロック（関数を定義せずに手続き型のコードをその場で実行する構文）でまとめて生成する。format は書式から文字列を組み立てる関数で、%L はSQLのリテラルとして、%s はそのままの文字列として値を埋め込む。

DO $$
DECLARE
  m date := '2023-01-01';
BEGIN
  WHILE m < '2025-01-01' LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS events_%s PARTITION OF events
         FOR VALUES FROM (%L) TO (%L)',
      to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
    m := (m + interval '1 month')::date;
  END LOOP;
END $$;

どのパーティションにも当てはまらない行が来ると INSERT は失敗する。想定外の日付を受け止める デフォルトパーティションを用意しておくと安全である。

CREATE TABLE events_default PARTITION OF events DEFAULT;

準備ができたら、退避した表からデータを移す。GENERATED BY DEFAULT AS IDENTITY は明示値の挿入を許すので id をそのまま持ち込めるが、その場合はシーケンスを最大値に追いつかせる必要がある。

INSERT INTO events (id, customer_id, event_type, occurred_at)
SELECT id, customer_id, event_type, occurred_at FROM events_old;

-- IDENTITY のシーケンスを既存最大値に合わせる（次の自動採番が衝突しないように）
SELECT setval(pg_get_serial_sequence('events', 'id'),
              (SELECT max(id) FROM events));

DROP TABLE events_old;   -- 移送を確認してから

主キー・一意制約はパーティションキーを含む必要がある

パーティションテーブルに主キーや一意制約を張るとき、その制約はパーティションキー列を必ず含まねばならない。含めずに PRIMARY KEY (id) を張ろうとすると、次のように拒否される。

ALTER TABLE events ADD PRIMARY KEY (id);

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  PRIMARY KEY constraint on table "events" lacks column "occurred_at"
        which is part of the partition key.

これは実装の都合ではなく必然である。一意性はパーティションをまたいで保証されず、各パーティション内でしか担保されない。もし id だけで一意にできてしまうと、「どのパーティションを見れば重複が分かるか」を決められない。だから一意制約はパーティションキーを含み、「同じキー値の行は必ず同じパーティションに入る」ことを利用して各パーティション内の一意性で全体の一意性を成立させる。events に主キーを持たせるなら、複合キーにする。

ALTER TABLE events ADD PRIMARY KEY (id, occurred_at);

partition pruning ― 不要なパーティションを計画から消す

パーティション分割の最大の狙いが partition pruning（パーティションの刈り込み）である。WHERE がパーティションキーで範囲を絞っていると、プランナは無関係なパーティションを実行計画から丸ごと除外する。3月ぶんだけを見るクエリを EXPLAIN すると、走査対象は events_2024_03 1つに絞られる。

EXPLAIN
SELECT count(*)
FROM events
WHERE occurred_at >= '2024-03-01' AND occurred_at < '2024-04-01';

 Aggregate  (cost=... rows=1 width=8)
   ->  Seq Scan on events_2024_03 events  (cost=... rows=... width=0)
         Filter: ((occurred_at >= '2024-03-01 00:00:00+09')
              AND (occurred_at <  '2024-04-01 00:00:00+09'))
(3 rows)

24か月ぶんのパーティションがあっても、計画に現れるのは1つだけである。対照的に、パーティションキーで絞らないクエリ（例えば event_type だけで絞る）は、全パーティションを走査する。

EXPLAIN
SELECT count(*) FROM events WHERE event_type = 'purchase';

 Aggregate  (cost=...)
   ->  Append  (cost=...)
         ->  Seq Scan on events_2023_01 events_1  (...)
         ->  Seq Scan on events_2023_02 events_2  (...)
         ...
         ->  Seq Scan on events_2024_12 events_24  (...)
         ->  Seq Scan on events_default  events_25 (...)
(28 rows)

Append は複数の子テーブルの結果を順に連結するノードで、その下に全パーティションが並ぶのが「刈り込みが効いていない」状態である。pruning は既定で有効（enable_partition_pruning = on）で、宣言的パーティショニングでは追加設定なしに働く。定数で絞った場合はプラン時に刈り込まれ、プリペアドステートメントのパラメータや実行時にしか値が定まらない場合は実行時刈り込みが働き、計画に Subplans Removed: N と現れる。実行計画の読み方の詳細は第8回を参照。

分割のもう1つの恩恵 ― 古いデータの高速な廃棄

月次パーティションは、保持期間を過ぎた古いログを捨てる運用と相性がよい。DELETE は行を1件ずつ削除して肥大化（bloat）を招くが、パーティションの DROP は表を丸ごと外すメタデータ操作で、ほぼ一瞬で終わる。オンラインで安全に外すなら CONCURRENTLY を使う。

ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;  -- ロックを最小化して切り離す
DROP TABLE events_2023_01;                                       -- 切り離したものを破棄

月次のパーティション作成・古い月の廃棄を自動化するなら、拡張 pg_partman が定番である。パーティションの事前作成（プリメイク）や保持期間（retention）に基づく自動 DETACH/DROP を担う。本講座では概観に留めるが、時系列データを継続運用するなら導入を検討する価値がある。

レプリケーション ― 同じデータを複数のサーバに

レプリケーションは、あるサーバ（プライマリ）の変更を別のサーバ（スタンバイ／レプリカ）へ複製し、 同じデータを複数台に持つ仕組みである。主な目的は2つ、可用性（プライマリが落ちてもスタンバイに昇格させて継続する冗長化）と、読み込み負荷の分散である。PostgreSQL には方式が2系統ある。

観点	ストリーミング（物理）レプリケーション	論理レプリケーション
複製する単位	WAL（クラスタ全体をバイト単位で複製）	行の変更（テーブル単位で選べる）
対象の粒度	クラスタ丸ごと。一部テーブルだけは不可	発行（publication）で選んだテーブルのみ
複製先の版	同一メジャーバージョン必須	異なるメジャーバージョン間も可
複製先への書き込み	不可（読み取り専用のホットスタンバイ）	可（別クラスタとして書き込める）
主な用途	HA（High Availability、冗長化）・読み取りレプリカ	版アップグレード・特定テーブルの連携・DWH への配送

ストリーミングレプリケーションはプライマリの WAL（更新ログ）をスタンバイへ送り続け、スタンバイはそれを適用してプライマリの複製を保つ。スタンバイは既定でホットスタンバイとして読み取りクエリを受けられる（hot_standby = on）。論理レプリケーションは発行側で CREATE PUBLICATION、購読側で CREATE SUBSCRIPTION を宣言し、行単位の変更を論理的に転送する。

-- 発行側（プライマリ、wal_level = logical が必要）
CREATE PUBLICATION pub_events FOR TABLE events;

-- 購読側（別クラスタ）
CREATE SUBSCRIPTION sub_events
  CONNECTION 'host=primary dbname=postshop'
  PUBLICATION pub_events;

同期と非同期

プライマリがコミットを返す前にスタンバイの受信を待つかどうかで、同期・非同期が分かれる。

非同期（既定）: プライマリはスタンバイの応答を待たずにコミットを返す。速いが、プライマリが故障した瞬間に未達の更新があると、フェイルオーバー時にごく直近の更新を失う可能性がある。
同期: プライマリは指定したスタンバイが更新を受け取る（または適用する）まで待ってからコミットを返す。データ損失は防げるが、1コミットごとにネットワーク往復のレイテンシが乗る。

# プライマリの postgresql.conf（同期レプリケーションの例）
synchronous_standby_names = 'standby1'
synchronous_commit = on

「絶対に失えないデータか、多少のレイテンシと引き換えにするか」を要件で決める。多くのシステムは非同期を基本に、重要な系だけ同期にする。

読み書き分離（read/write splitting）

ホットスタンバイを使うと、書き込みはプライマリ、読み込みはレプリカへ振り分けられる。読み取りの多い EC のようなワークロードでは、レプリカを増やすほど読み取りスループットを水平に伸ばせる。

                     write (INSERT / UPDATE / DELETE)
  app -------------------------------------------------> primary
   |                                                         |
   |   read (SELECT)                                         |  WAL
   +----------------------------> replica 1 <----------------+
   |                                                         |
   +----------------------------> replica 2 <----------------+

app はアプリケーション、primary はプライマリ、replica 1/2 はレプリカである。primary 側から各レプリカへ向かう <---- が WAL ストリーミングの流れを表す。要点は次の一文に尽きる。アプリはコマンドの種類で接続先を切り替え、更新系はプライマリ1台へ、参照系は複数のレプリカへ分散する。プライマリからレプリカへは WAL が流れ続ける。ただし非同期レプリケーションではレプリケーション遅延があり、プライマリに書いた直後にレプリカを読むとまだ反映されていないことがある（read-your-writes 問題）。「自分の直後の更新を確実に読む必要がある処理はプライマリから読む」など、遅延を前提にした設計が要る。この遅延は次節の結果整合性そのものである。

シャーディング ― 複数台にデータを分散する

レプリケーションが「同じデータを複製する」のに対し、シャーディングは異なるデータを複数台に分割配置する水平分割である。customer_id などのシャードキーで行き先ノードを決め、各ノードは全体の一部だけを持つ。総データ量と書き込み負荷を台数で割れるため、単一プライマリの書き込み上限を超えたい場合の最終手段になる。

代償は大きい。シャードをまたぐ操作が急に難しくなる。

クロスシャード JOIN: 結合相手が別ノードにあると、単純な結合が全ノードへの問い合わせ（fan-out）と結果のマージになる。シャードキーが揃わない結合は特に高くつく。
クロスシャード・トランザクション: 複数ノードにまたがる更新の原子性を保つには分散トランザクション（2相コミットなど）が要り、単一ノードの BEGIN ... COMMIT（第9回）ほど単純でも安価でもない。
一意性・集計: グローバルな一意採番や全体集計が、ノード横断の調整を必要とする。

したがってシャードキーは「大半のクエリが単一シャードで閉じる」ように選ぶのが鉄則で、ここを外すと分散の利益を fan-out のコストが食い潰す。PostgreSQL でシャーディングを実装するなら、拡張 Citus （分散テーブルとクロスシャードクエリのプランニングを提供）や、アプリ層でのシャーディングが選択肢になる。いずれにせよ、パーティションやレプリカで足りるうちはシャーディングに手を出さない。

CAP と結果整合性 ― 「壊れている」ではなく設計上の選択

複数ノードに同じデータを持つ分散システムには、避けられないトレードオフがある。CAP 定理は、ネットワーク分断（Partition、ノード間の通信が途切れる状況）が起きたとき、システムは次の2つを同時には満たせない、と述べる。

一貫性（Consistency）: どのノードを読んでも常に最新の同じ値が返る。
可用性（Availability）: どのノードも（多少古くても）必ず応答を返す。

分断は現実の分散システムでは起こりうる前提なので、設計者は分断時に「一貫性を守って一部の応答を拒む（CP）」か「応答を続ける代わりに一時的に古い値を許す（AP）」かを選ぶことになる。AP 寄りの選択が生むのが結果整合性（eventual consistency）――更新は即座に全ノードへ行き渡らないが、時間が経てば全ノードが同じ値に収束する、という保証である。前節のレプリケーション遅延はまさにこれで、レプリカが一時的に古い値を返すのは故障ではなく、非同期レプリケーションという設計上の選択の帰結である。

初学者は結果整合性を「データがバグっている／壊れている」と受け取りがちだが、これは誤解である。「いつ収束するか」「収束するまで何が見えうるか」が定義され、その範囲でアプリを設計するのが正しい向き合い方である。逆に、強い一貫性が必要な処理（在庫の二重引き当て防止、残高の整合など）を結果整合性の系に載せてはならない。整合性は要件ごとに選ぶものであり、「常に最強」を求めれば可用性やレイテンシで払わされる。

OLTP と OLAP ― 用途で分ける

同じ「データベースを使う仕事」でも、性質の異なる2種類がある。

OLTP（オンライン・トランザクション処理）: 1件の注文、1人のログインといった、小さく多数のトランザクション。少数行の読み書き・点検索が中心。本講座で扱ってきた orders への書き込みや顧客ごとの参照はこれである。
OLAP（オンライン分析処理）: 「月次の地域別売上」「全期間の購買傾向」など、大量行を舐めて集計する分析。少数の列を、非常に多くの行にわたって読む。

この2つは求めるストレージ構造が正反対である。

	行指向（row-oriented）	列指向（columnar）
物理配置	1行の全列をまとめて格納	同じ列の値を連続して格納
得意	1行・数行の読み書き（OLTP）	特定列を全行集計（OLAP）
圧縮	効きにくい	同種の値が並ぶため高圧縮・高速スキャン
代表	PostgreSQL のヒープ	列指向 DWH（BigQuery, Redshift, ClickHouse, DuckDB 等）

PostgreSQL のヒープは行指向で、OLTP に最適化されている。ここで重い OLAP クエリ（全期間の全件集計など）を本番で回すと、実害が出る。大量スキャンが共有バッファを本番データで温めていたキャッシュから追い出し、ディスク I/O を占有する。さらに長時間走るクエリは古いスナップショットを保持し続け、 VACUUM が不要行を回収できず肥大化を招く（第9回・第12回）。結果として、本来速いはずの OLTP のレイテンシが分析クエリに巻き込まれて悪化する。

対策は「集計を本番 OLTP から逃がす」ことである。段階に応じて選ぶ。

まずは読み取りレプリカで集計する。本番プライマリのキャッシュと I/O を汚さずに済む。
定期的な分析なら ETL（Extract/Transform/Load）で列指向の**データウェアハウス（DWH）**へデータを移し、そこで集計する。分析専用に最適化された環境で、OLTP を一切圧迫しない。

「本番 DB で GROUP BY を1本流したら全体が重くなった」は、OLTP と OLAP を分けていないことの典型的な症状である。

RDBMS を選ばない判断 ― NoSQL とベクトルDB

最後に、そもそも PostgreSQL（RDBMS）が最適でない仕事の見分け方に触れる。ここでも原則は同じで、「新しくて速そうだから」ではなく「関係モデルで無理が出るから」選ぶ。

NoSQL をいつ選ぶか。 NoSQL は「関係モデル・SQL・強い一貫性の一部を手放す代わりに、別の何かを得る」データストアの総称である。代表的な動機は次のいずれかである。

スキーマが定まらない／頻繁に変わるドキュメントを大量に扱う（ドキュメント指向）。
単純なキー引きを極端な高スループット・低レイテンシでこなしたい（キーバリュー、キャッシュ）。
単一 RDBMS の書き込み上限を超える規模で、結果整合性を許容できる（ワイドカラム等）。
関係の探索そのものが主役で、多段の JOIN が本質的に重い（グラフDB）。

ただし「スキーマ柔軟性が欲しい」程度なら、PostgreSQL の jsonb 型が多くを吸収する。RDBMS のトランザクション・制約・SQL を保ったままドキュメント的な柔軟性を得られるため、NoSQL へ移る前に jsonb で足りないかを必ず確認する。NoSQL は銀の弾丸ではなく、失う一貫性・失う JOIN・失う SQL の対価を払える場面でのみ選ぶ。

ベクトルDB（pgvector）をいつ選ぶか。 「意味の近さ」で検索したいとき――自然文の類似検索、推薦、 RAG（検索拡張生成）など――に使う。テキストや画像を機械学習モデルで数百〜数千次元の埋め込み（embedding）ベクトルに変換し、ベクトル間の距離が近いものを「意味的に近い」とみなして探す。 PostgreSQL では拡張 pgvector が vector 型と**近似最近傍探索（ANN）**の索引を提供するので、別の専用DBを立てずに既存の関係データと同居させられる。

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE product_embeddings (
  product_id bigint PRIMARY KEY REFERENCES products(id),
  embedding  vector(768)                    -- 768次元の埋め込み（モデル依存）
);

-- HNSW インデックス（近似最近傍探索。cosine 距離で近い順を高速化）
CREATE INDEX ON product_embeddings USING hnsw (embedding vector_cosine_ops);

-- 商品42に意味的に近い商品を10件（<=> は cosine 距離）
SELECT product_id
FROM product_embeddings
ORDER BY embedding <=> (SELECT embedding FROM product_embeddings WHERE product_id = 42)
LIMIT 10;

厳密な全探索は次元数×行数に比例して重いので、HNSW（Hierarchical Navigable Small World、階層化した近傍グラフをたどる方式）や IVFFlat（Inverted File with Flat compression、ベクトルを事前にクラスタへ振り分けて候補を絞る方式）といった ANN 索引で「多少の取りこぼしを許して高速に近いものを返す」のが実務の定石である。完全一致・範囲検索・集計は従来の B-tree（第7回）の仕事で、ベクトル索引はあくまで「意味の近さ」専用の別の道具だと切り分ける。

ハンズオン

課題1: events を月次パーティション化し、partition pruning を確認する

やること

既存の events（約1,000万行）を、occurred_at をキーにした月次のレンジパーティションへ作り替える。
特定月を範囲指定した集計クエリを EXPLAIN し、走査対象が1パーティションに刈り込まれることを確認する。
パーティションキーで絞らないクエリでは全パーティションが走査されることを対比で確認する。

想定解答

本編の手順どおり、退避 → 親作成 → 子作成（DO ブロックで24か月＋デフォルト）→ 移送を行う。

ALTER TABLE events RENAME TO events_old;

CREATE TABLE events (
  id           bigint      GENERATED BY DEFAULT AS IDENTITY,
  customer_id  bigint      REFERENCES customers(id),
  event_type   text        NOT NULL,
  occurred_at  timestamptz NOT NULL
) PARTITION BY RANGE (occurred_at);

DO $$
DECLARE m date := '2023-01-01';
BEGIN
  WHILE m < '2025-01-01' LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS events_%s PARTITION OF events
         FOR VALUES FROM (%L) TO (%L)',
      to_char(m, 'YYYY_MM'), m, (m + interval '1 month')::date);
    m := (m + interval '1 month')::date;
  END LOOP;
END $$;

CREATE TABLE events_default PARTITION OF events DEFAULT;

INSERT INTO events (id, customer_id, event_type, occurred_at)
SELECT id, customer_id, event_type, occurred_at FROM events_old;
SELECT setval(pg_get_serial_sequence('events','id'), (SELECT max(id) FROM events));

\d+ events で親と全パーティションの一覧を確認できる。次に刈り込みを見る。

EXPLAIN
SELECT count(*)
FROM events
WHERE occurred_at >= '2024-03-01' AND occurred_at < '2024-04-01';

 Aggregate
   ->  Seq Scan on events_2024_03 events
         Filter: ((occurred_at >= '2024-03-01 00:00:00+09')
              AND (occurred_at <  '2024-04-01 00:00:00+09'))
(3 rows)

計画に現れるのは events_2024_03 だけである。これが partition pruning が効いている証拠で、24か月ぶんのうち関係する1か月しか読まない。対して、パーティションキーを使わない条件は全パーティションを舐める。

EXPLAIN SELECT count(*) FROM events WHERE event_type = 'purchase';

 Aggregate
   ->  Append
         ->  Seq Scan on events_2023_01 events_1
         ->  Seq Scan on events_2023_02 events_2
         ...
         ->  Seq Scan on events_default  events_25

Append の下に全パーティションが並ぶ。両者の差が、そのまま「月次で切り、月で絞る」設計の効果である。なぜこれで速くなるかは、読むべきデータブロック量そのものが激減するからで、索引以前にスキャン対象を物理的に消せる点がパーティションの本質である。

課題2: 読み書き分離の構成を図で設計する

やること

読み取りの多い EC ワークロードを想定し、書き込みと読み込みを分離する構成を図と一文で設計する。レプリケーション遅延をどう扱うかまで言及する。

想定解答

構成は「書き込みはプライマリ1台、読み込みは複数レプリカへ分散し、プライマリからレプリカへは WAL を非同期ストリーミングする」である。

                              write
  app -------------------------------------------------> primary
   |                                                         |
   |   read                                                  |  WAL（非同期）
   +----------------------------> replica 1 <----------------+
   |                                                         |
   +----------------------------> replica 2 <----------------+

設計上の判断は次の3点である。第一に、更新系（INSERT/UPDATE/DELETE）は必ずプライマリへ送り、参照系（SELECT）をレプリカへ振る。第二に、レプリカは読み取り専用のホットスタンバイなので、読み負荷はレプリカ台数を増やすことで水平に伸ばせる。第三に、非同期レプリケーションにはレプリケーション遅延があるため、「注文直後に自分の注文一覧を表示する」ような自分の書き込みを直後に読む処理はプライマリから読むことにして、read-your-writes 問題を避ける。可用性の観点では、プライマリ障害時にレプリカを昇格させてフェイルオーバーする。データ損失を1件も許さない系だけ同期レプリケーションにし、その分のコミットレイテンシ増を受け入れる、という上乗せも設計に含めてよい。

よくあるつまずきと対処

症状	原因	対処
遅いのですぐ分散を検討してしまう	索引・実行計画・運用という単一ノードの手を使い切っていない	まず第7回索引・第8回計画・第12回監視、次に垂直スケール、最後に分散
`ALTER TABLE events PARTITION BY ...` が通らない	既存表は後からパーティション化できない	退避（RENAME）→ パーティション親を新規作成 → データ移送、の手順を踏む
`PRIMARY KEY (id)` が拒否される	一意制約はパーティションキー列を含む必要がある	`PRIMARY KEY (id, occurred_at)` のようにキー列を含めた複合キーにする
`EXPLAIN` で全パーティションが走査される	`WHERE` がパーティションキー（`occurred_at`）で絞っていない	期間指定は必ず `occurred_at` の範囲で書く。関数で包むと刈り込みが効かないことがある
どのパーティションにも入らず `INSERT` が失敗する	範囲外の日付でデフォルトパーティションが無い	`DEFAULT` パーティションを用意する。またはその月のパーティションを追加する
レプリカが古い値を返す＝壊れていると誤解する	非同期レプリケーションの結果整合性（遅延）を故障だと捉えている	遅延は設計上の選択。直後読みが必要な処理はプライマリから読む／必要なら同期にする
本番 DB で集計を回したら全体が重くなった	OLTP に OLAP を混在させ、キャッシュ汚染・I/O 占有・長時間スナップショットが起きた	集計は読み取りレプリカへ、または ETL で列指向 DWH へ逃がす

到達度チェックリスト

レプリケーション（同じデータを複数台）・パーティション（1台内で分割）・シャーディング（異なるデータを複数台）の違いを、解く問題ごとに説明できる。
ストリーミング（物理）と論理レプリケーションの違い、同期と非同期の違いを説明できる。
宣言的パーティショニングで events を月次分割する DDL を、退避から移送まで書ける。
パーティションの主キーがパーティションキー列を含まねばならない理由を説明できる。
期間指定クエリを EXPLAIN し、partition pruning が効いた計画とそうでない計画を見分けられる。
CAP と結果整合性を「設計上の選択」として説明でき、強い一貫性が要る処理と切り分けられる。
OLTP と OLAP を分ける理由（行指向 vs 列指向、本番で集計を回す害）を説明できる。
NoSQL・pgvector を「いつ選ぶか」を、失うものと得るものの観点で語れる。

宿題（次回までの自習）

課題1で作った月次パーティションに対し、ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY; で最古の月を切り離してから DROP TABLE する。同じ量を DELETE FROM events WHERE occurred_at < '2023-02-01'; で消す場合と比べ、所要時間と後片付け（VACUUM）の要否がどう違うかを考える。
events に対し、occurred_at >= '2024-06-01' AND occurred_at < '2024-07-01' の集計を、パーティション化前（events_old があれば）と後で EXPLAIN (ANALYZE, BUFFERS) を取り、読んだバッファ量（Buffers:）がどれだけ減ったかを比べる。
自分が関わっている（または想像上の）システムで、「本番 OLTP で回してしまっている集計クエリ」を 1つ挙げ、それを読み取りレプリカ／DWH のどちらへ、どう逃がすかを2〜3行で設計する。

次回への接続

本講座はここで一区切りとなる。全13回を貫いてきたのは、「まず1台の PostgreSQL を、正しいスキーマと型・制約で設計し（第1〜6回）、索引と実行計画で性能を出し（第7・8回）、トランザクションで整合性を守り（第9回）、ロール・RLS・境界防御で安全を保ち（第10・11回）、監視と手順で運用する（第12回）」という一本道である。最終回のスケールと分散は、その土台を使い切ってなお足りないときに初めて開く引き出しであり、順序を守る限り強力な道具になる。逆に土台を飛ばして分散へ走れば、単一ノードなら索引1本で片付いた問題を、結果整合性とクロスシャードの迷路にしてしまう。データベースの上達とは、より難しい仕組みを増やすことではなく、いま抱えている問題を解く最小の道具を正しい順で選べるようになることである。

参考

PostgreSQL 公式ドキュメント: "Data Definition" > "Table Partitioning"（宣言的パーティショニング・ partition pruning・パーティションキーと一意制約）
PostgreSQL 公式ドキュメント: "Server Administration" > "High Availability, Load Balancing, and Replication"（ストリーミング／論理レプリケーション・同期と非同期・ホットスタンバイ）
PostgreSQL 公式ドキュメント: "SQL Commands" > CREATE PUBLICATION / CREATE SUBSCRIPTION（論理レプリケーション）
PostgreSQL 公式ドキュメント: "SQL Commands" > EXPLAIN（実行計画と Subplans Removed の読み方）
pg_partman / Citus の各ドキュメント（パーティション自動管理・分散テーブル。概観として）
pgvector の README（vector 型・HNSW/IVFFlat 索引・距離演算子 <-> <=> <#>）
CAP 定理の解説（分断耐性の下での一貫性と可用性のトレードオフを扱う入門記事全般）