Startup vs Total Cost

PostgreSQL считает для каждой операции два показателя:

Startup Cost — работа до первой строки результата. Для seq scan почти ноль, потому что строки можно отдавать сразу. Для sort - сначала надо отсортировать весь набор, и только потом появятся строки на вывод.

Total Cost — startup плюс выполнение до конца, то есть стоимость получения всех строк.

Пример на базе pagila:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM film WHERE rental_duration = 5;

Результат:

Seq Scan on film  (cost=0.00..76.50 rows=191 width=390) (actual time=0.013..0.252 rows=191 loops=1)
  Filter: (rental_duration = 5)
  Rows Removed by Filter: 809
  Buffers: shared hit=64
Planning Time: 0.063 ms
Execution Time: 0.274 ms

cost=0.00..76.50 означает:

Startup cost: 0.00 — результат можно отдавать сразу
Total cost: 76.50 — чтение страниц, обработка строк, применение фильтра

Строка Buffers: shared hit=64 говорит, что запрос прошёл по 64 страницам буферного кеша. На этих страницах и строится стоимость:

Чтение страниц:      1.0 × 64 страниц  = 64.0
Обработка строк:     0.01 × 1000 строк = 10.0
Проверка фильтра:    0.0025 × 1000 строк = 2.5
Итого:               ~76.5

Основной вклад — чтение страниц. I/O стоит дороже, чем вычисления на CPU.

Поэтому понимание параметров стоимости важно: на них строится вся логика выбора плана.

Если интересно, расчёты можно найти в исходниках: src/backend/optimizer/path/costsize.c.

Теперь, разобрав основу стоимости, можно двигаться дальше — к тому, как PostgreSQL собирает статистику, чтобы эти оценки были осмысленными.

👉 @SQLPortal

Статистика: понимание своих данных

Формулы стоимости ничего не стоят без точного представления о данных. Сколько строк пройдёт по WHERE? Сколько уникальных значений у колонки? Какие значения встречаются чаще всего? Ответы на эти вопросы дают именно статистики.

Команда ANALYZE в PostgreSQL берёт выборку из таблиц и строит статистические сводки. Эти данные участвуют во всех решениях планировщика. Именно они отделяют нормальный план выполнения от полного провала.

Что отслеживает PostgreSQL

Для каждой колонки PostgreSQL хранит набор статистик:

Row Counts: количество живых строк в каждой таблице. Это база для всех оценок - если планировщик не знает примерный размер таблицы, он не сможет адекватно прикинуть стоимость запроса.

Null Fraction: доля NULL-значений. Когда вы пишете WHERE column IS NOT NULL, именно эта метрика помогает планировщику понять, насколько фильтр будет выборочным.

Most Common Values (MCVs): самые частые значения и их частоты. PostgreSQL хранит до ~100 таких значений на колонку.

Почему MCV важны. Представим таблицу:

-- У большинства фильмов rental_duration = 6 (21.2%), остальные распределены ровнее
SELECT * FROM film WHERE rental_duration = 6;
-- MCV показывает 21.2% → вероятнее всего последовательное сканирование

SELECT * FROM film WHERE rental_duration = 7;
-- MCV показывает 19.1% → всё ещё относительно выборочно

Один и тот же индекс, разные значения, разная селективность. MCV подсказывает планировщику, сколько строк примерно попадёт в выборку.

Histograms: для значений, которые не попали в MCV, строятся гистограммы. PostgreSQL разбивает диапазон на группы примерно с одинаковым количеством строк. Это помогает оценивать диапазоны:

SELECT * FROM payment WHERE amount BETWEEN 5.00 AND 10.00;

Планировщик смотрит в гистограмму и прикидывает, какая доля строк попадёт в диапазон. От этого зависит, имеет ли смысл использовать индексный скан.

Correlation: метрика, показывающая, насколько значения в колонке коррелируют с физическим порядком строк на диске. Высокая корреляция — страницы идут последовательно с похожими значениями. Низкая — значения размазаны по таблице хаотично.

Корреляция сильно влияет на стоимость index scan. В базе pagila у rental_date корреляция 0.95 — записи вставляются по датам, страницы читаются подряд, индексный скан быстрый. У customer_id корреляция около 0.0025 — данные раскиданы случайно, индексный скан прыгает по страницам → дорого.

👉 @SQLPortal

MongoDB выкатывает внезапный праздничный фичерелиз

К РАСШИРЕННОМУ РЕЛИЗУ

Пало-Альто, Калифорния — MongoDB с радостью объявляет о MongoBleed™, инновационной фиче, которая проактивно делится содержимым вашей базы данных со всем интернетом.

«Годы нам говорили: как сделать наши чувствительные данные доступнее?» — говорит вымышленный спикер. — «MongoBleed отвечает на этот запрос. Никакой авторизации. Никакого согласия. Просто свободный, беспрепятственный поток данных наружу».

Ключевые возможности:

- Zero-Click Sharing: пароли сами уходят в интернет
- Десятилетие доверия: тихая бета с 2015 года
- Релиз под праздники: ничто так не говорит «С Рождеством», как продакшен-секреты на GitHub
- Elastic Integration: писали явно люди, которые «поняли задачу» по-своему

Отзывы пользователей:
«Я ел рождественский ужин, когда прилетел пейдж. База начала делиться логинами по всему миру. Семья реально сплотилась — вокруг моего ноутбука, пока я плакал». — вполне настоящий IT-админ

Что дальше?

Планы на 2026:

- Автоматическая рассылка паролей на Shodan
- AI-добыча секретов (они тоже в AI теперь, а как же)
- Интеграция со Slack, которая шлёт ваш .env прямо в #general

О MongoDB: MongoDB — база, которая верит, что данные хотят быть свободными. Очень свободными.

👉 @SQLPortal

Просмотр статистики

PostgreSQL держит всю эту инфу в системных каталогах, которые можно дернуть обычным запросом.

-- Базовая статистика по таблице
SELECT schemaname, relname, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'film';

-- Результат:
-- schemaname | relname | n_live_tup | n_dead_tup
-- -----------+---------+------------+------------
-- public     | film    |       1000 |          0

-- Статистика по колонкам
SELECT tablename, attname, n_distinct, null_frac,
       most_common_vals, most_common_freqs,
       correlation
FROM pg_stats
WHERE tablename = 'film' AND attname = 'rental_duration';

-- Результат:
-- tablename | attname         | n_distinct | null_frac | most_common_vals  | most_common_freqs                      | correlation
-- ----------+-----------------+------------+-----------+-------------------+----------------------------------------+-------------
-- film      | rental_duration |          5 |         0 | {6,3,4,5,7}       | {0.212,0.203,0.203,0.191,0.191}        |      0.163

Это говорит планировщику что 21.2% записей имеют rental_duration = 6, 20.3% — 3, 20.3% — 4, 19.1% — 5 и 19.1% — 7. Корреляция 0.163 — низкая, значит значения раскиданы рандомно по страницам таблицы, нет кластеризации по этому полю.

Качество статистики напрямую влияет на качество плана. Если статистика протухла, оценки будут кривые, а за ними и план станет плохим. Поэтому ANALYZE важен: запускайте его регулярно после заметных изменений данных или просто доверяйте autovacuum, чтобы он делал это сам.

Когда есть и параметры стоимости, и актуальная статистика, планировщик уже может выбирать оптимальные стратегии выполнения.

👉 @SQLPortal

Вставки в БД всегда про компромисс по производительности: либо пишем быстро и раскладываем всё по полкам позже, либо сразу платим за организацию данных.

Хорошо видно на примере MySQL и Postgres. В MySQL с InnoDB новые строки сразу падают в кластерный индекс. Это даёт быстрые последующие выборки, но вставка сама по себе дороже из-за обхода дерева, вставки в нужное место и возможного page split.

Postgres просто пишет новые строки в файл страниц/кортежей, из-за чего вставка получается попроще, но оптимизация места (VACUUM) откладывается на потом. Плюс индексы обновляются отдельно, потому что они хранятся вне основного файла таблицы.

На картину влияет и формат хранения: B-деревья против LSM-деревьев.

В B-деревьях вставка требует больше работы сразу. LSM разнесён по уровням, и система регулярно делает фоновые проходы и слияния. Запись выглядит «быстрее» (вкинули данные в in-memory слой), но потом движок постоянно занимается оптимизацией.

Отдельная история — журнал (binlog, WAL и так далее). Логи позволяют ускорить запись, перенося обновление реальных структур на диск до чекпоинта или вытеснения страницы.

Даже с этим механизмом заметно, насколько падает или растёт производительность в зависимости от того, сколько работы откладывается «на потом».

👉 @SQLPortal

Как планировщик оценивает план выполнения запросов

Раз уж разобрались с cost’ами и статистикой, посмотрим, как PostgreSQL реально собирает и выбирает план.

Планировщик разбивает запрос на части и считает стоимость каждой отдельно.

Для каждой таблицы в запросе PostgreSQL оценивает разные варианты доступа. Пройтись по всей таблице seq scan? Использовать индекс? Задействовать несколько индексов вместе? Каждый вариант получает свою оценку стоимости на основании параметров cost’ов и статистики, о которых говорили в пред постах.

Для join’ов между таблицами PostgreSQL перебирает разные алгоритмы: nested loop, hash join, merge join. Каждый вариант также получает оценку.

Итоговая стоимость плана — это сумма всех операций: сканирование таблиц, join’ы, сортировки, фильтры. PostgreSQL генерирует несколько полноценных планов, считает итоговую стоимость каждого.

Побеждает тот, у которого минимальная оценка. Именно его PostgreSQL и исполнит.

Подход снизу вверх: оцениваем отдельные операции, собираем их в полный план, выбираем самый дешёвый. Так PostgreSQL ориентируется в огромном пространстве вариантов. Дальше посмотрим, как он оценивает варианты доступа к таблицам.

👉 @SQLPortal

pgcli и mycli — отличные апгрейды по сравнению со стандартными клиентами psql и mysql.

Есть автодополнение, подсветка синтаксиса и в целом куда приятнее пользоваться.

Если заходишь к базе прямо из терминала, ставь их как можно раньше.

👉 @SQLPortal

Кое-что полезное: учебное пособие по SQL для анализа данных

ЗАБИРАЕМ ЗДЕСЬ

👉 @SQLPortal

Это лучшая статья про MVCC в MySQL, что я читал.

MySQL и Postgres используют разные инженерные подходы для решения одной и той же задачи: undo log против нескольких версий кортежей.

Ещё одна отличная работа от Jeremy Cole.

👉 @SQLPortal

4,112,928 rows affected

👉 @SQLPortal

Нужна opеn-source альтернатива Airtable?

NocoDB позволяет поверх существующей базы сделать интерфейс как у таблиц, без написания бэкенда.

Подключаешь MySQL, PostgreSQL, SQL Server, SQLite и т.д., а дальше работаешь с данными через вьюхи, фильтры, формы и связи между таблицами.

Можно развернуть у себя бесплатно или взять их хостинг, если не хочется заморачиваться с инфраструктурой.

https://github.com/nocodb/nocodb

👉 @SQLPortal

Полная и актуальная документация по PostgreSQL

SQL-синтаксис, индексы, транзакции, планировщик запросов, репликация, расширения и внутренняя архитектура. Здесь подробно описано не только как писать запросы, но и как база данных работает.

Забираем здесь!

👉 @SQLPortal

Булевы значения и целые числа в базах данных: понимание компромиссов

Булевы значения просты и рациональны, но они плохо масштабируются, когда ваша модель данных эволюционирует. Целые числа могу элегантно обрабатывать множество состояний, уменьшая сложность схемы.

При проектировании базы данных разработчики часто сталкиваются с выбором казалось бы простого решения: следует ли использовать булевы значения или целые для представления поля? Хотя на первый взгляд разница может показаться тривиальной, выбор может оказать существенное влияние на хранилище, поддержку и масштабируемость. Давайте исследуем "за" и "против" каждого подхода, сосредоточив внимание на размере хранилища, накладных операционных расходах и обслуживании в долгосрочной перспективе.

Булевы значения: простой двоичный вариант

Исходная схема:

Булево поле подходит идеально, когда значение может иметь только два состояния: true или false. Например, поля типа isActive или isAdmin - подходящий вариант. Вот типичный пример:

CREATE TABLE users (
    id INT PRIMARY KEY,
    isActive BOOLEAN
);

Требования к хранилищу:

Для большинства баз данных (например, PostgreSQL и MySQL) булево поле обычно занимает 1 байт на хранение. Это полезно для слабо масштабируемых вариантов использования, особенно когда число таких полей ограничено.

Проблемы с булевыми значениями:

Хотя булевы значения просты и интуитивно понятны, они могут быстро стать проблематичными по мере эволюции приложения:

» Булево значение может представлять только два состояния. Если ваши требования приводят к увеличению числа состояний (например, active, pending, suspended), булевы значения перестанут отвечать этим требованиям.

» Множество булевых значений для соответствующих состояний. Добавление таких полей, как isSuspended или isPending, для дополнительных состояний может привести к раздуванию схемы и усложнению логики приложения.

Про целые числа в следующем посте ❤️

👉 @SQLPortal

Простая демонстрация того, насколько мощными могут быть вложенные выражения CASE для анализа данных.

👉 @SQLPortal

Целые числа: масштабируемая альтернатива

Исходная схема

Целочисленное поле обладает большей гибкостью, поскольку оно может представлять множество состояний. Вместо многочисленных булевых полей единственное поле status может кодировать все возможные состояния, например:

CREATE TABLE users (
    id INT PRIMARY KEY,
    status INT NOT NULL
);

Здесь поле status может использовать константы в логике вашего приложения для представления различных состояний:

const Status = {
    ACTIVE: 1,
    PENDING: 2,
    SUSPENDED: 3,
    DEACTIVATED: 4,
};

Требования к хранилищу

Обычно поле integer занимает в хранилище 4 байта, независимо от того, сколько состояний оно представляет. Это делает его эквивалентным четырем булевым полям, если говорить о хранении, - но при этом значительно более выразительным.

Ставь реакцию если делать пост про "сравнение хранения: булевы значения или целые числа" 🕺

👉 @SQLPortal

Полностью бесплатная веб-книга про индексы в SQL: https://use-the-index-luke.com/

Охватывает сразу несколько СУБД: MySQL, PostgreSQL, Oracle и другие.

👉 @SQLPortal

SQL-агент с самокоррекцией и визуализацией

Этот API на базе Flask предоставляет расширенные сервисы анализа и визуализации SQL-запросов с использованием LangChain и LangGraph.

⚡️ Ссылка

👉 @SQLPortal

Сравнение хранения: булевы значения или целые числа

1. Хранилище для множества булевых полей

Скажем, мы хотим представить следующие состояния: isActive, isPending, isSuspended и isDeactivated. Использование булевых значений:

- Каждое булево значение требует 1 байт.
- Для четырех булевых полей общий объем хранилища на запись составляет 4 байта.

Если набор данных состоит из 1 миллиона пользователей, то для 4 булевых полей потребуется 4 х 1000000 = 4000000 байтов (4 Мб).

Если при развитии системе потребовалось иметь 10 состояний, представленных 10 булевыми полями, требование к хранилищу растет линейно:

10 х 1000000 = 10000000 байтов (10 Мб).

По мере роста объема данных это линейное увеличение объема памяти становится все более заметным.

2.Хранилище для единственного поля состояний

Использование целочисленного поля:

- Целое требует четырех байтов.
- Суммарный объем хранилища для 1 миллиона пользователей: 4 х 1000000 = 4000000 байтов (4 Мб).

Даже если при развитии система будет включать 10 состояний, размер хранилища на одну запись останется постоянным - 4 байта. Для 100 миллионов пользователей потребуется размер хранилища 4 х 100000000 байтов (400 Мб).

Ключевой момент

Хотя размер хранилища эквивалентен для простых случаев, целочисленное поле сохраняет постоянный размер вне зависимости от числа состояний. Напротив, хранилище для булевых полей растет линейно с ростом добавляемых полей. Для сильно масштабируемых наборов данных разница в размерах хранилища становится значительной, делая целочисленное поле более масштабируемым вариантом.

👉 @SQLPortal

Популярный SQL-вопрос на собеседованиях: Напишите запрос, который находит вторую по величине зарплату в таблице employees. А что делать, если второй по величине зарплаты не существует?

Зачем его задают:

Этот вопрос проверяет не знание синтаксиса, а базовое SQL-мышление. Поиск второй зарплаты заставляет учитывать дубликаты, сортировку, фильтрацию и пограничные случаи вроде «а если значения нет». Он показывает, умеешь ли ты рассуждать о данных, а не просто вытаскивать их запросом.
Интервьюеры также смотрят, понимаешь ли ты, как SQL пошагово обрабатывает результаты, и способен ли писать устойчивые запросы, а не хрупкие. С виду задача простая, но она быстро вскрывает, реально ли человек понимает основы SQL или просто заучил пару шаблонов.

Как отвечать:

Очевидно, способов несколько. Логика простая:

Сначала определить максимальную зарплату.

Затем найти максимальное значение, которое меньше этого максимума.

Для примера возьмём таблицу employees (id, name, salary). В решении ниже используется подзапрос.

Такой подход автоматически корректно работает с дубликатами. Даже если несколько сотрудников получают максимальную зарплату, подзапрос всё равно вернёт одно значение MAX, а внешний запрос найдёт следующую отличающуюся зарплату. Никаких костылей или допущений не нужно.

Самое важное:

Решение с подзапросом надёжное. Если второй по величине зарплаты не существует, запрос спокойно вернёт NULL, а не сломается и не выдаст мусор. Для интервьюера это сигнал, что ты думаешь про edge cases и пишешь защитный SQL.

Решение:

SELECT (
    SELECT MAX(salary)
    FROM employees
    WHERE salary < (SELECT MAX(salary) FROM employees)
) AS second_highest_salary;

👉 @SQLPortal

SQL-совет: понимание оператора EXCEPT в SQL

SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
   OR sales_person = 'Yuki Nakamura'

EXCEPT -- используем EXCEPT, чтобы получить строки из первого запроса, которых нет во втором

SELECT sales_person, sales_amount
FROM sales
WHERE sales_person = 'Wei Zhang'
   OR sales_person = 'Giovanni Rossi';

В этом запросе используется оператор EXCEPT для сравнения двух наборов результатов и возврата строк, которые есть в первом SELECT, но отсутствуют во втором.
Первый запрос выбирает записи о продажах, где sales_person равен Wei Zhang или Yuki Nakamura, а второй — записи, где sales_person равен Wei Zhang или Giovanni Rossi.

Так как используется оператор EXCEPT, в итоговый результат попадают только строки, уникальные для первого запроса. В данном случае Yuki Nakamura присутствует в первом наборе результатов, но отсутствует во втором, поэтому его запись включается в финальный вывод.
В то же время Wei Zhang есть в обоих запросах, поэтому соответствующие строки исключаются из итогового результата.

👉 @SQLPortal

Операционные накладные расходы: почему целые числа лучше

1. Эволюция схемы

Добавление новых состояний является обычным требованием в развивающихся приложениях. Рассмотрим оперативные этапы для каждого подхода:

Булевы значения:

- Добавление нового состояния требует добавление в схему нового булева поля, что влечет за собой изменение базы данных, а также тестирование в различных средах в целях безопасности. Пример:

ALTER

- Изменения схемы могут вызвать блокировку больших таблиц, падение производительности и может потребоваться время простоя.

Целые значения (status):

- Добавление нового состояния может потребовать только изменения логики приложения (например, добавление новой константы в перечисление Status).
- Никаких изменений схемы не требуется.

2. Согласованность данных

При наличии большого числа булевых полей поддержка согласованности становится проблематичной:

Пример: Для пользователя не должно одновременно выполняться isActive = TRUE и isSuspended = TRUE.

Вам потребуется дополнительная логика приложения или ограничения на уровне базы данных, чтобы обеспечить соблюдение правильных состояний.

С единственным полем status пользователь сможет иметь в каждый момент времени только одно состояние, что естественным образом препятствует ошибочным сочетаниям.

3. Простые запросы

Запросы по множеству булевых полей сложны и подвержены ошибкам:

-- Множество булевых полей
SELECT * FROM users WHERE isActive = TRUE AND isSuspended = FALSE;

При единственном поле status запросы яснее:

-- Единственное поле status
SELECT * FROM users WHERE status = 1; -- Активный пользователь

В след. посте можем разобрать влияние на запросы клиентов, ставь реакшен 😆

👉 @SQLPortal