Рекомендация по производительности Postgres: индексируйте все внешние ключи.

Primary key в Postgres индексируется автоматически, но если этот primary key используется как внешний ключ в другой таблице, для него индекс там не создаётся сам по себе - его нужно добавить вручную.

👉 @SQLPortal

UUID имеет размер 128 бит (16 байт). Можно хранить его в бинарном виде и тратить 16 байт на запись. Можно хранить как текст в ASCII и тогда это уже 38 байт.

Можно спросить: какая разница, сейчас ведь 2025, память дешевая? Но здесь дело не в объёме хранения, а в IO и производительности.

Если поле UUID попадает в индекс, разница между 16 и 38 байт становится заметной. Каждая страница индекса имеет фиксированный размер. Из неё вычитается размер служебных заголовков страницы и заголовков для каждой записи. Всё остальное это очень ограниченное место для ключей и значений.

Если ключ занимает 38 байт, на страницу поместится меньше записей. С ключом в 16 байт на той же странице поместится примерно в два раза больше UUID. Одна операция чтения страницы (физическая или логическая) даёт больше данных, значит меньше операций ввода-вывода и лучше производительность.

Для точечных выборок разница не такая драматичная, но для range запросов и full scan она уже чувствуется. Особенно с UUIDv7, где диапазонные запросы становятся актуальными.

👉 @SQLPortal

С UUIDv7 в PostgreSQL 18 сортировка через ORDER BY теперь реально выдаёт записи в порядке их создания.

👉 @SQLPortal

В SQL какое существенное влияние на производительность и побочный эффект по типу данных возникает при использовании функции FORMAT() по сравнению с CAST() или CONVERT() для форматирования результатов?

A. FORMAT() потребляет меньше памяти, чем CAST(), но не поддерживает культурное форматирование.

B. FORMAT() в целом работает быстрее, но может обрабатывать только даты и числа.

C. FORMAT() сильно нагружает CPU и всегда возвращает тип данных NVARCHAR, что может потребовать дальнейшего преобразования.

D. FORMAT() возвращает NULL, если строка формата неверная, тогда как CAST() и CONVERT() генерируют ошибку.

👉 @SQLPortal

Базы данных реализуют уровень изоляции 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