СУБД. Mysql vs PostgreSQL

О чем поговорим?

Сравнение функциональности
Процессная/потоковая модель сервера
Путь выполнения запроса
MVCC
Индекс

Cравнение функциональности

Функция	PostgreSQL 14+	MySQL 8+
CTE	Есть	Есть
Декларативное секционирование	Есть	Есть
Полнотекстовый поиск	Есть	Есть
GIS и SRS	Есть	Есть
JSON	Есть	Есть
Логическая репликация	Есть	Есть
Полусинхронная репликация	Есть	Есть
Оконные функции	Есть	Есть
Транзакционный DDL	Есть	Есть

Процессы и потоки

Процессы Postgresql

Connection pool

PostgreSQL	MySQL
PGPool или PgBouncer	Сlient-side connection pool

Путь запроса Postgresql

"

Путь запроса MySQL

"

Статистика

Статистика по таблицам

MySQL	PostgreSQL
Размер кластерного индекса	Размер таблицы
Количество строк	Количество строк
	Количество страниц в памяти

Статистика по атрибутам

MySQL	PostgreSQL
Распределение данных, в том числе доля NULL-значений	Распределение данных, в том числе доля NULL-значений
	Средний размер колонки в байтах
	Количество уникальных значений
	Статистическая корреляция между физическим порядком строк и логическим порядком значений столбца
	Наиболее часто встречаемые значения и их частота

Межаттрибутная статистика

MySQL	PostgreSQL
	Функциональные зависимости
	Количество уникальных значений

ACID

ACID описывает требования к транзакционной системе, обеспечивающие наиболее надёжную и предсказуемую её работу.

Atomicity — Атомарность
Consistency — Согласованность
Isolation — Изолированность
Durability — Долговечность

MVCC

Multi Version Concurrency Control

UPDATE IN PLACE 1: TABLE

id	name	notes
1	Alice	Great at programming
2	Bob	Always talking to alice
3	Eve	Listens to everyone's conversations

UPDATE IN PLACE 2: SCAN

id	name	notes
1	Alice	read Great at programming
2	Bob	Always talking to alice
3	Eve	Listens to everyone's conversations

UPDATE IN PLACE 3: UPDATE

id	name	notes
1	Alice	Great at programming
~ update 2	Bob	Always talking to alice
3	Eve	Listens to everyone's conversations

UPDATE IN PLACE 4: UPDATED

id	name	notes
1	Alice	Great at programming
~ update 2	Bob	Working very hard
3	Eve	Listens to everyone's conversations

UPDATE IN PLACE 5: INSERT

id	name	notes
1	Alice	Great at programming
2	Bob	Working very hard
3	Eve	Listens to everyone's conversations
+ insert 4	Dave	Very promising new-hire

UPDATE IN PLACE 6: DELETE

id	name	notes
1	Alice	Great at programming
2	Bob	Working very hard
- delete 3	Eve	Listens to everyone's conversations
4	Dave	Very promising new-hire

UPDATE IN PLACE 7

id	name	notes
1	Alice	Great at programming
2	Bob	Working very hard
4	Dave	Very promising new-hire

UPDATE IN PLACE 8: REALITY

id	name	notes
1	Alice	read Great at programming
+ update 2	Bob	Working very hard
- delete 3	Eve	Listens to everyone's conversations
+ insert 4	Dave	Very promising new-hire

MVCC MySQL InnoDB

Cравнение реализацйи MVCC

Размер версии объекта
Insert
Восстановление предыдущей версии в случае отката транзакции
Освобождение места занятого старой версией
Влияние задержек очистки мертвых кортежей
Последовательное сканирование "распухшей таблицы"
Индексы

Индексы

Два основных типа архитектур индекса

Кластерный индекс
Некластерный индекс

Связанные понятия

Первичный индекс
Вторичный индекс

Индексы PostgreSQL

Все индексы являются некластерными (вторичными), так как хранятся в отдельной структуре в табличной куче

Индексы PostgreSQL

Есть поддержка составных, частичных, функциональных

Индексы PostgreSQL

Большой выбор встроенных типов индексов:

B-Tree
Hash индекс
GIN (Generalized Inverted Index)
GiST (Generalized Search Tree)
SP-GiST (Space Partitioned GiST)
BRIN (Block Range Index)

Индексы в MySQL

В табличке может быть 1 кластерный и несколько некластерных индексов

Индексы в MySQL

Есть поддержка составных, частичных, но нет поддержки функциональных

Индексы PostgreSQL

Большой выбор встроенных типов индексов:

B-Tree
Hash индекс
GIN (Generalized Inverted Index)
GiST (Generalized Search Tree)
Префиксные индексы