Как оптимизировать пагинацию отсортированных по времени создания записей?

Question

[Nikita Dergachov]

Нужно сделать пагинацию по большой таблице, записи должны быть отсортированные по дате создания. В качестве id используются UUID:V4

Запрос с limit offset работает медленнее с каждой страницей из-за деградации.
Запрос вида

select * from table where id > n order by id limit 1000

Не подходит, так как нужна сортировка по дате создания и используется случайный uuid

Comments

[Slava Rozhnev]

Вариант - отказаться от пагинации не рассматривали?

[Nikita Dergachov]

Slava Rozhnev, Нужно сделать админку по большой таблице, записей > 50 млн, без пагинация все записи показать невозможно будет)

[mayton2019]

1)
Nikita Dergachov, как ты себе представляешь глазами просмотреть 50 млн?
Ни один админ не осилит.

Подумай в направлении текстового поиска. Или фасетов.

2) Чтоб быстрее искать по толстым таблицам - посмотри в сторону partitioned tables.

3) Почитай также про UUID Variant 0. Там есть возможность впихнуть timestamp внутри. Может пригодится.

[alexalexes]

А индексы добавлены на id и дату создания?

[Akina]

Как я понимаю, требуемая пагинация - частовыполняемая операция, а сама таблица INSERT only? Обычно направление ускорения в указанном случае - предсортировка. То есть заранее создаётся опорная таблица, например (дата - количество записей ранее этой даты), что позволяет сразу вычислять начальную дату для текущей страницы, тем самым уменьшая OFFSET и соответственно ускоряя выборку для текущей страницы.

[Nikita Dergachov]

Akina, Изменения тоже будут происходить

[Nikita Dergachov]

alexalexes, Добавлены

[Akina]

Nikita Dergachov, Какие именно изменения? Будут удаляться записи? будет изменяться значение поля даты создания? или изменения не затрагивают предрасчётные данные?

Answer 1

[O . J]

Можно использовать альтернативный подход, который называется "ключевая пагинация" (keyset pagination). Вместо использования offset/limit, вы будете использовать значения последнего элемента на предыдущей странице для запроса следующей страницы данных.

Для этог нужно добавить индекс на столбец с датой создания в вашей таблице, если его еще нет:

CREATE INDEX idx_created_at ON your_table_name (created_at);

Запрос

SELECT * FROM table_name
WHERE created_at < (SELECT created_at FROM table_name WHERE id = :last_id)
ORDER BY created_at DESC
LIMIT 1000;

Здесь :last_id - это идентификатор последней записи на предыдущей странице.

Comments

[Ипатьев]

если есть несколько записей на одну дату, то часть из них будет показываться на обеих страницах

[O . J]

Да все так, но это можно решить немного изменив запрос

SELECT * FROM table_name
WHERE (created_at, id) < (SELECT created_at, id FROM table_name WHERE id = :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 1000;

Answer 2

[Ипатьев]

Ну, вариант "в лоб" это

select * from table where date >= n and id not in (...) order by date limit 1000

где n - это последняя дата с предыдущей страницы, а ... - это список id на ту же дату с той же страницы.

Но как правильно пишут в комментариях, если пагинация начинает тормозить, то надо что-то в консерватории менять. И например разбивать не по 1000 строк, а по дате.

Comments

[O . J]

Такой вариант решения будет работать, но он имеет ряд недостатков:
Основной - это производительность, топик стартер жалуется на производительность offset/limit, а ваш вариант несет за собой другие проблемы производительности решая основную
Использование IN или NOT IN с большим набором данных может быть медленным. Это особенно актуально, если таблица очень большая или количество записей с одинаковыми датами велико. В таких случаях выполнение этого запроса может привести к существенному снижению производительности.

Этот вариант так же сложнее потому что формирование списка идентификаторов в операторе IN или NOT IN может быть неэффективным, особенно если есть много записей с одинаковыми датами.

Answer 3

[Jack444]

Просто сделать такой индекс.
CREATE INDEX dtidx ON table (id, created DESC);
И всё, запрос будет искать id в первичном индексе, как найдёт там будет уже список таймштампов отсортированных по убыванию сделать по ним offset/limit вообще не чего не стоющая операция, ORDER BY писать уже не обязательно.