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

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

Offset-пагинация деградирует на больших объёмах данных, потому что база данных сканирует и отбрасывает все строки до offset. Основные решения: keyset (cursor-based) пагинация, индексация по сортировочным полям, ограничение глубины offset и гибридные подходы. Для API чаще используют cursor-based, для админок - offset с жёстким лимитом. В PostgreSQL дополнительно можно использовать WHERE по составному индексу вместо OFFSET.

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

Проблема offset-пагинации в том, что запрос вида LIMIT 20 OFFSET 100000 заставляет СУБД прочитать 100020 строк, отбросить первые 100000 и вернуть 20. С ростом offset стоимость растёт линейно, а при больших объёмах - квадратично из-за сортировки.

Основные стратегии:

1. Keyset (cursor-based) пагинация - вместо offset передаём значение последнего элемента предыдущей страницы и фильтруем по нему: WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 20. База использует индекс и не сканирует лишние строки. Минус - нет прямого перехода к странице N, только "вперёд/назад".

2. Индексация - для offset-пагинации нужен составной индекс на все поля из ORDER BY и WHERE. Но даже с индексом PostgreSQL должен пропустить offset строк, что всё равно деградирует.

3. Ограничение глубины - запрещаем offset больше определённого порога (например, 10000) и возвращаем ошибку или предлагаем перейти на keyset. Это прагматичное решение для большинства продуктов.

4. Гибридный подход - для первых N страниц используем offset (удобно для UI), дальше - keyset. Или используем offset для небольших наборов, keyset для больших.

5. Page-based с материализацией - если данные редко меняются, можно кэшировать полный список ID и делать WHERE id IN (SELECT ... LIMIT 20 OFFSET 100000) - но это лишь сдвигает проблему.

Выбор зависит от требований: нужен ли переход к произвольной странице, как часто меняются данные, какой объём.

На практике

Для REST API предпочтителен cursor-based: клиент получает next_cursor и prev_cursor в ответе. Для внутренних админок, где нужен переход к странице 50, используем offset с лимитом глубины и предупреждением.

Ключевые моменты реализации keyset:

• Сортировка должна быть стабильной - всегда добавляем id как tie-breaker.
• Курсор лучше кодировать (base64) и подписывать, чтобы клиент не подделывал.
• Индекс должен точно соответствовать ORDER BY и WHERE.
• Для фильтров по нескольким полям используем составной keyset: (field1, field2, id).

Для offset-пагинации с большими данными можно использовать "ленивый offset": сначала получить ID через покрывающий индекс, затем джойнить полные строки.

Пример кода

PYTHON
from typing import Optional
from fastapi import FastAPI, Query
from pydantic import BaseModel
from sqlalchemy import select, func
from sqlalchemy.ext.asyncio import AsyncSession

app = FastAPI()

class Page(BaseModel):
    items: list
    next_cursor: Optional[str]
    prev_cursor: Optional[str]

def encode_cursor(value: tuple) -> str:
    import base64, json
    raw = json.dumps(value).encode()
    return base64.urlsafe_b64encode(raw).decode()

def decode_cursor(cursor: str) -> tuple:
    import base64, json
    raw = base64.urlsafe_b64decode(cursor.encode())
    return tuple(json.loads(raw))

@app.get("/posts")
async def get_posts(
    cursor: Optional[str] = Query(None),
    limit: int = Query(20, ge=1, le=100),
    session: AsyncSession = Depends(get_session),
):
    # keyset пагинация по (created_at, id)
    stmt = select(Post).order_by(Post.created_at.desc(), Post.id.desc()).limit(limit + 1)
    
    if cursor:
        created_at, post_id = decode_cursor(cursor)
        stmt = stmt.where(
            (Post.created_at < created_at) | 
            ((Post.created_at == created_at) & (Post.id < post_id))
        )
    
    result = await session.execute(stmt)
    items = result.scalars().all()
    
    has_next = len(items) > limit
    items = items[:limit]
    
    next_cursor = None
    if has_next and items:
        last = items[-1]
        next_cursor = encode_cursor((last.created_at, last.id))
    
    return Page(
        items=items,
        next_cursor=next_cursor,
        prev_cursor=None  # для простоты опущено
    )

Для offset-пагинации с защитой:

PYTHON
MAX_OFFSET = 10000

@app.get("/admin/posts")
async def admin_posts(
    page: int = Query(1, ge=1),
    limit: int = Query(20, ge=1, le=100),
):
    offset = (page - 1) * limit
    if offset > MAX_OFFSET:
        raise HTTPException(400, "Offset too large, use cursor-based API")
    # обычный запрос с LIMIT/OFFSET

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

Начни с чёткого объяснения проблемы: "offset заставляет СУБД сканировать все строки до смещения". Затем предложи keyset как основное решение, объясни trade-off (нет перехода к странице N). Упомяни индексы и ограничение глубины как дополнительные меры. Спроси интервьюера про требования: нужен ли произвольный доступ к страницам. Если да - обсуди гибрид. Покажи понимание реализации: составной индекс, tie-breaker, кодирование курсора. В конце упомяни edge cases: concurrent updates, удаление элементов между страницами, сортировка по нестабильным полям.

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

• Понимание внутреннего устройства СУБД: как работает OFFSET и почему он деградирует.
• Умение взвешивать trade-off между разными подходами.
• Знание практических деталей: составные индексы, стабильная сортировка, кодирование курсора.
• Способность проектировать API с учётом ограничений.
• Понимание edge cases: concurrent writes, дедупликация, сортировка по полям с дубликатами.

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

• Предложение "просто добавить индекс" как панацея - индекс не решает проблему offset, только ускоряет сортировку.
• Игнорирование tie-breaker при keyset - если сортировать только по created_at, элементы с одинаковым значением потеряются или продублируются.
• Сортировка по нестабильным полям (например, по updated_at, который меняется) - курсор становится некорректным.
• Отсутствие лимита на offset - даже с keyset нужно ограничивать глубину, чтобы защитить БД от злоупотреблений.
• Неучёт удаления элементов - при offset-пагинации удаление между страницами приводит к пропуску или дублированию.
• Передача сырого курсора без подписи - клиент может подделать значение и получить несанкционированный доступ к данным.
• Использование OFFSET с большими значениями в production без мониторинга - нужно отслеживать время выполнения таких запросов.