Зимняя уборка в Postgres: посмотрите на самые медленные запросы — где не хватает индексов или есть простор для оптимизации.

В туториале по анализу производительности запросов:

- как использовать pg_stat_statements
- примеры SQL для поиска самых медленных запросов — можно просто скопировать 😉

👉 @SQLPortal

Хорошие новости для пользователей Postgres под конец года. Команда TimescaleDB выпустила и открыла исходники расширения pg_textsearch.

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

👉 @SQLPortal

Зимняя уборка в Postgres: проверь неиспользуемые индексы и удали их.
Это ускорит записи, снизит расход памяти и освободит место на диске на следующий год.

👉 @SQLPortal

☝️☝️☝️

👉 @SQLPortal

Хочешь наглядно разобраться в устройстве Oracle AI Database:

- процессы
- память
- объекты
- файлы

Загляни в документацию с диаграммами технической архитектуры.

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

Либо открыть все диаграммы сразу здесь

👉 @SQLPortal

Обобщённые инвертированные индексы (GIN) - мощный инструмент в Postgres.

Они хороши тем, что переворачивают привычную модель индексации.

Вместо логики вида: строка с ID 2 содержит значение "become a database expert", индекс хранит обратное соответствие: токен "database" указывает на строки с ID 1, 2 и 3, а "expert" — на строку с ID 2.

GIN-индексу на вход подаётся набор значений для каждой строки, которую нужно проиндексировать. Каждое уникальное значение становится ключом в индексе и сопоставляется с набором CTID (идентификаторов кортежей строк), в которых это значение встречается.

У такого подхода есть несколько сценариев применения, и один из самых популярных - полнотекстовый поиск. В MySQL для этого есть отдельный тип индекса FULLTEXT, а в Postgres похожая функциональность строится на более универсальных GIN-индексах.

Как это выглядит на практике:

(a) Добавляем колонку tsvector в таблицу:

CREATE TABLE post (
    author TEXT,
    publish_date DATE,
    content TEXT NOT NULL,
    search_vector tsvector
);

(b) Заполняем её лексемами (нормализованными словами):

UPDATE post
SET search_vector =
    to_tsvector('english', content);

(c) Создаём GIN-индекс по лексемам:

CREATE INDEX post_search_idx
ON post USING GIN(search_vector);

(d) И выполняем запросы:

SELECT * FROM post
WHERE search_vector @@
      to_tsquery('english', 'database & expert');

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

👉 @SQLPortal

Зимняя уборка в Postgres:

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

Посмотреть список всех БД на сервере и их размеры, отсортированные по убыванию:

SELECT
    datname AS database_name,
    pg_size_pretty(pg_database_size(datname)) AS size
FROM
    pg_database
ORDER BY
    pg_database_size(datname) DESC;

👉 @SQLPortal

Ускорение планирования JOIN’ов — до 16 раз быстрее

Скрытая стоимость избыточной осведомленности. Так можно сказать о ситуации, когда данные распределены неравномерно, целевые значения статистики Postgres установлены высоко, и планировщик пытается оценить стоимость операции JOIN.

Более 20 лет Postgres использовал простой цикл со сложностью O(N²) для сравнения списков наиболее частых значений (Most Common Values, MCV) во время оценки join. Это работало нормально, когда целевые значения статистики малы (по умолчанию default_statistics_target равен 100).

Но сегодня опыт PostgreSQL рекомендует увеличивать это значение. Известно, что пользователи используют высокие значения (1000, а иногда и выше), чтобы справиться со сложным распределением данных, и если добавить к этому запрос с 10 соединениями — этот «простой цикл» легко может стать тихим убийцей производительности на этапе планирования.

В Postgres 19 это меняется.

👉 @SQLPortal

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