> Как реализовать пагинацию для большого количества данных без проблем с производительностью при использовании offset (Python)

Основная проблема пагинации через OFFSET в SQL - это рост времени выполнения при увеличении смещения. База данных вынуждена прочитать и отбросить все строки до нужного смещения, что особенно критично для таблиц с миллионами записей. Для решения этой проблемы применяют несколько подходов.

1. Keyset Pagination (Cursor-based pagination)
Вместо OFFSET используем фильтрацию по уникальному упорядоченному полю (обычно id).
Пример на Python с SQLAlchemy:

PYTHON
def get_page(last_id: int = None, limit: int = 100):
    query = session.query(Post).order_by(Post.id).limit(limit)
    if last_id:
        query = query.filter(Post.id > last_id)
    return query.all()

Клиент передаёт last_id из последней записи предыдущей страницы. Запрос выполняется за O(log N) благодаря индексу.

2. Индексы для покрытия запроса
Если без OFFSET не обойтись (например, требуется произвольная страница), создайте составной индекс, включающий все поля из ORDER BY и WHERE. Для ORDER BY created_at DESC, id:

SQL
CREATE INDEX idx_created_at_id ON posts (created_at DESC, id);

Это позволит БД читать только нужные строки из индекса, не трогая таблицу.

3. Ленивая загрузка с предварительной фильтрацией
Для очень больших наборов данных (миллиарды записей) используйте шардирование или разделение по времени. Например, пагинация по месяцам:

PYTHON
start_date = datetime(2023, 1, 1)
end_date = datetime(2023, 2, 1)
page = session.query(Post).filter(
    Post.created_at >= start_date,
    Post.created_at < end_date
).order_by(Post.id).limit(100).all()

4. Комбинированный подход
Для API, где нужна и навигация по страницам, и произвольный доступ, используйте гибрид: для первых страниц - OFFSET (с кэшированием), для глубоких - keyset.

Важно: Избегайте OFFSET на таблицах без явного ORDER BY - результат непредсказуем. Всегда добавляйте уникальное поле в сортировку для стабильности.