• Совет дня №25

  Mar 10, 2026 postgres, sql

  Иногда мы хотим удалить что-то из базы, но нельзя. Причины разные:

  • высокая связность сущностей. Если это пользователь, на него завязаны публикации, комментарии, фотографии, упоминания, заказы, обращение в поддержку и так далее. Каскадный DELETE по всей базе – так себе решение.

  • В финансах и телекомах есть требования регулятора. Данные не должны удаляться бесследно.

  • Пользователь может передумать. Скажем, удалил письмо по ошибке, нажал Undo и оно вернулось в ящик.

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

  Словом, есть много случаев, когда DELETE вам не поможет.

  Часто поступают так: раз удалять нельзя, добавим флажок is_deleted и поставим ему истину. То есть вместо

  delete from users where id = 42
  

  выполним

  update users set is_deleted = true
  where id = 42
  

  Когда-то давно я тоже выступал за такой подход, у меня даже есть заметка в блоге. Что ж, я заблуждался. Мне довелось поработать в проекте, где в каждой таблице был флаг is_deleted, и это был ад.

  Если коротко – база, из которой ничего не удаляют, становится квартирой, из которой ничего не выбрасывают. Поживите неделю, складывая мусор в шкафы, и вы поймете.

  Во-первых, обращаясь к любой таблице, нужно не забыть условие where not is_deleted. Редко, но разработчики его теряли, и удаленные строки шли на фронт. Вы, конечно, скажете: в моей ORM можно задать фильтры по умолчанию. Знаю, я так делал в Django: при обращении к модели условие добавится само. Но не все работают с ORM.

  Можно создать вьюху users_active, которая выбирает всех не удаленных пользователей. Но это новая сущность, плюс понадобится много таких вьюх.

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

  В-третьих, если встречаемся уникальность, удаленная запись не позволит вставить новую. Скажем, если пользователь с почтой test@foobar.com отмечен удаленным и почта уникальна, вы не вставите другого пользователя с такой почтой. Кроме почты, уникальными могут быть другие поля, например номер транзакции или чека.

  Технически это можно исправить: создать уникальный индекс с условием:

  create unique index idx_user_email on users
  using btree (email) where not is_deleted
  

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

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

  И наконец: таблица, где ничего не удаляется, становится помойкой. Нельзя понять, почему удалили данные и что с ними делать. Пользователь удалился сам? Его забанили? За что? Он на модерации? Кто кого ждет?

  Данные о платежах не были обработаны? Почему? Когда планируется снова их обработать? Что делать, если опять не получится? Десятки вопросов, и никто ничего не знает.

  Решение будет изложено в следующем совете.

• Совет дня №24

  Mar 10, 2026 postgres, sql

  Когда мы попадаем в индекс, желательно понимать, что при этом происходит. В Postgres btree-индекс – это дерево распределительных узлов, и на последнем уровне хранятся пары: значение -> номер страницы (блока). Поэтому даже если вы попали в индекс, предстоит некоторая работа: проверить, есть ли такой блок в буферном кэше, если нет – подгрузить его с диска. Затем пройтись по всем строкам блока (линейным перебором) и найти ту, что подходит условию.

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

  Если выполнить такой запрос:

  select id from users where id = 123456
  

  , то значение id будет взято из индекса – нет смысла обращаться в таблицу. Конечно, это наивный пример: мы и так знаем айдишку. Но он полезен для диапазонов: скажем, выбрать айдишки между 100 и 200 или <= 10000. В этом случае они будут взяты из индекса.

  Более реалистичный пример: предположим, есть таблица пользователей. Из прошлого совета про ключи мы помним, что у людей должен быть суррогатный ключ, а почта используется для уникальности. Поэтому таблица объявлена так:

  create table users (
      id integer primary key,
      email text not null
  );
  

  Уникальность гарантирует специальный индекс:

  create unique index idx_users_email
  on users (email);
  

  На практике индекс строят по выражению trim(lower(email)), но сейчас это не важно.

  Вставим миллион пользователей:

  insert into users
  select x, format('email_%s@test.com', x)
  from generate_series(1, 1000000) as seq(x);
  

  Задача в следующем: нужно часто получать айди пользователя по его почте (обратный резолв). Нам передают почту, по ней мы находим айди, и все запросы пляшут от этого айдишника. Напишем следующий запрос:

  select id from users
  where email = 'email_500000@test.com';
  -- 500000
  

  Он довольно шустрый:

  ┌──────────────────────────────────────────
  │ Index Scan using idx_users_email on users
  │   Index Cond: (email = 'email_500000@test
  │ Planning Time: 0.155 ms
  │ Execution Time: 0.896 ms
  └──────────────────────────────────────────
  

  Теперь удалим этот индекс и построим немного другой:

  drop index idx_users_email;
  
  create unique index idx_users_email
  on users (email) include (id);
  

  Такой индекс называется покрывающим из-за выражения include (id). Смысл в том, что значение id хранится в дереве btree как метаданные. Это увеличивает размер индекса, зато позволяет взять указанное поле без обращения к диску. Вот что показывает новый план:

  ┌───────────────────────────────────────────────
  │ Index Only Scan using idx_users_email on users
  │   Index Cond: (email = 'email_500000@test.com'
  │   Heap Fetches: 0
  │ Planning Time: 0.830 ms
  │ Execution Time: 0.141 ms
  └───────────────────────────────────────────────
  

  Обратите внимание на “Index Only Scan” – только индекс без обращения к диску. Время выполнения ниже, стоимость тоже.

  Если прогреть индекс idx_users_email (см. прошлый совет), он будет находиться в памяти. В этом случае резолв айдишки по почте не отличается от Redis: все происходит в памяти без обращения к диску.

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

• Совет дня №23

  Mar 9, 2026 postgres, sql

  Почти любой таблице нужен первичный ключ. Это поле или их комбинация, которая однозначно определяет запись. Первичный ключ уникален и не допускает NULL (обычный UNIQUE допускает его). Для первичного ключа автоматом строится btree индекс.

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

  Важно, чтобы первичный ключ не раскрывал приватные данные. Скажем, серийный номер ноутбука состоит из частей: страна производства, код завода, номер партии, флаги модификаций и так далее. Не всегда безопасно выкладывать эти данные в общий доступ. В этом случае у записи может быть суррогатный ключ (число, UUID), а серийный номер хранится в отдельном поле — уникальном, но не первичном.

  Особая ситуация с людьми: попытки найти у них естественный ключ обречены на провал. Во-первых, комбинации ФИО, даты и места рождения не уникальны. Редко, но бывают полные тезки, родившиеся в один год. Кроме того, у людей все меняется. Женщины выходят замуж: была Иванова, стала Петрова. Мужчины тоже меняют имена, хоть и реже (знаю два случая). На прошлой работе коллега совершил гендерный переход: сменил имя и пол (не удивлюсь, если при этом пофиксил дату рождения).

  Если вы дизайните таблицу пользователей, электронная почта не может быть первичным ключом. Почта раскрывает данные: в некоторых системах она является самым сокровенным. Ни одна соцсеть не покажет почту пользователя, чтобы вы не вступили в прямой диалог. Почту предоставляет сторонний сервис, и к ней легко потерять доступ. Если первичный ключ пользователя — id, то почту легко сменить, пройдя процедуру. Если ключ — почта, придется обновить внешние ключи в десятках таблиц (заказы, отзывы, посты, комментарии, лайки, сообщения, фотографии, обращения в поддержку и так далее).

  Номера телефонов и паспортов — все это небезопасно и меняется со временем.

  На тему того, чем должен быть первичный ключ — счетчиком или UUID — я уже писал. Вкратце, счетчик делает вас заложником БД: только она знает, каким будет очередное значение. Это плохо подходит для распределенных систем. UUID отвязывает вас от БД, а кроме того, в нем можно хранить метаданные. Например, UUIDv7 содержит время, поэтому подлежит сортировке и заменяет поле created_at. Идентификатор Snowflake, придуманный в Твиттере, хранит коды региона, датацентра, сервера и другие метаданные.

  В больших дружных компаниях (банках, заводах, тюрьмах) сотрудникам назначают коды — нечто среднее между числами и идентификаторами. Бейджик — это и есть код сотрудника. Если сотрудник изменит имя или пол, админам не придется заводить новую запись: они перебьют поля в админке.

  Не всегда роль и человек совпадают один к одному. Скажем, один человек может быть двумя студентами одновременно (разные факультеты, очное-заочное и так далее). В этом случае есть таблица person, а таблица students ссылается на человека по внешнему ключу. Если гражданка Иванова сменила фамилию на Петрову, оба факультета это увидят. По аналогии в больших компаниях один человек может занимать несколько должностей, быть пациентом многих отделений клиники и так далее. Это надо учитывать.

• Совет дня №22

  Mar 9, 2026 postgres, sql

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

  К счастью, есть более простой способ прогрева. Расширение pg_prewarm делает буквально это: принимает имя таблицы или индекса и насильно загоняет в буферный кэш. Для особых случаев можно указать диапазон блоков, но маловероятно, что это вам пригодится.

  В целом прогрев выглядит так:

  pg_prewarm('some_schema.users')
  /* for table*/
  
  pg_prewarm('some_schema.idx_users_created_at)
  /* for index */
  

  Функция возвращает число прочитанных блоков. Поскольку блок в Postgres равен 8 килобайтам, умножьте результат на 8192, чтобы узнать размер таблицы или индекса в байтах (разумеется, есть и другие способы посчитать размер, но это чтобы убить двух зайцев).

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

  Техника с прогревом спорная, и нужно использвать ее по месту. На мой взгляд, полезно прогревать:

  • небольшие справочные таблицы, которые часто читают. Если таблица небольшая, индексы на нее не действуют, и будет full scan. Если вы часто читаете таблицу, поставьте ее на прогрев. Сюда относятся разные классификаторы, каталоги услуг, категории и так далее.

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

  Как прогревать таблицу или индекс по расписанию? Можно задать крон-джоб, который выполняет скрипт pg_prewarm(...) каждый час. Другой вариант – подключить расширение pg_cron. Его нет в поставке Postgres, но оно установлено у всех облачных провайдеров. На линуксе оно ставится из пакета postgresql-16-cron (замените 16 на вашу версию PG).

  Примеры:

  SELECT cron.schedule(
    'prewarm categories table',
    '0 */1 * * *',
    $sql$pg_prewarm('prod.categories')$sql$
  );
  
  SELECT cron.schedule(
    'prewarm users.created_at index',
    '5 */1 * * *',
    $sql$pg_prewarm('prod.idx_users_created_at')$sql$
  );
  

  Также рассмотрите опцию shared_buffers – размер буферного кэша. По умолчанию он равен 32 мегабайта, что довольно мало. В боевом режиме его оптимальная величина – 25% от всей памяти сервера. Например, если у вас 8 гигабайт памяти, то размер кэша можно выставить 2 гигабайта. При этом не рекомендуется превышать 40% всей памяти.

  Ссылка на документацию.

• Совет дня №21

  Mar 9, 2026 postgres, sql

  В прошлом совете упоминалось: команда EXPLAIN возвращает оценочный план запроса, при этом запрос не выполняется. Можно написать EXPLAIN drop table students, и данные не пропадут.

  Вариант EXPLAIN ANALYZE выполняет запрос и возвращает фактический план. Цифры из него будут реальными, а не оценочными. Вместе с тем Postgres покажет отдельное время планирования (planning) и выполнения (execute).

  Когда запросы тестируют, часто предпочитают EXPLAIN ANALYZE. При этом есть одна особенность, о которой пишут редко.

  Если выполнить один и тот же запрос дважды, то второй запуск будет гораздо быстрее. Разница существенна — один-два порядка! Например, первый запуск занял 900 миллисекунд, второй — 50. Во-первых, почему так? Во-вторых, все-таки 900 или 50? Нужно оптимизировать или так прокатит?

  Дело в том, как устроена работа с буферами. В Postgres любая работа с таблицами и индексами сводится к буферному кэшу. Это область памяти, доступная всем процессам на чтение и запись. Своего рода рабочий стол. Данные не читаются напрямую из таблицы: сперва они выгружаются в кэш и оттуда читаются строки. Запись работает в обратном порядке: страницы кэша обновляются в памяти, а затем сбрасываются на диск.

  Скорее всего, первый запуск не нашел нужных буферов в кэше и спровоцировал их чтение. Такой запуск называют холодным. Если выполнить запрос еще раз, данные окажутся в кэше, и результат получится на порядки быстрее. Это горячий запуск. Примерно как в Redis — прочитали из памяти без обращения к диску.

  На какой запуск ориентироваться? Иногда разработчик чувствует, что запрос не оптимален: первый вызов занял секунду. Долго. Он запускает еще раз и такой: а теперь 200 миллисекунд! Сойдет.

  Лично я ориентируюсь на первый (холодный) вариант. Рассчитывать, что каждый раз данные окажутся к кэше — слишком оптимистично. База выполняет много запросов параллельно и постоянно жонглирует страницами и кэшами. Полагайте, что чаще всего ваш запрос будет холодным.

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

  И еще одна неоднозначность. Предположим, вы тестируете запрос и часто его вызываете. В результате все буферы прогреты, и не ясно, как поведет себя запрос в холодном случае. Увы, нормального решения проблемы нет. Postgres не предлагает функции очистки кэша. Если это локальный Postgres, перезагрузите его, и первый запуск снова станет холодным. Разумеется, перезагружать удаленный сервер — моветон, поэтому сделайте следующее: заставьте Postgres заполнить буферный кэш чем-то другим. Запустите полное сканирование большой таблицы, погоняйте сложные запросы, и вскоре это сработает: запрос, что вы тестируете, станет холодным (метрика execute возрастет).

  Согласен: все это напоминает шаманство, но иногда приходится так делать.

• Совет дня №20

  Jan 31, 2026 postgres, sql

  В прошлый раз мы говорили вот о чем. Когда мы изучаем план запроса, то смотрим, было ли попадание в индекс. Однако бывает подвох: индекс был, да не тот.

  Скажем, есть запрос: показать топ-100 активных заказов по убыванию даты. Пожалуйста:

  select * from orders
  where status = 'active'
  order by created_at desc
  limit 100
  

  Для поля status создан индекс idx_orders_status, для created_at — idx_orders_created_at (по убыванию).

  Смотрим EXPLAIN, а там примерно такое:

  limit: 100
    filter scan condition: status = 'active'
      index scan idx_orders_created_at: desc
  

  Разработчик смотрит: index scan был? Был. Вот и ладушки.

  На самом деле произошло вот что. Вместо того, чтобы взять только активные заказы и отсортировать их, Postgres сделал обратное. Он пошел по всей таблице (full scan), просто не в случайном порядке (как записи лежат на диске), а согласно индексу. При этом записи были отобраны вручную: для каждой из них выполнялась проверка status = ‘active’. Фактически это был full scan, только в другом порядке.

  В идеале нам нужен другой план:

  limit: 100
    in-memory sort, key: created_at (desc)
      index scan idx_orders_status condition: status = 'active'
  

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

  Почему был первый план, а не второй? Во-первых, индекса на status может не быть. Если он есть, у значения active низкая селективность: доля активных заказов высокая. Может быть, настало время обновить статистику командой analyze, и значение active уйдет из массива частых значений, которые нельзя брать в индекс. Наконец, можно признать, что тут ничего не поделаешь и мы согласы жить с первым планом, а не вторым. Возможны и другие варианты, их смотрят по ситуации.

  Общий принцип такой. Часто фильтры и сортировка тянут одеяло на себя. Postgres должен решить, что выгоднее: отсортировать по индексу и пройтись вручную или наоборот: сначала отсечь по индексу и отсортировать в памяти. Второй вариант предпочтительней: когда выборка мала, любая операция над ней дешева: сортировка, группировка и так далее. Стремитесь к тому, чтобы WHERE отсекал как можно больше записей, и только потом вступали в действие другие операции.

• Совет дня №19

  Jan 31, 2026 postgres, sql

  Чтобы узнать план запроса, предварите его командой EXPLAIN:

  explain select * from applications;
  
  Seq Scan on applications
    (cost=0.00..359524.33 rows=965633 width=1745)
  

  Эта команда НЕ выполнит запрос, а только вернет его план и приблизительные оценки.

  План представляет дерево узлов; вложенность определяется пробелами слева. План читают от вложенных узлов к корню. Графические программы вроде PGAdmin показывают его в виде графа. У каждого узла как минимум три характеристики: стоимость (cost), число записей (rows) и средняя длина строки (width).

  Стоимость (cost) – это условные попугаи, в которых измеряется каждая операция. С помощью глобальных настроек можно задать свою стоимость некоторым операциям, но обычно это ни к чему. Кроме того, стоимость можно задать функциям при их объявлении.

  Число записей (rows) – ожидаемое число записей, которые произведет узел. Для них рассчитана средняя длина в байтах. Эти числа берутся из статистики и могут быть неточны.

  Стоимость выражается двумя цифрами: X..Y. Первая цифра – столько усилий уйдет на то, чтобы произвести первую запись. Вторая – последнюю, то есть все остальное.

  Зачем две цифры? Дело в том, что даже когда запрос производит много записей, важно знать, сколько усилий требует предварительная работа. Например, у обхода большой таблицы первый кост маленький, а второй – большой. Это нормально, потому что записи передаются следующему узлу без задержки. Если же добавить группировку, то следующий узел не получит данные, пока не выполнится группировка. Из-за этого первый кост будет большим. В идеале нужно держать его маленьким, чтобы клиент сразу начал получать строки.

  У команды explain много параметров, чтобы собрать дополнительные метрики узлов. Например, время в секундах, число прочитанных страниц, попадание в буферный кэш. Самый важный параметр называется ANALYZE. С ним запрос будет выполнен, и метрики будут реальными, а не оценочными.

  Модуль auto_explain автоматически логирует план запросов, которые выполнялись дольше порога, например одной секунды. Иногда его включают на проде.

  Читать план тяжело, этот навык приходит с годами. Вы должны точно понимать, какую информацию ищете и почему ее там нет. В простом случае вас интересует, использовался ли индекс. Вы запускаете explain analyze и смотрите, был ли узел с типом “index scan”.

  Узел “index only scan” означает, что данные получены из индекса без обращения к таблице. Это самый жир, лучше которого ничего не бывает. Узел “bitmap index scan” означает построение битовой карты, где каждый бит — номер блока. Такой обход используется для совмещения нескольких индексов, и он тоже хорош.

  Узел “full seq scan” означает полный обход таблицы. Это нормально, если таблица мала или нужна ее большая часть. Если вы рассчитывали на индекс, это повод пересмотреть его.

• Совет дня №18

  Jan 31, 2026 postgres, sql

  Когда Postgres выполняет запрос, он строит план. Это сложная задача: сперва нужно распрасить запрос, построить дерево, проверить синтаксис. Затем строится логический план: здесь некоторые шаги разбиваются на подшаги, а другие, наоборот, группируются в один. На последнем этапе выводится физический план: в какую таблицу пойти, брать индекс или нет, в каком порядке выполнить джоины.

  Парсинг запроса протекает достаточно быстро, потому что это чистая функция. Физический план, наоборот, дорогой. Вспомним: для того, чтобы определить, использовать индекс или нет, Postgres должен проверить, сколько в среднем строк в таблице; если она маленькая, проще сделать full scan. Далее нужно проверить, не является ли значение частым (с низкой селективностью). При анализе джоинов число комбинаций растет факториалом, поэтому Postgres применяет разные эвристики.

  Подготовленное выражение – это запрос, который прошел этапы выше, и для него построен план. Далее его можно выполнить с разными параметрами, и (в идеале) план будет переиспользоваться. В идеале – потому что даже когда подготовленное выражение готово, оно не гарантирует применения плана. Если параметров нет, Postgres использует план. Если выражение вызывается с разными параметрами, Postgres ведет хеш-таблицу: параметры -> план -> время. Для одинаковых параметров план будет один и тот же. Накопив пять вызовов с разными параметрами, Postgres наконец-то определится с планом (с минимальным временем), и далее он используется всегда.

  Некоторые клиенты к Postgres ведут свой кэш вида SQL -> preparedStatement. Каждый раз когда выполняется запрос, клиент ищет айди подготовленного выражения во внутреннем кэше. Если он есть, вызывается команда “выполни выражение foobar с такими-то параметрами” (аналог execute). Если Postgres ругнулся, что такого выражения нет, эта ошибка перехватывается, и посылается команда “тогда подготовь этот SQL и назначь ему имя foobar”. Далее все повторяется.

  Подготовленные выражения живут в рамках одного соединения с БД. Это особенно важно для пула соединений: когда мы выполняем запросы, то заимствуем подключение из пула, и они могут быть разными. Клиент учитывает это: кэш подготовленных выражений ведется в разрезе подключения. По мере ротации подключений подготовленные выражения распространяются по ним.

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

• Совет дня №17

  Jan 31, 2026 postgres, sql, uuid

  Наверное, вы много слышали про UUID версии 7. Дело в том, что недавно его утвердили окончательно, и он появился в Postgres 18. До этого люди писали свой UUID на коленке, но теперь все официально.

  В целом UUID – это контейнер из 128 бит. Первые несколько содержат информацию о версии, остальное заполняется в зависимости от нее. Самый популярный стандарт сегодня – UUID 4, который полностью случаен. Есть и другие алгоритмы: например, 1 и 2 использовали MAC-адрес машины, на которой генерировались, и таким образом раскрывали системную инфу.

  Чем хороша версия 7? Ее первые 48 битов содержат время Unix в миллисекундах. Это значит, такие ключи сохраняют порядок в разрезе тысячной секунды. Кроме того, из него можно извлечь дату и время, а также получить UUID из времени, зная, как заполнить хвост. Уникальность плюс время, два в одном.

  Существует три подмножества UUID v7. Они различаются тем, как заполняется хвост. Версия a) хвост заполняется рандомом. Версия b) используется возрастающий счетчик из 12 бит, остальное – рандом. Версия c) используется два счетчика: 12 и 10 бит (могу быть неточным, читал спеку давно).

  Счетчики в хвосте важны для супер-точных систем, например трейдинга. Лично я не сталкивался с ситуацией, когда порядка в рамках тысячной секунды недостаточно.

  Как я уже рассказывал, UUID v7 отлично подходит на роль первичного ключа. Во-первых, если у вас числовые айдишки, вы становитесь заложником базы. Только она назначает айдишки сущностям. Это становится проблемой в распределенных системах, очередях задач. Пока сущность не вставилась в базу, можно наначить ей временный айди, но это лишнее усложнение.

  Наоборот, в случае UUID можно присвоить сущности ключ до вставки в базу, например:

  # python
  user_id = generate_uuid()
  profile_id = generate_uuid()
  
  # sql
  insert into users (id) values (user_id)
  
  insert into profiles (id, user_id)
  values (profile_id, user_id)
  

  Оба запроса можно выполнить, не обращаясь к полю generated_id результата.

  Говорят: UUID длиней числовой айдишки, это раздувание таблицы. Вспомним, что UUIDv7 = id + created_at. Так или иначе нужно хранить время создания сущности. Типы timestamp(tz) занимают 8 байт, тип biginteger/bigserial – тоже 8 байт. В сумме 16, а это столько же, сколько занимает UUID. Все сходится.

  Главное: UUIDv4 (полностью случайный) не очень подходит для индексов btree. Если добавлять в него значения возрастанию, они будут наполнять бакеты последовательно. Примерно как фигурки в Тетрисе, когда профессиональные игроки укладывают их слева направо. Если данные случайны, фигурки падают в разные бакеты. Из-за этого выше фрагментация, а кроме того, часто срабатывает перераспределение узлов.

  Похожая беда с обходом индекса. Когда мы обходим btree-индекс с числами, то порядок блоков почти совпадает с порядком ключей, например:

  id   block
  1 -> 1001
  2 -> 1001
  3 -> 1002
  

  Если у нас уиды, маппинг будет такой:

  id                           block
  b7b0547b-...-148889ec0602 -> 51451
  d1897a03-...-520566c5c9a7 -> 13
  ef0093d3-...-26e923a66fe4 -> 7232
  

  Будет много случайного доступа к файлу, и обход такого индекса медленней, чем с числовыми айдишками.

  К счастью, седьмой уид сводит проблему на нет, так что пользуйтесь!

• Совет дня №16

  Jan 31, 2026 postgres, sql

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

  Предположим, в системе есть пользователи, и каждый указывает город:

  create table users (
    id uuid primary key,
    name text,
    city text
  )
  

  В реальности поле city ссылается на таблицу городов, но сейчас это не важно – оставим текст. Разработчик создал индекс на город:

  create index idx_user_city
  using btree on users(city);
  

  Выполняет запрос, чтобы найти пользователей из Москвы:

  select * from users where city = 'Moscow'
  

  Смотрит план, а там full scan. Почему?

  Самое важное: Postgres никогда не использует индекс просто потому, что он есть. Индекс берется в работу, только если а) по нему собрана статистика и б) она показывает, что индекс дешевле full scan.

  Разберем оба условия. Статистика по каждой таблице, ее колонке и индексу хранится в каталоге pg_statistics. Это очень низкоуровневые данные. В них записаны наиболее частые значения колонок, их гистограмма, приблизительные диапазоны значений. Поверх pg_statistics создана вьюха pg_stats, которая удобнее в работе и проверяет права на таблицы.

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

  analyze users
  

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

  Итак, статистика есть, но индекс “мигает” – то используется, то нет. Например, ищем пользователей из Читы, попадаем в индекс:

  explain
  select * from users where city = 'Chita'
  /* index scan on idx_user_city ... */
  

  А если из Москвы, будет full scan:

  explain
  select * from users where city = 'Moscow'
  /* full seq scan on users ... */
  

  В чем дело? Ответ – селективность, она же избирательность. Так называют долю найденных записей относительно их общего числа. С селективностью есть путаница: разные учебники по-разному трактуют этот показатель. Одни говорят: если процент малый (1-5%), то селективность высокая, а если большой (30 и выше) – то низкая. Другие наоборот: малый процент – низкая селективность, высокий — большая. Я предпочитаю первый (обратный) вариант: чем меньше записей охватывает условие, тем точнее (выше) селективность.

  Если проверить, сколько в базе пользователей из какого города, то окажется следующее. Из Читы – три человека (высокая селективность), а из Москвы – триста тысяч (крайне низкая). Москва окажется в каталоге pg_statistics в колонке “частые значения” – сигнал к тому, чтобы не брать индекс в работу. При анализе запроса Postgres это проверит и возьмет full scan. Москвичи пролетают!

  Какова должна быть селективность значения? Реальность такова, что Postgres берет в работу индекс, если селективность условия не превышает 5-10 процентов. Это довольно мало! Именно поэтому нет смысла создавать индексы на логические флаги (true/false), статусы (active, pending, done) и другие значения с малым разбросом. Их селективность слишком низкая (высокая доля выборки).

  Для таких значений используйте частичные индексы (см. прошлый совет). Например, только активные заказы, только москвичи и так далее. И потом ищите уже внутри этого подмножества.

  Почему Postgres предпочитает full scan для условия, которое покрывает 30% таблицы? Ведь прочитать треть таблицы быстрее, чем всю? Дело в том, что обход индекса возвращает не сами строки, а номера блоков, где они находятся. Блоки идут не один за другим, а разбросаны по всему файлу. Даже если отсортировать номера по возрастанию, между ними могут быть большие промежутки. В результате будет много дисковых операций. На крутящихся жестких дисках это приводило к частому переносу головки.

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

  Итого: выполняйте analyze, проверяйте селективность условия.

Страница 1 из 106 →