Обновляйте агрегаты одним запросом!

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

PostgreSQL позволяет объединить INSERT ... SELECT с ON CONFLICT:

INSERT INTO user_stats (user_id, total)
SELECT user_id, sum(amount)
FROM payments
GROUP BY user_id
ON CONFLICT (user_id)
DO UPDATE
SET total = EXCLUDED.total;

Здесь GROUP BY вычисляет суммы, а ON CONFLICT обновляет существующие записи или добавляет новые.

Для этого на user_stats(user_id) должно быть ограничение уникальности. Можно сразу пересчитывать несколько показателей:

INSERT INTO user_stats (user_id, total, orders_count)
SELECT user_id, sum(amount), count(*)
FROM payments
GROUP BY user_id
ON CONFLICT (user_id)
DO UPDATE SET
total = EXCLUDED.total,
orders_count = EXCLUDED.orders_count;

🔥 INSERT ... SELECT ... ON CONFLICT позволяет пересчитывать и сохранять агрегаты одним запросом, без циклов и промежуточной обработки в приложении.

➡️ SQL Ready | #совет

😍 Продвинутая оптимизация PostgreSQL — WAL, VACUUM и проектирование БД!

Статья о том, как повысить производительность PostgreSQL за счёт правильной настройки внутренних механизмов СУБД и проектирования структуры базы данных. Материал посвящён не только оптимизации SQL-запросов, но и причинам возникновения проблем с производительностью. Рассматриваются WAL (журнал предзаписи), VACUUM и AUTOVACUUM, очистка устаревших версий строк, управление транзакциями и проектирование таблиц.

📌 Оставляю ссылочку: dbserv.ru

➡️ SQL Ready | #сайт

🧐 Годная статья лежит на Хабре: «Как мы разрешили чтение с реплик PostgreSQL — и почему шесть лет говорили „нет“»!

В этой статье:
• Узнаете, почему чтение с реплик PostgreSQL может угрожать отказоустойчивости и приводить к проблемам с актуальностью данных;
• Разберёте, как организовать маршрутизацию запросов через Patroni, Consul и PgBouncer с учётом отставания репликации;
• Посмотрите, как перенос части запросов на реплики помог снизить пиковую нагрузку на primary и какие риски при этом остались.

🔊 Продолжай читать на Habr!

➡️ SQL Ready | #статья

Почему NOT EXISTS не всегда эквивалентен EXCEPT!

При сравнении двух наборов данных в sql часто используют NOT EXISTS или EXCEPT. Оба подхода позволяют найти строки, присутствующие в одном наборе и отсутствующие в другом. Однако их семантика различается при наличии дубликатов и NULL.

Рассмотрим задачу: найти товары поставщика, отсутствующие в основном каталоге. Создадим таблицы в PostgreSQL:

CREATE TABLE supplier_products (
    product_id INT
);

CREATE TABLE catalog_products (
    product_id INT
);

INSERT INTO supplier_products (product_id)
VALUES (10), (10), (20), (NULL);

INSERT INTO catalog_products (product_id)
VALUES (20), (NULL);

Один из вариантов решения — коррелированный NOT EXISTS:

SELECT s.product_id
FROM supplier_products s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_products c
    WHERE c.product_id = s.product_id
);

Результат (порядок строк не гарантируется):

product_id
----------
10
10
NULL

Дубликаты сохраняются, поскольку внешний запрос не содержит DISTINCT.

Строка с NULL также попадает в результат: сравнение NULL = NULL не возвращает TRUE, поэтому соответствие не обнаруживается. Теперь выполним EXCEPT:

SELECT product_id
FROM supplier_products

EXCEPT

SELECT product_id
FROM catalog_products;

Результат:

product_id
----------
10

Здесь проявляются два свойства стандартного EXCEPT. Во-первых, EXCEPT без ALL устраняет дубликаты, оставляя только уникальные строки. Во-вторых, при операциях над множествами два значения NULL считаются неразличимыми. Поэтому NULL, присутствующий в обоих наборах, исключается из результата.

Это отличается от обычного сравнения через =. Если необходимо сохранить кратность строк, стандарт sql предусматривает EXCEPT ALL:

SELECT product_id
FROM supplier_products

EXCEPT ALL

SELECT product_id
FROM catalog_products;

Результат:

product_id
----------
10
10

EXCEPT ALL работает с мультимножествами. Количество экземпляров каждой строки определяется формулой:

max(количество_слева - количество_справа, 0)

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

Ещё одно различие возникает при сравнении составных ключей.
Предположим, сравниваются пары (product_id, warehouse_id), причём warehouse_id может содержать NULL. Создадим таблицы:

CREATE TABLE supplier_stock (
    product_id INT,
    warehouse_id INT
);

CREATE TABLE catalog_stock (
    product_id INT,
    warehouse_id INT
);

INSERT INTO supplier_stock VALUES
(10, NULL), (10, NULL), (20, 1);

INSERT INTO catalog_stock VALUES
(10, NULL);

Обычное равенство не позволит сопоставить строки (10, NULL):

SELECT s.product_id, s.warehouse_id
FROM supplier_stock s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_stock c
    WHERE c.product_id = s.product_id
      AND c.warehouse_id = s.warehouse_id
);

Для NULL-безопасного сравнения стандарт SQL предусматривает IS NOT DISTINCT FROM.

Чтобы одновременно устранить дубликаты и воспроизвести семантику EXCEPT без ALL, добавим DISTINCT:

SELECT DISTINCT
    s.product_id,
    s.warehouse_id
FROM supplier_stock s
WHERE NOT EXISTS (
    SELECT 1
    FROM catalog_stock c
    WHERE c.product_id IS NOT DISTINCT FROM s.product_id
      AND c.warehouse_id IS NOT DISTINCT FROM s.warehouse_id
);

Результат:

product_id | warehouse_id
-----------+-------------
20         | 1

Для этих столбцов запрос эквивалентен EXCEPT без ALL. Все примеры рассчитаны на PostgreSQL. Поддержка отдельных конструкций зависит от СУБД и версии. По производительности универсального победителя нет: результат зависит от данных, индексов и плана выполнения.

🔥 NOT EXISTS проверяет отсутствие соответствия, EXCEPT вычисляет разность множеств без дубликатов, а EXCEPT ALL учитывает кратность строк. При выборе конструкции важно учитывать NULL и повторяющиеся значения.

➡️ SQL Ready | #практика

🐱 PostgreSQL Cookbook — практический справочник по работе с PostgreSQL на русском языке!

Материалы представлены в формате практических примеров: конкретная задача, способ её решения и пример SQL-кода. Такой формат позволяет не только изучать возможности PostgreSQL, но и использовать справочник непосредственно во время разработки. Особенно полезен, когда нужно разобраться с определённой конструкцией SQL, подобрать подходящий запрос или найти решение типовой задачи.

📌 Оставляю ссылочку: dsbasko.github.io

➡️ SQL Ready | #сайт

Почему оконная функция LAST_VALUE часто возвращает не последнюю строку!

Оконные функции позволяют выполнять вычисления по набору связанных строк без сворачивания результата через GROUP BY. Однако для некоторых функций результат зависит не только от PARTITION BY и ORDER BY, но и от границ оконного фрейма.

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

Предположим, данные представлены таблицей:

transactions(
    id,
    account_id,
    amount,
    created_at
)

Попробуем решить задачу через LAST_VALUE (PostgreSQL):

SELECT
    id,
    account_id,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at
    ) AS last_amount
FROM transactions;

На первый взгляд LAST_VALUE должна возвращать сумму последней операции в пределах account_id. Но результат может совпадать с amount текущей строки.

Причина заключается в оконном фрейме. При наличии ORDER BY стандартный фрейм в PostgreSQL определяется как:

RANGE BETWEEN UNBOUNDED PRECEDING
          AND CURRENT ROW

При этом CURRENT ROW в режиме RANGE включает группу строк с одинаковыми значениями сортировки — peer group. Рассмотрим операции одного счёта:

id  account_id  amount  created_at
--  ----------  ------  ----------
1   42          100     2026-01-10
2   42          200     2026-01-11
3   42          300     2026-01-12

Для запроса с LAST_VALUE результат будет:

id  amount  last_amount
--  ------  -----------
1   100     100
2   200     200
3   300     300

Фрейм заканчивается на текущей строке или её peer group, поэтому последующие операции с большими значениями сортировки не входят в расчёт.

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

SELECT
    id,
    account_id,
    amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS last_amount
FROM transactions;

Теперь фрейм охватывает все строки счёта, а LAST_VALUE возвращает сумму последней операции. Для приведённых данных last_amount будет равен 300 во всех строках.

Дополнительная сортировка по id обеспечивает однозначный порядок при совпадении created_at, если id уникален. Но есть ещё один нюанс — различие между ROWS и RANGE. Рассмотрим накопительную сумму:

SELECT
    id,
    account_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at
    ) AS running_total
FROM transactions;

Предположим, две операции одного счёта имеют одинаковое время:

id  amount  created_at
--  ------  ----------
1   100     10:00:00
2   200     10:00:00
3   300     11:00:00

При стандартном RANGE обе операции с временем 10:00:00 принадлежат одной peer group. Результат накопительной суммы:

id  amount  running_total
--  ------  -------------
1   100     300
2   200     300
3   300     600

Это корректное поведение: фрейм первой строки включает все операции с тем же значением created_at.

Если необходимо накапливать сумму строго по одной операции, следует использовать ROWS и уникальный порядок сортировки:

SELECT
    id,
    account_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY created_at, id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM transactions;

Теперь результат будет:

id  amount  running_total
--  ------  -------------
1   100     100
2   200     300
3   300     600

Различие принципиальное: ROWS задаёт границы относительно позиций строк, а RANGE — относительно значений сортировки. При этом без уникального ORDER BY последовательность строк с одинаковыми ключами остаётся неопределённой.

Также LAST_VALUE возвращает значение последней строки фрейма, а не максимальное значение столбца. Поэтому замена на MAX(amount) может изменить смысл расчёта. Поддержка оконных фреймов различается между СУБД, а порядок вывода строк без внешнего ORDER BY не гарантируется.

🔥 ORDER BY внутри OVER определяет порядок вычисления, но не обязательно включает весь раздел во фрейм. Для LAST_VALUE и накопительных агрегатов важно явно задавать границы фрейма и детерминированный порядок сортировки.

➡️ SQL Ready | #практика

Объединяйте соседние интервалы автоматически!

В PostgreSQL есть малоизвестный агрегат range_agg(), который объединяет пересекающиеся и соприкасающиеся диапазоны. Не нужны оконные функции, рекурсивные запросы и ручной поиск границ.

Допустим, в таблице bookings хранятся периоды занятости сотрудников.

SELECT employee_id, 
range_agg(period) AS busy_periods 
FROM bookings 
GROUP BY employee_id;

Результат — multirange: набор непересекающихся интервалов. Пересекающиеся и соседние периоды PostgreSQL объединит автоматически, сохранив промежутки между ними.

Можно сразу найти свободное время внутри рабочего дня:

SELECT employee_id, 
datemultirange( 
daterange('2026-11-01', '2026-11-08') 
) - range_agg(period) AS free_periods 
FROM bookings 
GROUP BY employee_id;

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

🔥 range_agg() вместе с multirange позволяет объединять занятые интервалы и находить свободные периоды без сложных оконных запросов.

➡️ SQL Ready | #совет

📂 Шпаргалка по структуре каталогов Linux!

Например, /etc содержит системные конфигурации, /var используется для хранения логов и изменяемых данных, а /proc предоставляет информацию о процессах и состоянии ядра.

На картинке — основные каталоги с кратким описанием их назначения и роли в файловой системе.

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

Используйте FETCH FIRST ... WITH TIES!

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

Например, запрос вернёт ровно три самых дорогих товара:

SELECT id, name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Но если у третьего и четвёртого товара одинаковая цена, четвёртый не попадёт в результат.

В PostgreSQL 13+ есть WITH TIES:

SELECT id, name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;

Теперь запрос вернёт три строки и все дополнительные строки с таким же значением сортировки, как у третьей. Не нужны RANK(), CTE и дополнительная фильтрация.

Важно: WITH TIES сравнивает весь набор выражений из ORDER BY. Если добавить туда уникальный id, строки с одинаковой ценой уже не будут считаться равными.

🔥 FETCH FIRST ... WITH TIES позволяет получать первые N строк вместе со всеми совпадениями на границе выборки без оконных функций.

➡️ SQL Ready | #совет

🤔 PostgreSQL — разбор методов оптимизации!

Статья о том, как анализировать производительность PostgreSQL, находить причины медленных запросов и устранять узкие места. Материал помогает разобраться, какие факторы влияют на скорость обработки данных и как подходить к оптимизации системно. Основное внимание уделяется индексам, планам выполнения SQL-запросов, инструменту EXPLAIN ANALYZE и настройке PostgreSQL.

📌 Оставляю ссылочку: senzpo.ru

➡️ SQL Ready | #сайт

Как использовать SKIP LOCKED для параллельной обработки задач в postgresql!

При реализации очереди задач на postgresql несколько воркеров могут одновременно выбирать задания из одной таблицы. SELECT ... FOR UPDATE блокирует выбранные строки, но остальные воркеры могут ожидать освобождения тех же блокировок.

Для таких случаев postgresql поддерживает FOR UPDATE SKIP LOCKED, позволяющий пропускать строки, уже заблокированные другими транзакциями. Рассмотрим таблицу очереди:

CREATE TABLE jobs (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    status TEXT NOT NULL DEFAULT 'pending',
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Для атомарного получения и резервирования одной задачи можно объединить выборку и обновление через UPDATE ... FROM:

WITH next_job AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs j
SET status = 'processing'
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.payload;

Подзапрос выбирает доступную задачу и блокирует её строку. Если другой воркер уже удерживает блокировку, SKIP LOCKED позволяет перейти к следующей подходящей записи без ожидания этой блокировки.

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

CREATE INDEX idx_jobs_pending
ON jobs (created_at, id)
WHERE status = 'pending';

Индекс ускоряет поиск задач со статусом pending. При этом SKIP LOCKED не гарантирует строгий FIFO: заблокированные строки пропускаются.

После резервирования необходимо завершить транзакцию (COMMIT), а обработку выполнять отдельно. При использовании autocommit транзакция завершается автоматически.

Если воркер упадёт, задача может остаться в статусе processing. Поэтому обычно используют аренду задач (locked_until) и механизм повторного получения после её истечения. SKIP LOCKED пропускает заблокированные строки, но не исключает ожидание табличных блокировок.

🔥 FOR UPDATE SKIP LOCKED позволяет нескольким воркерам конкурентно резервировать задачи без ожидания заблокированных строк. В сочетании с UPDATE ... RETURNING и частичным индексом это основа очереди на postgresql, но для надёжной обработки необходим механизм восстановления зависших задач.

➡️ SQL Ready | #практика

😎 Интересная статья попалась на Хабре: «Qwen3.8 27B обошла GPT-5.6 в SQL‑бенчмарке. И всё это — локально на ноутбуке»!

В этой статье:
• Узнаете, как локальная Qwen3.8 27B показала результаты выше GPT-5.6 в SQL-бенчмарке DABstep;
• Разберётесь, как запустить модель на ноутбуке с помощью LM Studio и DuckDB для анализа данных;
• Сравните точность, скорость и стоимость локальных и облачных LLM, а также влияние квантования на производительность.

🔊 Продолжай читать на Habr!

➡️ SQL Ready | #статья

Gemini Pro на 24 месяца за 800₽!

Вместо 39.000₽ — всего 800₽ ($9.9) за 2 года. По акции сейчас это 33₽ в месяц, в 48 раз выгоднее!

➡Argon 4.0 + Claude Opus 5.5 — для кода
➡Nano Banana 2 Pro — для картинок
➡Генерация видео — ролики и анимации
➡AntiGravity + 5 ТБ облака
➡Opus 5.5 и Sonnet 5.5
➡Youtube Premium

Акция ограничена по времени, дальше будет официальная цена 480$!

Приобрести можно в Магазине с 20.000+ покупок и 2000+ отзывов (есть возвраты и замены).