# R-01. Пагинация OFFSET и keyset в PostgreSQL 17: время ответа и глубина страницы

Статья: `app/content/research/paginaciya-offset-keyset-postgresql.json` (`/blog/paginaciya-offset-keyset-postgresql`).
Рецепт с готовым кодом API: `app/content/learn/articles/recept-sql-paginaciya.json` — стенд использует ту же схему и ту же формулу `created_at`.

## 1. Протокол (записан 12.10.2026 до первого измерения)

**Вопрос.** Как время получения одной страницы (20 строк) зависит от глубины страницы при `LIMIT … OFFSET` и при keyset-пагинации (`WHERE (created_at, id) < ($1, $2)`) на таблице из 1 000 000 строк с индексом `(created_at, id)` в PostgreSQL 17?

**Гипотеза.** Время `OFFSET` растёт примерно линейно с числом пропущенных строк (`(N − 1) × 20`); keyset даёт почти постоянное время и почти постоянное число прочитанных буферов на любой глубине. На первой странице варианты равны (это один и тот же запрос).

**Варианты.**
- `offset`: `SELECT … ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET $1`, `$1 = (N − 1) × 20`.
- `keyset`: страница 1 — тот же запрос без `OFFSET`; страница N > 1 — `WHERE (created_at, id) < ($1::timestamptz, $2::bigint) ORDER BY created_at DESC, id DESC LIMIT 20`, граница — последняя строка страницы N − 1 (вычисляется один раз до замеров, вне измерения).
- Глубины N: 1, 100, 1 000, 10 000, 50 000 (страница 50 000 — последняя: пропускается 999 980 строк).

**Метрики.**
1. Серверное время: `Execution Time` из `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)`; `TIMING OFF` — чтобы не добавлять замер времени на каждую строку узла (при `OFFSET` через узел проходит до миллиона строк). Также `Planning Time`.
2. Буферы: `Shared Hit Blocks`, `Shared Read Blocks` корневого узла плана.
3. Клиентское время: тот же запрос без `EXPLAIN`, `performance.now()` вокруг `client.query()` в Node.js (`pg`), одно соединение, параметры через `$n`.
4. План: тип верхнего узла сканирования (чтобы видеть, если планировщик сменит план на большой глубине).

**Критерии честного сравнения.**
- Одна таблица, один индекс, одно соединение, один контейнер, одинаковые настройки сервера для обоих вариантов.
- Перед замерами — `VACUUM (ANALYZE)` и прогрев: каждый запрос (оба варианта × все глубины) выполняется 2 раза; прогон записывается в CSV с `phase=warmup` и в медианы не входит.
- 10 раундов (≥ 5 повторов на точку). В каждом раунде порядок глубин перемешивается генератором с фиксированным seed, порядок вариантов в каждой точке чередуется (чётный раунд — `offset` первым, нечётный — `keyset`).
- Проверка корректности: в каждом клиентском замере `id` строк сравниваются с эталонной страницей (`rows_match`), чтобы сравнивать одинаковый результат.
- Сводка: медиана, p25/p75 и min/max по 10 повторам. Статистическая значимость не заявляется: одна машина, 10 повторов.
- Публикуются только цифры из `raw.csv`, `summary.txt` и `explain-*.txt`.

**Фиксированные настройки.** Образ `postgres:17-alpine`, контейнер `--cpus=2 --memory=2g`, `shared_buffers` по умолчанию (значение записывается в `env.txt`), `jit=off` — единственное отличие от умолчаний: компиляция JIT включается по оценке стоимости и добавила бы ко времени глубокого `OFFSET` своё, не связанное с пагинацией время. Остальные параметры — по умолчанию, их значения записываются в `env.txt`.

**Смешивающие факторы, известные заранее.** Docker Desktop на Windows (WSL2), клиент на хосте — клиентское время включает сетевой переход хост → VM; на той же машине работают другие контейнеры и dev-сервер; данные после прогрева в кэше (`shared_buffers`), холодное чтение с диска не измеряется.

## 2. Как повторить

Нужны Docker, Node.js 24 и свободный порт 55417. Из этой папки (Linux, macOS или Git Bash на Windows):

```bash
npm install          # pg 8.23.1 из package.json / package-lock.json
bash run.sh          # 3 прогона: свежий контейнер → setup.sql → bench.mjs → explain → docker stop
```

`run.sh` перезаписывает `raw.csv`, `summary.txt`, `env.txt` и `explain-r*-*.txt`. Один прогон вручную (PowerShell):

```powershell
docker run --rm -d --name kb-g-research --cpus=2 --memory=2g -e POSTGRES_PASSWORD=bench -e POSTGRES_DB=bench -p 55417:5432 postgres:17-alpine -c jit=off
docker exec kb-g-research pg_isready -U postgres -h 127.0.0.1   # повторять, пока не «accepting connections»
Get-Content setup.sql -Raw | docker exec -i kb-g-research psql -U postgres -d bench -v ON_ERROR_STOP=1
$env:RUN = 1; node bench.mjs > raw-1.csv; node bench.mjs --explain
node summarize.mjs raw-1.csv
docker stop kb-g-research
```

Проверка данных: `setup.sql` в конце печатает `1000000 | 48c42fd798489f22b2847d4427ff3f80 | 65 MB | 30 MB` — контрольная сумма совпала во всех прогонах 12.10.2026, генерация детерминирована (`setseed(0.42)`).

## 3. Файлы

| Файл | Что внутри |
|---|---|
| `setup.sql` | таблица `orders`, 1 000 000 строк, индекс `(created_at, id)`, `VACUUM (ANALYZE)`, контрольная сумма |
| `bench.mjs` | замеры (`RUN=n node bench.mjs`), планы (`--explain`), настройки сервера (`--settings`) |
| `summarize.mjs` | медиана, p25/p75, min/max по `raw.csv` → `summary.txt` |
| `run.sh` | полный цикл из трёх прогонов и запись `env.txt` |
| `raw.csv` | все замеры: 3 прогона × (20 прогревочных + 110 измерительных строк); `phase=warmup` в сводку не входит |
| `summary.txt` | сводка, на которой построены таблицы статьи (воспроизводится: `node summarize.mjs raw.csv`) |
| `env.txt` | железо, ОС, Docker, Node.js, pg, версия и настройки PostgreSQL, границы курсоров, время прогонов |
| `explain-r{1,2,3}-{offset,keyset}-p{10000,50000}.txt` | `EXPLAIN (ANALYZE, BUFFERS)` в тексте — по одному выполнению на прогон, вариант и глубину |

Колонки `raw.csv`: `run, phase, round, seq, variant, page, offset_rows, server_exec_ms, planning_ms, shared_hit, shared_read, client_ms, rows, rows_match, scan_node`. Строки `variant=ping` — клиентское время `SELECT 1` в начале каждого раунда (оценка накладных расходов соединения).

## 4. Результаты (12.10.2026, 3 прогона × 10 раундов = 30 замеров на точку)

Медиана [p25–p75] (min–max), мс. Полностью — `summary.txt`.

| Страница | Пропущено строк | OFFSET, сервер | keyset, сервер | OFFSET, клиент | keyset, клиент | Буферы OFFSET / keyset |
|---|---|---|---|---|---|---|
| 1 | 0 | 0.037 [0.033–0.043] | 0.042 [0.036–0.049] | 0.486 [0.415–0.587] | 0.448 [0.412–0.559] | 23 / 23 |
| 100 | 1 980 | 0.660 [0.587–0.986] | 0.034 [0.029–0.040] | 1.031 [0.961–1.402] | 0.518 [0.476–0.604] | 2 019 / 23 |
| 1 000 | 19 980 | 5.845 [5.414–7.148] | 0.038 [0.033–0.049] | 5.361 [4.990–6.814] | 0.530 [0.505–0.582] | 20 157 / 23 |
| 10 000 | 199 980 | 48.25 [42.78–71.58] (40.95–111.5) | 0.044 [0.038–0.055] | 46.84 [43.20–58.75] | 0.506 [0.465–0.678] | 201 535 / 23 |
| 50 000 | 999 980 | 243.1 [215.5–281.4] (200.5–503.3) | 0.059 [0.040–0.066] | 239.9 [211.9–307.9] (195.3–378.8) | 0.601 [0.537–0.681] | 1 007 665 / 23 |

- `shared_read = 0` во всех измерительных замерах: после прогрева всё читалось из `shared_buffers`.
- `rows_match = 1` во всех 300 измерительных строках вариантов (ещё 30 строк — `ping`): оба варианта вернули одинаковые 20 строк.
- Клиентское время `SELECT 1`: медиана 0.427 мс [0.342–0.504].
- Медианы OFFSET на странице 50 000 по прогонам: 222.4 / 272.7 / 245.1 мс (сервер).
- План keyset на странице 50 000: в прогоне 1 — `Index Scan Backward`, в прогонах 2 и 3 — `Bitmap Heap Scan` + `Sort` (оценка строк 22 против 20, см. `explain-r*-keyset-p50000.txt`). Число буферов в замерах — 23 в обоих планах.

## 5. Отклонения от протокола и честные оговорки

- До финального цикла было два отладочных прогона (проверка скрипта: подпись узла плана, имена файлов). Их результаты не публикуются. Абсолютные значения глубокого OFFSET в них заметно расходились, поэтому вместо одного прогона из 10 раундов сделано три независимых прогона на свежих контейнерах — это расширение протокола, а не отбор результатов: в `raw.csv` все три.
- Планировщик выбирал план keyset на последней странице по-разному между прогонами. Документация `ANALYZE` предупреждает, что статистика строится по случайной выборке и в редких случаях это меняет план; `setseed` на выборку `ANALYZE` не влияет.
- `jit=off`: максимальная оценка стоимости в планах — 63 748 (OFFSET, страница 50 000), ниже порога `jit_above_cost` по умолчанию (100 000), так что с настройками по умолчанию JIT в этих запросах тоже не включился бы.
- Файлы `explain-*.txt` сняты с поузловым замером времени (`TIMING` по умолчанию) и одним выполнением — для чтения плана, не для таблицы.
- Одна машина, Docker Desktop (WSL2) на Windows, параллельно работали другие контейнеры и dev-сервер; данные целиком в кэше; холодное чтение с диска, конкурентная нагрузка, другие размеры строк и таблиц не измерялись. Статистическая значимость не заявляется.