В PostgreSQL 18 появились виртуальные вычисляемые столбцы (virtual generated columns). Generated columns позволяют создавать новый столбец на основе других данных. Такие столбцы не хранятся на диске и вычисляются на лету.

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

CREATE TABLE products (
  id serial PRIMARY KEY,
  price numeric,
  tax_rate numeric DEFAULT 0.099995,
  total_price numeric GENERATED ALWAYS AS (price * (1 + tax_rate)) VIRTUAL
);

👉 @SQLPortal

Решение медленных запросов часто сводится к добавлению ещё одного индекса. Но переиндексация — вполне реальная проблема, и есть менее известная фича в Postgres получше: CREATE STATISTICS 💡

Я часто ловил себя на том, что пытаюсь починить медленный запрос или плохой execution plan, добавляя ещё один, более специфичный индекс. В большинстве случаев это работало, но… чёрт, это было дорого. Не только по дисковому пространству, но и по стоимости обновления индекса на пути записи (write path).

Малоизвестная возможность в Postgres — это расширенная статистика (extended statistics). Это то, о чём должен знать каждый, кто работает с PG. И я сам слишком долго был по другую сторону — жаль, что не узнал об этом раньше 🫣

PostgreSQL уже из коробки имеет немало информации о ваших таблицах. Однако эти данные в основном по отдельным колонкам (есть исключения) и не отражают взаимосвязи между двумя и более колонками.

CREATE STATISTICS (или extended statistics) позволяет PG собирать дополнительную многоколоночную статистику, чтобы учитывать зависимости между колонками, понимать вероятные, маловероятные и невозможные комбинации значений, а также улучшать оценку количества строк (row estimates). Причём улучшения могут быть больше чем на порядок

Это важно, потому что в реальных данных столбцы обычно не независимы друг от друга. Как кофе без кружки — это просто чёрная вода на столе.

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

И использовать это очень просто:

CREATE STATISTICS my_stats (mcv, dependencies)
ON region, plan_tier, billing_status
FROM tenants;

Требование по хранению — около 2 KiB. Аналогичный индекс занял бы ~30 MiB для того же набора данных. И execution plans при этом, как правило, получаются лучше.

Полный разбор — в этом блоге, там же есть бенчмарк (с сгенерированными execution plan’ами и отчётами) на GitHub. 👇

- Benchmark code + reports
- Blog post

👉 @SQLPortal

Концепции SQL, которые действительно нужно знать:

• CRUD → SELECT, INSERT, UPDATE, DELETE
• Ключи → PRIMARY KEY, FOREIGN KEY
• Ограничения → NOT NULL, UNIQUE, CHECK, DEFAULT
• Джоины → INNER JOIN, LEFT JOIN, RIGHT JOIN
• Агрегация → COUNT, SUM, AVG, MIN, MAX
• Группировка → GROUP BY, HAVING
• Фильтрация → WHERE, BETWEEN, IN, LIKE
• Сортировка → ORDER BY
• Подзапросы → SELECT (SELECT …)
• Индексы → CREATE INDEX
• Представления → CREATE VIEW
• Транзакции → BEGIN, COMMIT, ROLLBACK
• Пагинация → LIMIT, OFFSET
• Оптимизация → EXPLAIN

👉 @SQLPortal

Не имеет смысла пытаться инжектить relpages, так как планировщик проверяет фактический размер файла и масштабирует значения пропорционально.

Планировщик не доверяет pg_class.relpages. Он вызывает smgrnblocks(), чтобы считать реальное количество страниц по 8 КБ с диска. У вас таблица занимает 74 страницы на диске, а в pg_class.relpages указано 123 513? Планировщик использует это соотношение, чтобы масштабировать reltuples в соответствии с реальным размером файла. Относительные коэффициенты селективности остаются корректными, форма планов в целом сохраняется, но абсолютные оценки стоимости (cost) становятся неточными.

Для отладки одного запроса это обычно не критично. Но для автоматизированного регрессионного тестирования, где сравниваются значения EXPLAIN cost между запусками, это ломает воспроизводимость. Порог в 2× начинает означать разное, если базовая линия была рассчитана на «фейково» масштабированных данных.

В рамках работы над RegreSQL, автор рад представить расширение pg_regresql, которое решает эту проблему, напрямую внедряясь в планировщик.

https://boringsql.com/posts/regresql-extension/

👉 @SQLPortal

SQL-трюк, о котором знают немногие

Знаете ли вы, что можно использовать RETURNING, чтобы получать данные из DML-операций без отдельного SELECT?

Обычно после изменения таблицы пишут отдельный SELECT, чтобы посмотреть изменения. Это распространённый подход:

INSERT OR IGNORE INTO books (title, isbn, release_date)
VALUES('The Blue Book', '9780346', '1951-07-16');

-- Используем SELECT, чтобы посмотреть изменения
SELECT 
 id AS book_id,
 STRFTIME('%Y', release_date) AS year_of_release
FROM books;

В SQLite операторы INSERT, UPDATE и DELETE имеют необязательный RETURNING-клаузу (clause), которая возвращает строку, вставленную, обновлённую или удалённую. То есть вместо того, чтобы писать отдельный SELECT, можно просто добавить RETURNING.

INSERT OR IGNORE INTO books(title, isbn, release_date)
VALUES('The Blue Book', '9780346', '1951-07-16')
RETURNING 
 id AS book_id,
 STRFTIME('%Y', release_date) AS year_of_release;

Назначение RETURNING — заставить выражение возвращать по одной результирующей строке для каждой строки базы данных, которая была удалена, вставлена или обновлена. RETURNING не является стандартом SQL — это расширение.

👉 @SQLPortal

Совет по Postgres: Autovacuum включает ANALYZE для таблицы, а ручной VACUUM — нет.

VACUUM удаляет «мёртвые» строки. ANALYZE обновляет статистику для планировщика запросов. Если вы запускаете VACUUM вручную, обычно имеет смысл использовать VACUUM ANALYZE, чтобы получить оба эффекта.

👉 @SQLPortal

Postgres 18 обеспечивает чтение в 2–3 раза быстрее

В Postgres 18 ожидается серьёзное улучшение производительности чтения благодаря новой функции — Async I/O (асинхронный ввод-вывод). Она включена по умолчанию — io_method = worker.

👉 @SQLPortal

Миграция PostgreSQL объёмом 1 ТБ из AWS в Azure с помощью pgcopydb

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

В Meltwater мы недавно выполнили миграцию базы данных PostgreSQL объёмом 1 ТБ из AWS в Azure Flexible PostgreSQL в рамках более широкой задачи по консолидации инфраструктуры. Наша цель заключалась в том, чтобы безопасно перенести данные, минимизировав операционные риски и время простоя.

Для выполнения миграции мы использовали pgcopydb — open-source инструмент для миграции PostgreSQL, который автоматизирует многие сложные шаги, необходимые для копирования схемы, данных и индексов между инстансами PostgreSQL.

В этом посте мы рассмотрим:

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

👉 @SQLPortal

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

Мы понимаем, что, скорее всего, теперь вы будете генерировать SQL с помощью Claude (или другой LLM), но вам как минимум стоит понимать, что именно запрашивать.

Матрица подзапросов в Postgres

✔️ Что: subselect (подзапрос)

Когда: select внутри select
Пример:

SELECT
  sum(qty) AS total_qty, sku
FROM product_orders
WHERE
  sku IN (
    SELECT sku FROM products WHERE sale_price <= price * .75
  )
GROUP BY sku;

✔️Что: CTE

Когда: подзапросы с именованными частями
Пример:

WITH huge_savings AS (
  SELECT sku
  FROM products
  WHERE sale_price <= price * .75
)
SELECT sum(qty) AS total_qty, sku
FROM product_orders
JOIN huge_savings USING (sku)
GROUP BY sku;

✔️ Что: materialized view (материализованное представление)

Когда: сохранённый запрос в виде таблицы
Пример:

CREATE MATERIALIZED VIEW recent_product_sales AS
SELECT p.sku, SUM(po.qty) AS total_quantity
FROM products p
JOIN product_orders po ON p.sku = po.sku
JOIN orders o ON po.order_id = o.order_id
WHERE o.status = 'Shipped'
GROUP BY p.sku
ORDER BY 2 DESC;

✔️ Что: window functions (оконные функции)

Когда: агрегации по подмножествам данных
Пример:

SELECT
  sku,
  SUM(qty) OVER (PARTITION BY sku)
FROM product_orders
LIMIT 10;

✔️ Что: lateral join

Когда: коррелированный подзапрос
Пример:

SELECT accounts.id, last_purchase.*
FROM accounts
INNER JOIN LATERAL (
  SELECT *
  FROM purchases
  WHERE account_id = accounts.id
  ORDER BY created_at DESC
  LIMIT 1
) AS last_purchase ON true;

👉 @SQLPortal

На официальной вики PostgreSQL опубликована статья “Don’t Do This”, где собраны типичные ошибки при работе с базой данных и объясняется, почему их стоит избегать

👉 @SQLPortal

Бесплатный курс по SQL от Baraa Khatib Salkini, известного как DataWithBaraa. Курс охватывает всё от основ до сложных запросов и реальных проектов. 🤑

В GitHub-репозитории есть datasets/ с реальными данными из ERP и CRM, scripts/ с готовыми SQL-скриптами для практики и docs/ с документацией и материалами курса.

Ссылка на курс

👉 @SQLPortal

Сокращение в Postgres: USING можно использовать вместо JOIN ... ON a.id = b.id

SELECT DISTINCT
    t.amount AS transaction_amount
FROM
    accounts a
JOIN
    transactions t USING (account_id) 
WHERE
    a.account_id = 1;

👉 @SQLPortal

Перестаньте гадать, какие запросы тормозят вашу БД. В этом разборе разработчик показывает, как с помощью pg_stat_statements определить, какие запросы потребляют больше всего суммарного CPU-времени — и как отличить действительно медленный запрос от того, который просто слишком часто вызывается.

{ автор: Labeeb Ahmad }

👉 @SQLPortal