Совет по Postgres на сегодня: Покрывающие индексы

Индексы позволяют создавать как ключевые (индексируемые) столбцы, так и дополнительные столбцы с помощью INCLUDE. Эти INCLUDE-столбцы не входят в ключ индекса, но их данные сохраняются вместе с индексом, благодаря чему некоторые запросы могут быть обработаны исключительно за счёт индекса.

Ключевые столбцы индекса используются для сортировки и поиска. INCLUDE-столбцы «покрывают» дополнительные запросы, поэтому такие индексы часто называют покрывающими (covering indexes). Покрывающие индексы позволяют оптимизировать запросы, чтобы использовать только сканирование индекса (index-only scan) вместо полного сканирования таблицы.

Пример покрывающего индекса:

CREATE INDEX idx_orders_customer_date_include_total
ON orders (customer_id, order_date)
INCLUDE (total_amount);

> customer_id, order_date входят в ключ индекса и участвуют в поиске и сортировке
> total_amount включён, чтобы избежать обращения к таблице, если он требуется в запросе

Этот запрос может быть полностью выполнен с использованием только индекса:

SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY order_date;

👉 @SQLPortal

В версии Postgres 15, спустя многие годы ожидания, наконец появилась команда MERGE

MERGE объединяет несколько операций с данными (INSERT, UPDATE, DELETE) в один атомарный оператор.
До этого подобное поведение приходилось реализовывать через INSERT ... ON CONFLICT или с помощью SELECT + UPDATE + INSERT в связке с CTE и функциями.

MERGE особенно полезен при работе с крупными объёмами данных — например, при объединении батчей или стримов данных.

А вот статья, где Жан-Поль Аргюдо делится полезными примерами кода и советами по внедрению MERGE в ваши Postgres-процессы: тык

👉 @SQLPortal

Оконные функции в Postgres полезны, когда нужно выполнять вычисления по нескольким строкам, не объединяя их в одну. Такой набор строк называется "frame" (окно).

Именно конструкция OVER определяет границы этого окна.

Пример: накопительный итог продаж.

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

Результат: ты получаешь сумму продаж на каждый день с учётом всех предыдущих.
Это мощный способ анализировать тренды без подзапросов и JOIN

👉 @SQLPortal

SQL Cheatsheet — шпора, которую стоит сохранить

Она спасёт тебе интервью, проект и задницу 😃

👉 @SQLPortal

PostgreSQL позволяет задать любое строковое значение (включая Unicode-символы) для отображения NULL. Это удобно, чтобы легко отличать NULL от пустых строк.

\pset null '☘️'

Это значение можно прописать и по умолчанию — в конфигурационном файле psqlrc

👉 @SQLPortal

В копилку тренажёров: sqltutor.ru

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

👉 @SQLPortal

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

Если нужно сохранить, например, общую сумму как unit_price плюс налог, используйте:

ALTER TABLE coffee_shop_sales
  ADD COLUMN total_sale_amt numeric
  GENERATED ALWAYS AS
    ((unit_price * (1 + sales_tax_pct / 100) * transaction_qty)) STORED;

STORED означает, что значение хранится физически и автоматически пересчитывается при изменении исходных данных.

Пример нормализации телефонного номера, приходящего в разном виде:

ALTER TABLE contacts
  ADD COLUMN normalized_phone text
  GENERATED ALWAYS AS (REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) STORED;

Индексация — да, можно создавать индексы по генерируемым колонкам, что ускоряет фильтрацию и сортировку.

Преимущества генерируемых колонок:

> Производительность — ускоряют частые вычисления.
> Целостность данных — уменьшают риск ошибок при ручном написании выражений в запросах.

Виды генерируемых колонок:

> STORED — значения сохраняются в таблице и пересчитываются при вставке/обновлении строки. Улучшает производительность, так как нет вычислений при запросе.

> VIRTUAL — значения вычисляются на лету при запросе, но не хранятся. Удобно, когда хранение нецелесообразно (экономия места и др.).

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

👉 @SQLPortal

Разработчики на Postgres обожают CTE

Но один из способов их использования часто остаётся недооценённым — это обновление таблиц через CTE, например:

> Перемещение данных из одной таблицы в другую
> Или из одной таблицы в несколько

💡И поскольку INSERT и DELETE выполняются внутри CTE, они автоматически попадают в одну транзакцию — это удобно и безопасно.

Эта тема подробно разобрана с хорошими примерами в одной из архивных статей Дэвида Кристенсена

👉 @SQLPortal

Если ты разработчик, скорее всего, тебе приходится иметь дело с JSON-данными через большее количество API, чем хотелось бы.

А ты знал, что можно преобразовать любой JSON в реляционную таблицу с колонками SQL?

В PostgreSQL 17 есть фича — JSON_TABLE, которая позволяет на лету разворачивать массивы JSON и работать с ними, как с обычными таблицами в SQL

Ставьте лайки под постами. если они оказались полезны ✊

👉 @SQLPortal

Если вы начинаете новый проект на Postgres, какие расширения обязательно установить с первого дня?

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

> pg_stat_statements — для сбора и анализа статистики выполнения SQL-запросов. Помогает находить медленные и часто вызываемые запросы.
> pgAudit — для расширенного аудита действий в базе данных. Особенно важно для соответствия требованиям безопасности и контроля доступа.
> pg_cron — встроенный планировщик задач на основе cron. Удобен для регулярных бэкапов, очистки и других периодических задач прямо в базе.
> auto_explain — автоматически логирует планы выполнения запросов, особенно полезен для отладки медленных запросов.

👉 @SQLPortal

Большинство пользователей Postgres воспринимают эту возможность как должное.

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

А что произойдёт, если часть миграций выполнится успешно, а последующие — нет? Колонка была переименована, но код, создающий связь с этой колонкой, падает. Развёртывание нового кода срывается, а старый код по-прежнему ожидает старое имя колонки. Начинаются ошибки 500 и тревоги в системах мониторинга.

Почему миграции могут провалиться? Например, из-за неожиданных значений в продакшене, конфликтующих с новыми ограничениями. Или из-за конфликтов при слиянии веток, где обе добавляют одну и ту же таблицу. При больших объёмах данных и различиях между продакшеном и staging средой, CI/CD может отправить миграции, даже если вы к этому не готовы.

Что делать в такой ситуации?

Если вы используете Postgres — волноваться не о чем. В Postgres есть транзакции для DDL-операций (Data Definition Language). Современные инструменты управления данными (Prisma, Ruby on Rails, Django, Laravel) оборачивают DDL-команды в транзакции. Все изменения внутри такой транзакции применяются либо полностью, либо не применяются вовсе. Если миграционный скрипт вызывает ошибку, Postgres откатит все изменения, входящие в эту транзакцию.

А если вы не используете Postgres? Тогда остаются следующие варианты:

> Восстановление из резервной копии
> Ручной откат DDL-команд до предыдущего состояния
> Ручной запуск DDL-команд для завершения миграции

Без Postgres вас ждёт долгое утро по разгребанию последствий. 😁

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

👉 @SQLPortal

Шпаргалка Excel

👉 @SQLPortal

Когда вы в последний раз проверяли коэффициент попаданий в кэш?

В идеале, большая часть часто запрашиваемых данных должна находиться в буфере кэша. Для транзакционных нагрузок рекомендуется 98–99% попаданий; для аналитических — ниже.

Проверить коэффициент попаданий можно так:

SELECT 
  sum(heap_blks_read) as heap_read,
  sum(heap_blks_hit)  as heap_hit,
  sum(heap_blks_hit) / (sum(heap_blks_hit) +  sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

Для клиентов Bridge метрика попаданий в кэш доступна в разделе Insights кластера

👉 @SQLPortal

Практический вопрос

Выберите дату, товар и цену из таблицы items_ordered для всех строк, у которых значение цены находится в диапазоне от 10.00 до 80.00.

👉 @SQLPortal

Когда вы работаете напрямую в psql (CLI-редактор, поставляемый с Postgres), вы можете настроить автоформатирование результатов запроса в зависимости от размера шрифта и окна терминала.

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

Чтобы вручную включить или отключить расширенный вывод, выполните:

\x

Это позволяет вручную переключать формат отображения результатов запроса. Но настоящая магия — в команде:

\x auto

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

(Кстати, вы можете прописать это в .psqlrc, чтобы настройка применялась автоматически при каждом подключении к Postgres) 🪄

👉 @SQLPortal

Попрактикуемся в SQL

Напишите запрос, который возвращает студентов, у которых есть хотя бы один предмет с оценкой NULL, используя приведённые выше таблицы

👉 @SQLPortal

Если вы работаете с JSON в Postgres, вы, вероятно, заметили, что для этого существуют два отдельных типа данных. Так в чём разница между json и jsonb?

> json — лёгкое, необработанное хранение

> jsonb — структурированное, индексируемое и производительное

◘ json сохраняет текст в точности так, как он был введён, без обработки. Это, как правило, более лёгкий и быстрый вариант при записи, поскольку отсутствует дополнительный разбор. Однако операции с ним могут быть медленнее, так как разбор выполняется во время исполнения. Обычный json также не может быть проиндексирован.

◘ jsonb сохраняет данные в бинарном формате, при этом данные разбираются заранее при сохранении. jsonb обладает большей структурой (например, отсутствуют дублирующиеся ключи), может индексироваться и обеспечивает более быструю работу при выполнении запросов. Подавляющее большинство разработчиков, использующих JSON в Postgres, работают именно с jsonb.

👉 @SQLPortal

Нашёл ресурс для создания SQL-баз данных:

✓ Генерируй таблицы и данные с помощью ИИ
✓ Визуализируй диаграммы и связи
✓ Деплой в Supabase в один клик

И главное — бесплатно → https://database.build/

👉 @SQLPortal

Что такое векторная база данных?

От базовых понятий, таких как что такое вектор?, до продвинутых возможностей: настройка payload'ов, гибридный поиск, шардинг, мультитенантность, квантизация, RBAC и многое другое.

Эта статья охватывает всё, что нужно знать о векторных базах данных 🌹

👉 @SQLPortal

Краткая и точная шпаргалка по SQL.

👉 @SQLPortal