Мы уже знаем причину худшего сбоя Cloudflare с 2019 года — криво спроектированный SQL-запрос.

После изменения прав он начал дублировать данные.

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

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

👉 @SQLPortal

Postgres использует TOAST для хранения больших переменных значений вроде JSONB и TEXT.

Использование TOAST влияет на производительность, так что нужно понимать компромиссы.

Каждая строка хранится в файле, который разбит на страницы по 8 KB. Обычно одна страница может держать десятки или сотни строк, но это зависит от количества и типов колонок.

Широкие таблицы могут иметь строки размером в несколько килобайт. Если строка превышает порог (например, около 2 KB), Postgres решает использовать The Oversized-Attribute Storage Technique (TOAST), а не запихивать все данные в основной файл таблицы.

TOAST применяет сжатие и перенос больших значений в отдельную таблицу — TOAST-таблицу. У нее всего три колонки: chunk_id, chunk_seq и chunk_data. Большие значения режутся на чанки, эти чанки кладутся в отдельную таблицу, а в основной таблице остается только ссылка на них.

Это позволяет держать строки в основном файле компактными. Минус в том, что при обращении к таким данным нужно делать дополнительные I/O на другую таблицу (и возможно тратить CPU на декомпрессию). Гибкость в работе с большими полями удобна, но может ударить по перформансу по сравнению с хранением всего в основной таблице или нормализацией данных в компактные фиксированные колонки.

👉 @SQLPortal

Рекомендация по производительности 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