弱点補強 — OFFSET ページングとキーセット：自分で消した行で半分飛ぶ × 同じ値の並びが動く × 深いページほど遅い × 長いスナップショット

概要

数値は手元の PostgreSQL 14.11 での実測（乱数の種 3 つの範囲）。会員・問い合わせ件数は架空。Cloud SQL では測っていない。

問題

MOps 販促システムチームは 10月1日 02:00 に Argo Workflows の CronWorkflow で「休眠会員復帰クーポン」（comeback-2026-10）を一斉付与した。休眠＝最終購入から 180 日超。前日のダッシュボードでは対象は約 12 万人（会員 30 万人の約 4 割）。会員の約 4 割は 2019-04-01 に旧システムから一括移行したため created_at が同じ秒に並ぶ。

後輩A「最初に対象を全部 fetchall() してから配れば、ずれようがありません」
後輩B「全体を REPEATABLE READ の 1 トランザクションで囲めば、スナップショットが固定されて OFFSET のままでもずれません」
後輩C「半分飛ぶのは NOT EXISTS のせいなので外しましょう。再実行しなければ二重にはなりません」

設問

1. Bad の問題点を 7 つ以上（誰に何が起きるか、関係する仕様）
2. ステージングで全員に届いた理由と、「あと何回か流せば」が何回で終わるか
3. 後輩A・B・C の提案を測って評価
4. 改善後コード（落ちても何度再実行しても、対象の全員にちょうど 1 枚）
5. Bad に流すと赤になるテスト（本物の PostgreSQL）と変異テスト
6. 監視・止め方・既に漏れた約 6 万人への事後対応

制約・前提条件

• PostgreSQL 14 以上（本番は Cloud SQL）、Python 3.12+、psycopg 3
• 実行中もログイン・購入・新規登録が入り続ける（止められない）
• 対象は実行を始めた時点で休眠だった会員。途中で購入した人にも配る（販促と合意済み）
• 1 ページは数秒で終わる大きさ。Argo の retryStrategy は有効

Bad → Good（対比）

SELECT m.member_id FROM members m
WHERE m.last_purchase_at < now() - interval '180 days'
  AND NOT EXISTS (SELECT 1 FROM coupon_grants g
                  WHERE g.campaign_id = %(campaign)s
                    AND g.member_id = m.member_id)
ORDER BY m.created_at
LIMIT %(limit)s OFFSET %(offset)s

# ループ
rows = conn.execute(PAGE_SQL, {"offset": page * PAGE_SIZE})
INSERT INTO coupon_grants (campaign_id, member_id) VALUES ...
page += 1
print(f"done: {total} coupons granted")

# 1) 対象を開始時刻で一度だけ凍結（表に書き出す）
INSERT INTO coupon_grant_targets SELECT ... WHERE
  last_purchase_at < %(as_of)s - make_interval(days => 180)
ANALYZE coupon_grant_targets

# 2) 一意キーの続きから読む（キーセット）
WHERE campaign_id = %(campaign)s AND member_id > %(after)s
ORDER BY member_id LIMIT %(limit)s

# 3) 1 ページ = 1 トランザクション
INSERT ... ON CONFLICT (campaign_id, member_id) DO NOTHING
UPDATE grant_runs SET last_member_id = ids[-1]

# 4) 照合
if granted != target_count: raise GrantReconciliationError

設計の図（SVG）

上: OFFSET の「捨てる位置」が自分の書き込みでずれる仕組み（模式図）。下: sim_out.txt の EXPLAIN (ANALYZE, BUFFERS) 5 回の中央値。OFFSET は先頭の 20 ブロックから約 9,000 倍に増える。キーセットは統計が無いと索引を使わず、先頭ほど遅い（逆向き）。

ヒント（段階的開示）

📋 コピー# 1) 対象を実行開始時刻で一度だけ凍結する
INSERT INTO coupon_grant_targets (campaign_id, member_id)
SELECT %(campaign)s, member_id FROM members WHERE last_purchase_at < %(as_of)s - interval '180 days'

# 2) 一意キーの「続き」から読む（キーセット／シーク方式）
SELECT member_id FROM coupon_grant_targets
WHERE campaign_id = %(campaign)s AND member_id > %(after)s
ORDER BY member_id LIMIT %(limit)s

# 3) 1 ページ = 1 トランザクション: 付与（ON CONFLICT DO NOTHING）と進捗を一緒に確定
# 4) 最後に「対象数 = 付与済み数」を照合し、合わなければ失敗

問題点分析（8点）

ステージングが緑だった理由・再実行は何回で終わるか

ステージングの対象 845 人は 1 ページ（1,000 件）に収まった。2 ページ目が無ければ #1 は起きない。対象が 1 ページを 1 人でも超えると漏れる（staging_out.txt）。

3つの提案の評価（実測、会員 30 万人・3 回）

模範解答（改善後コード）

全文は logs/2026-10-11-weakness-offset-keyset-pagination-code/grant_good.py（150 行）。インラインのコメントを黄色、効いている修正を緑で示す。

📋 コピーPAGE_SIZE = 1000                 # 1 トランザクションで確定する人数。数秒で終わる大きさにする
DORMANT_DAYS = 180               # 休眠の定義（販促チームの定義）


class GrantReconciliationError(RuntimeError):
    """対象数と付与済み数が合わない。黙って「完了」にしないために投げる。"""


@dataclass(frozen=True, slots=True)
class GrantSummary:
    """1 回の実行の結果（値オブジェクト）。"""
    campaign_id: str
    as_of: dt.datetime
    target_count: int
    inserted_this_run: int
    pages_this_run: int


FREEZE_RUN_SQL = """
    INSERT INTO grant_runs (campaign_id, as_of, target_count) VALUES (%(campaign)s, %(as_of)s, 0)
    ON CONFLICT (campaign_id) DO NOTHING          -- 再実行では最初の as_of を使い続ける
    RETURNING as_of
"""

FREEZE_TARGETS_SQL = """
    INSERT INTO coupon_grant_targets (campaign_id, member_id)
    SELECT %(campaign)s, m.member_id FROM members m
    WHERE m.last_purchase_at < %(as_of)s - make_interval(days => %(days)s)   -- now() を使わない
"""

# キーセット（シーク）方式: OFFSET で読み飛ばさず、一意キーの「続き」から索引で引く
PAGE_SQL = """
    SELECT t.member_id FROM coupon_grant_targets t
    WHERE t.campaign_id = %(campaign)s
      AND t.member_id > %(after)s                 -- 前ページの最後の次から（>= にすると重なる）
    ORDER BY t.member_id                          -- 一意キーで並べる。同値があると続きが決まらない
    LIMIT %(limit)s
"""

GRANT_SQL = """
    INSERT INTO coupon_grants (campaign_id, member_id)
    SELECT %(campaign)s, unnest(%(ids)s::bigint[])
    ON CONFLICT (campaign_id, member_id) DO NOTHING   -- 一意制約が「1 人 1 枚」を最後に守る
"""


def freeze_targets(conn, campaign_id, as_of) -> dt.datetime:
    """対象者を as_of 時点で確定し、表に書き出す（2 回目以降は何もしない）。

    Args:
        conn: autocommit の接続。
        campaign_id: 施策 ID。
        as_of: 休眠判定の基準時刻。初回だけ使われる。

    Returns:
        実際に使われている基準時刻（再実行なら初回の値）。
    """
    with conn.transaction():
        row = conn.execute(FREEZE_RUN_SQL, {...}).fetchone()
        if row is not None:  # 初回: 同じトランザクションなので、半端な凍結は残らない
            cur = conn.execute(FREEZE_TARGETS_SQL, {...})
            conn.execute("UPDATE grant_runs SET target_count = %s ...", (cur.rowcount, campaign_id))
    if row is not None:
        # 書き出した直後は統計が空で、プランナが索引を使わず「全件読んで並べ替え」を選ぶ（実測 120 ms/ページ）
        conn.execute("ANALYZE coupon_grant_targets")
    return conn.execute("SELECT as_of FROM grant_runs WHERE campaign_id = %s", ...).fetchone()[0]


def run(conn, campaign_id, as_of, *, between_pages=None, before_commit=None,
        page_size=PAGE_SIZE) -> GrantSummary:
    """休眠会員に復帰クーポンを 1 人 1 枚配る。落ちたら同じ引数で呼び直せば続きから再開する。

    Raises:
        GrantReconciliationError: 最後の照合で対象数と付与済み数が合わないとき。
    """
    frozen_as_of = freeze_targets(conn, campaign_id, as_of)
    inserted = pages = 0
    while True:
        with conn.transaction():
            # 進捗の行をロックしてから読む: 同じ施策のバッチが 2 本走ってもページを取り合わない
            after = conn.execute("SELECT last_member_id FROM grant_runs WHERE campaign_id = %s FOR UPDATE", ...)
            ids = [r[0] for r in conn.execute(PAGE_SQL, {...})]
            if not ids:
                break
            if ids[-1] <= after:  # 続きが前に進まない＝読み方が壊れている。無限ループにせず止める
                raise RuntimeError(...)
            inserted += conn.execute(GRANT_SQL, {...}).rowcount
            # 付与と進捗を同じトランザクションで確定する（片方だけ残る瞬間を作らない）
            conn.execute("UPDATE grant_runs SET last_member_id = %s ...", (ids[-1], campaign_id))
        pages += 1
    target_count = _reconcile(conn, campaign_id)   # 対象数 ≠ 付与済み数 なら GrantReconciliationError
    return GrantSummary(campaign_id, frozen_as_of, target_count, inserted, pages)

created_at の順で読みたいときは、主キーを足して行値で比べる: WHERE (created_at, member_id) > (%(c)s, %(id)s) ORDER BY created_at, member_id（索引 (created_at, member_id)。向きが混ざると行値の比較は使えない）。一意制約の追加は CREATE UNIQUE INDEX CONCURRENTLY → ADD CONSTRAINT ... USING INDEX で書き込みを止めずに。

テストと変異（本物の PostgreSQL に流す）

変異テスト（grant_good.py を 1 か所ずつ壊す）

実行例（input → output、実測）

📋 コピー# Good のテスト（会員 2 万人、1 ページ 500 件）
$ IMPL=good uv run --with 'psycopg[binary]' --with pytest pytest -q test_grant.py
10 passed in 6.92s

# 会員 30 万人、乱数の種 1
Bad 静かな夜               targets=119719 granted= 60000 missed= 59719 dup=   0 pages= 60
D1 ガード無し ログインだけ   targets=119719 granted=119404 missed=   315 dup= 315 pages=120
D1 ガード無し 外の更新あり   targets=119719 granted=117078 missed=  2641 dup= 301 pages=118
後輩A fetchall             targets=119719 granted=119719 missed=     0 dup=   0 pages=120
後輩B REPEATABLE READ      targets=119719 granted= 60000 missed= 59719 dup=   0 pages= 60
Good 外の更新あり           targets=119719 granted=119719 missed=     0 dup=   0 pages=120

# 長いトランザクションと VACUUM
後輩B（1 トランザクション）  回収できた古い行=     0  回収できなかった古い行= 19191
Good（ページごとに確定）     回収できた古い行= 11337  回収できなかった古い行=     0

今日いちばん意外だった実測: 凍結表を書き出した直後は統計が空で、キーセットなのに先頭ページで 1,605 ブロック・約 112 ms（OFFSET の先頭は 20 ブロック）。ANALYZE 後は深さによらず 17 ブロック・1 ms 未満。キーセットは「書けば速い」のではなく「索引で引ける計画になって初めて速い」。

迷った案と採らなかった理由

直した後に見るもの・止め方・事後対応

• 見るもの: 対象数と付与済み数の差（0 でなければ Argo が Failed）／1 ページの処理時間の推移（深さに比例して伸びたら ANALYZE 忘れか索引の不在）／idle in transaction の時間と backend_xmin の古さ／付与数 ÷ 前日の対象数
• やめる条件: 付与が外部 API 呼び出しになったら（外部の成功と DB の確定が別になる）outbox へ。対象が数千万人で凍結の INSERT 自体が長くなるなら、凍結も範囲で分割
• 事後対応: 当初の対象を as_of = 2026-10-01 02:00 JST で凍結し直し、未付与の約 6 万人にだけ配る（期限は今回の配布日から）。日をまたいだ手動再実行で対象外に配った人を特定し、取り消すかは販促が金額影響を見て判断。問い合わせた 214 人への回答文を CS に。同じ形のバッチを OFFSET と NOT EXISTS で検索

ポイント解説

1. OFFSET は「位置」、キーセットは「値」で続きを指す: 位置は集合や並びが変わると別の行を指す。読みながら書くバッチでは値で指す
2. 自分の書き込みも外の変化: 6 万人の漏れは自分の INSERT のせい。REPEATABLE READ が効かないのも、自分の書き込みは自分に見えるから
3. 「完了」は照合で定義する: 照合があれば 02:00 の時点で Failed になっていた
4. 安全網は破れる状況を作ってテストする: 一意制約・同じトランザクション・行ロックは互いを隠す
5. 書籍との対応: 第 4 章 不変の活用（対象の凍結）、第 3 章 値オブジェクト（GrantSummary）、第 10 章 例外を握り潰さない（照合・進まないループ）、第 13 章 コマンドとクエリの分離。パターン: Seek Method／Snapshot Target Set／Transactional Checkpoint（Spring Batch の ItemStream と同じ考え方）／Idempotent Receiver

実務への応用

• メール配信・一斉付与: 配信リストを as_of で凍結、一意制約、照合。外部 API なら outbox
• エクスポート: 読むだけなら 1 本のスナップショットで十分。ただし長いスナップショットは VACUUM を止める（19,191 行）ので読み取りレプリカで
• 管理画面の「もっと見る」: Stripe の starting_after と同じ形のカーソル。カーソルは利用者の入力なので検証する
• Argo の retryStrategy: 何度流しても同じ結果になるステップが前提。Bad は流すたびに結果が変わった
• dbt incremental: 「前回の最大値より後」もキーセット。同じ秒の境界で取りこぼすので >= で取り直して一意キーで merge

扱わなかった範囲・一次資料

• Cloud SQL 上での実測 — 手元の PostgreSQL 14.11 のみ。ディスク読みが遅いぶん OFFSET の差は開くはず（未測定）
• 本当の並行実行 — 外の更新はページの合間に差し込んだ（決定的に再現するため）
• 並びが不定になる他の原因（並列スキャン・synchronize_seqscans・索引の作り直し）— 再現していない。M2 が生き残った理由
• 画面のページ番号・総件数 — バッチに絞った
• 景品表示法・クーポン規約上の扱い — 判断は販促と法務

参照日 2026-10-11。
PostgreSQL 7.6 LIMIT and OFFSET https://www.postgresql.org/docs/current/queries-limit.html（"The rows skipped by an OFFSET clause still have to be computed inside the server"）
PostgreSQL 9.9.5 Current Date/Time https://www.postgresql.org/docs/current/functions-datetime.html
PostgreSQL 13.2.2 Repeatable Read https://www.postgresql.org/docs/current/transaction-iso.html
PostgreSQL 25.1 Routine Vacuuming https://www.postgresql.org/docs/current/routine-vacuuming.html
Markus Winand, No Offset https://use-the-index-luke.com/no-offset
Stripe API Pagination https://docs.stripe.com/api/pagination
Spring Batch ItemStream https://docs.spring.io/spring-batch/reference/readers-and-writers/item-stream.html

今日のまとめ

読みながら書くバッチで OFFSET を使うと、自分が配った行が集合から消えるだけで半分飛ぶ（12 万人中 6 万人）。対象を開始時刻で凍結し、一意キーの続きから読み、付与と進捗を同じトランザクションで確定し、最後に件数を照合すれば、外の更新があっても・落ちても・何度流しても全員にちょうど 1 枚。ただしキーセットも統計が無ければ索引を使わない。

自己評価（あとで記入）