Какие плюсы и минусы пагинации через OFFSET, LIMIT в SQL?

Пагинация через OFFSET и LIMIT

OFFSET/LIMIT — самый простой способ реализации пагинации в SQL. Хотя удобен в использовании, этот подход имеет серьезные ограничения при работе с большими наборами данных.

Как работает OFFSET/LIMIT

-- Получить записи с 20 по 30 (третья страница, по 10 на странице)
SELECT * FROM users
ORDER BY id
LIMIT 10 OFFSET 20;

-- Эквивалент в других БД
SELECT * FROM users ORDER BY id LIMIT 10, 20;  -- MySQL
SELECT * FROM users ORDER BY id FETCH FIRST 10 ROWS OFFSET 20;  -- SQL Server

Пример на Python:

from sqlalchemy import text

def get_paginated_users(page: int, per_page: int = 10):
    offset = (page - 1) * per_page
    query = text(
        "SELECT * FROM users ORDER BY id LIMIT :limit OFFSET :offset"
    )
    result = db.execute(
        query,
        {"limit": per_page, "offset": offset}
    )
    return result.fetchall()

users_page_3 = get_paginated_users(page=3, per_page=10)

Плюсы OFFSET/LIMIT

1. Простота и понятность

Архитектурно простой подход — вычисляем смещение и берем N записей:

def simple_pagination(items: list, page: int, per_page: int) -> list:
    start = (page - 1) * per_page
    return items[start:start + per_page]

2. Поддержка перехода на произвольную страницу

Можно прыгать на любую страницу напрямую (хотя это иллюзия при минусах):

# Пользователь может запросить любую страницу
# UI с кнопками "1 2 3 ... 100"
page = request.args.get('page', 1, type=int)
users = get_paginated_users(page)

3. Хорошо работает для маленьких наборов данных

Для таблиц с < 100K записей работает нормально.

4. Знаком разработчикам

Это стандартный способ, который видели везде.

Минусы OFFSET/LIMIT (Критические!)

1. O(n) сложность сканирования

OFFSET должен отсканировать и отбросить все предыдущие записи!

# Страница 1: OFFSET 0 — быстро, сканирует 10 строк
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 0;

# Страница 50: OFFSET 490 — медленнее, сканирует 500 строк!
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 490;

# Страница 1000: OFFSET 9990 — очень медленно, сканирует 10000 строк!
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 9990;

Рост времени выполнения:

Страница    OFFSET    Сканировано     Время выполнения
1           0         10 строк        ~1ms
10          90        100 строк       ~2ms
100         990       1000 строк      ~10ms
1000        9990      10000 строк     ~100ms
10000       99990     100000 строк    ~1000ms (1 сек!)

2. Проблема "потерянных" записей при изменении данных

Если данные добавляются/удаляются между запросами, записи могут дублироваться или пропадать:

# Пользователь на странице 2, видит ID 21-30
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;
# Результат: ID 11-20

# В это время администратор удалил ID 5

# Пользователь переходит на страницу 3
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
# Результат: ID 21-30 (вместо ожидаемого 31-40!)
# Записи с ID 21-30 уже показаны на странице 2!

3. Невозможность надежно найти место в потоке данных

# Если новые комментарии добавляются в реальном времени,
# пользователь видит прыгающие позиции

# Запрос 1: видит ID 100-110
SELECT * FROM comments ORDER BY id DESC LIMIT 10 OFFSET 0;

# Добавлено 5 новых комментариев

# Запрос 2: хотим следующие 10, но первые 5 новые!
SELECT * FROM comments ORDER BY id DESC LIMIT 10 OFFSET 10;

4. Плохо работает с кэшированием и CDN

Основано на позиции, а не на данных:

# Нет кэширования по содержимому
# /api/posts?page=1 всегда пересчитывается
# Даже если данные не изменились

5. Может привести к Cartesian Product при JOIN

-- Плохо: при большом OFFSET и JOIN
SELECT u.*, o.* 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id
LIMIT 10 OFFSET 10000;

-- БД вычисляет все комбинации, потом берет 10

Альтернативы к OFFSET/LIMIT

Cursor-based пагинация (Keyset Pagination)

Используем значение последней записи вместо позиции:

# Вместо: LIMIT 10 OFFSET 100
# Используем: WHERE id > last_id LIMIT 10

def get_next_page(last_id: int, per_page: int = 10):
    query = text(
        "SELECT * FROM users WHERE id > :last_id ORDER BY id LIMIT :limit"
    )
    return db.execute(
        query,
        {"last_id": last_id, "limit": per_page}
    ).fetchall()

# Использование
first_page = db.execute(text("SELECT * FROM users ORDER BY id LIMIT 10")).fetchall()
last_id = first_page[-1].id

second_page = get_next_page(last_id)  # O(n) где n = page_size!

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

• O(1) относительно номера страницы
• Стабильно при добавлении/удалении записей
• Кэшируется лучше

Seek-based пагинация с кодированием

import base64
import json

def encode_cursor(user_id: int, name: str) -> str:
    """Кодирует курсор в base64."""
    data = {"id": user_id, "name": name}
    return base64.b64encode(json.dumps(data).encode()).decode()

def decode_cursor(cursor: str) -> dict:
    """Декодирует курсор из base64."""
    return json.loads(base64.b64decode(cursor.encode()).decode())

def get_page_with_cursor(cursor: str = None, per_page: int = 10):
    if cursor:
        last_cursor = decode_cursor(cursor)
        query = text(
            "SELECT * FROM users WHERE id > :id ORDER BY id LIMIT :limit"
        )
        results = db.execute(
            query,
            {"id": last_cursor["id"], "limit": per_page + 1}
        ).fetchall()
    else:
        results = db.execute(
            text("SELECT * FROM users ORDER BY id LIMIT :limit"),
            {"limit": per_page + 1}
        ).fetchall()
    
    items = results[:per_page]
    has_more = len(results) > per_page
    
    next_cursor = None
    if has_more and items:
        last_item = items[-1]
        next_cursor = encode_cursor(last_item.id, last_item.name)
    
    return {
        "items": items,
        "has_more": has_more,
        "next_cursor": next_cursor
    }

# API
# GET /api/users → {"items": [...], "next_cursor": "eyJpZCI6IDEwfQ=="}
# GET /api/users?cursor=eyJpZCI6IDEwfQ== → {"items": [...], "next_cursor": ...}

Timestamp-based пагинация

from datetime import datetime

def get_recent_posts(before: datetime = None, per_page: int = 10):
    if before is None:
        before = datetime.now()
    
    query = text(
        "SELECT * FROM posts WHERE created_at < :before ORDER BY created_at DESC LIMIT :limit"
    )
    
    return db.execute(
        query,
        {"before": before, "limit": per_page}
    ).fetchall()

# Использование
first_page = get_recent_posts()
if first_page:
    last_created_at = first_page[-1].created_at
    second_page = get_recent_posts(before=last_created_at)

Когда использовать OFFSET/LIMIT

• Маленькие таблицы (< 100K записей)
• Админ-панели, где пользователей < 10K
• Неправилось делать по-другому при прототипировании
• Когда ТОЧНО нужны произвольные страницы (очень редко)

Когда избегать

• Большие таблицы (> 1M записей)
• Real-time данные (новости, комментарии, чаты)
• Mobile приложения (плохая UX без кэша)
• Высоконагруженные сервисы

Практическая рекомендация

class PaginationStrategy:
    @staticmethod
    def paginate(table_size: int, is_realtime: bool):
        if table_size < 100_000 and not is_realtime:
            return "OFFSET/LIMIT"  # просто и работает
        elif is_realtime or table_size > 1_000_000:
            return "Cursor-based"  # стабильно и быстро
        else:
            return "Hybrid"  # комбинируй оба подхода