Не имеет смысла пытаться инжектить 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

5 мифов о SQL. Что часто неправильно понимают новички

SQL — это мощный инструмент, но некоторые мифы о нем могут сбить с толку новичков.

Давайте разберем пять распространенных заблуждений и развеем их: 👇

Миф 1: SQL — это только для извлечения данных.

✦ Реальность: Нет, это не так! SQL также может создавать, изменять и управлять базами данных, контролировать доступ и поддерживать консистентность данных.

✦ Как исправить: Изучите все возможности SQL, такие как DDL (для проектирования баз данных), DCL (для управления доступом) и TCL (для транзакций). SQL — это не только SELECT!

Миф 2: Использование SELECT * — это нормально.

✦ Реальность: Это может быть легко, но неэффективно. Извлечение всех колонок расходует память и замедляет производительность.

✦ Как исправить: Выбирайте только те колонки, которые вам действительно нужны. Это быстрее и чище. Не очень хорошо - SELECT * FROM employees; Лучше - SELECT employee_id, name, department FROM employees;

Миф 3: SQL не может обрабатывать сложный анализ данных.

✦ Реальность: SQL может делать гораздо больше, чем простые запросы! С такими концепциями, как оконные функции и CTE, вы можете обрабатывать действительно сложный анализ данных.

✦ Как исправить: Изучите продвинутые возможности SQL, такие как оконные функции (ROW_NUMBER(), RANK()) и CTE, чтобы улучшить свои навыки. Пример - Ранжирование сотрудников по зарплате в пределах их отдела

WITH ranked_salaries AS (SELECT employee_id, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees) SELECT * FROM ranked_salaries WHERE rank = 1;

Миф 4: Медленные запросы — всегда вина базы данных.

✦ Реальность: Обычно замедление происходит из-за неэффективных запросов. Причиной могут быть, например, отсутствующие индексы или неоптимизированный код.

✦ Как исправить: Правильно используйте индексы, избегайте сложных вычислений в условиях WHERE и проверяйте план выполнения запроса для выявления узких мест.

Миф 5: SQL устарел и скоро будет заменен.

✦ Реальность: SQL останется! Несмотря на рост популярности NoSQL, SQL по-прежнему является основой для структурированных данных.

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

Не позволяйте этим мифам останавливать вас. SQL — это мощный инструмент, и когда вы освоите его в полной мере, вы сможете делать потрясающие вещи с вашими данными.❤️

👉 @SQLPortal

Практически каждая операция, даже базовые запросы в Postgres, использует блокировки для управления доступом к ресурсам. Существует несколько типов блокировок, многие из которых не блокируют чтение или запись, но могут блокировать DDL-операции, изменяющие схему. Некоторые основные моменты.

👉 @SQLPortal

Сегодня мы изучим хранимые процедуры и функции:

Хранимые процедуры и функции — это предварительно скомпилированные объекты, хранящиеся в базе данных, которые позволяют инкапсулировать и повторно использовать логику на серверной стороне.
Хранимые процедуры:

Хранимая процедура — это набор SQL-операторов, которые могут быть выполнены как единое целое.

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL-операторы
END;

Выполнение хранимой процедуры:

EXEC procedure_name;

Функции:

Функция возвращает значение на основе входных параметров. Существуют два типа: скалярные функции и функции, возвращающие таблицы.

CREATE FUNCTION function_name (@param1 INT, @param2 VARCHAR(50))
RETURNS INT
AS
BEGIN
    -- SQL-операторы
    RETURN some_value;
END;

Вызов функции:

SELECT dbo.function_name(param1, param2);

Хранимые процедуры и функции повышают модульность и поддерживаемость кода. Они полезны для реализации бизнес-логики на стороне базы данных.

👉 @SQLPortal

Совет по Postgres: настройте префикс строки лога (log_line_prefix), чтобы при последующей фильтрации логов было больше контекста. По умолчанию там есть таймстамп и ID процесса, но можно добавить гораздо больше.

Например: user=%u, db=%d.

Новое в Postgres 18: %L — локальный IP-адрес сервера.

👉 @SQLPortal

Существует множество способов выполнить один и тот же запрос в Postgres.

- Делать поиск через последовательное сканирование (sequential scan) или по индексу?
- Какие индексы использовать?
- В каком порядке лучше джойнить таблицы?
- Какой алгоритм сортировки выбрать?

Все эти решения принимает query planner. Его задача — принять запрос, определить оптимальный план выполнения, а затем передать его в execution engine.

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

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

В таких случаях можно:

- настраивать параметры, например enable_seqscan=off
- вручную обновлять статистику
- или использовать расширения вроде pg_hint_plan, чтобы подсказать планировщику более подходящий вариант выполнения

👉 @SQLPortal

ORM обычно по умолчанию используют INSERT, но это не всегда лучший вариант — особенно если нужно загружать большие объёмы данных.

Для действительно крупных батчей стоит использовать COPY в PostgreSQL или \copy из командной строки.

Postgres: INSERT vs COPY

INSERT

- построчно (row by row), идеально для транзакций
- можно использовать ON CONFLICT
- подходит для нескольких тысяч строк, но COPY быстрее

COPY

- быстрее
- принцип «всё или ничего» (all-or-nothing)
- нужен для загрузки данных в больших объёмах
- можно запускать из psql через \copy

👉 @SQLPortal

Совет по PostgreSQL: \crosstabview

Если у вас есть результат из 3 столбцов, PostgreSQL может преобразовать его в сводную таблицу с помощью команды \crosstabview (в psql).

Для более сложных наборов данных используйте функцию crosstab()из расширения tablefunc — она требует более точной настройки с помощью SQL-запросов

👉 @SQLPortal

Нашёл коллекцию 100+ SQL-скриптов

Они могут помочь в управлении базами данных, оптимизации запросов и выполнении различных операций с данными.

Сохраняй если хочешь улучшить свои навыки работы с SQL. 👍

👉 @SQLPortal

Ищешь игры, которые помогут тебе выучить и практиковать SQL? 🤔

Попробуй SQL Noir — детективная игра по SQL

Пиши запросы, анализируй данные и раскрывай дела

Попробовать - https://www.sqlnoir.com/

👉 @SQLPortal