Базы данных реализуют уровень изоляции SERIALIZABLE через пессимистические блокировки.

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

PostgreSQL как всегда делает по-своему и использует Serializable Snapshot Isolation (SSI).

Он позволяет транзакциям работать параллельно без блокировок и проверяет конфликты только перед коммитом.

В этом режиме используются predicate locks (по сути это не настоящие блокировки).

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

Это нужно, чтобы избегать ошибок вроде write skew и read-only transaction anomaly.

Перед коммитом транзакция проверяется на "опасные" зависимости.

Если ты выполняешь:

SELECT * FROM engineers WHERE on_call = true;

Postgres ставит на эти строки SIREAD (snapshot isolation read) locks.

Эти блокировки не мешают другим транзакциям изменять строки.

Можно думать об этом как о "метках" на данных, чтобы Postgres смог понять зависимости.

Если запрос содержит диапазон, например:

WHERE age > 30

то predicate lock покрывает именно условие.

Если кто-то вставит нового человека с age = 35, predicate lock сработает, потому что новая запись удовлетворяет этому условию.

Разбор ситуации по шагам

1) Алиса запускает SELECT count(*) ...

2) Postgres вешает predicate lock на строки где on_call = true

3) Боб не блокируется. Он вообще не в курсе, что Алиса что-то делает.

4) Боб запускает SELECT count(*) ...

5) Postgres вешает predicate lock на те же строки.

6) Алиса обновляет свою строку (Alice: Off)

7) Postgres замечает predicate lock от Боба и фиксирует rw-conflict от Bob → Alice.

8) Боб обновляет свою строку (Bob: Off)

9) Postgres замечает predicate lock от Алисы и фиксирует rw-conflict от Alice → Bob.

10) Получается такая цепочка зависимостей:

Alice (Read) → Bob (Write) → Alice (Write) → Bob (Read)

11) Postgres понимает, что если разрешить коммит обеим, получится история, которую невозможно выстроить в корректную serial-последовательность.

12) Система выбирает одну транзакцию (обычно ту, которая первой пытается закоммититься или сделала меньше операций) и отменяет её ошибкой:

ERROR: 40001 (serialization_failure)

👉 @SQLPortal

Новость. Выпущено расширение pg_ai_query для PostgreSQL, которое позволяет генерировать SQL-запросы на естественном языке и анализировать производительность запросов с помощью ИИ.

https://github.com/benodiwal/pg_ai_query

👉 @SQLPortal

Postgres поддерживает point-in-time recovery — возможность откатить базу к состоянию в прошлом.

Это работает за счет комбинации восстановления из бэкапа и проигрывания WAL.

У Postgres должны быть регулярные бэкапы. Они служат базовой точкой для PITR. Когда начинается PITR, поднимается новый экземпляр Postgres, и на него разворачивается бэкап, который был сделан раньше и ближе всего к нужному моменту восстановления.

Это приближает нас к нужному времени, но чаще всего остается разрыв в несколько часов. Далее Postgres начинает проигрывать write-ahead log (WAL). Он применяет все вставки, обновления и удаления, которые произошли между временем бэкапа и точкой восстановления.

Основной инстанс Postgres можно настроить на непрерывный архив WAL, например, в s3 — именно оттуда он получает логи для PITR.

Пример: сегодня 24 ноября. Нужно восстановиться на 23 ноября в 5 утра. Есть ежедневные бэкапы в 1 утра и архив WAL с ретеншеном 48 часов. Для PITR выполняем:

- создаем новый Postgres-сервер
- восстанавливаем бэкап от 23 ноября 1:00
- проигрываем WAL за промежуток 1:00 -> 5:00

После этого можно просматривать базу в состоянии на этот момент времени.

👉 @SQLPortal

Lateral join отлично подходят, когда нужно выбрать top-N из нескольких таблиц.

LATERAL JOIN позволяет подзапросу в секции FROM обращаться к колонкам таблицы, которая стоит перед ним.

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

Lateral join работает как коррелированный подзапрос, но может возвращать несколько строк. Он выполняет подзапрос для каждой строки внешней (левой) таблицы.

Смотри пример в видео.

👉 @SQLPortal

Получите SQL базу в облаке.

Без регистрации, оплаты, ожидания и настройки.

Запустите в терминале → npx get-db

👉 @SQLPortal

Aurora DSQL теперь показывает стоимость SQL-запросов

В Aurora DSQL появилась функция EXPLAIN ANALYZE VERBOSE, которая позволяет узнать точную стоимость выполнения SQL-запроса. Это даёт возможность напрямую оценивать влияние оптимизации и индексов на финансовые затраты.

В регионе us-east-1 запрос без индекса стоит $0.00000208, что примерно соответствует $2.10 за миллион выполнений. После добавления индекса стоимость снижается почти в 12 раз — до $0.00000018, или около 18 центов за миллион запросов.

Повторное выполнение ещё быстрее: при кэшированном плане стоимость падает примерно в 10 раз по сравнению с первым запуском.

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

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

Подробнее: https://docs.aws.amazon.com/aurora-dsql/latest/userguide/understanding-dpus-explain-analyze.html

👉 @SQLPortal

Ищешь самое популярное значение в Postgres? Не обязательно городить GROUP BY + ORDER BY + LIMIT 1. Можно просто использовать mode.

Пример:

SELECT
  mode() WITHIN GROUP (ORDER BY color) AS most_popular_color
FROM
  products_sold;

Коротко и удобно.

👉 @SQLPortal

CTE против подзапросов: когда использовать CTE

✅ Когда важна читаемость и структура запроса. Запросы пишешь не только для себя, но и для будущего себя и других, поэтому стоит делать их понятными и логично оформленными.

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

👉 @SQLPortal

Supabase выпустил курс для изучения базы данных PostgreSQL с нуля и шаг за шагом.

✓ Более 5 часов в 39 видео.
✓ Запросы, соединения, JSON, индексы и создание таблиц.
✓ Все объясняется на практике и постепенно.
✓ Бесплатно → https://databaseschool.com/series/intro-to-postgres

👉 @SQLPortal

Разделить колонку, в которой встречаются нули, в Postgres можно через NULLIF, чтобы избежать деления на 0.

SELECT
  total_sales / NULLIF(total_customers, 0) AS avg_sale_per_customer
FROM
  sales_data;

👉 @SQLPortal

Уменьшаем время выборки из БД с 2–3 секунд до ~100 мс:

SELECT * FROM transactions
WHERE user_id = 40
ORDER BY created_at DESC
LIMIT 20 OFFSET 10000;

На первый взгляд запрос нормальный?
ДА, вроде ок.

Но:

- это OFFSET-пагинация
- и это прям ПЛОХО
- БД вытягивает 10020 строк и выкидывает 10000, чтобы показать 20, лол
- чем больше OFFSET, тем больше нагрузка на БД
- со временем запрос начинает занимать больше 2 секунд, пока растет объем данных

Решение:

KEYSET (seek) пагинация: добавляем условие вида created_at < last_seen_timestamp

SELECT * FROM transactions
WHERE user_id = 40
  AND created_at < '2024-05-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

- так БД может сразу прыгнуть по индексу
- по сути это "дай следующие 20 записей после этого timestamp", где timestamp используется как ключ
- время реально падает с секунд до примерно 100–200 мс

Что если timestamp не уникален, есть дубли:

Добавляем tie-breaker: (created_at, id) и в WHERE, и в ORDER BY:

WHERE (created_at, id) < ('2024-05-01 10:00:00', 98765)
ORDER BY created_at DESC, id DESC

Так пагинация остается быстрой и детерминированной, даже при одинаковых created_at.

👉 @SQLPortal

Частичное извлечение данных в Postgres с помощью SQL:

Все эти запросы возвращают один и тот же результат: 78901

SELECT substring('USER_ACCT-78901-NYC-ACTIVE' FROM 11 FOR 5);

SELECT split_part('USER_ACCT-78901-NYC-ACTIVE', '-', 2);

SELECT (regexp_matches('USER_ACCT-78901-NYC-ACTIVE', 'ACCT-(\d+)-'))[1];

👉 @SQLPortal

Когда лучше использовать подзапрос вместо CTE

1. Обновление таблицы: если ты обновляешь данные в таблице, можно использовать подзапрос, например коррелированный подзапрос.

2. Вычисление скалярного значения: когда нужно добавить одно агрегированное значение из другой таблицы как новый столбец для каждой строки в результирующем наборе.

3. Использование EXISTS или NOT EXISTS: когда нужно просто проверить, существуют ли связанные строки, а не извлекать сами данные.

👉 @SQLPortal

Postgres CONCAT_WS удобно собирает строку даже если есть NULL:

SELECT CONCAT_WS(' ', first_name, middle_initial, last_name);

Если middle_initial = 'T', то результат будет:

John T Smith

Если middle_initial = NULL, то получится:

John Smith

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

👉 @SQLPortal

Нашёл полезную статью, которая рассказывает, как автоматизировать резервное копирование удалённой PostgreSQL-базы по расписанию, не храня дампы на том же сервере.

Автор показывает пример на Python и Docker: pg_dump, cron, настройка переменных окружения и отправка уведомлений в Telegram. Решение подходит тем, кому нужно простое и дешевое резервирование без отдельного сервера под бэкапы.

👉 @SQLPortal

Неделя Postgres contrib. Поговорим о фичах, которые уже поставляются вместе с Postgres, но по умолчанию выключены.

Contrib-расширения входят в библиотеку contrib внутри PostgreSQL. То есть код расширения уже лежит в системе, если ты ставил Postgres из официальных пакетов. Все, что нужно — выполнить CREATE EXTENSION, чтобы включить его.

Расширения, которые не входят в contrib, надо собирать или компилировать вместе с Postgres перед тем, как их можно будет подключить через CREATE EXTENSION.

👉 @SQLPortal

😏😏😏

👉 @SQLPortal

SQL-совет:

Простая демонстрация вложенных выражений CASE.

👉 @SQLPortal

Postgres pgstattuple — расширение из contrib, которое показывает физическое состояние таблицы или индекса.

Пример:

SELECT * FROM pgstattuple('accounts');

Что там видно:
• tuples — количество строк
• dead tuples — строки, которые удалены или изменены
• free space и free percent — свободное место и его процент

Если dead tuples или free percent слишком высокие, стоит проверить настройки vacuum.

👉 @SQLPortal

В DBeaver можно спокойно работать с большими SQL-скриптами через панель Outline.

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

Функция доступна в десктопных версиях, начиная с 24.0.

👉 @SQLPortal

Postgres расширение из набора contrib под названием amcheck позволяет проверить таблицы и индексы на повреждение.

Проверка индекса:

SELECT * FROM bt_index_check('accounts_pkey'::regclass::oid);

Проверка таблицы:

SELECT * FROM verify_heapam('accounts');

Если вернулось 0 строк — все ок. Если что-то вернулось — значит есть повреждение.

Если ты собираешься решать Advent of Code в SQL или Postgres в этом году, у Грега Сабино Маллейна есть серия постов, где он пару лет назад проходил AoC на Postgres и разобрал кучу полезных приемов и примеров кода.

https://crunchydata.com/blog/topic/advent-of-code

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

👉 @SQLPortal