PostgreSQL高度: ロック、実行計画、パーティショニング

PostgreSQLの基本を理解している場合、高度なPostgreSQLは3つの主要な領域で理解できます：

ロックと同時実行性 — 複数のトランザクションがデータに同時にアクセスする場合、PostgreSQLはどのように処理するか？
実行計画 — PostgreSQLはSQLクエリを実行する方法をどのように決定し、クエリがどこで遅いかをどう知るか？
パーティショニング — 非常に大きなテーブルを複数の部分に分割して、より効率的なクエリ/管理を行う方法？

1. ロック — PostgreSQLはデータをどのようにロックするか？#

2つのトランザクションを想像してください：

-- トランザクションA
BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

sql

AがまだCOMMITしていない間、トランザクションBが実行されます：

BEGIN;

UPDATE accounts
SET balance = balance + 50
WHERE id = 1;

sql

BはAの処理が完了するのを待つ必要があります。

これは行レベルロックの一種です。

`SELECT ... FOR UPDATE`#

非常に一般的なパターン：

BEGIN;

SELECT *
FROM accounts
WHERE id = 1
FOR UPDATE;

-- ロジックを処理

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

COMMIT;

sql

FOR UPDATEはPostgreSQLに次のように伝えます：

“この行を変更する準備をしています。他のトランザクションが同時に変更しないようにしてください。”

次のようなロジックがある場合に非常に便利です：

読み取り → チェック → 変更

text

例：

SELECT stock
FROM products
WHERE id = 10
FOR UPDATE;

sql

その後：

UPDATE products
SET stock = stock - 1
WHERE id = 10;

sql

正しくロックしない場合、2つの同時リクエストが両方とも次のように見る可能性があります：

stock = 1

text

両方が製品がまだ在庫があると思ってしまいます。

知っておくべきロックの種類#

すべてをすぐに覚える必要はありません。最も重要なもの：

行ロック#

SELECT ...
FOR UPDATE;

sql

または：

SELECT ...
FOR SHARE;

sql

同時実行性がレコードレベルにある場合に使用します。

テーブルロック#

例：

LOCK TABLE accounts;

sql

テーブルレベルでロックします。

通常、アプリケーションコードはテーブルロックを気軽に使用すべきではありません。同時実行性を簡単に低下させるためです。

デッドロック#

これは非常に重要な部分です。

トランザクションA：

行1をロック
↓
行2を待つ

text

トランザクションB：

行2をロック
↓
行1を待つ

text

次のようになります：

A ──ロック──> 行1
A ──待機──> 行2

B ──ロック──> 行2
B ──待機──> 行1

text

誰も進めません。

PostgreSQLはデッドロックを検出し、1つのトランザクションを終了します。

防止方法#

すべてのトランザクションが同じ順序でロックを取得するようにします。

例えば、常に：

小さいアカウントを先にロック
→ 大きいアカウントをロック

text

このトランザクションの代わりに：

アカウント1 → アカウント2

text

そのトランザクション：

アカウント2 → アカウント1

text

2. 実行計画 — PostgreSQLは実際にクエリをどのように実行するか？#

これはPostgreSQLを最適化する際に非常に重要なスキルです。

クエリがあります：

SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

次のように尋ねないでください：

“インデックスはありますか？”

次のように尋ねてください：

PostgreSQLは実際にこのクエリをどのように実行していますか？

使用：

EXPLAIN
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

例えば、PostgreSQLは次のように返す可能性があります：

Index Scan using users_email_idx on users
  Index Cond: (email = 'alice@example.com')

text

これはPostgreSQLがインデックスを使用していることを意味します。

`EXPLAIN ANALYZE`#

さらに重要：

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE email = 'alice@example.com';

sql

EXPLAINは計画された実行を提供します。

EXPLAIN ANALYZEはクエリを実行して実際のパフォーマンスを測定します。

例：

Index Scan using users_email_idx on users
(cost=0.42..8.44 rows=1 width=100)
(actual time=0.035..0.037 rows=1 loops=1)

text

次に焦点を当てる必要があります：

cost
rows
actual time
loops
スキャンタイプ

シーケンシャルスキャン vs インデックススキャン#

テーブルに次があるとします：

10,000,000 users

text

クエリ：

SELECT *
FROM users
WHERE id = 123;

sql

PostgreSQLは次を使用する可能性があります：

Index Scan

text

1つの行を見つける必要があるだけだからです。

しかしクエリ：

SELECT *
FROM users
WHERE country = 'Vietnam';

sql

テーブルの70%がVietnamの場合、PostgreSQLは次を選択する可能性があります：

Seq Scan

text

countryにインデックスがある場合でも。

これは必ずしも問題ではありません。

一般的な間違い：

“Seq Scan = 悪いクエリ。”

正しくありません。

テーブルの大部分を読み取る必要がある場合、シーケンシャル読み取りは時々インデックスを使用するよりも安いです。

知っておくべき実行ノード#

最低限、次を理解してください：

Seq Scan
Index Scan
Index Only Scan
Bitmap Index Scan
Nested Loop
Hash Join
Merge Join
Sort
Aggregate

text

例：

SELECT *
FROM orders o
JOIN users u ON u.id = o.user_id;

sql

PostgreSQLは次を選択する可能性があります：

Nested Loop

text

または：

Hash Join

text

または：

Merge Join

text

データ、統計、コストモデルに依存します。

クエリ最適化の例#

次のとします：

SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

インデックス：

CREATE INDEX idx_orders_customer
ON orders(customer_id);

sql

ワークロードが頻繁に両方の条件をクエリする場合、次の方が良いかもしれません：

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

sql

その後：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 100
  AND created_at >= '2026-01-01';

sql

プランナーが新しいインデックスを利用しているかどうかを確認したいです。

3. パーティショニング — 大きなテーブルの分割#

次のとします：

orders

text

次があります：

2 billion rows

text

データに次があります：

created_at

text

月別にパーティション分割できます：

orders
├── orders_2026_01
├── orders_2026_02
├── orders_2026_03
├── ...
└── orders_2026_08

text

巨大なテーブルの代わりに。

範囲パーティショニング#

例：

CREATE TABLE orders (
    id bigint,
    created_at timestamp,
    customer_id bigint,
    amount numeric
) PARTITION BY RANGE (created_at);

sql

パーティションを作成：

CREATE TABLE orders_2026_08
PARTITION OF orders
FOR VALUES FROM ('2026-08-01')
             TO ('2026-09-01');

sql

翌月のパーティション：

CREATE TABLE orders_2026_09
PARTITION OF orders
FOR VALUES FROM ('2026-09-01')
             TO ('2026-10-01');

sql

パーティションプルーニング#

これがパーティショニングが役立つ理由です。

クエリ：

SELECT *
FROM orders
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-09-01';

sql

PostgreSQLは次のみを読み取る可能性があります：

orders_2026_08

text

次の代わりに：

orders_2026_01
orders_2026_02
orders_2026_03
...
orders_2026_08

text

これはパーティションプルーニングと呼ばれます。

しかしパーティショニングは「魔法のパフォーマンスボタン」ではありません#

例：

SELECT *
FROM orders
WHERE customer_id = 123;

sql

次でパーティション分割する場合：

created_at

text

PostgreSQLはまだ複数のパーティションを検索する必要がある可能性があります。

したがって、パーティションキーはワークロードと一致する必要があります。

パーティショニング前の重要な質問：

“最大のクエリは通常どの列でフィルタリングしますか？”

4. これら3つのトピックはどのように関連していますか？#

これが重要な部分です。

eコマースシステムを想像してください：

orders: 2 billion rows

text

次があります：

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= '2026-01-01';

sql

次が必要になる可能性があります：

パーティショニング#

分割：

orders
→ created_atで

text

インデックス#

各パーティション内：

(customer_id, created_at)

sql

実行計画#

確認：

EXPLAIN ANALYZE

sql

PostgreSQLに次があるかどうかを確認します：

パーティションプルーニング
       ↓
インデックススキャン
       ↓
少ない行

text

5. ロック + 実行計画も関連しています#

遅いクエリはユーザーを不快にするだけではありません。

例：

トランザクションA
    ↓
UPDATE
    ↓
ロックを保持
    ↓
クエリが30秒実行

text

その間：

トランザクションB
    ↓
同じ行をUPDATE
    ↓
待機

text

最適化されていないクエリはロックが長く保持される原因となり、次を作成します：

遅いクエリ
   ↓
長いトランザクション
   ↓
ロック競合
   ↓
より多くの待機
   ↓
より多くの遅いリクエスト

text

これが、本番PostgreSQLをデバッグする際、次だけを見てはいけない理由です：

“このクエリは5秒かかります。”

次も尋ねる必要があります：

“その5秒の間、どのロックを保持していますか？”

6. 高度なPostgreSQL学習のロードマップ#

目標がシニア/バックエンドエンジニアの場合、次の順序で学習します：

flowchart TD
    A[PostgreSQL] --> B[トランザクション]
    A --> C[クエリ計画]
    A --> D[ストレージ]
    
    B --> E[分離]
    B --> F[ロック]
    B --> G[デッドロック]
    
    C --> H[EXPLAIN]
    C --> I[インデックス]
    
    D --> J[MVCC]
    D --> K[VACUUM]
    D --> L[Bloat]
    
    F --> M[同時実行性]
    I --> N[パフォーマンス]
    
    M --> O[パーティショニング]
    N --> O
    O --> P[大規模データセット]

尋ねた3つのトピックの後、次に学ぶ価値のあるトピックは次のとおりです：

MVCC
VACUUM / autovacuum
インデックス内部 — B-tree、GIN、GiST、BRIN
トランザクション分離
デッドロックデバッグ
CTE / マテリアライズドCTE
ウィンドウ関数
クエリプランナーと統計
テーブル/インデックスの膨張
接続プーリング
レプリケーション
読み取りレプリカ
パーティションメンテナンス

実践的な方法で学習する場合、トリオEXPLAIN ANALYZE + ロック + MVCCを優先する必要があります。これらはPostgreSQL本番の問題の大部分をデバッグするのに役立つためです。

1. ロック — PostgreSQLはデータをどのようにロックするか？#

SELECT ... FOR UPDATE#

知っておくべきロックの種類#

行ロック#

テーブルロック#

デッドロック#

防止方法#

2. 実行計画 — PostgreSQLは実際にクエリをどのように実行するか？#

EXPLAIN ANALYZE#

シーケンシャルスキャン vs インデックススキャン#

知っておくべき実行ノード#

クエリ最適化の例#

3. パーティショニング — 大きなテーブルの分割#

範囲パーティショニング#

パーティションプルーニング#

しかしパーティショニングは「魔法のパフォーマンスボタン」ではありません#

4. これら3つのトピックはどのように関連していますか？#

パーティショニング#

インデックス#

実行計画#

5. ロック + 実行計画も関連しています#

6. 高度なPostgreSQL学習のロードマップ#

参考文献#

`SELECT ... FOR UPDATE`#

`EXPLAIN ANALYZE`#