Обобщённые инвертированные индексы (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

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