Урок 02

PostgreSQL vs MySQL: производительность

Введение

Производительность зависит от:

  • Нагрузки — чтение/запись.
  • Схемы — нормализация, индексы.
  • Железа — CPU, RAM, диск.
  • Настройки — параметры СУБД.
  • Паттернов доступа — точечные или диапазонные.

Правило: нельзя сказать «X быстрее Y» без контекста.

Точечное чтение по PK

PostgreSQL:

Index Scan → ctid → Heap Fetch

MySQL InnoDB:

PK lookup → данные в листьях индекса

Что быстрее: InnoDB немного быстрее — одно чтение вместо двух.

Разница: 5-15%. На больших нагрузках заметно.

Диапазонное чтение по PK

PostgreSQL:

  • Heap Scan по ctid — не рядом физически.
  • Random I/O при большом диапазоне.
  • Нужен CLUSTER — разово пересортирует.

MySQL InnoDB:

  • Данные физически отсортированы по PK.
  • Sequential I/O.
  • Быстро для диапазонов по PK.

Что быстрее: InnoDB значительно быстрее для range scan по PK.

Пример:

SELECT * FROM events WHERE id BETWEEN 1000000 AND 1010000;

InnoDB: sequential read. Postgres: random read.

Что делать в Postgres:

  • BRIN-индекс по id, если данные естественно отсортированы.
  • CLUSTER по важному индексу (разово).
  • Партиционирование — данные в партиции рядом.

Write-heavy нагрузка

PostgreSQL:

  • MVCC → новая версия строки в heap.
  • Старая версия — мёртвая, ждёт VACUUM.
  • Больше записей, чем вставок.
  • Bloat растёт.

MySQL InnoDB:

  • UPDATE in-place, если возможно.
  • Undo log для откатов.
  • Меньше физических записей на UPDATE.

Что быстрее: InnoDB обычно быстрее для update-heavy.

Правило:

  • Postgres: autovacuum агрессивно.
  • InnoDB: long transaction → undo log растёт.

Insert-heavy

PostgreSQL:

  • Heap insert — быстро.
  • Индексы обновляются.
  • Write-ahead log (WAL).

MySQL InnoDB:

  • Clustered insert — в PK-порядке.
  • Если PK random (UUID v4) → фрагментация.
  • Sequential PK (auto_increment) → быстро.

Что быстрее:

  • Postgres: вставка вне зависимости от PK.
  • InnoDB: sequential PK быстрее, random — медленнее.

Правило: в InnoDB PK — auto_increment. В Postgres — можно UUID.

JOIN

PostgreSQL:

  • Hash Join, Merge Join, Nested Loop.
  • Оптимизатор очень мощный.
  • Умеет переставлять порядок JOIN.

MySQL:

  • Hash Join с 8.0.18.
  • До этого — только Nested Loop.
  • STRAIGHT_JOIN — хинт для порядка.

Что быстрее: Postgres обычно быстрее на сложных JOIN.

Правило: MySQL 5.7 без hash join → плохо на больших JOIN.

GROUP BY и агрегаты

PostgreSQL:

  • Хорошо оптимизирует.
  • Parallel query — с 9.6.
  • JIT — с 11.

MySQL:

  • Хорошо оптимизирует.
  • Parallel — нет.
  • Оптимизация под PK/индексы.

Что быстрее: Postgres на сложных агрегатах.

Пример:

SELECT country, COUNT(*), AVG(age)
FROM users
GROUP BY country;
  • Postgres: параллелит, использует hash aggregate.
  • MySQL: обычно быстрее на маленьких, медленнее на больших.

Full-text поиск

PostgreSQL:

  • tsvector, tsquery.
  • GIN-индексы.
  • Стемминг для языков.
  • Русский язык — встроен.

MySQL:

  • FULLTEXT-индекс (InnoDB с 5.6).
  • N-gram parser для русского (не всегда удобно).
  • BOOLEAN MODE.

Что быстрее: Postgres обычно, особенно для русского.

Правило: для серьёзного поиска — Elasticsearch.

JSONB vs JSON

PostgreSQL JSONB:

  • Бинарный, GIN-индексы.
  • Операторы @>, ?, ?|, ?&.
  • Очень быстро для фильтров.

MySQL JSON:

  • Текстовый (оптимизированный).
  • Генерируемые колонки + индексы.
  • Multi-valued индексы (8.0.17).

Что быстрее: Postgres с GIN обычно быстрее для сложных запросов.

Репликация и масштабирование

PostgreSQL:

  • Streaming replication — быстро.
  • Logical replication — гибко.
  • Citus — шардирование.

MySQL:

  • Binlog replication — работает.
  • Group Replication — multi-master.
  • Vitess — шардирование.
  • ProxySQL — роутинг.

Что быстрее: примерно одинаково. Экосистема MySQL чуть зрелее для шардирования.

Ресурсы

Память:

  • Postgres: shared_buffers + work_mem (per sort/hash) + per-connection overhead (~10 MB).
  • MySQL: innodb_buffer_pool_size + per-thread (~500 KB).

Postgres чувствителен к числу соединений. MySQL — легче.

CPU:

  • Postgres: parallel query.
  • MySQL: single-threaded на запрос (обычно).

Диск:

  • Postgres: heap + индексы + WAL. Блоат.
  • MySQL: clustered + undo + redo + binlog.

Настройка

PostgreSQL ключевое:

shared_buffers = '4GB'           # 25% RAM
effective_cache_size = '12GB'     # 75% RAM
work_mem = '64MB'
maintenance_work_mem = '1GB'
random_page_cost = 1.1            # для SSD
max_worker_processes = 8
max_parallel_workers_per_gather = 4
wal_buffers = '16MB'
checkpoint_completion_target = 0.9
autovacuum_vacuum_scale_factor = 0.05

MySQL ключевое:

innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
max_connections = 200

Правило: настройка важнее выбора СУБД. Плохо настроенный Postgres проиграет хорошо настроенному MySQL.

Бенчмарки

Инструменты:

  • pgbench — для Postgres.
  • sysbench — для MySQL.
  • HammerDB — оба.

Что важно:

  • TPC-C — OLTP.
  • TPC-H — OLAP.
  • YCSB — KV.

Что показывает:

  • OLTP: MySQL быстрее на точечных.
  • OLAP: Postgres быстрее.
  • Смешанные: зависит от настройки.

Реальные сценарии

Интернет-магазин (OLTP):

  • MySQL: быстрее на простых CRUD, если правильно настроен.
  • Postgres: лучше на аналитике, JSONB-характеристиках.

Соцсеть:

  • Postgres: сложные запросы, графы через recursive CTE.
  • MySQL: проще, но упирается в JOIN.

SaaS:

  • Postgres: схемы для multi-tenant, RLS.
  • MySQL: проще, но нужен tenant_id в каждом запросе.

Логи, метрики:

  • Не MySQL/Postgres. ClickHouse, TimescaleDB.

Аналитика:

  • Postgres с партициями или ClickHouse.

Как выбрать

Правила:

  • Точечные CRUD, много соединений → MySQL.
  • Сложные запросы, аналитика → Postgres.
  • UUID PK → Postgres.
  • JSONB → Postgres.
  • Расширения (PostGIS, pgvector) → Postgres.
  • Legacy/WordPress → MySQL.
  • Managed RDS → любой.

Что важнее производительности

  • Правильная схема.
  • Индексы.
  • Оптимизация запросов.
  • Кэш.
  • Реплики.
  • Партиционирование.
  • Настройка СУБД.

Правило: 90% проблем производительности — не выбор СУБД.

Частые ошибки

1. Выбор СУБД по бенчмаркам:

Свой workload ≠ бенчмарк

Решение: тестировать на своих данных.

2. Игнорирование настройки:

Postgres по умолчанию

Решение: pgTune, my.cnf tuning.

3. UUID v4 PK в InnoDB:

Фрагментация

Решение: auto_increment или UUID v7.

4. Postgres без PgBouncer:

Connection overhead

Решение: пул.

5. MySQL 5.7:

Нет оконных функций, hash join

Решение: MySQL 8.

6. Аналитика на OLTP:

Тяжёлый запрос блокирует

Решение: реплики или ClickHouse.

7. Раздутие (bloat) в Postgres:

Autovacuum не настроен

Решение: tune autovacuum.

8. Long transactions в InnoDB:

Undo log растёт, purge тормозит

Решение: короткие транзакции.

Практика

1. Поставьте оба СУБД в Docker.

2. Залейте одинаковый датасет (например, 1M строк).

3. Прогоните одинаковые запросы:

  • Точечный по PK.
  • Диапазон по PK.
  • JOIN двух таблиц.
  • GROUP BY.
  • JSONB/JSON.

4. Замерьте время, EXPLAIN.

5. Настройте оба через pgTune/my.cnf.

6. Повторите замеры.

7. Сделайте выводы для своего workload.

Итог

  • Точечные CRUD — InnoDB чуть быстрее.
  • Диапазон по PK — InnoDB значительно быстрее (clustered).
  • Update-heavy — InnoDB быстрее.
  • Insert-heavy — зависит от PK.
  • JOIN, GROUP BY — Postgres обычно быстрее.
  • JSONB — Postgres быстрее и мощнее.
  • Full-text — Postgres обычно лучше.
  • UUID PK — Postgres, не MySQL.
  • Настройка важнее выбора.
  • Свой workload — свой benchmark.

Дальше — SQL vs NoSQL: критерии выбора.