Как использовать 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 | #статья

Почему SELECT перед INSERT не защищает от дубликатов!

Рассмотрим пример на PostgreSQL: две транзакции одновременно пытаются создать пользователя с одинаковым email. Сначала создадим таблицу с ограничением UNIQUE:

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

Перед добавлением пользователя приложение проверяет, существует ли такой email:

SELECT id
FROM users
WHERE email = 'admin@example.com';

Если запись не найдена, выполняется вставка:

INSERT INTO users (email)
VALUES ('admin@example.com');

При параллельных запросах обе транзакции могут не найти запись. Первая выполнит INSERT и COMMIT, а вторая получит ошибку нарушения UNIQUE. Это типичная race condition (check-then-act). Без ограничения UNIQUE возникли бы дубликаты.

В PostgreSQL такую ситуацию можно обработать через ON CONFLICT без предварительного SELECT:

INSERT INTO users (email)
VALUES ('admin@example.com')
ON CONFLICT (email) DO NOTHING
RETURNING id;

Если запись создана, RETURNING вернёт её id. При конфликте вставка пропускается, а результат будет пустым. Если существующую запись необходимо обновить, используется DO UPDATE:

INSERT INTO users (email)
VALUES ('admin@example.com')
ON CONFLICT (email)
DO UPDATE SET email = EXCLUDED.email
RETURNING id;

Но обновлять email тем же значением только ради получения id не стоит: операция UPDATE может создавать новую версию строки, запускать триггеры и увеличивать нагрузку.

Если нужен id существующей записи без обновления, после DO NOTHING можно выполнить отдельный SELECT, учитывая уровень изоляции и возможность повторной попытки.

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

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

Ищите данные внутри JSON без разворачивания массивов!

Когда нужно отфильтровать элементы массива внутри JSON, обычно используют jsonb_array_elements(), дополнительные соединения и условия WHERE.

Но PostgreSQL поддерживает JSONPath — язык запросов к JSON, который позволяет описать фильтрацию прямо внутри пути.

Например, получить названия товаров дороже 1000:

SELECT jsonb_path_query_array(
payload,
'$.items[*] ? (@.price > 1000).name'
)
FROM orders;

Здесь [*] перебирает элементы массива, ? (...) фильтрует их, а .name извлекает нужное поле. Результат возвращается одним JSON-массивом.

Можно использовать JSONPath и непосредственно в WHERE:

SELECT id
FROM orders
WHERE payload @? '$.items[*] ? (@.price > 1000)';

Оператор @? проверяет, существует ли элемент, соответствующий указанному пути. Для подходящих запросов по jsonb PostgreSQL также может использовать индекс GIN.

🔥 JSONPath позволяет фильтровать вложенные JSON-массивы прямо в выражении, без разворачивания элементов и дополнительных соединений.

➡️ SQL Ready | #совет

📂 Шпаргалка по AWS RDS — управление SQL-базами данных!

Amazon RDS — управляемый сервис AWS, который упрощает развёртывание, администрирование и масштабирование реляционных баз данных.

На картинке — 6 основных этапов работы с RDS: от создания и настройки БД до резервного копирования, мониторинга и обеспечения отказоустойчивости.

Шпаргалка поможет быстро вспомнить основные возможности сервиса и разобраться, какие задачи AWS автоматизирует. Сохрани, чтобы не потерять!

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

✍️ PostgreSQL — подборка статей о работе с базами данных!

Статьи помогают разобраться в технических аспектах PostgreSQL и расширить знания о работе с реляционными базами данных. Ресурс будет полезен для изучения возможностей PostgreSQL, практических подходов к работе с данными и решения задач, возникающих при разработке приложений. Подойдёт backend-разработчикам, SQL-разработчикам и администраторам баз данных.

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

➡️ SQL Ready | #сайт

Как избежать блокировки всей таблицы при добавлении UNIQUE в PostgreSQL!

Добавление ограничения UNIQUE на большую таблицу может стать проблемой на проде. ALTER TABLE ... ADD CONSTRAINT UNIQUE создаёт уникальный индекс и требует блокировки, которая на время операции препятствует конкурентным изменениям таблицы.

Рассмотрим таблицу с миллионами записей:

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email TEXT NOT NULL
);

Необходимо гарантировать уникальность email, сохранив возможность выполнять INSERT, UPDATE и DELETE во время построения индекса.

В PostgreSQL для этого предусмотрен CREATE UNIQUE INDEX CONCURRENTLY:

CREATE UNIQUE INDEX CONCURRENTLY
    users_email_uidx
ON users (email);

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

После успешного построения индекс можно привязать к ограничению:

ALTER TABLE users
ADD CONSTRAINT users_email_key
UNIQUE USING INDEX users_email_uidx;

postgresql использует существующий индекс, поэтому повторное построение не требуется. Однако ALTER TABLE всё равно запрашивает блокировку ACCESS EXCLUSIVE, пусть обычно и на короткое время.

Во-первых, CREATE INDEX CONCURRENTLY нельзя выполнять внутри явного блока транзакции:

BEGIN;

CREATE UNIQUE INDEX CONCURRENTLY
    users_email_uidx
ON users (email);

COMMIT;

Такой запрос завершится ошибкой.

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

DROP INDEX CONCURRENTLY users_email_uidx;

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

Наконец, USING INDEX поддерживает не любой индекс: для ограничения UNIQUE нужен подходящий уникальный B-tree индекс без выражений и частичного предиката.

🔥 Для добавления UNIQUE на большую таблицу можно сначала построить индекс через CREATE UNIQUE INDEX CONCURRENTLY, а затем подключить его через ALTER TABLE ... USING INDEX. Это позволяет избежать длительной блокировки записи при построении индекса, но не исключает кратковременную блокировку при добавлении самого ограничения.

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

😍 PostgreSQL — серия обучающих статей по работе с базами данных!

Русскоязычная подборка материалов, посвящённых изучению PostgreSQL и работе с реляционными базами данных. Статьи объединены в серию, что позволяет последовательно знакомиться с СУБД и изучать отдельные темы. Ресурс поможет разобраться в основах PostgreSQL, принципах работы с данными и использовании SQL. Такой формат удобен как для самостоятельного обучения, так и для повторения материала при решении практических задач.

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

➡️ SQL Ready | #сайт