Мы измерили SQLite и PostgreSQL на 100 тысячах строк и разобрали планы запросов. Индекс ускорил выборку в 156 раз

Семь базовых шагов в пяти конфигурациях, разбор планов EXPLAIN, цена составного индекса и стоимость сетевых обращений. Показываем реальные планы запросов, скрытую ловушку с приведением типов и честное сравнение режимов надёжности.
Мы измерили SQLite и PostgreSQL на 100 тысячах строк и разобрали планы запросов. Индекс ускорил выборку в 156 раз

Мы создали в SQLite и PostgreSQL таблицу на 100 тысяч строк одной и той же командой и выполнили на ней одинаковый набор из семи шагов: загрузку, три вида чтения и три вида записи. Затем на тех же 100 тысячах строк проверили два фильтрующих запроса до и после добавления составного индекса и разобрали их планы через EXPLAIN QUERY PLAN и EXPLAIN (ANALYZE, BUFFERS). Клиент один, скрипт на Python общий, меняются только база, режим журнала, настройки синхронизации с диском и наличие индекса. Версии свежие: SQLite 3.54.1 от 16 октября и PostgreSQL 18.7 от 12 ноября.

На нашем стенде один запрос по ключу к PostgreSQL занимает в среднем около 200 микросекунд, потому что идёт по TCP к серверу и обратно. SQLite работает внутри процесса приложения и справляется за 6–14 микросекунд в зависимости от режима журнала. На тяжёлом агрегате по всей таблице картина меняется: PostgreSQL считает его в 1,6 раза быстрее. Когда мы добавили в эксперимент фильтрацию по статусу и сумме, один составной индекс ускорил выборку топ-20 в 156 раз у SQLite и в 39 раз у PostgreSQL, а заодно вскрыл в EXPLAIN скрытое приведение типов между numeric(10,2) и float. Ниже — стенд, цифры, разбор планов и честные пары для сравнения.

Что и на чём мерили

Таблица заказов: числовой ключ, покупатель, статус, сумма, дата и индекс по покупателю. Покупателей тысяча, статусов четыре.

        CREATE TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  amount numeric(10,2) NOT NULL,
  created_at text NOT NULL
);
CREATE INDEX orders_customer ON orders (customer_id);
    
Параметр Значение
Процессор AMD Ryzen 9 5900X, 22 логических ядра доступны в WSL2
Система Ubuntu 22.04.5 под WSL2, ядро 6.18, виртуальный диск с ext4
Python 3.13.18, модуль sqlite3 и psycopg 3.3.9
SQLite 3.54.1, база в файле на том же диске
PostgreSQL 18.7, настройки по умолчанию, shared_buffers 128 МБ
Ограничение сервер и каждый прогон в группах systemd с лимитом памяти 2 ГБ

PostgreSQL мы запустили без установки в систему: сервер, Python и библиотеки взяли из conda-forge во временный каталог, а кластер создали командой initdb. Клиент подключался по TCP к 127.0.0.1. Во время замера машина была занята другими задачами, средняя загрузка держалась между 8 и 14 при 22 ядрах, поэтому мы приводим медиану пяти прогонов, а для записи — ещё и разброс.

Семь шагов

  1. Загрузка: 100 тысяч строк через executemany в одной транзакции.
  2. Чтение по ключу: 10 тысяч запросов SELECT ... WHERE id = ? со случайными ключами. Здесь и ниже запросы записаны с плейсхолдером ? модуля sqlite3, для psycopg скрипт подставляет %s.
  3. Заказы покупателя: тысяча запросов WHERE customer_id = ? ORDER BY id LIMIT 20.
  4. Агрегат: 100 запросов SELECT status, count(*), sum(amount) FROM orders GROUP BY status.
  5. 2 000 обновлений, каждое в своей транзакции.
  6. 2 000 вставок, каждая в своей транзакции.
  7. Обновление тех же 2 000 строк (только столбца amount) в одной транзакции.

Генератор случайных ключей запускается с фиксированным начальным значением, поэтому все конфигурации получают одинаковые запросы. В конце скрипт сверяет результат: во всех пяти конфигурациях в таблице 102 000 строк и одинаковая сумма по столбцу amount. Типы при этом устроены по-разному: PostgreSQL хранит numeric(10,2) как точное десятичное число, а SQLite точность в скобках не учитывает и хранит такие значения как целые или вещественные. На скорости агрегата это могло сказаться.

Конфигураций пять. Они отличаются режимом журнала и тем, когда база синхронизирует данные с диском:

        # SQLite: по умолчанию
con = sqlite3.connect("bench.db", isolation_level=None)

# SQLite: WAL
con.execute("PRAGMA journal_mode=WAL")

# SQLite: WAL и synchronous=NORMAL
con.execute("PRAGMA journal_mode=WAL")
con.execute("PRAGMA synchronous=NORMAL")

# PostgreSQL: по умолчанию
con = psycopg.connect("host=127.0.0.1 port=54329 dbname=postgres user=bench", autocommit=True)

# PostgreSQL: асинхронная фиксация
cur.execute("SET synchronous_commit = off")
    

Результат

Время в секундах, медиана пяти прогонов.

Шаг SQLite по умолчанию SQLite WAL SQLite WAL, NORMAL PostgreSQL PostgreSQL, async
Загрузка 100 тыс. строк 0,159 0,166 0,167 1,837 1,842
10 тыс. чтений по ключу 0,144 0,062 0,064 2,014 1,917
1 тыс. выборок по покупателю 0,035 0,033 0,027 0,321 0,298
100 агрегатов 2,860 2,890 2,843 1,741 1,695
2 тыс. обновлений по одному 8,796 1,849 0,071 2,558 1,015
2 тыс. вставок по одной 8,709 1,845 0,084 2,368 0,985
2 тыс. обновлений в одной транзакции 0,032 0,023 0,027 0,390 0,382

Одна строка сырого вывода скрипта, первый прогон конфигурации WAL с NORMAL:

        {"mode": "sqlite-wal", "version": "SQLite 3.54.1", "settings": {"journal_mode": "wal", "synchronous": 1}, "sec": {"load_100k_one_tx": 0.1569, "select_pk_10k": 0.0487, "select_customer_1k": 0.0379, "aggregate_100": 2.8427, "update_2k_each_tx": 0.0623, "insert_2k_each_tx": 0.0838, "update_2k_one_tx": 0.025}, "checks": {"pk_rows": 10000, "cust_rows": 20000, "agg_rows": 400, "final": [102000, "25282143.38"]}}
    

Чтение: SQLite выигрывает на мелких запросах

Одно чтение по ключу в SQLite с WAL занимает около 6 микросекунд, в PostgreSQL — около 200. Разница примерно в 30 раз мало говорит о скорости поиска в PostgreSQL: время уходит на дорогу. Каждый запрос отправляется к серверу по сети и возвращается обратно, даже если сервер стоит на той же машине. SQLite работает внутри процесса приложения, и запрос для него — обычный вызов функции. Разработчики SQLite описывают это сами и объясняют, почему сотни мелких запросов на страницу для SQLite не проблема.

Тем же, по нашей оценке, объясняется разница на загрузке и на 2 000 обновлений в одной транзакции: данные и команды идут к серверу PostgreSQL через сетевой протокол. Загрузку, вероятно, ускорила бы команда COPY, но мы намеренно оставили код одинаковым для обеих баз.

На агрегате картина обратная. Запрос один, а работы внутри базы много: нужно прочитать все 100 тысяч строк и сгруппировать их. PostgreSQL тратит на такой запрос 17 миллисекунд, SQLite — 28.

Режим журнала на чтение почти не влияет, кроме одной строки: чтения по ключу в SQLite с WAL примерно в 2,3 раза быстрее, чем с журналом по умолчанию, — 0,062 против 0,144 секунды.

Индексы и планы запросов на тех же 100 тысячах строк

В таблице orders изначально есть только первичный ключ id и индекс orders_customer (customer_id). Чтобы посмотреть, как оба движка строят планы без нужного индекса и с ним, мы прогнали на тех же 100 000 строк два новых запроса в SQLite 3.54.1 (WAL, synchronous=NORMAL) и PostgreSQL 18.7:

  1. Выборка топ-20 дорогих оплаченных заказов (SELECT id, customer_id, amount, created_at FROM orders WHERE status = ? AND amount >= ? ORDER BY amount DESC LIMIT 20), 200 прогонов.
  2. Группировка по покупателям (SELECT customer_id, count(*), sum(amount) FROM orders WHERE status = ? AND amount >= ? GROUP BY customer_id), 50 прогонов.

Затем мы добавили составной индекс orders_status_amount — в SQLite по столбцам (status, amount, customer_id), а в PostgreSQL в виде (status, amount DESC) INCLUDE (customer_id, created_at) — и повторили замеры. Медиана пяти прогонов, время в секундах:

Запрос SQLite до SQLite после Ускорение SQLite PostgreSQL до PostgreSQL после Ускорение PostgreSQL
200 выборок топ-20 1,076 0,0069 в 156 раз 1,924 0,0498 в 39 раз
50 агрегатов по покупателям 3,7498 0,1098 в 34 раза 0,5321 0,3130 в 1,7 раза

Само построение индекса orders_status_amount на 100 000 строк заняло 0,0856 с в SQLite и 0,1911 с в PostgreSQL.

Планы в SQLite (EXPLAIN QUERY PLAN)

Без составного индекса выборка топ-20 читает всю таблицу и складывает найденное во временное дерево для сортировки:

        SCAN orders
USE TEMP B-TREE FOR ORDER BY
    

Для агрегата по покупателям без нужного индекса планировщик SQLite выбрал другой путь: он пролистал записи по индексу orders_customer (SCAN orders USING INDEX orders_customer), чтобы сразу идти в порядке группировки по customer_id. Поскольку столбцов status и amount в этом индексе нет, обращения к основной таблице всё равно остаются, а сам индекс здесь непокрывающий. После создания orders_status_amount (status, amount, customer_id) оба запроса переходят на точечный поиск по индексу, а агрегат уже обходится без чтения таблицы (COVERING INDEX):

        -- Топ-20:
SEARCH orders USING INDEX orders_status_amount (status=? AND amount>?)

-- Агрегат по покупателям:
SEARCH orders USING COVERING INDEX orders_status_amount (status=? AND amount>?)
USE TEMP B-TREE FOR GROUP BY
    

Именно переход от полного сканирования с сортировкой к поиску по индексу сократил время 200 выборок с 1,076 с до 0,0069 с.

Планы в PostgreSQL (EXPLAIN (ANALYZE, BUFFERS))

До создания индекса PostgreSQL на выборке топ-20 сканирует таблицу последовательно (Seq Scan), просматривает 788 буферов и отбрасывает 94 999 лишних строк ради 20 нужных:

        Limit  (cost=2760.62..2760.67 rows=20 width=25) (actual time=7.340..7.342 rows=20.00 loops=1)
  Buffers: shared hit=788
  ->  Sort  (cost=2760.62..2781.53 rows=8366 width=25) (actual time=7.338..7.339 rows=20.00 loops=1)
        Sort Key: amount DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..2538.00 rows=8366 width=25) (actual time=0.011..6.812 rows=5001.00 loops=1)
              Filter: ((status = 'paid'::text) AND ((amount)::double precision >= '400'::double precision))
              Rows Removed by Filter: 94999
Planning Time: 0.228 ms
Execution Time: 7.362 ms
    

После создания orders_status_amount время выполнения падает с 7.362 ms до 0.156 ms, а вместо 788 буферов хватает 20 попаданий в shared buffers и 3 чтений блоков вне этого кеша:

        Limit  (cost=0.42..10.66 rows=20 width=25) (actual time=0.098..0.137 rows=20.00 loops=1)
  Buffers: shared hit=20 read=3
  ->  Index Scan using orders_status_amount on orders  (cost=0.42..4242.93 rows=8286 width=25) (actual time=0.096..0.133 rows=20.00 loops=1)
        Index Cond: (status = 'paid'::text)
        Filter: ((amount)::double precision >= '400'::double precision)
        Index Searches: 1
Planning Time: 0.208 ms
Execution Time: 0.156 ms
    

На агрегате по покупателям индекс включил Index Only Scan с Heap Fetches: 0: PostgreSQL перестал обращаться к основной таблице и берёт все нужные столбцы прямо из индекса:

        HashAggregate  (cost=1257.85..1270.35 rows=1000 width=44) (actual time=9.887..9.972 rows=250.00 loops=1)
  Group Key: customer_id
  Batches: 1  Memory Usage: 193kB
  Buffers: shared hit=4 read=150
  ->  Index Only Scan using orders_status_amount on orders  (cost=0.42..1195.70 rows=8286 width=10) (actual time=0.036..8.731 rows=5001.00 loops=1)
        Index Cond: (status = 'paid'::text)
        Filter: ((amount)::double precision >= '400'::double precision)
        Rows Removed by Filter: 19999
        Heap Fetches: 0
    

В этих же планах всплыла практическая ловушка. Столбец amount объявлен как numeric(10,2), а скрипт на Python передавал порог суммы как float. Из-за несовпадения типов PostgreSQL привёл столбец к double precision: в Index Cond попал только status = 'paid'::text, а условие по amount ушло в Filter: ((amount)::double precision >= '400'::double precision). Индекс всё равно отсёк чужие статусы и отдал строки в готовом порядке amount DESC для LIMIT 20, но условие по сумме проверялось фильтром уже после чтения из индекса. На агрегате это дало Rows Removed by Filter: 19999. Чтобы условие по amount работало прямо в Index Cond, передавайте параметр как Decimal или приводите его в запросе к numeric.

Запись: всё решает сброс на диск

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

Конфигурация Одна транзакция Разброс на 2 тыс. обновлений
SQLite по умолчанию 4,4 мс 8,20–24,96 с
SQLite WAL 0,92 мс 1,77–9,28 с
SQLite WAL, NORMAL 0,036 мс 0,058–0,140 с
PostgreSQL 1,28 мс 2,36–6,59 с
PostgreSQL, async 0,51 мс 0,97–1,09 с

В режиме по умолчанию SQLite оказался в 3,4 раза медленнее PostgreSQL и показал разброс до 24,96 с на 2 000 транзакций: при каждой фиксации с журналом отката содержимое записывается дважды (в журнал и в основной файл базы) с ожиданием диска, а на виртуальном диске под фоновой нагрузкой это, вероятно, усиливало выбросы. После переключения на PRAGMA journal_mode=WAL медиана сразу опустилась с 8,796 с до 1,849 с, и SQLite обогнал PostgreSQL (2,558 с). Когда мы дополнительно включили synchronous=NORMAL, SQLite перестал ждать синхронизации диска на каждом коммите, и 2 000 обновлений прошли за 0,071 с — в 124 раза быстрее режима по умолчанию и без многосекундных выбросов.

Честные пары и цена трёх сетевых команд

Быстрые режимы меняют гарантии долговечности, поэтому сравнивать их с медленными напрямую некорректно. При WAL с synchronous=NORMAL SQLite остаётся согласованным, но по документации транзакция может откатиться при отключении питания или сбое ОС (при падении самого приложения данные сохраняются). У PostgreSQL с synchronous_commit = off похожий отказ от синхронизации каждого коммита, но другое поведение при сбое самого процесса базы: сервер отвечает клиенту раньше сброса журнала на диск, так что неподтверждённые транзакции могут потеряться и при падении сервера PostgreSQL, хотя сама база не повредится.

Поэтому на нашем стенде корректно сравнивать две пары:

  • С полной синхронизацией на диск: SQLite WAL против PostgreSQL по умолчанию (1,849 с против 2,558 с на 2 000 обновлений).
  • С асинхронной фиксацией: SQLite WAL с NORMAL против PostgreSQL async (0,071 с против 1,015 с).

Во второй паре разницу в 14 раз вероятнее всего объясняют накладные расходы клиент-серверного выполнения. Диска PostgreSQL больше не ждёт, но каждая из 2 000 транзакций в скрипте состоит из трёх отдельных команд по TCP к 127.0.0.1: BEGIN, UPDATE и COMMIT. Если одна клиентская операция (проход по локальному TCP, обработка в драйвере и на сервере) занимает порядка 150–200 микросекунд, три команды на транзакцию как раз и складываются в 0,51 мс (1,015 с на весь цикл). Это хорошо видно по седьмому шагу замера: когда обновление тех же 2 000 строк (только столбца amount) упаковано в одну общую транзакцию (один BEGIN, 2 000 UPDATE и один COMMIT), время PostgreSQL падает до 0,382–0,390 с, а SQLite внутри процесса укладывается в 0,023–0,032 с.

Что показывают эти замеры

  • С одним писателем на 100 тысячах строк SQLite в режиме WAL на нашем стенде обошёл PostgreSQL на загрузке, точечном чтении и во всех вариантах записи и уступил только на неиндексированных агрегатах по всей таблице (2,843–2,890 с против 1,695–1,741 с).
  • Один правильно подобранный составной индекс orders_status_amount ((status, amount, customer_id) в SQLite и (status, amount DESC) INCLUDE (customer_id, created_at) в PostgreSQL) закрывает сразу оба тяжёлых сценария: в SQLite выборка топ-20 ускорилась в 156 раз (до 0,0069 с на 200 запросов), а агрегат благодаря покрывающему индексу — в 34 раза.
  • Переход на Index Scan и Index Only Scan в PostgreSQL может незаметно споткнуться о несовпадение типов: передача float в numeric(10,2) вытеснила условие по amount из Index Cond в Filter после индекса, и в агрегате база отбрасывала почти 20 тысяч лишних индексных записей.
  • В клиент-серверной архитектуре накладные расходы протокола и обработки умножаются на число команд: цикл из BEGIN/UPDATE/COMMIT даже без ожидания диска упирается в 0,51 мс на транзакцию, потому что требует трёх отдельных обращений к серверу вместо одного.

Ограничения стенда остаются в силе: в тесте работал один клиент (конкурентную запись нескольких процессов в SQLite мы не проверяли, хотя именно для неё разработчики SQLite рекомендуют клиент-серверную базу), PostgreSQL слушал TCP вместо Unix-сокета, диск был виртуальным под WSL2, а таблица целиком помещалась в оперативную память.

Источники

Комментарии

ЛУЧШИЕ СТАТЬИ ПО ТЕМЕ