Апдейт Postgres 18: индексируемые UUID

Postgres 18 сейчас в бете и выйдет в продакшен этой осенью.
В uuid добавили поддержку UUIDv7.

UUID — это случайно сгенерированные строки, которые по определению глобально уникальны.

Почему UUID хороши для первичных ключей

1. Уникальность — можно генерировать ключи в разных местах.
2. Развязка — приложение может создать первичный ключ ещё до того, как отправит данные в БД.
3. Безопасность — если в URL используются ID (.../users/5), атакующий легко подберёт другие (.../users/6, .../users/7) и увидит общее количество пользователей.

С UUID (.../users/f47ac10b-58cc-4372-a567-0e02b2c3d479) такое невозможно.

Что изменилось

- Раньше в Postgres нативно поддерживался UUIDv4, но сортировка и индексация в больших таблицах работала медленно.
- UUIDv7 решает проблему сортировки и индексации.

Он по-прежнему случайный, но первые 48 бит (12 символов) — это таймстамп, остальные биты остаются случайными.

Таймстамп

Таймстамп хранится в hex (по сути сжатое десятичное число).

Пример:

0189d6e4a5d6 (hex) = 2707238289622 (decimal),
что соответствует количеству миллисекунд с 1970 года.

Пример DDL для UUIDv7

CREATE TABLE user_actions (
    action_id UUID PRIMARY KEY DEFAULT uuidv7(),
    user_id BIGINT NOT NULL,
    action_description TEXT,
    action_time TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Если раньше у тебя были проблемы с UUID -сейчас можно уже попробовать бету

👉 @SQLPortal

В SQL можно комбинировать PARTITION BY и ORDER BY в оконных функциях.

Это делит строки на группы по значению PARTITION BY,
а затем считает накопительный итог внутри каждой группы.

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

👉 @SQLPortal

Как B- и B+-деревья лежат в основе индексов в базах данных (например, InnoDB в MySQL), почему размер и тип первичного ключа (например, BIGINT против UUID) напрямую влияют на глубину дерева, порядок хранения и количество I/O, и как это отражается на скорости поиска и диапазонных запросов

https://planetscale.com/blog/btrees-and-database-indexes

👉 @SQLPortal

Тутор по ограничениям в Postgres — тык

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

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

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

👉 @SQLPortal

Мы часто обсуждаем с людьми тему «Почему именно Postgres?». Какие фичи делают его сильнее других баз. В этом списке почти всегда фигурирует индексируемый JSONB.

JSONB в Postgres изначально сделан для эффективных запросов и индексации

Вот отличный материал с обзором этих возможностей.

👉 @SQLPortal

SQL против Python для работы с данными: must-have для аналитиков и дата-сайентистов, которые прокачивают скиллы

👉 @SQLPortal

Используй правильный тип данных при определении колонок в базе данных. Например:

для чисел — numeric, для дат/времени — date или timestamp

Неправильный выбор типа данных приводит к неявным преобразованиям, что может:

- отключить использование индексов → замедлить SQL
- вызвать ошибки во время выполнения

Выбирай типы внимательно.

👉 @SQLPortal

Давай разберём типы сканов в Postgres. Многие из нас уже смотрели планы через EXPLAIN. Ниже — детали по разным видам сканов и схема, которая помогает понять, как они работают.

❖ Sequential Scan
Читает всю таблицу построчно и проверяет каждую строку на соответствие условиям запроса.

❖ Index Scan
Базовый индексный скан использует B-tree, чтобы быстро найти точное место данных. Это двухшаговый процесс: сначала Postgres находит запись в индексе, потом извлекает строку из таблицы.

❖ Bitmap Scan

Bitmap Index Scan. Сначала Postgres сканирует один или несколько индексов и строит в памяти «битмап» — карту страниц таблицы, где возможно находятся нужные строки.

Bitmap Heap Scan. Затем этот битмап используется для прохода по основной таблице. Важный момент — чтение нужных страниц идёт последовательно с диска, что обычно быстрее, чем случайные переходы при стандартном Index Scan.

❖ Parallel Sequential Scan
Postgres запускает несколько background worker’ов, чтобы одновременно сканировать одну большую таблицу. Таблица делится на части, каждая часть достаётся отдельному worker’у. В конце результаты собираются вместе через gather.

❖ Parallel Index Scan
Несколько worker’ов параллельно сканируют разные части одного B-tree индекса и возвращают совпавшие строки из heap. Worker’ы читают индекс по очереди.

❖ Index-Only Scan
Самый быстрый вариант: запрос полностью обслуживается данными, которые есть в индексе. Таблица вообще не трогается.

👉 @SQLPortal

Индексируй базу данных с осторожностью.

Каждый новый индекс приводит к:

(a) увеличению затрат на хранение (и, как следствие, дублированию данных)
(b) увеличению накладных расходов на INSERT

Общий принцип такой: индексы ускоряют чтение, но замедляют запись.

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

Хороший обзор этой темы есть у Маркуса в «Use the Index, Luke.»

👉 @SQLPortal

Для проверки видимости строк Postgres собирает все активные транзакции в массив, а строки сверяет с этим списком. Это становится частью снимка состояния транзакции.

Если строку создала одна из этих транзакций, она не видна.

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

Postgres сортирует этот массив и делает по нему бинарный поиск. Есть патчи, которые при необходимости переносят этот список в хеш-таблицу

👉 @SQLPortal

Вся жизнь перед глазами пронеслась

👉 @SQLPortal