• Совет дня №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. Введение в документы

  Jan 25, 2026 postgres, json, book, sql

  Содержание

  • Табличное мышление
  • Подход NoSQL
  • Знакомство с документами
  • Доводы в пользу документов
  • Задачи, которые решают документы
  • Слабые стороны документов
  • Замечание о переоценке
  • Почему Postgres
  • Тайное преимущество SQL

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

  Табличное мышление

  Если вы бэкенд-разработчик, то скорее всего работали с реляционными базами данных. Наиболее известные их представители — это MySQL, ее форк MariaDB, PostgreSQL, Microsoft SQL Server, Oracle DB. Реляционные базы называются так из-за лежащей в их основе реляционной алгебры (она же алгебра отношений). Это математический аппарат, который строится на отношениях (множествах кортежей) и операций над ними (проекция, объединение, пересечение и другие).

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

  • любой массив данных является таблицей;
  • любая операция над таблицей порождает таблицу.

  В каждой базе найдется техника, которая не подходит под эти правила. Однако общую картину это не меняет: в основе реляционных баз лежат таблицы и операции над ними.

  Read more →

• Совет дня №15

  Jan 23, 2026 postgres, sql

  Пагинация, продолжение.

  Вернемся к случаю, когда нужна пагинация по убыванию времени (created_at, updated_at) и так далее. Этот критерий встречается так часто, что для него есть лазейка.

  В прошлый раз мы использовали кортеж (created_at, id). Идея в том, что поскольку created_at не уникален, он не дает точного положения в таблице. Но так как id уникален, их комбинация – тоже уникальна.

  От кортежа можно избавиться, если в качестве ID используется UUID v7. Дело в том, что v7 совмещает в себе эти два свойства: время и уникальность. Существуют разные подвиды UUID v7, но не зависимо от них пагинация будет:

  • точно попадать в границы;
  • использовать btree индекс.

  Итак, если у вас API и нужна пагинация, ваши варианты:

  • limit и offset; убедитесь, что offset не превышает какого-то разумного числа, например тысячи. Иначе вас будут парсить.

  • keyset: комбинация полей (some_field + id) для уникальности. Требует отдельного индекса.

  • UUIDv7 – если требуется пагинация по created_at. Это частый случай, поэтому рассмотрите его.

  Перейдем к пагинации, которая используется не в API, а для служебных нужд. Например, в миграциях, переносе данных и так далее. Нужно обойти огромную таблицу, при этом выгрузить ее в память нельзя – слишком большая (например, 100 миллионов записей).

  Один из способов – использовать курсор и FETCH API. Апишка у него довольно простая:

  BEGIN;
  
  DECLARE cur_foo CURSOR FOR SELECT * FROM items;
  
  FETCH FORWARD 100 FROM cur_foo;
  FETCH FORWARD 100 FROM cur_foo;
  FETCH FORWARD 100 FROM cur_foo;
  ...
  CLOSE cur_foo;
  COMMIT;
  

  Объявляем курсор, затем в цикле вытягиваем по 100 записей, пока результат не пустой. В конце закрываем курсор.

  Казалось бы, вот он – святой Грааль. Но беда в том, что курсор требует транзакции. В момент открытия он запоминает текущий снимок (номер транзакции) и просматривает записи, версия которых не превышает его. Чтобы пользоваться курсором, нужно держать транзакцию в течение всего цикла. Для больших таблиц это дорого, поэтому подходит только для всяких ночных скриптов.

  Кроме того, курсор привязан к конкретному соединению с БД. Использовать их в HTTP API невозможно.

  Другой способ обойти большую таблицу – сдампить ее в файл при помощи COPY (см. прошлый совет). Чтобы таблица не заняла весь диск, ее сжимают в gzip. В результате у вас оказывается файл my_table.gzip, вы отрываете его и спокойно парсите. Идея в том, чтобы забрать данные из базы как можно скорее и потом не мучить ее пагинацией. Если скрипт упадет, не придется насиловать базу снова – у вас уже есть файл.

  Третий способ – использовать драйвер, который позволяет обрабатывать записи в полете. Например, мой pg2. Функция execute принимает запрос и всякие опции. Среди прочих можно передать редьюсер – функцию трех тел:

  (fn
    ([]
     (make-acc ...))
    ([acc row]
     (conj acc row))
    ([acc]
     (finalize acc)))
  

  Тело без аргументов – инициатор аккумулятора (может быть мутабельным), тело с одним аргументом – финализатор аккумулятора, с двумя – приращение записи к аккумулятору. Из коробки доступны разные редьюсеры (см документацию). Легко написать свой, который будет отправлять каждую строку в сеть, в асинхронный канал и так далее. И все это – в полете, то есть по мере получения сообщений от Postgres, без исчерпания всей памяти.

  Полагаю, это все, что можно сказать про пагинацию.

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