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

Использование OFFSET в пагинации приводит к проблемам производительности на больших объёмах данных: база данных вынуждена сканировать и отбрасывать все строки до указанного смещения. Оптимизация через id (или другой уникальный ключ) основана на принципе cursor-based pagination (пагинация на основе курсора).

Основная идея: вместо номера страницы передаём идентификатор последнего элемента с предыдущей страницы и выбираем записи, у которых id больше (или меньше) этого значения.

Пример реализации на Python (SQLAlchemy):

PYTHON
from sqlalchemy import select, desc
from models import Post

def get_paginated_posts(cursor_id: int | None = None, limit: int = 20):
    query = select(Post).order_by(Post.id).limit(limit)
    
    if cursor_id is not None:
        query = query.where(Post.id > cursor_id)
    
    result = session.execute(query).scalars().all()
    
    # Формируем ответ с курсором для следующей страницы
    next_cursor = result[-1].id if len(result) == limit else None
    return {
        "items": result,
        "next_cursor": next_cursor
    }

Преимущества:

• Константная производительность независимо от глубины пагинации (O(log n) при индексе по id).
• Стабильность: при добавлении новых записей порядок не смещается (в отличие от OFFSET).
• Идеально для бесконечной прокрутки (infinite scroll).

Недостатки и их решение:

• Невозможность перейти на произвольную страницу - только последовательная навигация. Для UI с номерами страниц можно комбинировать с кешированием курсоров.
• Требуется уникальный, монотонно возрастающий ключ (например, автоинкрементный id или created_at с уникальностью). Если данные сортируются не по id, создайте составной индекс: (sort_field, id).

Пример для сортировки по дате (с учётом возможных дубликатов):

PYTHON
def get_paginated_posts_by_date(cursor: tuple | None = None, limit=20):
    query = select(Post).order_by(desc(Post.created_at), desc(Post.id)).limit(limit)
    
    if cursor:
        created_at, post_id = cursor
        query = query.where(
            (Post.created_at < created_at) | 
            ((Post.created_at == created_at) & (Post.id < post_id))
        )
    
    result = session.execute(query).scalars().all()
    next_cursor = (result[-1].created_at, result[-1].id) if len(result) == limit else None
    return {"items": result, "next_cursor": next_cursor}

Ключевой момент: всегда добавляйте в ORDER BY и WHERE уникальный ключ (id) для устранения неоднозначности при равных значениях сортируемого поля.