• Совет дня №30

  Mar 21, 2026 postgres, sql

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

  Например, в базе есть функция для форматирования центов:

  create or replace function format_cents(cents integer)
  returns text
  immutable strict parallel safe language sql
  return to_char(cents, '999.999');
  

  Тест посылает такой запрос:

  select
   x,
   format_cents(x) as result
  from (values
   (1), (999), (null), (0), (-42)) as vals(x);
  

  Читаем результат и проверяем, что он следующий:

  ┌────────┬──────────┐
  │   x    │  result  │
  ├────────┼──────────┤
  │      1 │    1.000 │
  │    999 │  999.000 │
  │ <null> │ <null>   │
  │      0 │     .000 │
  │    -42 │  -42.000 │
  └────────┴──────────┘
  

  Вот и все. Если кто-то поправит функцию, тест упадет.

  Сложную логику тестируют точно так же. Достаточно нескольких шагов:

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

  Функция удобна тем, что данные не нужно готовить. Передал — получил. База фактически не участвует. А для сложной логики нужно готовить данные.

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

  Базу обычно запускают в Докере, потому что в нем многое можно задать из коробки (базу, пользователя, пароль, начальные .sql файлы и другое). Кто не любит Докер, пусть ставит локальный Постгрес — он есть везде.

  На этом месте начинаются вопли: мол, тесты-то не юнит-, а интеграционные! Ну и что? Юнит-тесты нужны, но порой их недостаточно. Когда сетевые вызовы закрыты моками, легко оказаться в мире Оруэлла: дважды два равно пяти. Тесты пробегают, прод падает.

  Так что если у вас Постгрес — тестируйте, как приложение ходит в Постгрес.

• Совет дня №29

  Mar 21, 2026 postgres, sql

  Когда речь заходит о бизнес-логике в базе, большинство людей теряют лицо. Начинаются крики, словно им намерены причинить увечье. А между тем логика в базе – хорошая вещь, нужно лишь правильно к ней подойти.

  Возьмем за аксиому следующее: никто не учит базы данных. Все учат Питоны-Джавы, почему-то забывая: если работаете с базой, нужно знать ее тоже. Однако все делается на ORM, а база – черный ящик. Редкий разработчик интересуется, какие запросы в нее идут. Еще реже – попадают ли запросы в индекс, каков план и так далее.

  Прикладной программист мыслит объектами. Когда он пишет на Питоне, в его распоряжении строки, числа и их комбинации – объекты. На ORM он выдает примерно такой код:

  user = User.get_by_id(user_id)
  if user is None:
    return None
  
  profiles = Profile.get_by_ref('user_id', user.id)
  profile = profile.first()
  if not profile:
    return None
  

  Если вынудить программиста перенести этот код в базу, он напишет что-то такое:

  create function get_user_by_id(id integer)
  returns user
  return select * from users where ? = id
  
  create function get_profiles_by_user_id(user_id integer)
  returns profile
  return select * from profiles where ? = user_id limit 1
  

  Далее та же петрушка: получить кортеж пользователя, проверить на NULL; получить профиль, проверить на NULL; далее что-то сделать. Это калька с императивного языка, и в SQL она выглядит уродливо. SQL предназначен для другого; он недостаточно выразителен для императивных нужд, поэтому писать на нем в таком стиле – сущее издевательство.

  Решение простое: пишите на SQL реляционно! Это язык для операций над отношениями (таблицами): проекция, объединение, соединение, полу- и анти-соединения и другие их виды. В SQL единица операции – не строка или число, а таблица. Бизнес-логика на SQL сводится к тому, что есть наборы данных и нужно выполнить операции над ними. Например, соединить два набора, что-то отсечь, пересечь с другим набором, дополнить третьим, записать результат в таблицу. Дубликаты записей либо обновить, либо игнорировать.

  Все это прекрасно ложится на SQL, и в прошлых советах были примеры. Напишите подобное на ORM – и кода станет больше, не говоря уж о том, что не будет малейшего понимания, что происходит с базой.

  Говорят: логика в базе уродлива. Увы, когда я вижу, как соединяют данные в приложении (бесконечные экраны кода) вместо запроса на три строчки, то понимаю — вон он, уродливый код.

  Разумеется, иногда один и тот же код повторяется, и его выносят в функции. Например, в фирме особые требования к форматированию денег. Чтобы не копировать одно и тоже, пишут чистую функцию:

  create function format_mln_eur(cents int)
  returns text
  immutable strict parallel safe
  return ...
  

  Далее вызывают ее в запросах. Такой подход в порядке вещей, потому что он предотвращает копирование кода.

  Другой пример – в финансах временной интервал хранится как четверка чисел: число лет, месяцев, недель и дней. Пишется функция tenor_to_interval, которая приводит четверку к типу interval и наоборот:

  create function tenor_to_interval(y int, m int, w int, d int)
  returns interval
  immutable strict parallel safe
  return ...
  

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

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

• Совет дня №28

  Mar 21, 2026 postgres, sql

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

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

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

  Покажу, как управлять данными в базе без приложения. Для начала сделаем две таблицы: пользователи и профили:

  create table users (
      id integer primary key,
      name text not null,
      points integer not null default 0
  );
  
  create table profiles (
      id integer primary key,
      user_id integer not null,
      avatar text null,
      del_requested boolean null default false
  );
  

  Кроме имени, в пользователе записано число баллов. Профиль ссылается на пользователя, и в нем два поля: аватарка (может быть пустой) и запрос на удаление (по умолчанию ложь). Вставим много записей:

  insert into users
  select
      x,
      format('user_%s', x),
      round(random() * 100000)
  from
      generate_series(1, 10000) as seq(x);
  
  insert into profiles
  select
      x,
      x,
      null
      false
  from
      generate_series(1, 10000) as seq(x);
  

  Оператор INSERT принимает SELECT, который порождает данные. Не нужно генерить их на Питоне и передавать по сети, все происходит внутри базы.

  Первая задача – поместить в отдельную таблицу топ-100 пользователей. Это делается запросом:

  drop table if exists users_top_100;
  
  create table users_top_100 as
  select * from users
  order by points desc
  limit 100;
  
  table users_top_100;
  

  ┌──────┬───────────┬────────┐
  │  id  │   name    │ points │
  ├──────┼───────────┼────────┤
  │ 6637 │ user_6637 │  99996 │
  │ 8909 │ user_8909 │  99994 │
  │ 9040 │ user_9040 │  99979 │
  │ 2631 │ user_2631 │  99978 │
  │ 7240 │ user_7240 │  99961 │
  │  972 │ user_972  │  99958 │
  │ 8698 │ user_8698 │  99924 │
  │ 3850 │ user_3850 │  99914 │
  │  382 │ user_382  │  99892 │
  │ 2841 │ user_2841 │  99877 │
  │ 2607 │ user_2607 │  99870 │
  │ 5207 │ user_5207 │  99858 │
  

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

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

  update profiles
  set avatar = format('htts://path/to/file_%s', id)
  where id between 500 and 600;
  -- UPDATE 101
  

  Вот как накинуть баллы тем, у кого она есть:

  update users u
  set points = points + 1000
  from profiles p
  where u.id = p.user_id
  and p.avatar is not null;
  -- UPDATE 101
  

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

  Третья задача: некоторые пользователи отметили в профиле значок “удалите меня”:

  update profiles
  set del_requested = true
  where id between 666 and 999;
  -- UPDATE 334
  

  Вот как удалить такие профили и их пользователей одним запросом:

  with del_profiles(user_id) as (
      delete from profiles
      where del_requested
      returning user_id
  )
  delete from users u
  using del_profiles del
  where u.id = del.user_id;
  -- DELETE 334
  

  Во всех случаях мы ничего не передавали клиенту.

  Вставки могут быть с реакцией на конфликт (выражение ON CONFLICT). Есть команда MERGE, которая делит два набора данных на категории: только слева, пересечение, только справа. Для каждой категории можно задать реакцию: пропустить, удалить, записать куда-то и так далее.

  Управлять данными при помощи SQL – это настоящий праздник. Нет зависимости от приложения и рантайма. База делает все сама: работает производительный код на Си, многократно проверенный и отлаженный. Не отбирайте у базы ее работу. Она сделает все лучше вас, достаточно базовых навыков SQL.

• Совет дня №27

  Mar 21, 2026 postgres, sql

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

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

  Предположим, нужно обновить статус пользователа по коду. На ORM это выглядит так:

  user = models.User.get_by_id(42)
  user.status = 'active'
  user.save()
  

  Разумеется, никому не интересно, какие запросы будут выполнены. Их никто не смотрит и даже не знает, как включить (см. прошлый совет). А запросы будут такими:

  select * from users
  where id = 42
  
  update users set status='active'
  where id = 42
  

  Внимание: зачем нужен первый запрос? Мы выбрали все поля пользователя. Зачем? Второй UPDATE делает именно то, что нужно: обновляет статус по номеру. Ради этого не стоило читать пользователя! Нужно оставить только второй запрос.

  Говорят: перед обновлением я должен убедиться, что пользователь существует. Это легко сделать и после: оператор UPDATE возвращает число обновленных записей. Если оно равно нулю, пользователя не было. Не говоря уж о том, что существование записи проверяется SELECT-ом без полей или условием EXISTS – незачем выгребать все поля.

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

  begin;
  select * from users where id = 42 for update;
  update users set ... where id = 42;
  commit;
  

  Зачем читать и блокировать запись? Просто обнови ее.

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

  create or replace pg_temp.get_new_rating(user user)
  returns integer
  immutable strict parallel safe language sql
  return ...
  

  и обновляют сразу много пользователей:

  update users
  set rating = get_new_rating(users)
  FROM subquery
  where id = subquery.user_id
  

  Поздапрос subquery выбирает пользователей, которых нужно обновить, а функция get_new_rating возвращает новый рейтинг.

  Уже слышу истерику: омг, хранимки, мои глаза! Ну, тут уж на ваше усмотрение. Хотите пользоваться базой нормально? Понадобится сырой скуль. Для этого нужно отложить ORM и почитать книжки. Но оно того стоит.

• Совет дня №26

  Mar 21, 2026 postgres, sql

  Продолжение прошлой заметки про удаление.

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

  create table goods (
   id serial primary key,
   title text not null,
   created_at timestamptz not null default current_timestamp
  );
  

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

  insert into goods(title)
  select
   format('good_%s', x)
  from
   generate_series(1, 10000) as seq(x);
  

  Подготовим таблицу удаленных записей. Ее структура такая же, поэтому используем выражение like <table>. Индексы и ограничения скопированы не будут.

  create table goods_deleted (like goods);
  

  Далее мы решили, что товары с кодами с 555 по 666 почему-то плохие, и нужно их удалить. Разработчики делают так: сперва читают данные в приложение:

  select * from goods
  where id between 555 and 666;
  

  Удаляют их:

  delete from goods
  where id between 555 and 666;
  

  Вставляют в таблицу goods_deleted строки, которые висят в приложении:

  insert into goods_deleted ...;
  

  В обратную сторону — то же самое: прочитали, удалили, вставили. Решение нормальное и встречается часто — и в нем все плохо.

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

  with deleted as (
   delete from goods
   where id between 555 and 666
   returning *
  )
  insert into goods_deleted
  select * from deleted;
  

  Если DELETE имеет на конце returning, он не отличается от SELECT (разве что с побочным эффектом). В выборке deleted окажутся удаленные строки. Далее мы вставляем их в goods_deleted. Оба запроса протекают в неявной транзакции, снимок данных один и тот же.

  Теперь в goods не окажется товаров с айдишками 555…666, зато они будут в goods_deleted.

  Теперь в обратную сторону: удалить из goods_deleted и вставить в goods:

  with restored as (
   delete from goods_deleted
   where id between 555 and 666
   returning *
  )
  insert into goods
  select * from restored;
  

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

  Теперь таблицы. Выше мы клонировали таблицу goods и назвали ее goods_deleted. И что, делать это для каждой таблицы? users_deleted, orders_deleted, photos_deleted, profile_deleleted…?

  А во-вторых, миграции. Если таблица goods изменится, перенос отвалится, потому что состав колонок будет другим. Придётся явно прописывать, что на что маппить.

  Поэтому вместо goods_deleted, users_deleted и так далее заведем таблицу any_deleted:

  create table any_deleted (
   entity text not null,
   pk integer not null,
   data jsonb not null,
   created_at timestamptz not null default current_timestamp
  );
  

  Поле entity означает сущность (user, profile и другие), pk — ее первичный ключ. В идеале это UUID, чтобы быть уникальным в разрезе всех сущностей. В поле data хранится строка в формате JSON. Вот как выполнить перенос:

  with deleted as (
   delete from goods
   where id between 555 and 666
   returning *
  )
  insert into any_deleted
  select
   'goods',
   deleted.id,
   to_jsonb(deleted)
  from deleted;
  

  Посмотрим, что оказалось в удаленной таблице:

  ┌─[ RECORD 1 ]─────────────────────────────────
  │ entity  │ goods
  │ pk      │ 555
  │ data    │ {"id": 555, "title": "good_555",
  │ created_at │ 2026-01-24 14:57:32.595317+03
  ├─[ RECORD 2 ]─────────────────────────────────
  │ entity  │ goods
  │ pk      │ 556
  │ data    │ {"id": 556, "title": "good_556",
  │ created_at │ 2026-01-24 14:57:32.595317+03
  ├─[ RECORD 3 ]─────────────────────────────────
  │ entity  │ goods
  │ pk      │ 557
  │ data    │ {"id": 557, "title": "good_557",
  │ created_at │ 2026-01-24 14:57:32.595317+03
  

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

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

  with deleted as (
   delete from any_deleted
   where
           entity = 'goods'
       and pk between 555 and 666
   returning *
  )
  insert into goods
  select
   rec.id,
   rec.title,
   rec.created_at
  from
   deleted,
   jsonb_to_record(deleted.data) as rec(
       id integer,
       title text,
       created_at timestamptz
   );
  

  Функция jsonb_to_record приводит JSON-объект к записи. Даже если в исходной таблице добавились колонки, после распаковки они будут null.

  В итоге у нас одна таблица удаленных сущностей. Она бесконечно растет вниз, устойчива к миграциям. Активные таблицы не захламляются. Чудо!

  Что бы еще добавить? Пожалуй, у таблицы any_deleted могут быть поля done_by (кто удалил), reason (причина: ban, conflict, legacy), комментарий (бывает нужен), срок хранения (для регулятора).

  Минус переноса в том, что нужно ослабить внешние ключи. Обычно против этого возражают, но с ростом базы это неминуемо. Если сущность удаляется (подлежит переносу), отключите foreign key constraint в тех таблицах, которые на нее ссылаются. Для джоинов используйте inner join, чтобы отсекать пустые ссылки. Записи, которые ссылаются вникуда, можно найти анти-соединением и либо удалить физически, либо тоже перенести. Часть этой работы можно поставить на крон (см. pg_cron в прошлых советах).

• Совет дня №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 возрастет).

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

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