Мы создали в 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 ядрах, поэтому мы приводим медиану пяти прогонов, а для записи — ещё и разброс.
Семь шагов
- Загрузка: 100 тысяч строк через
executemanyв одной транзакции. - Чтение по ключу: 10 тысяч запросов
SELECT ... WHERE id = ?со случайными ключами. Здесь и ниже запросы записаны с плейсхолдером?модуляsqlite3, для psycopg скрипт подставляет%s. - Заказы покупателя: тысяча запросов
WHERE customer_id = ? ORDER BY id LIMIT 20. - Агрегат: 100 запросов
SELECT status, count(*), sum(amount) FROM orders GROUP BY status. - 2 000 обновлений, каждое в своей транзакции.
- 2 000 вставок, каждая в своей транзакции.
- Обновление тех же 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:
- Выборка топ-20 дорогих оплаченных заказов (
SELECT id, customer_id, amount, created_at FROM orders WHERE status = ? AND amount >= ? ORDER BY amount DESC LIMIT 20), 200 прогонов. - Группировка по покупателям (
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, а таблица целиком помещалась в оперативную память.
Источники
- https://www.sqlite.org/datatype3.html
- https://www.sqlite.org/wal.html
- https://www.sqlite.org/pragma.html
- https://www.sqlite.org/np1queryprob.html
- https://www.sqlite.org/whentouse.html
- https://www.sqlite.org/changes.html
- https://www.postgresql.org/docs/18/wal-async-commit.html
- https://www.postgresql.org/docs/release/18.7/
Комментарии