16 вопросов
PG: больше соответствие SQL-стандарту, оконные функции, CTE, рекурсивные запросы, типы (JSONB, array, custom), расширения, строгая консистентность по умолчанию. MySQL: проще администрирование, широкое распространение в хостингах, репликация из коробки. PG часто выбирают для сложной логики и аналитики; MySQL - для типичного веб-приложения. Оба поддерживают ACID, индексы, репликацию.
VACUUM освобождает место от "мертвых" строк (удаленных или обновленных; в PG обновление создает новую версию строки). Не возвращает место ОС (обычно), но помечает для переиспользования. VACUUM FULL переписывает таблицу и возвращает место, но блокирует таблицу. ANALYZE обновляет статистику для оптимизатора. Автоавкуум (autovacuum) запускается по расписанию. Без вакуума растет bloat и замедляются запросы.
Bloat - раздувание таблиц и индексов из-за мертвых строк (старых версий при MVCC). Запросы читают больше страниц, индексы занимают лишнее место. Уменьшение: VACUUM (обычный и при необходимости VACUUM FULL), настройка autovacuum. Мониторинг: pg_stat_user_tables (n_dead_tup, n_live_tup), расширения для оценки bloat.
B-tree (по умолчанию), Hash (для равенства), GIN (полнотекст, массивы, JSONB), GiST (гео, диапазоны, полнотекст), SP-GiST, BRIN (для очень больших таблиц с естественной сортировкой). CREATE INDEX ... USING btree; и т.д. Выбор зависит от типа данных и операторов запросов.
JSONB - бинарное представление JSON, хранится разобранным. Поддерживает индексы (GIN, btree по выражению), операторы @>, ?, ?&, |, #-, функции jsonb_path_query, jsonb_agg и др. Индексирование: GIN для содержания/ключа; выражение для часто запрашиваемых полей. Эффективнее JSON для запросов и индексов.
SELECT * FROM events WHERE data @> '{"type": "click"}';
CREATE INDEX ON events USING GIN (data);Разделение одной логической таблицы на физические партиции по диапазону, списку или хешу. Запросы с условием по ключу партиционирования обращаются только к нужным партициям (partition pruning). Упрощает архивацию и удаление старых данных, может ускорить запросы. В PG: declarative partitioning (PARTITION BY RANGE/LIST/HASH); в MySQL - аналогично. Требует планирования ключа партиционирования.
Расширение PostgreSQL, собирающее статистику по выполненным запросам: текст (нормализованный), число вызовов, суммарное и среднее время, rows. Позволяет найти самые тяжелые запросы. Включается shared_preload_libraries = 'pg_stat_statements', создается расширение CREATE EXTENSION pg_stat_statements. Очистка: pg_stat_statements_reset().
Прокси-пулер соединений к PostgreSQL. Приложение подключается к PgBouncer, PgBouncer держит меньше реальных соединений к БД и мультиплексирует запросы. Режимы: session (один клиент = одно соединение к БД на время сессии), transaction (соединение возвращается в пул после каждой транзакции), statement. Уменьшает нагрузку на БД от большого числа соединений (например, от пула воркеров приложения).
Блокировки на уровне приложения: именованные целые числа или пары (bigint). Не привязаны к строке таблицы. Используются для координации между процессами: например, один воркер на задачу по id. pg_advisory_lock(id) блокирует до освобождения; pg_try_advisory_lock - не блокирует, возвращает true/false. Advisory locks снимаются в конце транзакции или сессии.
SELECT pg_advisory_lock(12345);
-- критическая секция
SELECT pg_advisory_unlock(12345);Построение индекса без эксклюзивной блокировки таблицы на запись. Таблица остается доступной для INSERT/UPDATE/DELETE. Дольше обычного CREATE INDEX и не может выполняться внутри транзакции. При сбое индекс может остаться INVALID - удалить и пересоздать. Используется на проде для добавления индексов без даунтайма.
The Oversized-Attribute Storage Technique. Большие значения (обычно > 2 KB) хранятся в отдельной TOAST-таблице; в основной строке остается ссылка. Поля типа TEXT, JSONB, большие bytea сжимаются и/или выносятся в TOAST. Ускоряет сканирование основной таблицы, когда большие столбцы не запрашиваются. Можно настроить стратегию хранения столбца (PLAIN, EXTENDED, EXTERNAL, MAIN).
Типы tsvector (нормализованный текст с позициями) и tsquery (запрос). Функции to_tsvector, to_tsquery, plainto_tsquery; операторы @@ (match). GIN-индекс по tsvector для быстрого поиска. Конфигурация языка (english, russian и др.) для стемминга и стоп-слов. Пример: WHERE to_tsvector('russian', body) @@ plainto_tsquery('russian', 'поиск').
Расширения - добавляемые модули: типы данных, функции, операторы. Установка: CREATE EXTENSION name; (из пакетов ОС или contrib). Примеры: pg_stat_statements, uuid-ossp, postgis, hstore, pg_trgm. Просмотр: SELECT * FROM pg_extension; Версии и зависимости управляются СУБД.
Sequence - объект, выдающий уникальные целые числа (nextval, currval). SERIAL/BIGSERIAL - удобный способ создания столбца с default nextval('sequence'). При вставке без указания значения подставляется следующее значение sequence. Не гарантирует отсутствие пропусков (откат транзакции, сбой). Для распределения - UUID или sequence с шагом. ALTER SEQUENCE ... RESTART для сброса.
Лог запросов, выполняющихся дольше порога (long_query_time). Включается slow_query_log = 1, файл slow_query_log_file. Анализ: mysqldumpslow, pt-query-digest. Позволяет находить тяжелые запросы для оптимизации. Дополнительно: log_queries_not_using_indexes - запросы без использования индексов.
InnoDB - движок по умолчанию: транзакции, внешние ключи, row-level locking, crash recovery, MVCC. Подходит для OLTP. MyISAM - без транзакций, table-level locking, полнотекстовый поиск (старый), меньше overhead для только чтения. Для новых проектов используют InnoDB; MyISAM устаревает. InnoDB поддерживает полнотекстовые индексы с MySQL 5.6+.