---
note: バックアップ/PITR・無停止スキーマ変更・VACUUM・pg_stat_statementsによる遅いクエリの定点観測
created: 2026-08-14T10:00:00+09:00
---

# 第12回｜運用＋pg_stat_statements（定点観測）

> バックアップ・スキーマ変更・肥大化対策という守りの運用に、遅いクエリを継続的に見つける「定点観測」を足す回。

## この回のねらい

ここまでで設計・クエリ・実行計画・トランザクション・権限を扱ってきた。今回はそれらを本番で
「動かし続ける」ための運用を扱う。柱は4つある。第一に、バックアップと PITR（Point-In-Time
Recovery、任意時点への復旧）で、誤操作や障害から狙った時刻に戻せること。第二に、サービスを
止めずにスキーマ変更・マイグレーションを進める技術。第三に、MVCC の副産物である肥大化（bloat）
を VACUUM で抑えること。そして第四に、`pg_stat_statements` を使って「総実行時間の重いクエリ」を
定点観測し、[第8回](08-explain.md)の `EXPLAIN` による改善につなげる流れである。運用は「取れて
いるか」ではなく「戻せるか」「見ているか」で価値が決まる。この回はその視点を徹底する。

## 到達目標

- 論理バックアップ（`pg_dump` / `pg_dumpall`）と物理バックアップ（`pg_basebackup`）＋WAL アーカイブの
  違いを説明でき、それぞれをいつ使うか判断できる。
- WAL アーカイブを前提に、`recovery_target_time` を指定した PITR で「誤操作の直前」に復旧する手順の
  骨子を説明できる。
- 「バックアップは復旧試験をして初めて価値がある」ことを理由とともに説明できる。
- `CREATE INDEX CONCURRENTLY` と、テーブル書き換え／`ACCESS EXCLUSIVE` ロックを取る危険な `ALTER TABLE`
  を区別でき、`NOT VALID`→`VALIDATE`・新列追加＋バックフィルといった段階的手法に置き換えられる。
- `lock_timeout` を使い、DDL がロック待ち行列の先頭で全体を止める事故を避けられる。
- MVCC の死タプルが bloat を生む仕組みを説明でき、`VACUUM` と `VACUUM FULL` の違い（後者は
  `ACCESS EXCLUSIVE` で本番危険）を判断できる。
- `pg_stat_statements` を有効化し、`total_exec_time` 順で重いクエリを特定し、区間比較（統計リセット）と
  `pg_stat_activity`・キャッシュヒット率まで含めた監視指標の型を持てる。

## 前提と準備

第1回で構築した PostgreSQL 16 系のデータベース `postshop` に、共通スキーマとデータが投入済みである
ことを前提にする。今回はサーバ設定の変更（`shared_preload_libraries`）や再起動、バックアップ用の
ディレクトリ作成を伴うため、`psql` に加えてシェル（`pg_dump` / `pg_basebackup` など）とサーバの
再起動権限がある学習環境を想定する。本番相当のクラスタでは実行しないこと。

`pg_stat_statements` は共有ライブラリのプリロードが必要で、これは再起動を伴う。設定を確認しておく。

```bash
psql -d postshop -c "SHOW shared_preload_libraries;"
```

```text
 shared_preload_libraries
--------------------------

(1 row)
```

まだ空であれば、本編の「定点観測」の節で有効化する。

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

第7回〜第11回で参照だけしてきた `VACUUM` 系の語を、この回で正面から扱う。
回をまたいで使う語は[用語集](glossary.md)にもまとめている。

| 用語 | 一行での説明 |
|---|---|
| 論理バックアップ | テーブル定義と行を、SQL文や独自フォーマットとして書き出したもの |
| 物理バックアップ | データファイルそのものを、バイト列としてコピーしたもの |
| WAL（Write-Ahead Log） | 変更をデータファイルに反映する前に記録する先行書き込みログ |
| PITR（Point-In-Time Recovery） | 物理バックアップにWALを適用し、任意の時点まで復旧すること |
| 死タプル | 更新・削除によって無効化されたまま残っている古い行版 |
| bloat（肥大化） | 回収されない死タプルが溜まり、表が実データ以上に膨らんだ状態 |
| `VACUUM` | 死タプルの領域を「再利用可能」として回収する保守処理。読み書きを止めない |
| `VACUUM FULL` | 表を書き直して物理的に縮める処理。全アクセスを止めるため本番では危険 |
| autovacuum | 更新量に応じて `VACUUM` を自動で走らせる仕組み |
| `pg_stat_statements` | 実行されたSQLを正規化して集計し、総実行時間や呼び出し回数を蓄える拡張 |

## 本編

### 論理バックアップと物理バックアップ

バックアップには大きく2系統ある。**論理バックアップ**は、テーブル定義と行を SQL 文（または独自
フォーマット）として書き出す。`pg_dump` が単一データベース、`pg_dumpall` がクラスタ全体（後述の
ロールなどグローバルオブジェクトを含む）を対象にする。**物理バックアップ**は、データファイルそのもの
（クラスタのディレクトリ）をコピーする。`pg_basebackup` がこれを担う。

両者は性質がまったく異なる。

| 観点 | 論理（`pg_dump`/`pg_dumpall`） | 物理（`pg_basebackup`＋WAL） |
|---|---|---|
| 取るもの | スキーマ＋データの論理表現（SQL） | データファイルのバイト列 |
| 粒度 | データベース／テーブル単位で選べる | クラスタ全体 |
| 復旧先バージョン | メジャーバージョンをまたげる／移設に強い | 原則同一メジャーバージョン・同一アーキテクチャ |
| 任意時点への復旧 | できない（取得時点のみ） | **WAL と組み合わせれば PITR 可能** |
| サイズ・所要時間 | 大規模ほど遅く大きくなりがち | 大規模でも比較的速い |
| 典型用途 | 移行・部分復旧・別環境への複製 | 本番の全体復旧・PITR・レプリケーション基盤 |

論理バックアップの取得と復元の骨子は次のとおり。`-Fc`（custom フォーマット）にしておくと
`pg_restore` で並列復元や部分復元ができる。

```bash
# 単一DBを custom フォーマットで取得
pg_dump -d postshop -Fc -f /backup/postshop.dump

# 別DBへ復元（-j で並列）
createdb postshop_restore
pg_restore -d postshop_restore -j 4 /backup/postshop.dump

# ロール・テーブルスペースなどクラスタ共通のオブジェクトは pg_dumpall で
pg_dumpall --globals-only -f /backup/globals.sql
```

論理バックアップは取得時点の**一貫したスナップショット**である（内部的に1つのトランザクションから
読むため、途中の更新が混ざらない）。ただし取れるのは「その時点」だけで、10分後の状態には戻せない。
「昨日の誤操作の直前」に戻すには、次の物理バックアップ＋WAL が要る。

### WAL と PITR ―― 任意時点への復旧

PostgreSQL は全ての変更を、データファイルに反映する前に **WAL（Write-Ahead Log、先行書き込みログ）**
に記録する。物理バックアップ（ある時点のデータファイル全体）に、その後の WAL を順に適用していけば、
バックアップ時点から「WAL に記録された任意の時刻」まで再生できる。これが PITR の原理である。

必要な設定は、WAL を保存し続ける「アーカイブ」を有効にすることである（`postgresql.conf`）。

```text
wal_level = replica            # 既定。アーカイブに十分
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
```

準備ができたらベースバックアップを取る。`-X stream` で取得中の WAL も同梱される。

```bash
pg_basebackup -D /backup/base -Fp -Xs -P
```

復旧の骨子は次の順序になる。PostgreSQL 12 以降は旧 `recovery.conf` は廃止され、復旧パラメータは
`postgresql.conf`（または `postgresql.auto.conf`）に書き、空の **`recovery.signal`** ファイルを
置くことでリカバリモードに入る。

```text
1. サーバを停止する
2. 壊れた（または誤操作された）データディレクトリを退避し、ベースバックアップを展開する
3. 復旧パラメータを設定する:
     restore_command = 'cp /archive/%f %p'
     recovery_target_time = '2026-08-14 10:29:59+09'   # 誤操作の「直前」を指定
     recovery_target_action = 'promote'                # 目標到達後に通常運用へ
4. データディレクトリに空の recovery.signal を作成する
5. サーバを起動する → ベースバックアップに WAL を適用し、指定時刻で再生を止める
```

`recovery_target_time` に「誤って `DELETE` した時刻の直前」を指定すれば、その `DELETE` を含まない
状態でクラスタが立ち上がる。時刻のほか、`recovery_target_lsn`（WAL の位置）や
`recovery_target_xid`（トランザクションID）でも目標を指定できる。

ここで最重要の原則を1つ。**バックアップは、復旧を実際に試して初めて価値がある。** 取得が成功して
いても、アーカイブの WAL に欠落があったり、`restore_command` が誤っていたり、復元先の空き容量が
足りなかったりすれば、いざという時に戻せない。これは「バックアップは取れているが復旧を試したことが
ない」という、後述するつまずきの筆頭である。定期的に別環境へ復元し、行数やチェックサムを突き合わせる
**復旧試験**を運用に組み込む。試したことのないバックアップは、無いのと大差ないと考えてよい。

### 無停止スキーマ変更 ―― ロックを意識する

本番でのスキーマ変更が危険なのは、多くの `ALTER TABLE` や `CREATE INDEX` が強いロックを取り、その間
そのテーブルへの読み書きを止めてしまうからである。特に `ACCESS EXCLUSIVE` ロックは、参照すら含めた
あらゆるアクセスをブロックする最強のロックである。

まずインデックス作成から。通常の `CREATE INDEX` は対象テーブルに `SHARE` ロックを取り、その間の
書き込み（`INSERT`/`UPDATE`/`DELETE`）を止める。大きなテーブルでは数分〜数十分書き込み不能になりうる。
これを避けるのが `CREATE INDEX CONCURRENTLY` である。

```sql
-- 書き込みを止めずにインデックスを作る（本番向け）
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
```

`CONCURRENTLY` は書き込みをブロックしない代わりに、テーブルを2回スキャンするため遅く、トランザクション
ブロックの中では実行できない。さらに途中で失敗すると**無効な（INVALID）インデックス**が残る。その場合は
`DROP INDEX CONCURRENTLY` で削除してから作り直す。無効なインデックスは検索に使われず、更新コストだけを
払う存在なので放置しない。

```sql
-- 無効インデックスの検出（indisvalid = false のものが残骸）
SELECT c.relname
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;
```

`ALTER TABLE` は操作によって危険度がまるで違う。**テーブル書き換え（rewrite）を伴う操作**は、全行を
新しいファイルに書き直しながら `ACCESS EXCLUSIVE` を握り続けるため、大テーブルでは長時間の完全停止に
なる。代表は列の型変更である。

```sql
-- 危険: 多くの場合テーブル全体を書き換え、その間 ACCESS EXCLUSIVE で全アクセスを止める
ALTER TABLE orders ALTER COLUMN status TYPE varchar(20);
```

| 操作 | 挙動（PostgreSQL 16 系） | 危険度 |
|---|---|---|
| `ADD COLUMN ... DEFAULT 定数` | カタログ更新のみ。書き換えなし（PG 11 以降） | 低（ただし一瞬 `ACCESS EXCLUSIVE`） |
| `ADD COLUMN ... DEFAULT 揮発関数` | 全行を埋めるため書き換え | 高 |
| `ALTER COLUMN ... TYPE`（型変更） | 多くの場合テーブル書き換え | 高 |
| `SET NOT NULL` | 全行スキャンで検査（後述の回避策あり） | 中〜高 |
| `ADD CONSTRAINT ... CHECK/FK` | 既存行を検査（`NOT VALID` で回避可） | 中〜高 |
| `DROP COLUMN` | 論理削除のみ。書き換えなし | 低 |

危険な操作は**段階的な手法**に置き換える。制約の追加は、まず検査をスキップして `NOT VALID` で貼り、
別途 `VALIDATE CONSTRAINT` で既存行を確認する。`VALIDATE` は `SHARE UPDATE EXCLUSIVE` ロックで済み、
読み書きを止めない。

```sql
-- 1) 既存行を検査せず即座に制約を追加（新規行だけは以後チェックされる）
ALTER TABLE order_items
  ADD CONSTRAINT chk_qty_positive CHECK (qty > 0) NOT VALID;

-- 2) 読み書きを止めずに既存行を検査して「有効」にする
ALTER TABLE order_items VALIDATE CONSTRAINT chk_qty_positive;
```

`SET NOT NULL` も、先に等価な `CHECK (col IS NOT NULL) NOT VALID` を貼って `VALIDATE` してから
`SET NOT NULL` すると、PostgreSQL はその有効な制約を使って全行スキャンを省略できる。

列の追加は、いきなり「NOT NULL の新列＋デフォルト計算」を一発でやらず、**新列追加＋バックフィル**に
分ける。まず NULL 許容の列を足し（カタログ更新のみで速い）、既存行はバッチに分けて埋め、最後に制約を
段階的に付ける。バックフィルを1本の巨大 `UPDATE` でやると、長大なトランザクションと大量の死タプル
（＝bloat）を生むので、主キー範囲で区切って少しずつ流す。

```sql
-- 1) まず NULL 許容で列を足す(速い)
ALTER TABLE orders ADD COLUMN memo text;

-- 2) 主キー範囲でバッチ更新(1回で全行やらない)
UPDATE orders SET memo = '' WHERE id BETWEEN 1 AND 100000;
-- ... 範囲をずらして繰り返す ...
```

最後に、あらゆる DDL に効く安全弁が `lock_timeout` である。`ALTER TABLE` は一瞬でも
`ACCESS EXCLUSIVE` を要求するが、長時間走っているクエリがあると、それが終わるまでロックを取れず
**待ち行列**に並ぶ。しかもその DDL の後ろに来た通常のクエリは、DDL のロック取得を追い越せず一緒に
詰まる。結果、「軽いはずの ALTER」が全体を止める。`lock_timeout` を短く設定しておけば、ロックを
取れない DDL は自分だけ諦めて失敗し、後続を巻き込まない。

```sql
SET lock_timeout = '2s';   -- 2秒でロックを取れなければこの文は失敗する
ALTER TABLE orders ADD COLUMN memo2 text;
-- 失敗したら、原因の長時間クエリを片付けてからリトライする
```

### VACUUM と autovacuum ―― 肥大化(bloat)の仕組みと対処

PostgreSQL は MVCC（Multi-Version Concurrency Control、多版同時実行制御）で並行性を実現している
（詳細は[第9回](09-transactions.md)）。`UPDATE` は既存行をその場で書き換えず、**新しい行バージョンを
追加し、古い版を「死タプル（dead tuple）」として残す**。`DELETE` も行を即消さず死タプルにする。
どのトランザクションからも見えなくなった死タプルが占める領域が、回収されないまま溜まったものが
**肥大化（bloat）**である。テーブルが実データ以上に膨らみ、スキャンが遅くなり、キャッシュ効率も
落ちる。

死タプルを回収するのが `VACUUM` である。`VACUUM` は死タプルの領域を「再利用可能」として空き領域
マップに登録する。これにより以後の `INSERT`/`UPDATE` がその領域を使い回せる。重要なのは、通常の
`VACUUM` は `SHARE UPDATE EXCLUSIVE` ロックで動き、**読み書きを止めない**点、そして**ファイルサイズ
を OS に返さない**（縮まない）点である。普段はこれで十分で、実運用では **autovacuum**（更新量に応じて
自動で走る VACUUM）が背後で回収し続ける。

一方 `VACUUM FULL` はテーブルを新しいファイルに丸ごと書き直して物理的に縮める。ファイルサイズは
OS に返るが、その間 `ACCESS EXCLUSIVE` を握るため**全アクセスを止める**。本番の稼働テーブルに対する
`VACUUM FULL` は、事実上のダウンタイムを意味する。安易に打つと後述のつまずきを踏む。

| 操作 | ロック | サイズ | 用途 |
|---|---|---|---|
| `VACUUM`（＝autovacuum） | `SHARE UPDATE EXCLUSIVE`（読み書き可） | 縮まない（再利用可にする） | 常時の bloat 抑制 |
| `VACUUM FULL` | `ACCESS EXCLUSIVE`（全停止） | 縮む（OS に返す） | 極端に肥大した時の最終手段 |

肥大の度合いは `pg_stat_user_tables` の `n_dead_tup`（死タプル数）や `n_live_tup`（生存タプル数）、
最終 VACUUM 時刻で観測する。

```sql
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
```

```text
   relname   | n_live_tup | n_dead_tup | dead_pct |          last_autovacuum
-------------+------------+------------+----------+-------------------------------
 orders      |    1000000 |          0 |      0.0 | 2026-08-14 09:50:11+09
 order_items |    3000000 |          0 |      0.0 |
 ...
(10 rows)
```

死タプルが増え続けているのに `last_autovacuum` が古い、`dead_pct` が高止まりしている、といった状態は
autovacuum が追いついていないサインである。更新の激しいテーブルは、テーブル単位で autovacuum を
積極化する（しきい値やスケール係数を下げる）ことで対処する。

```sql
-- 更新が激しいテーブルは autovacuum を積極化する
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
```

### 定点観測 ―― pg_stat_statements

ここからが今回の主題である。個別の遅いクエリは[第8回](08-explain.md)の `EXPLAIN` で分析できるが、
そもそも「どのクエリを見るべきか」を教えてくれるのが `pg_stat_statements` である。実行された SQL を
正規化（リテラルを `$1` などに置換）して集計し、**総実行時間・呼び出し回数・平均時間**などを蓄積する。
1回1回は速くても大量に呼ばれるクエリは、総実行時間で初めて浮かび上がる。

有効化には共有ライブラリのプリロードが要る。`postgresql.conf` に追記して再起動する。

```text
shared_preload_libraries = 'pg_stat_statements'
```

```bash
pg_ctl restart      # 学習環境で。設定を読み込み直す
```

再起動後、拡張を作成する。

```sql
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
```

重いクエリの抽出は、`total_exec_time`（このクエリに費やした累計実行時間）の降順で並べるのが基本である。
PostgreSQL 13 以降、実行時間は `total_exec_time` / `mean_exec_time` に分かれている（計画時間は既定で
集計されない）。

```sql
SELECT
  round(total_exec_time::numeric, 1) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  left(query, 60)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
```

```text
 total_ms  | calls  | mean_ms | rows  |                        query
-----------+--------+---------+-------+------------------------------------------------------
 48213.6   |   1200 |   40.18 |  1200 | SELECT * FROM orders WHERE customer_id = $1 ORDER BY
  9902.1   | 500000 |    0.02 | 500000| SELECT price FROM products WHERE id = $1
 ...
(10 rows)
```

この表の読み方が肝心である。1行目は平均40msだが1200回呼ばれ、総時間が突出している。2行目は平均
0.02ms と極めて速いが、50万回呼ばれて総時間では2番手につけている。**平均が遅いクエリ**（1本を直す）と
**総時間が重いクエリ**（呼び出し回数の削減やインデックスで効く）は別物であり、`total_exec_time` 順は
その両方を「効くところ」から順に見せてくれる。ここで特定した上位クエリを、[第8回](08-explain.md)の
`EXPLAIN (ANALYZE, BUFFERS)` に持ち込んで、Seq Scan になっていないか・想定インデックスが使われているかを
確認する、という接続が今回のクライマックスである。

**区間比較（統計リセット）。** `pg_stat_statements` の値はサーバ起動以降の累計なので、「いまリリース
した変更が効いたか」を見るには**リセットして区間を切る**。拡張専用の関数を使う。

```sql
SELECT pg_stat_statements_reset();   -- 集計をゼロに戻す
-- ここでワークロードを流す / リリース後の負荷を一定時間受ける
-- その後もう一度 total_exec_time 順で見て、改善前後を比較する
```

注意として、`pg_stat_statements` をリセットするのは `pg_stat_statements_reset()` であって、
`pg_stat_reset()` ではない。後者は `pg_stat_user_tables` などクラスタ標準の統計（スキャン回数や
死タプル数の集計）をリセットする別物である。混同しない。

**実行中のクエリ。** 蓄積された統計とは別に、「いま何が走っているか」は `pg_stat_activity` で見る。
長時間走っている文や、ロック待ちで固まっている文の発見に使う。

```sql
SELECT pid,
       now() - query_start AS duration,
       state, wait_event_type, wait_event,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY duration DESC;
```

**キャッシュヒット率。** ディスクから読まず共有バッファで済んだ割合。低ければメモリ不足かインデックス
不備を疑う。データベース全体は `pg_stat_database`、テーブル別は `pg_statio_user_tables` で見る。

```sql
SELECT datname,
       round(blks_hit * 100.0 / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database
WHERE datname = current_database();
```

```text
  datname  | cache_hit_pct
-----------+---------------
 postshop  |         99.32
(1 row)
```

一般に OLTP では 99% 以上が目安で、これが継続的に下がっていれば黄信号である。

**「取っているが見ていない」を避ける監視指標の型。** 運用で最悪なのは、指標を集めてはいるが誰も見て
おらず、悪化に気づけない状態である。定期的に確認する指標を、意味とともに型として固定しておく。

| 指標 | 取得元 | 見るべき理由 |
|---|---|---|
| 総実行時間 top のクエリ | `pg_stat_statements`（`total_exec_time`） | 改善の投資対効果が最大のクエリを特定する |
| キャッシュヒット率 | `pg_stat_database` / `pg_statio_user_tables` | メモリ／インデックス不足の早期検知 |
| 死タプル・dead_pct | `pg_stat_user_tables`（`n_dead_tup`） | bloat と autovacuum の追随状況 |
| 長時間クエリ・ロック待ち | `pg_stat_activity`（`now()-query_start`, `wait_event`） | 進行中の障害・詰まりの検知 |
| Seq Scan の多いテーブル | `pg_stat_user_tables`（`seq_scan`, `idx_scan`） | インデックス不足の候補出し |

## ハンズオン

### 課題1: バックアップ → 誤操作 → PITR 復旧（手順の骨子）

**やること**

1. WAL アーカイブを有効化し、`pg_basebackup` でベースバックアップを取得する。
2. わざと誤操作（例: `DELETE FROM orders WHERE ordered_at >= '2026-01-01';`）を実行し、その**直前の時刻**を
   控える。
3. サーバを停止し、ベースバックアップを展開、`recovery_target_time` に誤操作直前を指定して起動し、
   誤操作が「なかった」状態に戻ることを確認する。

**想定解答**

設定（`postgresql.conf`）とベースバックアップ取得。

```text
wal_level = replica
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
```

```bash
pg_basebackup -D /backup/base -Fp -Xs -P
```

誤操作の前後で件数を控える。

```sql
SELECT now();                         -- 誤操作直前の時刻を記録
SELECT count(*) FROM orders;          -- 例: 1000000
DELETE FROM orders WHERE ordered_at >= '2026-01-01';   -- 誤操作
SELECT count(*) FROM orders;          -- 例: 640000（消えてしまった）
```

復旧はサーバ停止後、ベースバックアップを展開し、復旧パラメータを設定して `recovery.signal` を置く。

```text
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-08-14 10:29:59+09'   -- 誤操作直前
recovery_target_action = 'promote'
```

```bash
touch /var/lib/postgresql/16/main/recovery.signal
pg_ctl start
```

起動後に件数を確認し、`DELETE` 前の値（例: 1000000）に戻っていれば復旧成功である。

```sql
SELECT count(*) FROM orders;          -- 例: 1000000 に戻っている
```

ポイントは、**復旧を最後まで通して行数を突き合わせるところまでが1課題**という点である。ここまでやって
初めて「このバックアップは戻せる」と言える。手順を紙の上で持っているだけでは、いざという時に通らない。

### 課題2: 大量更新後の bloat を計測し、VACUUM の効果を確認する

**やること**

1. `orders` の複製 `bloat_demo` を作り、初期サイズを測る。
2. 全行を `UPDATE`（＝全行を死タプル化）し、`n_dead_tup` とサイズの増加を確認する。
3. `VACUUM` を実行し、死タプルが回収される（`n_dead_tup` が減る）一方でサイズは縮まないことを確認する。
4. `VACUUM FULL` でサイズが縮むことを確認し、両者の違いを体感する。

**想定解答**

```sql
CREATE TABLE bloat_demo AS SELECT * FROM orders;   -- 例: 100万行
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size;   -- 例: 65 MB
```

全行更新で死タプルを作る。autovacuum が先に回収してしまうと観測できないので、確認は素早く行う
（学習中だけ `ALTER TABLE bloat_demo SET (autovacuum_enabled = off);` で止めてもよい）。

```sql
UPDATE bloat_demo SET status = status;             -- 全行に新バージョンを作る

SELECT n_live_tup, n_dead_tup,
       pg_size_pretty(pg_relation_size('bloat_demo')) AS size
FROM pg_stat_user_tables WHERE relname = 'bloat_demo';
```

```text
 n_live_tup | n_dead_tup |  size
------------+------------+--------
    1000000 |    1000000 | 130 MB     ← 死タプルの分ほぼ倍に膨らむ（例）
(1 row)
```

通常の `VACUUM` を打つと、死タプルは回収されて `n_dead_tup` が 0 付近に落ちるが、`size` は縮まない
（領域は「再利用可能」になっただけで OS には返らない）。

```sql
VACUUM bloat_demo;

SELECT n_dead_tup,
       pg_size_pretty(pg_relation_size('bloat_demo')) AS size
FROM pg_stat_user_tables WHERE relname = 'bloat_demo';
```

```text
 n_dead_tup |  size
------------+--------
          0 | 130 MB     ← dead は消えたがサイズは据え置き（例）
(1 row)
```

物理的に縮めたい場合だけ `VACUUM FULL` を使う。サイズは元に近づくが、`ACCESS EXCLUSIVE` を握るため
本番の稼働テーブルには打てない、という感覚をここで掴む。

```sql
VACUUM FULL bloat_demo;
SELECT pg_size_pretty(pg_relation_size('bloat_demo')) AS size;   -- 例: 65 MB に縮む
```

結論として、平常運転の bloat 対策は autovacuum＋通常 `VACUUM` に任せ、`VACUUM FULL` は縮小が本当に
必要な時の最終手段として、停止を許容できる時間帯にのみ打つ。

### 課題3: pg_stat_statements で総実行時間 top を特定し、第8回の手順で改善する

**やること**

1. `pg_stat_statements` を有効化し、`pg_stat_statements_reset()` で区間を切る。
2. ワークロードを流す（同種のクエリを繰り返し実行する）。
3. `total_exec_time` 順で重いクエリを特定する。
4. そのクエリを `EXPLAIN (ANALYZE, BUFFERS)` で分析し、インデックスを追加して改善、リセット→再計測で
   効果を確認する。

**想定解答**

有効化（本編のとおり再起動と `CREATE EXTENSION` 済み）後、区間を切ってからワークロードを流す。

```sql
SELECT pg_stat_statements_reset();

-- ワークロード（例: インデックスの無い customer_id で繰り返し検索する想定）
SELECT count(*) FROM orders WHERE customer_id = 12345;
SELECT count(*) FROM orders WHERE customer_id = 23456;
-- ... 多数回 ...
```

総時間 top を見る。

```sql
SELECT round(total_exec_time::numeric,1) AS total_ms, calls,
       round(mean_exec_time::numeric,2) AS mean_ms, left(query,50) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;
```

```text
 total_ms | calls | mean_ms |                query
----------+-------+---------+--------------------------------------------
 52130.4  |  1000 |  52.13  | SELECT count(*) FROM orders WHERE customer
 ...
(5 rows)
```

上位のクエリを[第8回](08-explain.md)の手順で分析する。

```sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders WHERE customer_id = 12345;
```

```text
 Aggregate  (cost=... rows=1)
   ->  Seq Scan on orders  (cost=... rows=20)
         Filter: (customer_id = 12345)
         Rows Removed by Filter: 999980
 ...
```

`Seq Scan` で毎回100万行を舐めているのが総時間の正体である。インデックスを（本番想定で
`CONCURRENTLY`）追加し、リセットして同じワークロードを流し直す。

```sql
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
SELECT pg_stat_statements_reset();
-- 同じワークロードを再実行して total_exec_time を比較する
```

再計測で該当クエリの `total_ms` が桁で下がり、`EXPLAIN` の計画が `Index Scan` に変わっていれば改善成功で
ある。この「定点観測で見つける → EXPLAIN で分析する → 直す → 区間比較で確かめる」の一巡が、運用で
遅いクエリを継続的に潰していくループである。

## よくあるつまずきと対処

| 症状 | 原因 | 対処 |
|---|---|---|
| 本番で `VACUUM FULL` を打ってサービスが止まった | `VACUUM FULL` は `ACCESS EXCLUSIVE` で全アクセスをブロックする | 平常時は autovacuum＋通常 `VACUUM` に任せる。`FULL` は停止を許容できる時間帯の最終手段 |
| 軽いつもりの `ALTER TABLE` が全体を止めた | 型変更などは書き換え＋`ACCESS EXCLUSIVE`。一瞬のロックでも長時間クエリの後ろで待ち行列を作る | 危険な操作を段階的手法に置換。`lock_timeout` を設定し、詰まったら諦めさせる |
| `CREATE INDEX` 中に書き込みが止まった | 通常の `CREATE INDEX` は書き込みをブロックする | `CREATE INDEX CONCURRENTLY` を使う（失敗時の INVALID インデックスは作り直す） |
| いざ復旧しようとしたら戻せなかった | バックアップは取れていたが復旧を試したことがなかった | 定期的な復旧試験を運用に組み込み、行数・チェックサムで検証する |
| 指標を集めているのに障害に気づけない | 「取っているが見ていない」。閾値も定点確認もない | 監視指標の型（総時間top・ヒット率・死タプル・長時間クエリ）を定期確認に固定する |
| `pg_stat_statements` をリセットしたのに標準統計まで消えた／消えない | `pg_stat_statements_reset()` と `pg_stat_reset()` の混同 | 拡張の集計は前者、`pg_stat_user_tables` 等は後者。目的で使い分ける |
| `pg_stat_statements` が空／存在しない | `shared_preload_libraries` 未設定、または `CREATE EXTENSION` 未実行 | 設定追加後に**再起動**し、`CREATE EXTENSION pg_stat_statements` を実行する |

## 到達度チェックリスト

- [ ] 論理（`pg_dump`/`pg_dumpall`）と物理（`pg_basebackup`＋WAL）の違いと使い分けを説明できる。
- [ ] `recovery_target_time` を使った PITR の手順の骨子（ベースバックアップ→WAL 適用→目標時刻で停止）を説明できる。
- [ ] 「バックアップは復旧試験をして初めて価値がある」と、その理由を言える。
- [ ] `CREATE INDEX CONCURRENTLY` と通常の `CREATE INDEX` のロックの違いを説明できる。
- [ ] 書き換え／`ACCESS EXCLUSIVE` を伴う危険な `ALTER TABLE` を挙げ、`NOT VALID`→`VALIDATE`・新列追加＋バックフィルに置き換えられる。
- [ ] `lock_timeout` が DDL の待ち行列事故をどう防ぐか説明できる。
- [ ] MVCC の死タプルが bloat を生む仕組みと、`VACUUM`／`VACUUM FULL`／autovacuum の違いを説明できる。
- [ ] `pg_stat_statements` を有効化し、`total_exec_time` 順で重いクエリを特定して `EXPLAIN` につなげられる。
- [ ] `pg_stat_statements_reset()` による区間比較、`pg_stat_activity`、キャッシュヒット率を監視の型として説明できる。

## 宿題（次回までの自習）

1. 自分の学習環境で課題1（PITR）を、`recovery_target_time` ではなく `recovery_target_xid`
   または `recovery_target_lsn` で指定して再現し、どの目標指定が「誤操作の直前」を最も正確に狙えるかを
   考察する。
2. `pg_stat_statements` を有効にしたまま、第3回〜第8回で書いた自分のクエリを一通り流し、
   `total_exec_time` 上位5件を書き出す。そのうち1件を `EXPLAIN (ANALYZE, BUFFERS)` で分析し、
   改善余地があるかをメモする。
3. `pg_stat_user_tables` を1日おきに2回スナップショットして、`n_dead_tup` と `seq_scan` の増分を
   比較する。増分の大きいテーブルが、次に監視・インデックス・autovacuum 調整を考えるべき候補になる。

## 次回への接続

今回の定点観測で「重いクエリ」と「肥大化・スキャンの多いテーブル」が見えるようになった。個々の
チューニングを尽くしてなお1台に収まらなくなったとき、答えはスケールと分割になる。
[第13回](13-scaling.md)では、`events`（約1000万行）を月次でパーティション化し、レプリケーションや
シャーディングといった「1台の外」へ広げる話に進む。今回覚えた `pg_stat_statements` と各種統計は、
スケール後も「どこがボトルネックか」を測り続ける共通の物差しであり続ける。

## 参考

- PostgreSQL 公式ドキュメント: "Server Administration" > "Backup and Restore"
  （`pg_dump`/`pg_dumpall`、継続的アーカイブと PITR、`recovery_target_time` ほか）
- PostgreSQL 公式ドキュメント: "Server Administration" > "Routine Database Maintenance Tasks" >
  "Routine Vacuuming"（死タプル・`VACUUM`／autovacuum・bloat）
- PostgreSQL 公式ドキュメント: "Reference" > "SQL Commands" > `ALTER TABLE` / `CREATE INDEX`
  （`CONCURRENTLY`、`NOT VALID`/`VALIDATE`、ロックレベル）
- PostgreSQL 公式ドキュメント: "Additional Supplied Modules" > `pg_stat_statements`
- PostgreSQL 公式ドキュメント: "Server Administration" > "Monitoring Database Activity"
  （`pg_stat_activity`、`pg_stat_user_tables`、`pg_statio_*`、`pg_stat_database`）