メインコンテンツへスキップ
見出し画像
Photo by4540_73

PostgreSQLのページネーションをOFFSETからKeysetへ:重複を防ぐSQL設計

    個人開発しているもの

    日々の開発で得た知識を発信しながら、「あったら便利」「作ってみたい」と思ったアイデアを形にしています。

    これまでに、グループ旅行アプリ Trip Supporter、スコア管理サービス PointPia、リアルタイム共有サービス JustNow などを開発しました。

    さらに、刺繍やオリジナルキャラクターを中心としたアパレルブランド MAD TOMMY WITH... も運営しています。

    開発したサービスや技術記事は chicchi.work で紹介しています。

    記事一覧や履歴APIの「次の20件」を取得するとき、LIMIT 20 OFFSET 10000 のようなSQLを書いていないでしょうか。

    OFFSET は理解しやすい反面、ページが深くなるほど読み飛ばす行が増えます。また、ページを移動する途中で新しい行が追加されると、同じ行が再登場することもあります。

    今回は、Keyset Pagination(キーセット方式、Seek Paginationとも呼ぶ)へ切り替えるSQLを、実際に試せるデータ付きで解説します。これは、前ページ最後の行の「並び順の値」を使って、次の行から読み進める方法です。

    対象はPostgreSQL。公式ドキュメントのLIMIT/OFFSET、行の比較、複合インデックス、EXPLAINの説明を2026年11月5日に確認しています。性能改善の大きさはデータ量や実行計画によって異なり、本記事では未計測の速度を数値で断定しません。

    1. OFFSET方式の落とし穴

    典型的なページングは次のように書きます。

    SELECT id, created_at, title
    FROM posts
    ORDER BY created_at DESC, id DESC
    LIMIT 20 OFFSET 10000;
    • ORDER BY:結果の順序を決めます。

    • LIMIT 20:最大20行を返します。

    • OFFSET 10000:先頭の10,000行を読み飛ばします。

    PostgreSQL公式ドキュメントは、OFFSETで飛ばす行もサーバー内で計算する必要があり、大きなOFFSETは非効率になり得ると説明しています。また、LIMITを使う場合は一意に決まる順序をORDER BYで指定することが重要です。

    created_atだけでは同時刻に作られた記事の順序が定まりません。そこで、一意なidを後ろに追加します。

    より厄介なのは更新が続く一覧です。例えば1ページ3件として、最初に [6, 5, 4] を表示した後、新しい行7が先頭に追加されたとします。次にOFFSET 3を実行すると、[4, 3, 2] が返り、4が重複します。

    2. 再現用のテーブルとデータ

    以下は空の検証用データベースで実行する例です。既存テーブルがある本番環境にそのまま流さないでください。

    CREATE TABLE posts (
        id bigint PRIMARY KEY,
        created_at timestamptz NOT NULL,
        title text NOT NULL
    );
    
    INSERT INTO posts (id, created_at, title) VALUES
    (1, '2026-11-05 09:00:00+09', 'post-1'),
    (2, '2026-11-05 09:10:00+09', 'post-2'),
    (3, '2026-11-05 09:10:00+09', 'post-3'),
    (4, '2026-11-05 09:20:00+09', 'post-4'),
    (5, '2026-11-05 09:30:00+09', 'post-5'),
    (6, '2026-11-05 09:30:00+09', 'post-6');
    
    CREATE INDEX posts_created_at_id_desc_idx
        ON posts (created_at DESC, id DESC);
    • bigint:大きな整数を保存する型です。実際のアプリでは自動採番列を使うこともできますが、ここでは再現性のためIDを固定しています。

    • timestamptz:時刻をタイムゾーンを考慮して扱う型です。

    • 複合インデックス:複数列を組み合わせて検索・並べ替えを補助する索引です。今回はcreated_atとidの降順を指定します。

    まず1ページ目を取得します。

    SELECT id, created_at, title
    FROM posts
    ORDER BY created_at DESC, id DESC
    LIMIT 3;

    結果のIDは6, 5, 4です。5と6は同じ作成時刻ですが、id DESCで順序が安定します。

    3. Keyset方式:最後の行をカーソルにする

    カーソルとは、次のページの開始位置を表す情報です。データベースのカーソル機能を使うという意味ではなく、ここでは「最後に表示したcreated_atとid」というAPI上の目印を指します。

    1ページ目の最後は(2026-11-05 09:20:00+09, 4)なので、2ページ目は次のSQLです。

    SELECT id, created_at, title
    FROM posts
    WHERE (created_at, id)
          < ('2026-11-05 09:20:00+09'::timestamptz, 4::bigint)
    ORDER BY created_at DESC, id DESC
    LIMIT 3;

    結果のIDは3, 2, 1です。

    (created_at, id) < (時刻, ID)は行コンストラクターの比較です。左から順番に比較し、まず時刻が古いかを判定します。時刻が同じならIDの大小を比較します。今回はどちらも降順なので「前ページの最後より小さい組」を探します。

    この書き方が成立する前提は、並び順がcreated_at DESC, id DESCで、両列がNULLでないことです。片方だけ昇順にする並びやNULLを含む並びでは、そのまま使わず比較条件を組み立て直してください。

    途中で新規投稿があった場合

    最初のページを読んだ後、次の行を追加してみます。

    INSERT INTO posts (id, created_at, title)
    VALUES (7, '2026-11-05 09:40:00+09', 'post-7');

    この状態でOFFSET 3なら4, 3, 2となり、4が重複します。一方、前述のKeysetの2ページ目は3, 2, 1のままです。新しい行が先頭に挿入されても、位置のずれに引きずられにくいことが分かります。

    ただし、Keyset方式も「複数リクエストをまたいで完全に同じ一覧を再現する」機能ではありません。行の削除やcreated_atの変更、フィルター変更などによって見え方は変わります。完全なスナップショットが必要なら、データのバージョン固定など別の設計を検討します。

    4. APIの実装で必要なこと

    実際のバックエンドでは、カーソル値を文字列連結でSQLへ埋め込まず、プレースホルダーを使います。プレースホルダーとは、値をSQL本文とは別にバインドするための印です。

    -- $1: 最後に表示したcreated_at
    -- $2: 最後に表示したid
    -- $3: ユーザーに見せる件数(例: 20)
    SELECT id, created_at, title
    FROM posts
    WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
    ORDER BY created_at DESC, id DESC
    LIMIT ($3 + 1);

    LIMIT (件数 + 1)にして、表示予定数より1行多く取得します。例えば20件表示するAPIなら21件を読み、21件目があればhasNext: trueと判断します。クライアントに返すのは先頭20件だけで、次のカーソルは返した20件の最後の行から作ります。21件目をカーソルにすると1行飛ばしてしまうので注意してください。

    初回リクエストにはカーソルがないため、WHERE条件を省いたクエリを使います。APIのレスポンスは、例えば次の形です。

    {
      "items": [
        { "id": 6, "title": "post-6" },
        { "id": 5, "title": "post-5" },
        { "id": 4, "title": "post-4" }
      ],
      "hasNext": true,
      "nextCursor": "サーバーが発行するカーソル文字列"
    }

    ※このJSONはレスポンス形状の例です。上記SQLデータは6行あるので、3件表示ならhasNextはtrueになります。

    カーソル文字列には時刻とIDの組をエンコードできます。Base64などによるエンコード自体に改ざん防止効果はありません。サーバーでは形式、型、ページサイズ、テナント・フィルター条件を検証し、必要なら署名を付けます。カーソルを権限チェックの代わりにはせず、アクセス制御は毎回SQL側で適用します。

    テナント別の一覧なら、例えばWHERE tenant_id = $1 AND (created_at, id) < (...)のように絞り込みます。その場合は(tenant_id, created_at DESC, id DESC)のようなインデックスも候補になります。実データの選択性と実行計画を見て決めてください。

    5. 効果を測る:EXPLAIN (ANALYZE, BUFFERS)

    PostgreSQLは、SQLをどの順序・方法で実行するかを実行計画として選びます。インデックスを作れば必ず採用されるわけではありません。

    EXPLAIN (ANALYZE, BUFFERS)
    SELECT id, created_at, title
    FROM posts
    ORDER BY created_at DESC, id DESC
    LIMIT 20 OFFSET 10000;
    
    EXPLAIN (ANALYZE, BUFFERS)
    SELECT id, created_at, title
    FROM posts
    WHERE (created_at, id) < ('2026-11-05 09:20:00+09'::timestamptz, 4::bigint)
    ORDER BY created_at DESC, id DESC
    LIMIT 20;
    • EXPLAIN:DBが選んだ実行計画を表示します。

    • ANALYZE:実際にクエリを実行し、処理時間や行数などを計測します。更新系SQLで使う場合は実際に変更が起きるので要注意です。

    • BUFFERS:共有バッファなどへのアクセス情報を表示します。

    比較時は、同程度の結果件数、十分なデータ量、適切なカーソル位置を使い、actual rows・実行時間・バッファアクセスを確認します。上記の6行だけの例では深いページの性能差を適切に測れません。また、キャッシュ状態や統計情報によって計測値は変動します。

    6. 使い分けとハマりどころ

    • 要件:「次を読み込む」無限スクロール、候補:Keyset、理由:既知の終端位置から読み進めやすい

    • 要件:更新頻度が高い新着一覧、候補:Keyset、理由:先頭追加によるOFFSETの重複を避けやすい

    • 要件:任意の100ページ目へジャンプ、候補:OFFSETなど、理由:Keysetは前のカーソルなしに任意ページへ直接飛びづらい

    • 要件:小規模・更新の少ない管理画面、候補:OFFSET、理由:実装の単純さが勝ることもある

    • 要件:厳密な固定時点の一覧、候補:別途スナップショット設計、理由:Keysetだけでは複数リクエスト間の整合性を保証しない

    特に次の点で設計が崩れがちです。

    • 同じ時刻の行を考慮しない:created_at < カーソル時刻だけでは、同時刻の未取得行を飛ばすことがあります。一意なidをタイブレーカー(同値時の決着用のキー)にします。

    • ソート列が途中で変更される:並び順のキーが変われば、重複・取りこぼしが生じ得ます。新着一覧なら通常変更されない作成日時などを選びます。

    • インデックスとORDER BYが合っていない:複合インデックスの列順・昇降順・絞り込み条件を確認します。

    • カーソルに認可情報を任せる:カーソルは位置情報であってアクセス権限の証明ではありません。

    • 件数+1を返却範囲に混ぜる:余分に取得した1行は存在確認用で、次ページを飛ばす材料にしません。

    まとめ

    OFFSETは簡潔ですが、深いページほど読み飛ばす行が増え、新しい行が先頭に追加される一覧ではページ境界の重複が起き得ます。created_at DESC, id DESCのように一意な並び順を用意し、最後のキーで次を探すKeyset方式は、続き読み型APIに向いた選択肢です。

    ただし、万能な最適化ではありません。必要なページ移動UX、データ更新の仕方、インデックス、認可条件を合わせて設計し、EXPLAIN (ANALYZE, BUFFERS)で実測してください。

    参考資料(PostgreSQL公式)

     
     
    バックエンドエンジニア・個人開発者のちっちです。AWS/Java/Spring Bootが専門。個人開発でTrip Supporter、PointPia、JustNow等を運営し、技術知見を発信中。趣味はコーヒー/猫/バイク https://chicchi.work/

    あなたへのおすすめ