きっかけ
REST API でサーバーサイドページネーションを実現したいと考えたのですが、指定したページのデータをデータベースから効率よく検索できないのであれば、一回ですべてを取得した方が結果的に効率的になります。
クライアントのスペックをそれなりに見込むことができれば、クライアントサイドページネーションの方が効率的ですが、ヒットする件数が膨大だったり、1レコードのデータ量が多い場合はそれも難しくなるため、何らかの形でサーバーサイドページネーションを検討する必要がでてきます。
ChatGPT に REST API でのサーバーサイドページネーションの実現方法について訊いたところ、以下の3つの方式を提案されました。
オフセットベースページネーション
GET /items?offset=20&limit=10
特徴
- 利点：実装が簡単。UI側でも扱いやすい。
- 欠点：大量のデータでページ数が大きいと、パフォーマンスが低下（インデックスが効かなくなる可能性）。
もっともよく見かけるパターンだと思います。どこから取得するか（offset）と最大何件取得するか（limit）で指定する方法になります。そもそも一度に取得する件数に200件とかの上限があって、201件目以降を取得したい場合はoffiset付きで取得してください、といった使われ方をするのが多いのではないでしょうか。
単純増加かつ昇順で検索する場合はデータの重複や抜けが起きにくいですが、単純増加かつ降順の場合や途中にデータが差し込まれる場合は前回取得した位置がズレるので、データの抜けや重複が発生する場合があります。
ページベースページネーション
GET /items?page=3&size=10
特徴
- 利点：ユーザー視点ではページ単位が直感的。
- 欠点：内部的にはoffset/limitと同じ課題（大きなページ数で遅くなる）。
オフセットベースと基本的に同じ発想ですが、指定方法が具体的な件数ではなくページという単位にすることで指定しやすくした方式でです。多くのサービスでは１ページあたりのサイズ（size）は指定せず、ページのみでアクセスさせるのではないかと思います。サービス側で設定しているページサイズをもとにデータを区切って何ページ目を取得するか、という形で指定することになります。
指定方法が異なるだけで、基本的な性質はオフセットベースと同じです。
カーソルベースページネーション
GET /items?after=eyJpZCI6MTIzfQ==&limit=10
特徴
- 利点：安定して高速（インデックスが効く）。データが更新されても影響を受けにくい。
- 欠点：実装がやや複雑。前のページに戻るのが難しい。
前述のオフセットベースとページベースでは、単純増加のキーで保存されているデータを降順で取得するとデータの抜けの心配があります。多少前後で同じデータが表示されたり、抜けがあってもそれほど問題にならない場合は、オフセットベースやページベースが簡単だと思いますが、特に抜けを許容できない場合はカーソルベースページネーションが有効な手立てとなります。
条件として検索キーが単純増加かつ途中に差し込まれることがないテーブルに対して指定した値（after）よりも前や後のデータをヒットさせるようにすることで重複や抜けがないようにデータを取得することができます。
いずれのパターンにおいても、テーブル設計やインデックス設計が重要となることに加え、取得されたデータの重複や抜けをどこまで対策するか許容するかの要件によって選択肢が変わってきます。
後述の検証では、カーソルベースページネーションをPostgreSQLのLIMIT句を使って実現できるかを検証してきます。
PostgreSQLのLIMIT句の挙動について
PostgreSQLのLIMIT句を使ってカーソルベースページネーションが有効に働くかを訊いたところ、以下のように回答がありました。
質問
以下のようなSQLを発行したときに、インデックスを使って条件に合致する10件のみをフェッチできますか？
SELECT * FROM histories
WHERE create_user_id = ? AND id < ?
ORDER BY id DESC
LIMIT 10;
前提条件
- id：PK（= 自動で昇順インデックスがある）＋ id DESC の降順インデックスも明示的に付与されている
- create_user_id：昇順インデックスあり（例：CREATE INDEX ON histories (create_user_id)）
回答
PostgreSQLは 複合インデックス がある場合に、以下のような最適化を実現できます：
CREATE INDEX idx_histories_userid_id_desc
ON histories (create_user_id, id DESC);
どうやら、個別のインデックスではダメで、WHERE句とLIMIT句の両方を満たす1つのインデックスがあれば、インデックスを効率的に使用して条件に合致する10件のみをフェッチできるようです。
ChatGPT からは PostgreSQL をデータベースとして使用している場合に、 LIMIT句を使った方法が可能であると回答をもらいました。ただし、生成AIは必ずしも正しい回答をするわけではないので、実際に試して検証することにします。
カーソルベースページネーションの検証
Docker composeでPostgreSQLを構築する
手早くPostgreSQLの環境を構築するために、Docker composeで作成しました。
services:
db:
image: postgres:17
container_name: postgres17
restart: unless-stopped
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
ports:
- "5432:5432"
volumes:
- ./initdb:/docker-entrypoint-initdb.d:ro
すでに同じcontainer_nameを起動している場合は他の名前を指定してください。とりあえず起動確認だけしておきます。
$ docker compose up -d
[+] Running 2/2
✔ Network pagination-demo_default Created
✔ Container postgres17 Started
$ psql -h localhost -U postgres -d postgres
Password for user postgres:
psql (17.4, server 17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.
postgres=# \dt
Did not find any relations.
postgres=#
postgres-# \q
とりあえず問題なさそうです。
検証用のテーブルを作成する
今回は以下の要件を満たす、download_historiesテーブルを作成します。
- PKはレコードの登録順でインクリメントされ、必ず時系列順になる（途中で割り込まれることがない）
- ユーザーIDをもち、ユーザーIDで対象レコードを絞り込むことができる
以下のようなテーブルにしましたが、今回検証で使用するのはid列とuser_id列です。
CREATE TABLE download_histories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
こちらも念のため動作確認しておきます。
$ docker compose down -v
[+] Running 2/2
✔ Network pagination-demo_default Created
✔ Container postgres17 Started
$ psql -h localhost -U postgres -d postgres
Password for user postgres:
psql (17.4, server 17.2 (Debian 17.2-1.pgdg120+1))
Type "help" for help.
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+--------------------+-------+----------
public | download_histories | table | postgres
(1 row)
postgres=# \d download_histories
Table "public.download_histories"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+------------------------------
id | bigint | | not null | generated always as identity
user_id | integer | | not null |
created_at | timestamp with time zone | | not null | now()
Indexes:
"download_histories_pkey" PRIMARY KEY, btree (id)
postgres=# \q
問題なくテーブルが作成されていることを確認できました。
ページネーションに必要なインデックスを設定する
ページネーションを実現するための要件は以下のとおりです。
- データは
id列の降順（＝created_at列の降順）で取得する
- 指定した
user_idと一致すレコードを取得する
id列は単純増加のため、降順でid=100まで取得していれば次のデータは必ず100未満になることが保証されています。（取得するデータは最新のデータから過去の向けって取得する）
動作を検証するために以下の2種類のインデックスを試します。
- PKに加え、
id列の降順インデックスとuser_id列の昇順インデックスをそれぞれ追加する
user_id列の昇順＋id列の降順の複合インデックスを追加する
-- 1. id の降順インデックス
CREATE INDEX idx_download_histories_id_desc
ON download_histories (id DESC);
-- 2. user_id の昇順インデックス
CREATE INDEX idx_download_histories_user_id_asc
ON download_histories (user_id ASC);
-- 3. user_id 昇順 + id 降順 の複合インデックス
CREATE INDEX idx_download_histories_user_id_id_desc
ON download_histories (user_id ASC, id DESC);
これは必要になったタイミングで設定するようにします。
テストデータを用意するSQLを作成する
インデックスが十分に効いているかを検証したいので、それなりにデータ量が必要となります。
今回は以下の条件を満たすレコードを生成することにしました。
user_idは1から100までの100名とする- 1つの
user_idに対して100,000件のレコードを登録する
登録するレコード数は10,000,000件になるため、1行ずつ登録するのは非効率なため、以下のSQLでまとめて登録するようにしています。
INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100) AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);
これをinitdb配下に配置した初期データ投入用のSQLファイルに以下のように定義してコンテナを再起動します。
CREATE TABLE download_histories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100) AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);
ページネーションで発行する想定のSQLをexplain付きで実行すると、以下のようになりました。
postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..201.87 rows=10 width=20)
-> Index Scan Backward using download_histories_pkey on download_histories (cost=0.43..363532.10 rows=18047 width=20)
Index Cond: (id < 12345)
Filter: (user_id = 56)
(4 rows)
体感では非常に高速でしたが、実行計画を見るとcostが高いことがわかります。少ない件数で試すとコストが下がっているので、唯一存在するPKによるインデックススキャンは使用しつつも登録されているデータ件数に依存したコストがかかっていることがわかります。
これでひととおりの準備が整いました。次に前述のインデックスを当てた時に上記のSQLのコストがどう変わるのかを確認していきたいと思います。
検証１：user_id列とid列に個別のインデックスを設定する
ChatGPT の回答では十分な効果が得られないと言われていた個別のインデックスを設定する方法を試してみます。具体的には、以下のINDEXを追加して前述のSELECT文の実行計画を確認します。
-- 1. id の降順インデックス
CREATE INDEX idx_download_histories_id_desc
ON download_histories (id DESC);
-- 2. user_id の昇順インデックス
CREATE INDEX idx_download_histories_user_id_asc
ON download_histories (user_id ASC);
使用する初期データ投入用のSQLファイルの全体は、以下になります。
CREATE TABLE download_histories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_download_histories_id_desc
ON download_histories (id DESC);
CREATE INDEX idx_download_histories_user_id_asc
ON download_histories (user_id ASC);
INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100) AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);
同じクエリを実行したところ以下のようになり、追加したインデックスは使用していないことがわかります。
postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..201.87 rows=10 width=20)
-> Index Scan Backward using download_histories_pkey on download_histories (cost=0.43..363532.10 rows=18047 width=20)
Index Cond: (id < 12345)
Filter: (user_id = 56)
(4 rows)
データの件数が少なくてPKのインデックスを逆順で使えば十分と判断された可能性もありますが、とりあえずそのまま検証を続けていきます。
検証２：user_id列とid列の複合インデックスを設定する
続いて、user_id列とid列の複合インデックスを使って同じクエリを試してみます。使用する初期データ投入用のSQLファイルの全体は以下のようになります。
CREATE TABLE download_histories (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id INTEGER NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_download_histories_user_id_id_desc
ON download_histories (user_id ASC, id DESC);
INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 100) AS gs_users(user_id)
CROSS JOIN generate_series(1, 100000) AS gs_seq(seq);
同じクエリを実行したところ以下のようになり、追加した複合インデックスが使われていることがわかります。
postgres=# explain select * from download_histories where user_id = 56 and id < 12345 order by id desc limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..18.87 rows=10 width=20)
-> Index Scan using idx_download_histories_user_id_id_desc on download_histories (cost=0.56..189.12 rows=103 width=20)
Index Cond: ((user_id = 56) AND (id < 12345))
(3 rows)
コストが格段に下がっており、インデックスを使用したときのフェッチ件数も18047から103に下がっていることがわかります。
追加検証：データのバランスを変更したどうなるか？
検証１で追加したインデックスが使われなかった点についてもう少しだけ検証してみます。データ量を少し増やしみて、それでも追加したインデックスは使われないのかを検証します。
以下のようにデータ量を増やして再度検証してみます。
- ユーザー数：100 → 1,000
- 1ユーザーあたりのデータ件数：100,000 → 10, 000
ダウンロード履歴テーブルなので、1ユーザーのダウンロード回数を抑えつつ、利用者数を増やす形にしています。
INSERT INTO download_histories (user_id)
SELECT gs_users.user_id
FROM generate_series(1, 1000) AS gs_users(user_id)
CROSS JOIN generate_series(1, 10000) AS gs_seq(seq);
実行するクエリはデータのバランスが変更されたことに合わせて、以下のようにしました。
explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
上記の条件で、各パターンで実行計画を取得すると以下のようになりました。
追加インデックスなし
postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..492.30 rows=13 width=20)
-> Index Scan Backward using download_histories_pkey on download_histories (cost=0.43..492.30 rows=13 width=20)
Index Cond: (id < 12345)
Filter: (user_id = 1234)
(4 rows)
個別インデックスあり
postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..2014.80 rows=100 width=20)
-> Index Scan Backward using download_histories_pkey on download_histories (cost=0.43..363532.10 rows=18047 width=20)
Index Cond: (id < 12345)
Filter: (user_id = 1234)
(4 rows)
複合インデックスあり
postgres=# explain select * from download_histories where user_id = 1234 and id < 12345 order by id desc limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.56..355.46 rows=100 width=20)
-> Index Scan using idx_download_histories_user_id_id_desc on download_histories (cost=0.56..64049.50 rows=18047 width=20)
Index Cond: ((user_id = 1234) AND (id < 12345))
(3 rows)
サーバーサイドページネーションは必ずしも必要ないかもしれない
2回目の検証でも1ユーザーあたり10,000回のダウンロード履歴を想定しています。何をダウンロードするかにもよりますが、営業日に平均1日1回ダウンロードするとしても年間240回、10,000回ダウンロードするには40年以上かかる計算になります。
となると、サーバーサイドページネーションが必要となるかどうかは、データの増加量とレスポンスサイズによると考えられます。
適当な状況を想定して計算してみましょう。
システムによって異なりますが、一般的にはレスポンスボディのサイズが10MB以下に設定するとします。ヘッダーの計算はちょっと面倒なので、2MBバイトだとしてレスポンスボディのサイズは8MB以下になるようにするとしましょう。
1件のデータが仮に1KBだとすると、8,192行のデータがレスポンスとして返せる計算になります。通常の利用範囲でシステムの耐用年数使い続けてもこの行数を超えないのであれば、そもそもサーバーサイドページネーションは不要ということになります。毎日平均10行増えるとしても1年間3,650行増えるので、2年ちょっとの間はこのサーバーサイドページネーションが必要ないと言えます。
ダウンロードを行った履歴となるとダウンロードしたファイル名やダウンロード日時などを含むだけなので、多めに見積もって512バイトだとして、月多くても10回ほどしかダウンロードしないとすると、年間120行増えるだけなので、レスポンスボディの上限に到達するには136年もかかることになるため、コストをかけてまでサーバーサイドページネーションを実装する必要はない、という結論になります。
このことから、かなりのペースでデータが増加したり、ヒットするデータ量が膨大になるなどの前提がある場合はサーバーサイドページネーションを検討する価値がありますが、ユーザー自身の操作でデータが増えるだけだとサーバーサイドページネーションがなくてもシステム的には問題ないという場合もある点は重要な学びです。
まとめ
最初はChatGPTの回答を検証することが目的でしたが、最終的にはサーバーサイドページネーションが必要かどうかはデータ量の見積もりによって決まるという話になりました。
最初の疑問に立ち返ると、ChatGPTの回答どおりにWHERE句とLIMIT句の条件を組み合わせた複合インデックスを追加することで、性能が向上することは確認できました。ただし、圧倒的に優れているかと言われるとデータのバランス次第という感じだったので、実際に検索するテーブルに想定されるバランスのテストデータを登録して実際に実行されるクエリを使って検証することが重要だと思います。
