Ivan Grishaev

Ivan Grishaev's blog

Writing on programming, education, books and negotiations.

Home About Open Source Bookshelf RSS
Clojure Postgres Emacs Python Programming Interview Video
Книга «Clojure на производстве»
Книга «Clojure на производстве», второй том

  • Совет дня №34

    ,

    Банально, но все же: если какой-то атрибут уникален, нужно повесить на него уникальное ограничение. С ним физически невозможно добавить в базу дубликат. Многие разработчики проверяют уникальность вручную: если нет, то вставить, если есть, то ругнуться. Однажды запрос приходит от потребителя, который не в курсе этих заморочек, и получается дубль. Со временем на него ссылаются другие сущности, он обрастает связями. Когда дубль вскрывается, и все чешут голову — как же так получилось?

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

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

    Опытный разработчик знает, как исправить дубль; более опытный знает, как его не допустить.

    При создании таблицы поле можно пометить словом unique. При этом Postgres создаст уникальный btree-индекс на это поле. Заметим, что уникальность допускает значения NULL, потому что они не равны друг другу. Если нулы запрещены, добавьте в поле NOT NULL.

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

    create unique index idx_user_email_u_btree
on users using btree
(trim(lower(email)))

    После этого в базе не может быть емейлов "Ivan@test.com" и " iVan@TEST.com " одновременно.

    Уникальность можно повесить на поле JSON-документа. Если код находится где-то в глубине, индекс выглядит так:

    create unique index idx_item_code_u_btree
on users using btree
(lower(data #>> '{attrs,path,to,code}'))

    Следите за уникальностью. Добавить ее — дело пяти минут, исправлять последствия — пять дней.

  • Совет дня №33

    ,

    Разберем еще один случай, когда нужен срез строк – геохэш (geohash). Это быстрый и дешевый способ кодировать положение объектов, а также искать их.

    Представим себе карту мира. Точки отсчета – Лондон и экватор. Это квадрат. Разделим его пополам по вертикали. Если человек живет западней Лондона, запишем 0, если восточнее – то 1. Теперь разделим область по горизонтали. Если человек находится северней, добавим 0, если южнее – 1. Продолжаем дробить область по горизонтали и вертикали и накапливать нули и единицы. В результате мы достигнем квадрата, достаточного малого, чтобы остановиться. Скажем, если речь о здании, квадрат может быть 25x25 метров. Если стадион, деревня или город, то еще больше.

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

    Прелесть вот в чем: есть взять какой-то квадрат, то хэши всех точек в нем имеют общую часть. Например, есть квадрат hsGHrgga. В нем будут точки hsGHrgga3ysg6, hsGHrgga3ysgS, hsGHrgga6sg и так далее. Это значит, для их поиска нужно выбрать строки, которые начинаются с hsGHrgga. С индексом btree, что мы рассмотрели в прошлом совете, это крайне эффективно.

    Похоже устроен поиск ближних объектов. Скажем, точка hsGHrgga3ysg6 указывает на кинотеатр и нужно показать ближайшие. Отломаем у хэша последний символ и таким образом поднимемся на квадрат выше: hsGHrgga3ysg. Найдем по нему все кинотеатры и покажем. Что-то лишнее можно почистить в приложении, так дешевле. Вопрос лишь в том, насколько “отъезжать” при поиске: на один символ, два и так далее.

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

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

    И не только многомерным – одномерным! Представим, на отрезке множество точек. Делим отрезок пополам: слева – 0, справа – 1. Переходим на нужный под-отрезок, делим пополам и так далее, пока не достигнем нужной точности. Накопленные нули и единицы кодируем в строку. Потом, чтобы получить точки в нужном отрезке, делаем срез по префиксу.

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

  • Совет дня №32

    ,

    В Postgres индексы бывают разных типов. Самый базовый и нужный называется btree, который основан на одноименной структуре данных – дереве B-Tree. Оно состоит из распределительных узлов, и на последнем уровне находятся пары вида (значение, страница).

    Btree поддерживает равенство и сравнение. Это значит, в нем можно искать по точному значению и диапазонам, а еще он подходит для сортировки, потому что упорядочен.

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

    Есть, однако, лазейка, чтобы использовать btree для частичного сравнения. Если применить его к строке с особой опцией, можно искать по началу строки оператором (i)like и регулярным выражением.

    Предположим, в таблице demo хранятся пути к файлам:

    create table demo(
    id integer primary key,
    path text not null
);

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

    insert into demo
select
    x,
    format('%s/%s/picture.jpeg', to_char((now() - interval '10 years' *  random()), 'yyyy/MM/DD/HH'), gen_random_uuid())
from
    generate_series(1, 999999) as seq(x);

    Навесим индекс на путь. Опция text_pattern_ops нужна, чтобы зафиксировать локаль. С ней сравнение строк не зависит от текущей локали:

    create index path_btree on demo
using btree
(path text_pattern_ops);

    Выберем файлы за октябрь 2023 года:

    select * from demo
where path like '2023/10/12%'
limit 100;

    Справа от like стоит шаблон, а не простой текст. Символ % означает любые символы. Вот что вернет запрос:

    ┌────────┬─────────────────────────────────────────┐
│   id   │                path                     │
├────────┼─────────────────────────────────────────┤
│ 213875 │ 2023/10/12/01/00ec...af263/picture.jpeg │
│ 670319 │ 2023/10/12/01/04e2...67afd/picture.jpeg │
│  34516 │ 2023/10/12/01/063a...ebddc/picture.jpeg │
│ 122843 │ 2023/10/12/01/3237...6ffd7/picture.jpeg │
│ 227530 │ 2023/10/12/01/5a48...c4ec0/picture.jpeg │
│ 141900 │ 2023/10/12/01/6a78...da021/picture.jpeg │
│ 663626 │ 2023/10/12/01/9d6d...84da7/picture.jpeg │
│ 154138 │ 2023/10/12/01/a02e...5570c/picture.jpeg │
│  37955 │ 2023/10/12/01/a465...47a7a/picture.jpeg │
│ 864446 │ 2023/10/12/01/ae47...943d9/picture.jpeg │
│ 196849 │ 2023/10/12/01/cd15...25874/picture.jpeg │

    Но гораздо интересней план:

    explain analyze
select * from demo
where path like '2023/10/12%'
limit 100;

│ Limit  (cost=0.55..8.57 rows=100 width=68)
│ (actual time=0.054..0.364 rows=100 loops=1)
│   ->  Index Scan using path_btree on demo
│       (cost=0.55..8.57 rows=100 width=68)
│         Index Cond: ((path ~>=~ '2023/10/12'::text)
│                  AND (path ~<~ '2023/10/13'::text))
│         Filter: (path ~~ '2023/10/12%'::text)
│ Planning Time: 0.375 ms
│ Execution Time: 0.400 ms
└────────────────────────────────────────────────────

    Видим, что Postgres поступает хитро: заключает путь в диапазон 2023/10/12 и 2023/10/13. Цифра 3 на конце получилась как следующий символ за 2.

    Попадаем в индекс, стоимость копеечная.

    Если поставить в начало шаблона %, индекс не сработает. Другими словами, с таким подходом нельзя искать пути по вхождению.

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

    2025/10/23/%cat%.jpeg

    Так мы получим все фотографии в папке 2025/10/23/… со словом cat в названии и расширением jpeg. Кроме шаблонов like, индекс поддерживает регулярки, и с ними критерий поиска можно описать точнее.

    Чем больше лидирующих символов мы задали, тем быстрее будет поиск.

    Еще один трюк: что если нужно искать с конца строки? Например, имеется таблица товаров с серийными номерами:

    create table demo2(
    id integer primary key,
    serial_number text not null
);

    Вставим миллион серийных номеров:

    insert into demo2
select
    x,
    to_char(random() * 1000000000000, '0000-0000-0000')
from
    generate_series(1, 999999) as seq(x);

┌────────┬─────────────────┐
│   id   │  serial_number  │
├────────┼─────────────────┤
│      1 │  7084-1644-4953 │
│      2 │  2694-3476-5136 │
│      3 │  3274-5880-3765 │
│      4 │  9346-6945-3461 │
│      5 │  3352-3981-1479 │
│      6 │  7103-7860-4732 │
│      7 │  2634-9208-2518 │
│      8 │  1485-3601-0146 │
│      9 │  8068-5128-1222 │
│     10 │  5832-3252-5642 │

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

    create index path_btree_rev on demo2
using btree
(reverse(serial_number) text_pattern_ops);

    Чтобы искать по ним, выражение поиска тоже нужно перевернуть. Давайте найдем все номера, которые заканчиваются на -0500:

    select * from demo2
where reverse(serial_number) like reverse('%-0500')
limit 100;

    Результат:

    ┌────────┬─────────────────┐
│   id   │  serial_number  │
├────────┼─────────────────┤
│ 237074 │  5088-3410-0500 │
│ 231067 │  0411-7020-0500 │
│ 728812 │  3489-6520-0500 │
│ 618494 │  3972-6720-0500 │
│ 486950 │  4871-5330-0500 │
│ 634762 │  8038-7440-0500 │
│ 733815 │  8856-2250-0500 │
│ 451464 │  3640-0450-0500 │
│ 868649 │  7289-2270-0500 │
│ 485383 │  7016-0370-0500 │
│ 782061 │  4076-8080-0500 │
│ 234715 │  4279-2180-0500 │
│  36948 │  9033-7280-0500 │
│ 460010 │  5881-8090-0500 │

    Самое интересное – план:

    │ Limit  (cost=0.42..8.45 rows=100 width=16) (actual
│   ->  Index Scan using path_btree_rev on demo2  (co
│         Index Cond: ((reverse(serial_number) ~>=~ '
│         Filter: (reverse(serial_number) ~~ '0050-%'
│ Planning Time: 0.286 ms
│ Execution Time: 1.088 ms
└────────────────────────────────────────────────────

    Работает!

    Повторю, что btree и text_pattern_ops работают только для начала строки. Если нужен поиск посередине, обратитесь к расширению pg_tgrm (триграммы). Этот индекс, однако, требует Gin.

    У text_pattern_ops есть еще один полезный сценарий, но о нем – в следующий раз.

  • Совет дня №31

    ,

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

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

    Еще пример – рейтинг пользователя. Начинающие программисты создают в таблице users поле rating, и зря. Рейтинг – это забава, в которую любят играть продакт-оунеры. Как только вы сделаете рейтинг, вам скажут: теперь сделаем рейтинг по сезонам и будем отслеживать взлеты и падения. Вы же не будете делать колонки rating_summer_2025, rating_autumn_2025 и так далее. Все это поедет в новые таблицы, и первый рейтинг тоже должен был храниться отдельно.

    Технический момент: в Postgres любая операция над строкой порождает новую строку. В этом плане он похож на Git: единица изменения – строка. Если в поле 20 колонок и вы изменили одну, будет создана новая строка на 20 колонок. Старая будет болтаться, пока ее не соберет vacuum. Если таблица обновляется часто, в ней должно быть как можно меньше полей. Если какой-то набор колонок не меняется, лучше вынести их в таблицу и связать один к одному.

    Еще пример: иногда сущность таскает за собой огромный JSON-документ. Это исторические данные, которые перетащили из какой-то системы и которые некому разобрать. Если обновить хотя бы одно поле, документ будет скопирован. Лучше вынести его в отдельную таблицу.

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

    Предположим, у нас аналог Хабра, и есть таблица posts с полем content – статьей. Логично добавить поле tsvector и складывать в него разобранный документ. При этом мы считаем: поскольку сайт русскоязычный, то при разборе документа указываем язык ru.

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

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

    Идет текст на русском, а потом <quote lang="en">some text in English</quote>.

    Может быть, границы языка определяются гистограммой символов. Так иначе, у нас есть функция split_by_langs, которая принимает текст и возвращает его нарезку:

    select * from split_by_lang(post.comment);

┌──────┬──────┬─────────────────────────┐
│ part │ lang │  content                │
├──────┼──────┼─────────────────────────┤
│    1 │ ru   │ Всем привет! ...        │
├──────┼──────┼─────────────────────────┤
│    2 │ en   │ A quote from Wikipedia  │
├──────┼──────┼─────────────────────────┤
│    3 │ ru   │ дальнейший текст        │
├──────┼──────┼─────────────────────────┤
│    4 │ en   │ another quote from wiki │

    Каждая часть индексируется языком из колонки lang. Накопленные части объединяются в один документ и записываются в поле tsvector.

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

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

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

    Решение о том, что хранить в основной таблице, а что в связанной, часто ситуативно. Нужно знать контекст и условия задачи. Но одно можно сказать точно: объединить данные проще, чем разделить. “Design is about keeping things apart” (c).

  • Совет дня №30

    ,

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

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

    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

    ,

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

    Возьмем за аксиому следующее: никто не учит базы данных. Все учат Питоны-Джавы, почему-то забывая: если работаете с базой, нужно знать ее тоже. Однако все делается на 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

    ,

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

    Обновление силами 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

    ,

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

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

    Предположим, нужно обновить статус пользователа по коду. На 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

    ,

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

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

    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

    ,

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

    • высокая связность сущностей. Если это пользователь, на него завязаны публикации, комментарии, фотографии, упоминания, заказы, обращение в поддержку и так далее. Каскадный 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 недостаточно, чтобы понять: почему данные удалены. В идеале мы бы хотели знать время события, пользователя, кто это сделал и минимальный комментарий. Выходит, к каждой таблице нужно добавить еще два-три поля?

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

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

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

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