--- note: レプリケーション・パーティション・OLTP/OLAP分離・NoSQL/ベクトルDBの選択基準を俯瞰する created: 2026-08-14T10:00:00+09:00 --- # 第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 のパッケージ管理側の作業になる）。 ```sql -- pgvector を使う場合のみ CREATE EXTENSION IF NOT EXISTS vector; ``` 現在の `events` の規模を確認しておく。 ```bash psql -d postshop -c "SELECT count(*), min(occurred_at), max(occurred_at) FROM events;" ``` ```text count | min | max -----------+----------------------+---------------------- 10000000 | 2023-01-01 00:00:... | 2024-12-31 23:59:... (1 row) ``` ## この回で新しく出てくる用語 3つのスケール手法は名前が似ていて混同しやすいので、違いを先に一覧で置く。 回をまたいで使う語は[用語集](glossary.md)にもまとめている。 | 用語 | 一行での説明 | |---|---| | 垂直スケール | より強いマシンへ移行して1台の能力を上げること | | 水平スケール（スケールアウト） | 台数を増やして、負荷やデータを複数のサーバに分けること | | パーティショニング | 論理的には1つの表を、1台の中で複数の子テーブルに分割すること | | partition pruning | 条件に無関係なパーティションを、実行計画から丸ごと除外すること | | レプリケーション | 同じデータを複数台のサーバに複製すること | | シャーディング | 異なるデータを複数台に分割して配置すること | | CAP定理 | ネットワーク分断時に、一貫性と可用性は同時には満たせないという定理 | | 結果整合性 | 更新が即座には行き渡らないが、時間が経てば全ノードが同じ値に収束する保証 | | OLTP / OLAP | 小さく多数のトランザクション処理と、大量行を舐める分析処理 | | `Append` | 複数の子テーブルの結果を順に連結する実行計画のノード | | `DO $$ ... $$` | 関数を定義せずに手続き型のブロックをその場で実行する構文 | ## 本編 ### まず1台を使い切る ― スケールの前提 スケールの話に入る前に、順序を確認する。分散システムは、単一ノードでは決して得られない可用性や スループットを与える代わりに、整合性・運用・デバッグの難しさをまとめて背負い込む。したがって 最初の問いは常に「本当に1台では足りないのか」である。多くの「遅い」は分散不足ではなく、次のいずれか で解ける。 - **索引が足りない／効いていない**。適切なインデックスは、全件スキャンを数千分の一に減らす（[第7回](07-storage-indexes-collation.md)）。 - **実行計画が悪い**。`EXPLAIN (ANALYZE, BUFFERS)` で実際のボトルネックを特定する（[第8回](08-explain.md)）。 - **運用で見えていない**。`pg_stat_statements` で「どのクエリが総時間を食っているか」を定点観測する（[第12回](12-operations-monitoring.md)）。 これらを使い切ったうえで、なお1台の CPU・メモリ・ディスク I/O が限界なら、まず**垂直スケール** （より強いマシンへの移行）を検討する。垂直スケールは分散に伴う整合性問題を一切生まないため、 コストが許す限り最も安価な選択肢である。以下で扱うのは、1台の中で表を分割するパーティションと、 台数を増やして負荷やデータを複数のサーバに分ける**水平スケール**（スケールアウト。レプリケーションと シャーディングがこれにあたる）で、いずれも垂直スケールでも足りなくなった段階の道具として読む。この 順序を飛ばすことが、本章の「つまずき」の筆頭に挙げる**早すぎる分散化**である。 スケール手法の全体像を、解く問題の軸で整理しておく。 | 手法 | 主に解く問題 | 台数 | 主なコスト | |---|---|---|---| | パーティション | 1台の中の巨大な表の管理・スキャン量 | 1台 | 設計とキー選定 | | レプリケーション | 読み負荷の分散・可用性（冗長化） | 複数台（同じデータ） | 結果整合性（レプリケーション遅延） | | シャーディング | 書き込み負荷・総データ量の分散 | 複数台（異なるデータ） | クロスシャード JOIN／トランザクション | ### パーティション ― 1台の中で大きな表を分割する パーティショニングは、論理的には1つの表を、物理的には複数の子テーブル（パーティション）に分割する 仕組みである。台数は増やさない（1台の中の話）。PostgreSQL の**宣言的パーティショニング**では、親 テーブルを「どの列をキーに、どう分割するか」だけ宣言し、実データは各パーティションに格納される。 `events` は時系列ログなので、`occurred_at` を使った**レンジ分割（月次）**が定石である。 既存の表は後からパーティション化できない（`ALTER TABLE ... PARTITION BY` は存在しない）。そこで 「既存表を退避 → パーティション親を作成 → 子を作成 → データを移送」という手順を踏む。 ```sql -- 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)`）ので、隣接する月を隙間なく・重なりなく並べられる。 ```sql 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` はそのままの文字列として値を埋め込む。 ```sql 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` は失敗する。想定外の日付を受け止める **デフォルトパーティション**を用意しておくと安全である。 ```sql CREATE TABLE events_default PARTITION OF events DEFAULT; ``` 準備ができたら、退避した表からデータを移す。`GENERATED BY DEFAULT AS IDENTITY` は明示値の挿入を 許すので `id` をそのまま持ち込めるが、その場合はシーケンスを最大値に追いつかせる必要がある。 ```sql 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)` を張ろうとすると、次のように拒否される。 ```sql ALTER TABLE events ADD PRIMARY KEY (id); ``` ```text 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` に主キーを持たせる なら、複合キーにする。 ```sql ALTER TABLE events ADD PRIMARY KEY (id, occurred_at); ``` #### partition pruning ― 不要なパーティションを計画から消す パーティション分割の最大の狙いが **partition pruning**（パーティションの刈り込み）である。`WHERE` がパーティションキーで範囲を絞っていると、プランナは無関係なパーティションを実行計画から丸ごと除外 する。3月ぶんだけを見るクエリを `EXPLAIN` すると、走査対象は `events_2024_03` 1つに絞られる。 ```sql EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2024-03-01' AND occurred_at < '2024-04-01'; ``` ```text 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` だけで絞る）は、全パーティションを走査する。 ```sql EXPLAIN SELECT count(*) FROM events WHERE event_type = 'purchase'; ``` ```text 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回](08-explain.md)を参照。 #### 分割のもう1つの恩恵 ― 古いデータの高速な廃棄 月次パーティションは、保持期間を過ぎた古いログを捨てる運用と相性がよい。`DELETE` は行を1件ずつ削除 して肥大化（bloat）を招くが、パーティションの `DROP` は表を丸ごと外すメタデータ操作で、ほぼ一瞬で 終わる。オンラインで安全に外すなら `CONCURRENTLY` を使う。 ```sql 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` を宣言し、行単位の変更を論理的に転送する。 ```sql -- 発行側（プライマリ、wal_level = logical が必要） CREATE PUBLICATION pub_events FOR TABLE events; -- 購読側（別クラスタ） CREATE SUBSCRIPTION sub_events CONNECTION 'host=primary dbname=postshop' PUBLICATION pub_events; ``` #### 同期と非同期 プライマリがコミットを返す前にスタンバイの受信を待つかどうかで、同期・非同期が分かれる。 - **非同期（既定）**: プライマリはスタンバイの応答を待たずにコミットを返す。速いが、プライマリが 故障した瞬間に未達の更新があると、フェイルオーバー時に**ごく直近の更新を失う**可能性がある。 - **同期**: プライマリは指定したスタンバイが更新を受け取る（または適用する）まで待ってからコミット を返す。データ損失は防げるが、1コミットごとにネットワーク往復のレイテンシが乗る。 ```text # プライマリの postgresql.conf（同期レプリケーションの例） synchronous_standby_names = 'standby1' synchronous_commit = on ``` 「絶対に失えないデータか、多少のレイテンシと引き換えにするか」を要件で決める。多くのシステムは 非同期を基本に、重要な系だけ同期にする。 #### 読み書き分離（read/write splitting） ホットスタンバイを使うと、**書き込みはプライマリ、読み込みはレプリカ**へ振り分けられる。読み取りの 多い EC のようなワークロードでは、レプリカを増やすほど読み取りスループットを水平に伸ばせる。 ```text 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回](09-transactions.md)）ほど単純 でも安価でもない。 - **一意性・集計**: グローバルな一意採番や全体集計が、ノード横断の調整を必要とする。 したがってシャードキーは「大半のクエリが単一シャードで閉じる」ように選ぶのが鉄則で、ここを外すと 分散の利益を 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回](09-transactions.md)・[第12回](12-operations-monitoring.md)）。 結果として、本来速いはずの OLTP のレイテンシが分析クエリに巻き込まれて悪化する。 対策は「集計を本番 OLTP から逃がす」ことである。段階に応じて選ぶ。 1. まずは**読み取りレプリカで集計する**。本番プライマリのキャッシュと I/O を汚さずに済む。 2. 定期的な分析なら **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を立てずに既存の関係データと同居させられる。 ```sql 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回](07-storage-indexes-collation.md)） の仕事で、ベクトル索引はあくまで「意味の近さ」専用の別の道具だと切り分ける。 ## ハンズオン ### 課題1: events を月次パーティション化し、partition pruning を確認する **やること** 1. 既存の `events`（約1,000万行）を、`occurred_at` をキーにした月次のレンジパーティションへ作り替える。 2. 特定月を範囲指定した集計クエリを `EXPLAIN` し、走査対象が1パーティションに刈り込まれることを確認する。 3. パーティションキーで絞らないクエリでは全パーティションが走査されることを対比で確認する。 **想定解答** 本編の手順どおり、退避 → 親作成 → 子作成（`DO` ブロックで24か月＋デフォルト）→ 移送を行う。 ```sql 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` で親と全パーティションの一覧を確認できる。次に刈り込みを見る。 ```sql EXPLAIN SELECT count(*) FROM events WHERE occurred_at >= '2024-03-01' AND occurred_at < '2024-04-01'; ``` ```text 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か月しか読まない。対して、パーティションキーを使わない条件は全パーティションを舐める。 ```sql EXPLAIN SELECT count(*) FROM events WHERE event_type = 'purchase'; ``` ```text 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 を 非同期ストリーミングする」である。 ```text write app -------------------------------------------------> primary | | | read | WAL（非同期） +----------------------------> replica 1 <----------------+ | | +----------------------------> replica 2 <----------------+ ``` 設計上の判断は次の3点である。第一に、更新系（`INSERT`/`UPDATE`/`DELETE`）は必ずプライマリへ送り、 参照系（`SELECT`）をレプリカへ振る。第二に、レプリカは読み取り専用のホットスタンバイなので、読み 負荷はレプリカ台数を増やすことで水平に伸ばせる。第三に、非同期レプリケーションにはレプリケーション 遅延があるため、「注文直後に自分の注文一覧を表示する」ような**自分の書き込みを直後に読む処理は プライマリから読む**ことにして、read-your-writes 問題を避ける。可用性の観点では、プライマリ障害時に レプリカを昇格させてフェイルオーバーする。データ損失を1件も許さない系だけ同期レプリケーションにし、 その分のコミットレイテンシ増を受け入れる、という上乗せも設計に含めてよい。 ## よくあるつまずきと対処 | 症状 | 原因 | 対処 | |---|---|---| | 遅いのですぐ分散を検討してしまう | 索引・実行計画・運用という単一ノードの手を使い切っていない | まず[第7回](07-storage-indexes-collation.md)索引・[第8回](08-explain.md)計画・[第12回](12-operations-monitoring.md)監視、次に垂直スケール、最後に分散 | | `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. 課題1で作った月次パーティションに対し、`ALTER TABLE events DETACH PARTITION events_2023_01 CONCURRENTLY;` で最古の月を切り離してから `DROP TABLE` する。同じ量を `DELETE FROM events WHERE occurred_at < '2023-02-01';` で消す場合と比べ、所要時間と後片付け（`VACUUM`）の要否がどう違うかを 考える。 2. `events` に対し、`occurred_at >= '2024-06-01' AND occurred_at < '2024-07-01'` の集計を、 パーティション化前（`events_old` があれば）と後で `EXPLAIN (ANALYZE, BUFFERS)` を取り、読んだ バッファ量（`Buffers:`）がどれだけ減ったかを比べる。 3. 自分が関わっている（または想像上の）システムで、「本番 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 定理の解説（分断耐性の下での一貫性と可用性のトレードオフを扱う入門記事全般）