SQL-совет: как работает ORDER BY с несколькими колонками

Когда в запросе используется ORDER BY с несколькими полями, база сначала сортирует весь результат по первой колонке (в примере — sales).
Так формируются группы строк с одинаковым значением первичного поля, например все строки с sales = 12000.

Дальше включается вторичная сортировка (age ASC).
Она применяется не ко всей таблице, а только внутри этих групп.

В результате видно, что строки с одинаковым sales стоят рядом.
Например, Mira и Bob сгруппированы вместе, потому что у них одинаковые продажи (12000).
Первичная сортировка по sales DESC дала равенство, и тут в дело вступает второе поле.

Так как сортируем по age по возрастанию, Mira (30) идет перед Bob.

То же самое происходит для всех значений, которые совпали в первой сортировке:
каждое следующее поле в ORDER BY работает как тай-брейкер для предыдущего.

👉 @SQLPortal

PostgreSQL Query Planner: Практический гид по EXPLAIN ANALYZE и ускорению запросов на 50–200%

Представьте ситуацию: вчера приложение работало нормально, сегодня получили звонок – «Сайт грузит 10 секунд!». Заглядываете в логи, видите, что SQL-запрос выполняется 8 секунд вместо обычных 0.2 секунды. Но база данных, таблицы – всё то же самое. Что изменилось?

Дело в том, что PostgreSQL Query Planner – оптимизатор запросов – принимает решения на основе статистики. Когда статистика устаревает или таблица растёт в 10 раз, планировщик может выбрать неэффективный план выполнения. Например, вместо быстрого Index Scan он выберет медленный Seq Scan всей таблицы. Это не баг – это следствие неправильной информации о данных.

Читать гид

👉 @SQLPortal

О, классная новость: в Postgres 19 планируют добавить read-your-writes для реплик при асинхронной репликации. Для этого вводят новую команду WAIT FOR LSN, по смыслу аналогичную WAIT_FOR_EXECUTED_GTID_SET в MySQL. Очень круто.

👉 @SQLPortal

Не нужно заучивать SQL наизусть. Нужно понимать концепции.

Вот пример, что дает именно понимание концепций:

«Мне нужна общая сумма продаж по каждому клиенту. Значит, начинать надо с таблицы продаж (FROM). Скорее всего, нужно соединить ее с таблицей клиентов, чтобы получить имена. Дальше нужно сгруппировать все строки продаж по каждому клиенту (GROUP BY customer_id). Для каждой такой группы выбрать имя клиента и сумму по колонке sale_amount. В конце отсортировать результат от большей суммы к меньшей».

Ты только что логически собрал запрос, не написав ни одного ключевого слова SQL. В этом и сила понимания концепций » синтаксис становится просто переводом твоей мысленной модели в код.

Вот курс который как раз прокачивает эти концепции

👉 @SQLPortal

Доступ к атрибутам JSON-данных, хранящихся в Oracle AI Database

Простая dot-нотация:
<alias>.<column>.<path>.<to><attr>

Или через JSON_VALUE:
JSON_VALUE(column, '$.<path>.<to><attr>')

Dot-нотация вернёт NULL, если не сможет корректно обработать атрибут.
JSON_VALUE даёт полный контроль над поведением в таких случаях.

👉 @SQLPortal

Прежде чем дропать NULL , прочитай это

Прежде чем выкидывать все строки с NULL, вот что сначала делает дата-инженер.

Сначала выясни, откуда взялся NULL.
Он пришёл из источника, появился из-за неудачного кастинга или кривого join’а?
NULL не возникает просто так.

Потом посмотри на сырые данные.
Перед очисткой » осмотр. Паттерны в битых значениях часто говорят больше, чем любая трансформация.

Дальше проверь трансформацию, которая породила NULL.
Каст типов - один из главных виновников. Spark не падает с ошибкой. Он молча подставляет NULL.

Теперь оцени влияние.
Сколько строк ты сейчас потеряешь? Десять? Тысячу? Сорок процентов?
Если не знаешь » ты просто гадаешь.

После этого реши, действительно ли NULL — проблема.
Какие-то поля опциональны, какие-то нет. Не каждый NULL это грязные данные.

Перед фильтрацией пометь записи.
Проставь флаг. Отдели их. Сохрани для аудита или повторной обработки.
Удалённые без следа данные — это технический долг.

И в конце задокументируй решение.
Будущий ты скажет спасибо.

Фильтрация NULL это не шаг очистки.
Это решение.

👉 @SQLPortal

Базовые кирпичи стоимости

Разберём основные параметры стоимости, которые использует PostgreSQL. Самые важные категории здесь - I/O-стоимости и CPU-стоимости.

I/O-стоимости отвечают за чтение данных с диска:

seq_page_cost (по умолчанию: 1.0) — базовая величина, стоимость последовательного чтения одной страницы. Все остальные значения считаются относительно неё.

random_page_cost (по умолчанию: 4.0) — стоимость случайного чтения страницы. Почему в 4 раза дороже? Потому что на классических HDD случайный доступ реально сильно медленнее: головке диска нужно физически перемещаться. На SSD случайный доступ почти такой же быстрый, как последовательный, поэтому там это значение обычно снижают, например, до ~1.1.

Это соотношение критично для планировщика.
Высокое значение --» PostgreSQL чаще выбирает последовательное сканирование.
Низкое значение --» индексные сканы становятся намного привлекательнее.

CPU-стоимости гораздо проще, потому что процессор быстрый:

cpu_tuple_cost (по умолчанию: 0.01) — стоимость обработки одной строки.

cpu_index_tuple_cost (по умолчанию: 0.005) — стоимость обработки одной записи индекса. Она ниже, потому что индексные записи меньше.

cpu_operator_cost (по умолчанию: 0.0025) — стоимость выполнения одного оператора или вызова функции.

Обрати внимание: CPU-стоимости заметно ниже, чем I/O-стоимости. Но важно помнить, что они считаются на строку. Когда обрабатываются миллионы строк, CPU-затраты быстро накапливаются и могут серьёзно повлиять на итоговую стоимость плана.

👉 @SQLPortal

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

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

Полный анонс и подробный FAQ здесь

👉 @SQLPortal

Новости: выпущена версия pgFormatter v5.9

pgFormatter пусть и не идеален, но это самый продвинутый форматтер и бьютификатор SQL и PL/pgSQL, заточенный именно под PostgreSQL. Поставляется как CLI-утилита или CGI-программа.

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

Основные изменения:

- Исправлено несколько проблем с форматированием JOIN
- Починены отступы после RAISE EXCEPTION ... USING
- Исправлено кривое форматирование CASE с присваиванием переменных
- Добавлен CI-экшен для прогона pgFormatter при каждом commit push
- Исправлена обработка dollar-quoted строк, когда разделитель прилеплен к слову
- Починены отступы у UNION в RETURN QUERY
- Исправлен false negative при детекте ключевых слов QUERY после RETURN
- Добавлен конфиг pre-commit hook
- Исправлено форматирование PL/pgSQL с DECLARE и CASE
- Починено форматирование научной нотации с экспонентами
- Исправлено ошибочное удаление лишних скобок в CREATE POLICY
- Закрыты проблемы с форматированием в PL/pgSQL при использовании DECLARE и CASE

Полный список изменений смотри в release notes

👉 @SQLPortal

sqlit — это TUI в стиле lazygit для работы с SQL в терминале.

Поддерживает больше 10 баз (Postgres, SQLite, Supabase, ClickHouse, DuckDB и другие), есть история запросов, автодополнение и прочие удобства.

https://terminaltrove.com/sqlit/

👉 @SQLPortal

PostgreSQL позволяет клонировать базу на 6 ГБ за 212 миллисекунд вместо 67 секунд. Вот как это работает.

Клонирование баз данных полезно в нескольких сценариях:

* тестирование миграций без трогания продакшн-данных
* поднятие свежих копий под каждый прогон тестов
* сброс sandbox-окружения между сессиями
* воспроизводимые снапшоты для отладки

Когда база весит несколько мегабайт, pg_dump вполне справляется. Но когда речь идёт о сотнях гигабайт, «просто сделать копию» превращается в серьёзный узкий момент.

В PostgreSQL система шаблонов была всегда. Каждый CREATE DATABASE тихо клонирует template1 под капотом, и вместо template1 можно использовать любую базу.

В версии 15 появился параметр STRATEGY, и по умолчанию включили WAL_LOG — поблочное копирование через журнал предзаписи (WAL). I/O становится ровнее, но для больших баз это медленнее.

В PostgreSQL 18 появилась опция file_copy_method = clone. На современных файловых системах вроде XFS, ZFS или APFS она использует операцию FICLONE. Вместо копирования байтов файловая система создаёт новые метаданные, которые указывают на те же физические блоки. Обе базы используют одно и то же хранилище, пока вы ничего не меняете.

Всю магию здесь делает файловая система, создавая copy-on-write (CoW) клон файлов.

Когда вы обновляете строку, файловая система запускает copy-on-write только для затронутых страниц. Всё остальное остаётся общим. Клон на 6 ГБ изначально не занимает дополнительного места и растёт только по мере расхождения данных.

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

Довольно круто.

👉 @SQLPortal

Свежак: Elasticsearch не обязателен. BM25 уже есть в Postgres.

Теперь поиск можно закрывать прямо в базе. Postgres для всего.

Читать

👉 @SQLPortal

Шаги SQL-запроса

Когда выполняется SQL-запрос:

SELECT owner, count(*)
FROM cats
WHERE owner != 3
GROUP BY owner
HAVING count(*) = 2
ORDER BY owner DESC;

я представляю это так: каждая строка в запросе превращает одну таблицу в другую.

👉 @SQLPortal

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