先月、社内で外部向けの一覧APIの設計レビューをしていたときに、「このページ送り、OFFSETで大丈夫ですか」と聞かれました。
私は「更新の少ないデータなので問題ないです」と答えかけて、そこで止まりました。OFFSETでページを送ると行が重複したり抜けたりする、というのは知っています。ただ、それは読んでいる最中に行が追加・削除されたときの話だと理解していて、その理解のまま十年近くやってきました。更新がなければ本当にずれないのか、自分で確かめたことは一度もなかったんですよね。
受託の業務システムでは、一覧APIが「画面に1ページ出す」以外の使われ方をするケースもありました。取引先のシステムが夜間に1ページ目から最後まで読み切って取り込む、管理画面の「CSV出力」が内部で同じAPIを全ページ回す、といった使い方です。こういう読み方で行がずれると、件数は合っているのに中身が合わない、という気付きにくい壊れ方をします。
なので、手元のPostgreSQL 16（Docker）で実測してみました。
再現した状況
注文テーブルに1,000件。夜間バッチで1日250件ずつ、4日分を取り込んだ想定です。取り込みは1回のトランザクションで流すので、同じ日の行は created_at が完全に同じ値になります。DEFAULT now() で時刻を入れているテーブルだと、だいたいこうなっているはずです。
-- 4日分の取り込みを再現（同じ日の行は created_at が完全に同じ値）
CREATE TABLE orders (
id bigserial PRIMARY KEY,
created_at timestamptz NOT NULL,
amount int NOT NULL
);
INSERT INTO orders (created_at, amount)
SELECT timestamptz '2026-09-25 02:00:00+09' + (g % 4) * interval '1 day',
(g * 37) % 10000
FROM generate_series(0, 999) AS g
ORDER BY md5(g::text); -- 物理的な並びはばらばらにしておく
-- よくある一覧APIのクエリ（50件ずつ、新しい順）
SELECT id FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET :offset;これを1ページ50件で最後まで読み、返ってきた id を数えます。重複がいくつあり、一度も返ってこなかった行がいくつあるか、です。集計に使ったのは次のスクリプトです。
import collections, subprocess
def query(sql):
# docker 上の psql に投げて、先頭の列だけを返す（検証用の手抜き実装）
out = subprocess.run(
["docker", "exec", "-i", "pg8838", "psql", "-U", "postgres", "-d", "shop", "-At"],
input=sql, capture_output=True, text=True, check=True)
return [line.split("|")[0] for line in out.stdout.splitlines() if line]
seen, offset = [], 0
while rows := query(f"SELECT id FROM orders ORDER BY created_at DESC LIMIT 50 OFFSET {offset}"):
seen += rows
offset += 50
counts = collections.Counter(seen)
print(len(seen), len(counts), 1000 - len(counts))
# 実行結果（created_at, id の複合インデックスあり）: 1000 1000 0
# 実行結果（並べ替えに使える索引なし） : 1000 818 182最初の計測では、何も起きなかった
最初は (created_at, id) の複合インデックスを張った状態で測りました。結果は1,000件取得、重複0、欠落0。「ほら、更新がなければ大丈夫」と思ったのが正直なところです。
ところが、インデックスを外して同じスクリプトを回すと、集まった id は818種類しかありませんでした。182件が2回ずつ返ってきて、別の182件は一度も返ってきていない。データは1行も更新していません。もう一度回しても、同じ182件・182件でした。
インデックスを外すのは乱暴に見えるかもしれませんが、現場ではむしろ普通の状態だったりします。一覧APIに「金額順」「ステータス順」のような並べ替えを足すと、その列にはまずインデックスがありませんし、CSV出力のバッチだけ別の列で並べている、というのもよくある話です。
原因は実行計画を見ると分かりました。EXPLAIN ANALYZE で各ページのソート方式を見ると、OFFSET 400 までは top-N heapsort、OFFSET 450 からは quicksort でした。上位N件だけを残すやり方と、全部を並べてから切り出すやり方が、ページの位置によって切り替わっていたわけです。どちらの方式でも created_at が同じ250件の中の並びは決まっていないので、ページをまたいだところで並びが揺れて、重なりと穴ができます。インデックスがあるときに揃っていたのは、インデックスを逆順にたどると同じ時刻の行が id の順に出てくるから。正しかったのではなく、たまたま揃っていただけでした。
ドキュメントにはこう書いてあるけど、という話でいうと、PostgreSQLのマニュアルには、LIMIT と OFFSET の値が変わると別の実行計画が選ばれうるので、結果の順序は ORDER BY で決めておくように、とあります。同じく、ORDER BY に書いた列がすべて同じ値の行は実装依存の順で返る、とも書かれています。私は前者だけを覚えていて、「ORDER BY を書いていれば大丈夫」と読み違えていました。必要なのは、行の順序が一意に決まる ORDER BY です。
対策そのものは単純で、ORDER BY created_at DESC, id DESC のように一意な列を最後に足すだけです。インデックスなしのままでも、これで重複0・欠落0になりました。
それでも、読んでいる途中に行が増えるとずれる
ここからは、よく知られている方のずれです。id を足したOFFSET方式で、1ページ読むたびに新しい注文を1件追加しながら読み切ると、取得は1,020件、重複は20件でした。新しい行が先頭に入るたびに既存の行が1つずつ後ろへ押し出され、前のページの最後の行が次のページの先頭にもう一度出てきます。削除が挟まれば逆に行が飛ばされます。
夜間の取り込み中に日中の注文が入ってくる、というのは十分ありうる状況です。全件を読み切る用途なら、OFFSETではなく「前のページの最後の行より後ろ」を条件にするカーソル方式（キーセット方式）にするべきだ、という結論になりました。
最初のアプローチ: created_at だけでカーソルを作った
ここで一度失敗しています。最初に書いたカーソルは、前ページ最後の行の created_at だけを覚えておき、次のページを WHERE created_at < :last_created_at で取る形でした。
結果は200件取得、欠落800件です。1ページ目は最新の日の250件のうち50件で終わります。2ページ目の条件は「その時刻より前」なので、同じ時刻の残り200件を丸ごと飛ばして前日に移ってしまう。4日分それぞれで50件ずつしか読めませんでした。不等号を <= にすれば済むかというと、今度は同じ日の行を頭から読み直すことになって、ページが進まなくなります。
時刻が重なるのはまれだろう、と考えていたのが甘かったところです。今思えば、時刻だけで行を特定しようとしたのは、OFFSET版で id を足し忘れていたのと同じ間違いでした。
改善後のアプローチ: 並び順の列すべてと一意キーで比較する
最終的には、並び順に使う列と一意な id の組をカーソルにして、行値の比較で次のページを取る形にしました。
-- 並び順と同じ列順の複合インデックス（逆順にたどれば DESC にも使える）
CREATE INDEX orders_created_at_id_idx ON orders (created_at, id);
-- 1ページ目
SELECT id, created_at FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;
-- 2ページ目以降: 前ページ最後の行の (created_at, id) より後ろだけ
SELECT id, created_at FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;ORDER BY の列と向き、WHERE の行値比較、インデックスの列順の3つを揃えるのがポイントです。created_at DESC, id ASC のように向きを混ぜると、この1行の比較では書けなくなります。どうしても混ぜたいときは条件を OR で展開することになります。ここは好みが分かれるところですが、私は並びの向きを揃える方を選んでいます。
これで、更新なしでは1,000件・重複0・欠落0。1ページごとに1件追加しながら読んでも1,000件・重複0・欠落0でした。読み始めた後に入った新しい注文は今回の読み取りには含まれず、次回の取り込みで拾われます。
ついでに、選ばなかった案も書いておきます。id だけをカーソルにする案は、表示順が created_at なので採りませんでした。過去分の移行データを後から入れると、id は新しいのに created_at は古い、という行ができるからです。トランザクションを張ったまま全ページを読んで一貫した断面を見る案も考えましたが、HTTPのリクエストをまたいでトランザクションを持ち越すのは現実的ではありませんし、長いトランザクションは不要行の掃除（VACUUM）も妨げるので見送りました。
| 読み方 | 条件 | 取得行数 | 重複 | 欠落 |
|---|---|---|---|---|
| OFFSET（created_at のみで並べる） | 複合インデックスあり・更新なし | 1,000 | 0 | 0 |
| OFFSET（created_at のみで並べる） | インデックスなし・更新なし | 1,000 | 182 | 182 |
| OFFSET（id を最後に足す） | インデックスなし・更新なし | 1,000 | 0 | 0 |
| OFFSET（id を最後に足す） | 1ページ読むごとに1件追加 | 1,020 | 20 | 0 |
| カーソル（created_at のみ） | 更新なし | 200 | 0 | 800 |
| カーソル（created_at と id） | 更新なし | 1,000 | 0 | 0 |
| カーソル（created_at と id） | 1ページ読むごとに1件追加 | 1,000 | 0 | 0 |
| カーソル（created_at と id） | カーソルの時刻をミリ秒に丸めて往復 | 971 | 0 | 29 |
脱線: カーソルの時刻が、途中でミリ秒に丸められる
表の最後の行は、エッジケース好きの性分でつい試してしまったものです。ただ、これが一番現場で踏みそうだと感じています。
created_at に clock_timestamp() を使っていたり、取り込み処理がアプリ側で1行ずつ時刻を振っていたりすると、時刻はマイクロ秒単位で少しずつずれた値になります。今回は1,000件を97マイクロ秒間隔で入れました。1ミリ秒に10件前後が入り、全体で約97ミリ秒に収まる密度です。
PostgreSQLの timestamptz はマイクロ秒まで持っています。一方、APIをNode.jsで書いていて、ドライバが timestamptz をJavaScriptの Date に変換する既定の動きのままだと、Date はミリ秒までしか持てないので、カーソルに入れる時刻はその時点でミリ秒に切り捨てられます。手元ではNode.jsは使わず、Pythonでカーソルの時刻をミリ秒で切り捨てて往復させ、この状況を再現しました。
結果は971件取得、欠落29件。切り捨てでカーソルの時刻がわずかに過去へずれ、新しい順に読んでいるので、その差の分を飛び越えていました。消えていたのは、前ページ最後の行と同じミリ秒の中で、それより少し前の時刻を持つ行です。新しい順に読む限り、切り捨ては「欠落」として出ます。古い順に読んでいたら、逆に重複として出ていたはずです。
なので、カーソルには時刻をDBの精度のまま入れ、クライアントには中身を意識させない文字列として渡すことにしました。
import base64, json
def encode_cursor(row):
# created_at は DB 側で文字列にしてから受け取り、マイクロ秒まで残す
# （Date や float に一度でも通すと、そこで精度が落ちる）
payload = {"t": row["created_at_text"], "id": row["id"]}
return base64.urlsafe_b64encode(json.dumps(payload).encode()).decode()
def decode_cursor(cursor):
payload = json.loads(base64.urlsafe_b64decode(cursor.encode()))
return payload["t"], int(payload["id"])
# 改善の余地あり: 並び順や絞り込み条件をカーソルに含めていないので、
# 条件を変えたうえで古いカーソルを渡されても検知できない。署名もしていない。created_at_text は、SELECTの段階で to_char を使い、マイクロ秒まで含めたUTCの文字列にしています。白状すると、このコードには上のコメントのとおり穴が残っています。絞り込み条件を変えて古いカーソルを使い回されると、黙って変な位置から読み始めてしまいます。外部に公開するAPIなら、条件のハッシュを入れて照合するか、署名を付けるところまでやるべきだと考えています。
いま一覧APIを作るときに確認していること
結局、次のように整理しました。
- 並び順は、最後に一意な列を足して一意に決まる形にする。OFFSETでもカーソルでも同じ
- 画面でページ番号を選ばせる管理画面は、OFFSETのままでよい。総件数やページ番号が必要で、件数も多くないことが多いので
- 連携先が全件を読み切る用途、CSV出力、無限スクロールはカーソルにする。読んでいる途中の追加や削除でずれないため
- カーソルは、並び順の列すべてと一意キーの組で作り、
ORDER BY・行値比較・インデックスの列と向きを揃える - カーソルの値はDBの精度のまま往復させる。時刻を言語側の日時型に通さない
- 試験では、同じ値の行を大量に作り、全ページを読んで重複0・欠落0を確かめる。インデックスの有無の両方で回す
カーソルにも弱点はあります。50ページ目にいきなり飛ぶことはできませんし、総件数を出すには別に COUNT を流すことになります。読み始めた位置より後ろに、古い日付の行が後から入ってきた場合も読めません。OFFSETの深いページほど読み捨てる行が増えて遅くなる、という性能面の話もありますが、今回の1,000件では差が出なかったので、ここでは扱っていません。
振り返って
一番こたえたのは、インデックスがあるときの「重複0・欠落0」を、正しさの証拠だと受け取りかけたことでした。結果が正しかったのは実行計画がたまたまそうなっていたからで、並べ替えの列が1つ変われば崩れる状態だったわけです。
今後は、一覧APIの試験に「同じ値の行を大量に入れ、全ページを読んで重複と欠落を数える」検査を標準で入れていきたいと考えています。今回のスクリプトはそのまま種になりそうです。それと、更新日時を見て差分だけを取り込む連携では、後から入る古い日付の行の扱いがまた別の問題になるので、そちらも一度測ってみるつもりです。