LabHub

ブログ

DB 完全ガイド — 内部構造・インデックス・クエリプランナ・パーティショニング・Vector DB (Season 2 Ep 13, 2025)

2026-04-15 · 17 分で読めます · #database#postgresql#b-tree#lsm-tree#indexing#query-planner#explain#transaction-isolation#partitioning#sharding#vector-db#pgvector#season-2

한국어English日本語中文

はじめに — なぜDBを深く知る必要があるのか

「SQLさえ書ければいいのでは?」への反例:

2025年のシニアエンジニアにとってDBを深く知ることは必須教養だ。

第1部 — ストレージエンジンの二つの哲学

1.1 B-Tree (Balanced Tree)

読み取り最適化。従来のRDBMS(PostgreSQL, MySQL InnoDB)のデフォルト。

     [50]
    /    \
  [20]  [80]
 /  \   /  \
[10][30][70][90]

特徴:

欠点: ランダム書き込みが多いと分裂 + I/O。

1.2 LSM-Tree (Log-Structured Merge)

書き込み最適化。RocksDB, Cassandra, ScyllaDB, LevelDB, ClickHouse。

Memtable (RAM)
   ↓ flush
L0 SSTables (ディスク)
   ↓ compact
L1 SSTables
   ↓ compact
L2 SSTables ...

特徴:

欠点: 読み取りが複数レイヤの探索になる → Bloom Filterなどで緩和。

1.3 選択の基準

ワークロード好ましい方式
OLTP (読み取り ~ 書き込み)B-Tree (Postgres, MySQL)
Write-heavy (ログ・メッセージ)LSM (Cassandra, RocksDB)
時系列LSM またはカラムナ (InfluxDB, TimescaleDB)
分析カラムナ (ClickHouse, DuckDB)

1.4 2024〜2025のトレンド: B-TreeとLSMの融合

第2部 — インデックス深掘り

2.1 インデックスの8種類

種類用途
B-Tree範囲クエリ (most common)
Hash等値条件のみ
GIN全文検索、JSONB、Array
GiST地理・図形、範囲型
SP-GiST空間データ
BRIN巨大テーブルの近似インデックス
Bloom複数カラムのOR検索
Covering (INCLUDE)インデックスに追加カラム (Index-only scan)

2.2 インデックス設計の5原則

  1. クエリが先: 実際のクエリを見て設計する
  2. 選択度: Selectivityの高いカラムを優先
  3. 複合インデックスの順序: 最も頻繁に使うフィルタから
  4. INCLUDEでCovering: テーブルアクセスを回避
  5. 使っていないインデックスは削除: 書き込み性能を損なう

2.3 インデックスが効かない理由 Top 10

  1. 関数の適用: WHERE LOWER(email) = ... → Expression Indexが必要
  2. 暗黙の型変換: WHERE id = '123' (idはint)
  3. 先頭ワイルドカード: LIKE '%foo' → Full scan
  4. OR条件: オプティマイザが諦めることがある → UNION
  5. Not Equals (!=): ほとんどインデックスを使わない
  6. NULL比較: Postgresは IS NULL のインデックスが可能、設計に注意
  7. データが小さい: プランナがSequential Scanを選ぶ
  8. 複合インデックスの順序が違う: (a, b) インデックスに WHERE b = ?
  9. 統計が古い: ANALYZEが必要
  10. インデックスのBloat: REINDEXが必要

第3部 — クエリプランナとEXPLAIN

3.1 プランナの役割

SQL (宣言) → 実行計画 (手続き) へ変換する。コストベース最適化 (CBO)。

3.2 PostgreSQL EXPLAINの読み方

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'KR'
GROUP BY u.name;

出力例:

HashAggregate  (cost=1234..5678 rows=100)
  Group Key: u.name
  ->  Hash Join  (cost=500..1200 rows=10000)
        Hash Cond: o.user_id = u.id
        ->  Seq Scan on orders o  (cost=0..800 rows=100000)
        ->  Hash  (cost=300..300 rows=500)
              ->  Index Scan on users_country_idx  (cost=0..300 rows=500)

読み方:

3.3 重要なノード種別

ノード意味
Seq Scan全体スキャン (小さいテーブル・選択度が低いときはOK)
Index Scanインデックスを使用
Index Only Scanインデックスだけで解決 (速い)
Bitmap Heap Scanインデックスで探してからテーブルを一括で
Nested Loop Join小さいouter + インデックスのあるinner
Hash Joinハッシュテーブルを作ってマッチング
Merge Join両側がソート済みの場合
Hash AggregateGROUP BY
SortORDER BY

3.4 Plan HintはPostgresにない

MySQL・Oracleにはヒントがある。PostgresはANALYZE + 統計目標 + random_page_costの調整で誘導する。

第4部 — トランザクション分離

4.1 ACIDの再定義

4.2 分離レベルの4段階 (SQL-92)

レベル防ぐもの
Read Uncommitted(ほとんど何も防がない)
Read CommittedDirty Read
Repeatable Read+ Non-repeatable Read
Serializable+ Phantom

4.3 DBごとのデフォルト分離レベル

DBデフォルト
PostgreSQLRead Committed
MySQL InnoDBRepeatable Read
OracleRead Committed (Serializableオプションあり)
SQL ServerRead Committed

4.4 Snapshot Isolation vs Serializable

4.5 Write Skewの例

二人の医師のうち少なくとも一人は当直が必要:

-- T1: on_call を読んで2名 → OK
-- T2: 同時に on_call を読んで2名 → OK
-- 両方が自分の状態を off-call に変更
-- コミット → 当直0名。制約違反

Serializableでのみ検知される。

4.6 Deadlock

二つ以上のトランザクションが互いのロックを待つ。DBが検知 → 1つを犠牲にする (abort)。

防止:

第5部 — MVCC (Multi-Version Concurrency Control)

5.1 MVCCの基本

「書き込みは読み取りを妨げず、読み取りは書き込みを妨げない。」

各行の複数バージョンを保持し、トランザクションは自分のスナップショットのバージョンを読む。

5.2 PostgresのMVCC

5.3 Vacuum Bloatの問題

5.4 MySQL InnoDBのMVCC

第6部 — パーティショニングとシャーディング

6.1 垂直 vs 水平

6.2 Partitioning (単一DB内)

Postgresネイティブ (10+):

CREATE TABLE events (
  id bigserial,
  event_time timestamptz,
  data jsonb
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2025_01 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

メリット: パーティションプルーニング、管理が容易 (DROP old partition)。 限界: 単一DB内にとどまる。単一サーバの容量制約。

6.3 Sharding (複数DB)

データを複数サーバに分散する。

シャードキーの選択:

シャーディング方式:

6.4 Cross-shardクエリ

基本的に高くつく。 JOIN・ORDER BYが複数シャードにまたがると、アプリケーションレベルのmergeになる。

解決:

6.5 分散SQL DB (シャーディング自動)

第7部 — ReplicationとHA

7.1 Replicationの方式

7.2 Postgres Replication 2025

7.3 Failoverのパターン

Primary の障害を検知
  ↓ (consensus: etcd・Patroni)
Standby の中から最新を選択
  ↓
新 Primary へ昇格 + アプリが DSN を切り替え
  ↓
旧 Primary は Rebuild

核心: Split-brainの防止 (Fencing)。

7.4 Read Replicaの活用

第8部 — PostgreSQLの2025年の独走

8.1 なぜPostgresが勝ったのか

2024〜2025年のDB選定でPostgresが事実上のデフォルトになった理由:

  1. 拡張性: 数多くのExtension
  2. JSONB: NoSQL機能を内蔵
  3. pgvector: Vector DBの機能
  4. PostGIS: GIS最強
  5. Timescale: 時系列
  6. Citus: 分散
  7. Logical Replication: CDC・マイグレーション
  8. ライセンス: PostgreSQL License (寛容)
  9. コミュニティ: 安定、大企業への依存がない
  10. クラウド管理型: Aurora, RDS, Cloud SQL, Neon, Supabase

8.2 Postgres Extension Top 15 (2025)

Extension用途
pgvectorVector Search
PostGIS地理
TimescaleDB時系列
Citus分散
pg_partmanパーティション管理
pg_cronスケジュール
pg_stat_statementsクエリ性能
hypopg仮想インデックス
pg_repackオンライン再構成
pg_hint_planプランヒント
pg_trgm類似検索
unaccentアクセント除去
uuid-osspUUID生成
pgcrypto暗号
hstoreキー・バリュー

8.3 2024〜2025のPostgresエコシステム

第9部 — NoSQL: いつ使うか

9.1 NoSQLの5カテゴリ

カテゴリ例用途
DocumentMongoDB, Firestoreスキーマが柔軟
Key-ValueRedis, DynamoDB高速な参照
Wide-columnCassandra, ScyllaDB, HBase大規模書き込み
GraphNeo4j, Neptune関係中心
Time-SeriesInfluxDB, Timescale時系列

9.2 2025の現実: 「Postgresで十分」

ほとんどのNoSQLユースケースをPostgresがカバーする:

それでもNoSQLを選ぶとき:

9.3 Redisの立ち位置

インメモリのキャッシュ・キュー・ランキング・Rate Limit。「一つくらいは誰もが持っている」道具。

2024年のライセンス変更 → Valkeyフォーク (AWS・Google主導)。選択肢が増えた。

第10部 — Vector DB

10.1 Vector DBが必要な理由

LLMの埋め込み検索・推薦などのための近似最近傍 (ANN) 検索。

10.2 ANNアルゴリズム

10.3 2025のVector DB比較

DB特徴
pgvectorPostgres拡張、統合が最高
QdrantRust、速い、フィルタリングが強い
Weaviateハイブリッド検索、GraphQL API
Milvus大規模、Zilliz
PineconeSaaS、運用が楽
LanceDBファイルベース、組み込み
TurbopufferServerless

10.4 2025のおすすめ

10.5 pgvectorの例

CREATE EXTENSION vector;

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- 検索
SELECT content
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

第11部 — DBロードマップ6か月

Month 1: SQL深掘り

Month 2: Postgres内部

Month 3: Storage Engine

Month 4: 分散DB

Month 5: Vector DB

Month 6: 最適化・運用

第12部 — DBチェックリスト12

  1. B-Tree vs LSM-Tree の選択基準を知っている
  2. Postgresのインデックス型5種 を用途別に知っている
  3. インデックスが効かない理由5つ を言える
  4. EXPLAIN ANALYZE の出力を読める
  5. トランザクション分離の4段階 を防ぐ現象とセットで知っている
  6. Write Skew を説明できる
  7. MVCCとVacuum の関係を知っている
  8. Partitioning vs Sharding の違いを知っている
  9. Hash vs Rangeシャーディング のトレードオフを知っている
  10. Postgres Extension Top 5 を知っている
  11. pgvectorとQdrant の選択基準を知っている
  12. Connection Pool (PgBouncer) の必要性を知っている

第13部 — DBアンチパターン10

  1. ORMだけを信じてクエリを点検しない: N+1、過剰なカラムのロードがよくある
  2. インデックスを無制限に足していく: 書き込み性能の低下。使われていないものは削除
  3. Long transaction: MVCC bloat。トランザクションは短く
  4. デフォルト分離レベルの盲信: Read Committedの限界を知らずWrite Skewのバグ
  5. Connectionを一つずつ管理: Poolなしで数千connection → Postgresが落ちる
  6. JSONBに何もかも: スキーマの強みを放棄。定型はカラムで
  7. シャードキーを間違える: Hot Shard → 再シャーディング地獄
  8. Migrationの無計画なdowntime: 無停止マイグレーション戦略が必須
  9. Vacuumチューニングの無視: 気づかないうちにテーブルが10倍bloat
  10. DBをメッセージキューに: 可能だが非推奨。Redis/Kafkaを使う

おわりに — DBは「本番システムの重力」だ

アプリケーションは替えられても、DBスキーマは簡単には変えられない。DBは本番システムで最も慣性の大きい部分だ。

だから最初にうまく設計しなければならない:

2025年のDBエンジニアリングはこうだ:

次回予告 — 「ネットワーク完全ガイド: HTTP/3・QUIC・TLS 1.3・gRPC・WebSocket・CDN」

Season 2 Ep 14はインターネットの配管、ネットワーク。次回は:

見えない電線を、次回に。

原文を読む: https://www.youngju.dev/blog/culture/2026-04-15-database-complete-guide-internals-index-query-planner-partitioning-vector-db-deep-dive-guide-2025.ja

コメント

まだコメントはありません。

ログインするとコメントできます