PostgreSQL vs MySQL: архитектура

Зачем сравнивать

PostgreSQL и MySQL — две самые популярные реляционные СУБД. Различия — не только в синтаксисе, но и в архитектуре, что влияет на:

• Производительность под нагрузкой.
• Возможности (типы, индексы, фичи).
• Расширяемость.
• Сценарии использования.

Правило: понимание архитектуры = правильный выбор.

Процессная модель

PostgreSQL — process-per-connection:

Client 1 → backend process 1
Client 2 → backend process 2
Client 3 → backend process 3

Что даёт:

• Изоляция на уровне ОС.
• Один процесс упал — остальные живы.
• Расширения могут быть на C.

Что стоит:

• ~10 MB RAM на соединение.
• 500+ соединений → память уходит.
• Нужен PgBouncer для pool.

MySQL — thread-per-connection:

Client 1 ─┐
Client 2 ─┼─→ mysqld (один процесс)
Client 3 ─┘

Что даёт:

• Легче по памяти на соединение (~500 KB).
• 1000+ соединений — реально.

Что стоит:

• Один процесс → thread crash может задеть других.
• Пул всё равно нужен для больших нагрузок.

Итог: MySQL эффективнее по памяти на соединение, но оба требуют пул в проде.

Хранение данных

PostgreSQL — heap tables:

• Данные в куче (heap).
• Индексы → ctid (физический адрес строки).
• MVCC — версии строк в heap, старая версия помечается мёртвой.
• VACUUM — очистка мёртвых версий.

Что даёт:

• Гибкость — индексы независимы.
• Плюс HOT updates (без обновления индексов, если индекс не менялся).

Что стоит:

• Раздутие (bloat) — heap растёт, нужен VACUUM.
• Нет clustered-таблицы по произвольному ключу (только через CLUSTER — разово).

MySQL (InnoDB) — clustered index:

• Данные хранятся в порядке PRIMARY KEY.
• Secondary-индексы → PK (не физический адрес).
• MVCC — undo log хранит старые версии.
• Purge — автоочистка undo.

Что даёт:

• Быстрое чтение по PK (данные рядом).
• Быстрый range scan по PK.
• Меньше блоат.

Что стоит:

• PK определяет физический порядок — важно выбрать правильно.
• Secondary-индексы делают 2 lookup (индекс → PK → строка).
• Random PK (UUIDv4) — фрагментация вставки.

Итог: InnoDB быстрее для PK-запросов, PostgreSQL гибче.

Clustered vs Heap — что это значит

Пример: SELECT * FROM users WHERE id = 5.

PostgreSQL:

1. Индекс по id → ctid.
2. Heap → прочитать строку.

InnoDB:

1. PK — это сам индекс с данными в листьях.
2. Одно чтение — данные сразу.

Правило: в InnoDB короткий PK (BIGINT) → всё быстрее. UUID → хуже.

Индексы

PostgreSQL:

• B-tree, Hash, GIN, GiST, BRIN, SP-GiST, Bloom.
• Частичные индексы.
• Функциональные индексы.
• Covering через INCLUDE.
• Index Only Scan — без чтения таблицы.

MySQL:

• B-tree, FULLTEXT, SPATIAL, Hash (MEMORY).
• Нет частичных (эмуляция через generated column).
• Функциональные — с 8.0.13.
• Covering — если все колонки в индексе.
• Index Condition Pushdown, Multi-Range Read.

Итог: PostgreSQL — больше типов и возможностей.

MVCC

PostgreSQL:

• Старые версии строк в heap.
• UPDATE = новая строка + старая помечена мёртвой.
• VACUUM чистит.
• Long-running transaction блокирует vacuum → блоат.

MySQL InnoDB:

• Старые версии в undo log.
• UPDATE = изменение строки + undo-запись.
• Purge чистит автоматически.
• Long transaction → undo log растёт.

Что это значит:

• Postgres — больше диска на блоат, нужен VACUUM.
• InnoDB — undo log может расти, но без блоата таблиц.

Транзакции

PostgreSQL:

• MVCC + serializable snapshot isolation (SSI).
• Реальный Serializable с автоматическим retry-сценарием.
• Default — Read Committed.

MySQL InnoDB:

• Default — Repeatable Read.
• SERIALIZABLE — через блокировки.
• Gap locks (для RR).

Что это значит:

• Postgres — предсказуемее для сложных транзакций.
• InnoDB — быстрее для простых, но gap locks бывают неожиданными.

DDL

PostgreSQL:

• DDL транзакционный. CREATE TABLE можно откатить.
• ALTER TABLE — безопасно, часто без блокировки (для новых колонок).
• CREATE INDEX CONCURRENTLY — без блокировки записи.

MySQL:

• DDL не транзакционный. Каждый ALTER — autocommit.
• ALTER TABLE — копирует таблицу (или online — MySQL 8).
• ADD COLUMN в новых версиях — instant (без копирования).

Что это значит:

• Postgres — миграции безопаснее и легче.
• MySQL — риск при ALTER на больших таблицах.

Расширяемость

PostgreSQL:

• Расширения на C — PostGIS, pgvector, TimescaleDB.
• Свои типы данных.
• Свои операторы.
• Свои функции.
• FDW (Foreign Data Wrappers).

MySQL:

• Плагины (storage engines).
• UDF (user-defined functions) на C.
• Ограниченнее по типам и операторам.

Что это значит: PostgreSQL — платформа, MySQL — СУБД.

Репликация

PostgreSQL:

• Streaming replication — физическая.
• Logical replication — на уровне таблиц.
• Synchronous_commit — настраивается.
• Multi-master — через BDR, Patroni.

MySQL:

• Binlog replication — async по умолчанию.
• GTID — упрощает failover.
• Semi-sync — плагин.
• Group Replication — встроенный multi-master.
• Galera — через MariaDB.

Что это значит:

• Postgres — гибкая логическая репликация.
• MySQL — проще настройка классического master-slave.

Оконные функции и CTE

PostgreSQL:

• Оконные функции — с 8.4 (2009).
• CTE — с 8.4.
• Recursive CTE — с 8.4.
• MATERIALIZED CTE — с 12.

MySQL:

• Оконные функции — с 8.0 (2018).
• CTE — с 8.0.
• Recursive CTE — с 8.0.

Что это значит:

• MySQL 5.7 — нет оконных функций, CTE.
• MySQL 8.0 — есть всё, но новее.
• Postgres — зрелее и стабильнее.

JSON

PostgreSQL JSONB:

• Бинарный формат.
• GIN-индексы по любым ключам.
• Индексы по конкретным путям.
• Операторы @>, ?, ->, ->>, #>.
• jsonb_path_query — JSONPath.

MySQL JSON:

• Текстовый формат (оптимизированный).
• Generated columns + индексы на них.
• Multi-valued индексы (с 8.0.17).
• Операторы ->, ->>, JSON_EXTRACT.

Что это значит: PostgreSQL JSONB — почти документная БД внутри реляционной.

Что общего

• ACID в default storage engine (InnoDB / Postgres).
• MVCC.
• Репликация.
• Транзакции с уровнями изоляции.
• Индексы B-tree.
• Оконные функции (в новых версиях).
• CTE.
• JSON.

Ключевые различия

Что выбрать

PostgreSQL, если:

• Нужны сложные запросы, аналитика.
• Расширяемость (PostGIS, pgvector).
• JSONB для документов.
• DDL транзакционный важен.
• UUID как PK.
• Новый проект без legacy.

MySQL, если:

• Простая CRUD-нагрузка.
• Много соединений, мало RAM.
• Legacy/WordPress/Bitrix.
• Уже есть экспертиза.
• Managed-сервис (RDS MySQL, Aurora).

Правило: для нового проекта — PostgreSQL по умолчанию, если нет конкретной причины иначе.

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

1. UUID v4 как PK в MySQL:

Фрагментация, медленный INSERT

Решение: auto_increment или UUID v7 (с 8.0.30 через UUID_TO_BIN(uuid, 1)).

2. Postgres без PgBouncer:

1000 соединений × 10 MB = 10 GB RAM

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

3. MySQL 5.7 в новом проекте:

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

Решение: MySQL 8 или Postgres.

4. Ожидание DDL-транзакций в MySQL:

ALTER TABLE нельзя откатить

Решение: понимать различия.

5. Постгрес без VACUUM tune:

Блоат, диск кончается

Решение: autovacuum настройка.

6. MyISAM вместо InnoDB:

Нет транзакций, FK

Решение: InnoDB.

7. Repeatable Read в Postgres:

Возможны ошибки сериализации

Решение: понимать уровень.

8. MySQL для аналитики:

GROUP BY по 100M строк медленно

Решение: ClickHouse или Postgres + партиции.

Практика

1. Сравните планы запросов:

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 5;

-- MySQL
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 5;

2. Проверьте clustered vs heap:

• Вставьте 1M строк, замерьте range scan по PK и по другой колонке.

3. Проверьте DDL:

-- PostgreSQL
BEGIN;
CREATE TABLE test (id int);
ROLLBACK;
-- таблицы нет

-- MySQL
START TRANSACTION;
CREATE TABLE test (id int);
ROLLBACK;
-- таблица всё равно есть

4. JSONB vs JSON:

• Создайте индекс GIN, сравните с generated column.

5. Оконные функции:

SELECT email, ROW_NUMBER() OVER (ORDER BY created_at) FROM users;

Работает в обоих (MySQL 8+).

Итог

• Postgres — heap + ctid, MySQL InnoDB — clustered PK.
• Postgres — 1 процесс на connection, MySQL — threads.
• UUID PK — ок в Postgres, плохо в MySQL.
• DDL транзакционный — только Postgres.
• Индексы — Postgres богаче (GIN, GiST, BRIN, partial).
• JSONB — Postgres мощнее.
• Оконные функции/CTE — Postgres раньше.
• Default isolation — Read Committed vs Repeatable Read.
• Postgres по умолчанию для новых проектов.

Дальше — PostgreSQL vs MySQL: производительность.