Пошук уроків, статей та іншого контенту
Порівняйте OFFSET-пагінацію, її обмеження та вплив великих зміщень на продуктивність.
Пагінація розбиває великий набір рядків на окремі сторінки. Замість повернення всіх записів клієнт запитує лише потрібну кількість.
У PostgreSQL для цього найчастіше використовують:
LIMIT — максимальна кількість рядків;
OFFSET — кількість рядків, які потрібно пропустити перед поверненням результату.
Базовий запит має такий вигляд:
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 40;Цей запит повертає третю сторінку по 20 записів:
перша сторінка: OFFSET 0;
друга сторінка: OFFSET 20;
третя сторінка: OFFSET 40.
ORDER BYБез ORDER BY PostgreSQL не гарантує порядок рядків. Навіть якщо результати кілька разів виглядають однаково, порядок може змінитися через:
створення або видалення рядків;
зміну плану виконання;
використання іншого індексу;
обслуговування таблиці.
Тому пагінація без сортування є ненадійною:
-- Ненадійний варіант
SELECT id, title
FROM posts
LIMIT 20
OFFSET 40;Для стабільної пагінації потрібно явно вказати порядок:
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 40;Додавання id як другого поля важливе, якщо created_at не є унікальним. У такому разі всі рядки мають однозначний порядок навіть за однакової дати.
OFFSETПрипустімо, що запит має такий вигляд:
SELECT id, title
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 100000;PostgreSQL має знайти та впорядкувати достатню кількість рядків, пропустити перші 100000, а потім повернути наступні 20.
OFFSET не означає, що база даних миттєво переміщується до потрібного номера рядка. Пропущені рядки все одно потрібно обробити.
У спрощеному вигляді робота виглядає так:
знайти перший рядок у потрібному порядку;
пройти наступні рядки;
відкинути перші OFFSET рядків;
повернути LIMIT рядків.
Через це час виконання зазвичай зростає разом із величиною OFFSET.
Наведений приклад можна виконати в PostgreSQL. Він створює таблицю, додає тестові записи та індекс для сортування.
DROP TABLE IF EXISTS posts;
CREATE TABLE posts (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
created_at timestamptz NOT NULL
);
INSERT INTO posts (title, created_at)
SELECT
'Публікація ' || number,
now() - (number || ' minutes')::interval
FROM generate_series(1, 200000) AS numbers(number);
CREATE INDEX posts_created_at_id_idx
ON posts (created_at DESC, id DESC);
-- Перша сторінка
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 0;
-- Сторінка з великим зміщенням
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 100000;Індекс відповідає порядку:
(created_at DESC, id DESC)Тому PostgreSQL може використовувати його для отримання рядків у потрібній послідовності.
Для аналізу плану та фактичного часу виконання використовуйте EXPLAIN ANALYZE:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 0;Порівняйте його з варіантом із великим зміщенням:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 100000;У плані можна побачити, скільки рядків реально обробив PostgreSQL. Навіть якщо результат містить лише 20 рядків, для великого OFFSET база даних може прочитати значно більше.
Типовий фрагмент плану може виглядати приблизно так:
Limit
-> Index Scan using posts_created_at_id_idx on posts
rows: 100020Точні значення залежать від даних, версії PostgreSQL, статистики та стану кешу. Важливий сам принцип: OFFSET 100000 LIMIT 20 може вимагати проходження приблизно 100020 рядків.
Нехай розмір сторінки дорівнює 20.
| Сторінка | OFFSET | Приблизна кількість рядків для обробки | |---|---:|---:| | 1 | 0 | 20 | | 2 | 20 | 40 | | 100 | 1980 | 2000 | | 5000 | 99980 | 100000 |
У реальному застосунку час не обов’язково зростатиме строго лінійно, але загальна тенденція саме така.
Великий OFFSET особливо проблемний, коли:
таблиця містить мільйони рядків;
користувачі можуть переходити на далекі сторінки;
запит виконується часто;
сортування відбувається за неіндексованим стовпцем;
рядки широкі або складні для перевірки видимості.
Індекс зменшує витрати на сортування та пошук порядку, але не скасовує необхідність пропускати рядки.
OFFSET-пагінація також може повертати дублікати або пропускати записи, якщо дані змінюються між завантаженнями сторінок.
Наприклад:
клієнт завантажив першу сторінку;
між запитами додався новий запис, який потрапив на початок списку;
клієнт завантажив другу сторінку через OFFSET 20.
Новий запис змістив усі наступні позиції. Через це один запис може з’явитися на обох сторінках, а інший — не потрапити до жодної.
Те саме може статися після видалення рядка: позиції змістяться в протилежний бік.
Стабільний ORDER BY робить порядок визначеним, але не захищає від зміщення позицій після вставок і видалень.
OFFSET є прийнятнимOFFSET-пагінація добре підходить, коли:
таблиця невелика;
користувач переглядає лише перші сторінки;
потрібна навігація за номерами сторінок;
дані рідко змінюються;
простота запиту важливіша за оптимізацію дуже далеких сторінок.
Приклад запиту для невеликого каталогу:
SELECT id, name, price
FROM products
WHERE is_active = true
ORDER BY name ASC, id ASC
LIMIT 25
OFFSET 50;Для такого сценарію OFFSET може бути достатньо простим і зрозумілим рішенням.
OFFSET стає проблемоюВід OFFSET варто відмовитися або обмежити його, коли:
користувачі переглядають стрічку з великою кількістю записів;
API дозволяє довільний номер сторінки;
дані часто додаються або видаляються;
потрібна стабільна навігація під час змін;
запити з великими зміщеннями створюють навантаження на базу даних.
Практичним захистом може бути максимальне дозволене зміщення:
-- Наприклад, API не дозволяє отримувати сторінки після 1000-го рядка
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 980;Обмеження не усуває недоліки OFFSET, але не дозволяє клієнтам випадково створювати надто дорогі запити.
Для великих таблиць часто використовують пагінацію за курсором, або keyset pagination. Замість номера сторінки клієнт передає значення останнього рядка попередньої сторінки.
Для сортування за created_at DESC, id DESC наступна сторінка може виглядати так:
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;Параметри $1 і $2 — це created_at та id останнього рядка попередньої сторінки.
У такому випадку PostgreSQL може перейти до потрібної позиції індексу, а не пропускати всі попередні рядки. Для індексу з прикладу підходить такий запит:
CREATE INDEX posts_created_at_id_idx
ON posts (created_at DESC, id DESC);
SELECT id, title, created_at
FROM posts
WHERE (created_at, id) < ('2026-01-15 12:00:00+00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;Цей підхід краще масштабується для послідовного перегляду, але не дає простого переходу одразу на сторінку з конкретним номером. Тому вибір залежить від поведінки інтерфейсу.
Індекс має відповідати фільтрації та сортуванню запиту. Для запиту:
SELECT id, title, created_at
FROM posts
WHERE author_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 1000;може бути корисним складений індекс:
CREATE INDEX posts_author_created_id_idx
ON posts (author_id, created_at DESC, id DESC);Після створення індексу план потрібно перевірити за допомогою EXPLAIN (ANALYZE, BUFFERS). Сам факт існування індексу не гарантує, що PostgreSQL використає саме його: оптимізатор враховує статистику, вибірковість умов і вартість різних планів.
ORDER BYSELECT *
FROM posts
LIMIT 20
OFFSET 20;Порядок рядків не гарантований. Завжди задавайте явне сортування.
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC
LIMIT 20;Кілька рядків можуть мати однаковий created_at. Додайте унікальний стовпець:
SELECT id, title, created_at
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;OFFSET дешевимІндекс допомагає отримувати рядки в правильному порядку, але PostgreSQL усе одно має пройти пропущені рядки.
SELECT *
FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20
OFFSET 10000;Краще повертати лише потрібні стовпці. Це зменшує обсяг даних, які потрібно прочитати та передати клієнту.
Клієнт може випадково або навмисно надіслати дуже великий номер сторінки. API має перевіряти:
максимальний розмір сторінки;
невід’ємне значення OFFSET;
максимальне допустиме зміщення.
LIMIT визначає кількість рядків, а OFFSET — кількість пропущених рядків.
Для передбачуваної пагінації потрібен стабільний ORDER BY.
У сортування варто додавати унікальний стовпець, наприклад id.
Великий OFFSET не є швидким переходом до номера рядка: PostgreSQL має обробити пропущені записи.
Індекси допомагають із сортуванням і фільтрацією, але не усувають вартість великих зміщень.
OFFSET добре підходить для невеликих наборів і перших сторінок.
Для великих таблиць і послідовного перегляду ефективнішою може бути пагінація за курсором.
Продуктивність потрібно перевіряти за допомогою EXPLAIN (ANALYZE, BUFFERS).