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

Короткий ответ

Пагинация через id (keyset pagination) эффективнее offset, потому что не сканирует и не отбрасывает строки до нужной позиции. Вместо OFFSET используется условие WHERE id > last_seen_id с сортировкой по тому же ключу. Это даёт стабильную производительность O(log n) на страницу и корректную работу при вставках/удалениях. Основной trade-off - невозможность перейти на произвольную страницу без хранения её ключа.

Подробное объяснение

Offset-пагинация (LIMIT 20 OFFSET 40) заставляет базу данных прочитать все строки до смещения, отсортировать их и отбросить. При больших значениях offset стоимость растёт линейно, а при параллельных записях возможны пропуски или дубликаты.

Keyset-пагинация использует уникальный упорядоченный ключ (обычно id). Запрос выглядит так: WHERE id > $last_id ORDER BY id LIMIT 20. База данных находит первую строку по индексу за O(log n) и читает следующие 20 - без лишних операций.

Ключевые свойства:

• производительность не зависит от номера страницы;
• консистентность при изменении данных между запросами;
• требуется передача ключа последней записи в следующем запросе (обычно через query-параметр или заголовок);
• для сортировки по другим полям нужен составной ключ, например (created_at, id), чтобы сохранить уникальность порядка.

На практике keyset-пагинацию используют в API с бесконечной лентой, логами, сообщениями. Для UI с номерами страниц она не подходит - там остаётся offset или cursor-пагинация с зашифрованным ключом.

На практике

При реализации на Python (FastAPI, Django, Flask) обычно:

1. Принимаем cursor - закодированный id последней записи предыдущей страницы.
2. Добавляем условие WHERE id > cursor (или < для обратной навигации).
3. Сортируем по id (или составному ключу).
4. Возвращаем next_cursor из последней записи выборки.
5. Для обратной навигации меняем направление сравнения и сортировки.

Для составных ключей условие строится по принципу: (created_at, id) > ($last_created_at, $last_id) - в SQL это (created_at > $1) OR (created_at = $1 AND id > $2).

Пример кода

PYTHON
from fastapi import FastAPI, Query
from sqlalchemy import select, desc
from sqlalchemy.orm import Session

app = FastAPI()

def get_page(session: Session, cursor: int | None, limit: int = 20):
    stmt = select(Post).order_by(Post.id)
    if cursor is not None:
        stmt = stmt.where(Post.id > cursor)
    stmt = stmt.limit(limit + 1)  # +1 чтобы понять, есть ли следующая страница
    rows = session.execute(stmt).scalars().all()
    
    has_next = len(rows) > limit
    rows = rows[:limit]
    next_cursor = rows[-1].id if has_next else None
    return rows, next_cursor

@app.get("/posts")
def list_posts(cursor: int | None = Query(default=None), limit: int = 20):
    with Session(engine) as session:
        rows, next_cursor = get_page(session, cursor, limit)
    return {
        "items": [{"id": r.id, "title": r.title} for r in rows],
        "next_cursor": next_cursor,
    }

Для составного ключа:

PYTHON
def get_page_by_created_at(session, last_created_at, last_id, limit=20):
    stmt = select(Post).order_by(Post.created_at, Post.id)
    if last_created_at is not None:
        stmt = stmt.where(
            (Post.created_at > last_created_at) |
            ((Post.created_at == last_created_at) & (Post.id > last_id))
        )
    stmt = stmt.limit(limit + 1)
    # ... аналогично

Как отвечать на собеседовании

Начни с проблемы offset: рост времени, нестабильность при изменениях. Затем объясни принцип keyset: условие по индексу вместо смещения. Упомяни, что ключ должен быть уникальным и упорядоченным, иначе нужен составной. Приведи пример с id и с (created_at, id). Скажи, когда keyset не подходит: произвольный переход по страницам, UI с номерами. Если спросят про cursor - объясни, что это просто закодированный ключ последней записи.

Что проверяет интервьюер

• понимание внутреннего устройства индексов и операций БД;
• умение видеть trade-off между простотой и производительностью;
• знание edge cases: составные ключи, обратная навигация, отсутствие следующей страницы;
• способность реализовать на реальном стеке (SQLAlchemy, FastAPI).

Типичные ошибки

• использование OFFSET для больших таблиц без осознания проблемы;
• сортировка по неуникальному полю без добавления id - приводит к пропуску записей;
• забывают передавать next_cursor в ответе;
• путают направление сравнения при обратной пагинации;
• не учитывают, что keyset не позволяет перейти на страницу 5 напрямую;
• считают, что keyset работает только с числовыми id - на самом деле подходит любой упорядочиваемый тип.