Ivan Grishaev's blog

Совет дня №10

Jan 16, 2026 postgres, sql
Прелесть рекурсивных запросов в том, что их источником может быть что угодно – не обязательно таблица. В примере ниже мы рассмотрим, как обойти вложенный JSON-документ.

Проблема в следующем. В базе лежит огромный JSON с каталогом услуг. Его схема примерно такая:
```
DOC = {
 "id": ID,
 "code": CODE,
 "children": [DOC]
}
```
Головной элемент содержит некоторые поля и children, в которых список элементов. У каждого элемента свои children и так далее. Вложенность значительна: иная ветка достигает 30 элементов.

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

Есть, однако, простой способ привести JSON к плоскому виду – рекурсивный запрос:
```
prepare flatten as
with recursive step as (
    select
        jt.*,
        0 as level
    from
        json_table($1::jsonb, '$' columns(
            id int4 path '$.id',
            code text path '$.code',
            children jsonb path '$.children'
    )) as jt
    UNION ALL
    select
        jt.*,
        step.level + 1 as level
    from
        step,
        json_table(step.children, '$[*]' columns(
            id int4 path '$.id',
            code text path '$.code',
            children jsonb path '$.children'
    )) as jt)
select id, code, level from step
order by level;
```
Первая часть производит строку с полями id, code и children. В нем – JSON-массив вложенных элементов. Рекурсивная часть берет каждый элемент и делает то же самое: расщепляет его на id, code и другие элементы. Все это колбасится, пока вложенные элементы не исчерпаются. А теперь – пример:
```
execute flatten($${
    "id": 1,
    "code": "Product A",
    "children": [
        {"id": 2, "code": "Product B", "children": [
            {"id": 5, "code": "Product E", "children": [
                {"id": 6, "code": "Product F", "children": []}
            ]}
        ]},
        {"id": 3, "code": "Product C", "children": [
            {"id": 7, "code": "Product F", "children": [
                {"id": 8, "code": "Product G", "children": []}
            ]}

        ]},
        {"id": 4, "code": "Product D", "children": [
            {"id": 5, "code": "Product E", "children": [
                {"id": 9, "code": "Product H", "children": []}
            ]}
        ]}
]}$$);
```
Результат:
```
┌────┬───────────┬───────┐
│ id │   code    │ level │
├────┼───────────┼───────┤
│  1 │ Product A │     0 │
│  2 │ Product B │     1 │
│  3 │ Product C │     1 │
│  4 │ Product D │     1 │
│  5 │ Product E │     2 │
│  7 │ Product F │     2 │
│  5 │ Product E │     2 │
│  6 │ Product F │     3 │
│  8 │ Product G │     3 │
│  9 │ Product H │     3 │
└────┴───────────┴───────┘
```
Такой обход очень полезен. Быстро выяснилось, что плоские продукты нужны аналитикам, которые не владеют Кложей или другим ЯП, чтобы их распутать. Я написал им запрос, и они чуть не заплакали от счастья. Во-вторых, продукты изменяются редко, поэтому из запроса легко сделать материализованную вьюху и обновлять ее каждый день. Для аналитиков это обычная таблица, которую можно джойнить в запросах.

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

Jan 16, 2026 postgres, sql
В комментарии к прошлой заметке прислали ссылку на Кирилла Макевнина. Он пишет о хранении деревьев в базе, и первый абзац такой:

Обычно, для хранения деревьев в базе в таблицу добавляется поле parent_id. Это дубовое решение, которое работает хорошо в небольшом количестве ситуаций. Кому-то даже может хватить, но есть запросы, на которых такая схема не работает. Например если нам понадобится извлечь ветку этого дерева. В таком случае понадобится рекурсивно выполнять запрос с parent_id где для каждого нового запроса parent_id становится id записи из предыдущего запроса. Кто-то пытается решать эту задачу прямо в коде, создавая очень не эффективное решение, кто-то с помощью возможностей базы данных, что сильно привязывает к ней, плюс, страдает интеграция с ORM.

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

Приведу минимальный пример с деревом в базе. Предположим, мы храним вот такое дерево:
```
┌─────────────────────────────┐
│            ┌───┐            │
│       ┌────│ A │────┐       │
│       ▼    └───┘    ▼       │
│     ┌───┐         ┌───┐     │
│   ┌─│ B │─┐     ┌─│ C │─┐   │
│   ▼ └───┘ ▼     ▼ └───┘ ▼   │
│ ┌───┐   ┌───┐ ┌───┐   ┌───┐ │
│ │ D │   │ E │ │ F │   │ G │ │
│ └───┘   └───┘ └───┘   └───┘ │
└─────────────────────────────┘
```
Запишем его в базу:
```
create table tree(
    id text primary key,
    parent_id text null
);

insert into tree values
    ('a', null),
    ('b', 'a'),
    ('c', 'a'),
    ('d', 'b'),
    ('e', 'b'),
    ('f', 'c'),
    ('g', 'c');
```
Напишем запрос, чтобы выгрести поддерево, зная вершину:
```
prepare get_branch as
with recursive sub as (
    select tree.*, 0 as level from tree where id = $1
    UNION ALL
    select tree.*, sub.level + 1
        from tree, sub
    where tree.parent_id = sub.id
)
select * from sub order by level;
```
Проверка:
```
execute get_branch('b');
┌────┬───────────┬───────┐
│ id │ parent_id │ level │
├────┼───────────┼───────┤
│ b  │ a         │     0 │
│ d  │ b         │     1 │
│ e  │ b         │     1 │
└────┴───────────┴───────┘
```
Второй запрос, чтобы найти путь к вершине:
```
prepare get_path as
with recursive sub as (
    select tree.*, 0 as level from tree where id = $1
    union all
    select tree.*, sub.level - 1
        from tree, sub
    where tree.id = sub.parent_id
)
select * from sub order by level;
```
Проверка:
```
execute get_path('f');
┌────┬───────────┬───────┐
│ id │ parent_id │ level │
├────┼───────────┼───────┤
│ a  │ <null>    │    -2 │
│ c  │ a         │    -1 │
│ f  │ c         │     0 │
└────┴───────────┴───────┘
```
Как видим, в обоих случаях был один запрос, циклы не понадобились, база все сделала сама. Напоминаю, что with recursive позволяет собрать вершины вглубь и вширь, находить циклы и многое другое.

Материализованные пути, о которых пишет Кирилл, тоже бывают полезны. Скажем, если у вас система файлов и папок (виртуальное файловое хранилище), то иной раз это даже лучше, чем parent_id, потому что позволяет эффективно искать по префиксу. Но есть и недостатки: если каждый узел – UUID, путь становится огромным. Кроме того, если нужно переместить поддерево, то в случае с parent_id достаточно переключить ссылку, а materialized path требует пересчета всех путей поддерева.

Ну а отказывать себе в мощных средствах только потому, что с ними не дружит ORM… это примерно как рушить свою жизнь из-за непутевого друга.
Совет дня №8

Jan 16, 2026 postgres, sql
Если в базе есть хоть какой-то намек на иерархию – parent_id, children_ids и так далее – вам не избежать рекурсивных запросов. Если точнее, избежать их можно: выбрать данные и обойти в приложении. Но зачем, если это может сделать база?

Рекурсивный запрос на самом деле не имеет отношение к рекурсии. Это цикл или свертка. В целом он выглядит так:
```
with recursive NAME as (
  init-part
  UNION (ALL)
  continuous-part
)
select * from NAME
```
Часть init-part выполняется однажды. Часть continuous-part выполняется много раз, при этом псевдоним NAME ссылается на результат прошлой выборки. Например, когда continuous-part сработает в первый раз, в NAME будет то, что вернула init-part. Когда continuous-part сработает второй раз, в NAME будет то, что вернула continuous-part до этого и так далее.

Цикл заканчивается, когда в очередной раз continuous-part вернула пустой результат. Должно быть ясное условие остановки, иначе запрос уйдет в бесконечный цикл. В итоговом SELECT алиас NAME содержит результат всех операций.

При помощи рекурсивного запроса легко обойти дерево потомков, графы, отношения, различные связи. Более того – внутри рекурсивного запроса доступны формы SEARCH DEPTH FIRST и SEARCH BREADTH FIRST, выявление циклов по массиву вершин и другие приятные вещи. Так что когда на собеседовании скажут обойти граф, расчехляйте psql!

Последнее, кстати, не такая уж и шутка. Скажем, дали вам файл с вершинами. А откуда взялся файл? Скорее всего, из базы. “Вот где графы храните, там и обходите.” (с) Поэтому связные структуры я обхожу в SQL без выгрузки в приложение. Это и быстрее, и результат виден сразу, и удобно коллегам-аналитикам, которые не знают Кложу/Питон.
Совет дня №7

Jan 16, 2026 postgres, sql

Коль скоро COPY – ваш лучший друг, присмотритесь к бинарному формату. В среднем он на 30% меньше, чем текстовый и CSV. Числа хранятся компактно, а не по принципу “байт на разряд”. Строки не экранируются. Это очень удобно: не нужно бежать по строке и проверять, есть ли обратный слэш и что-то за ним. Прочитал байтовый массив, обернул в (new String) и готово.

Бинарный формат COPY в целом прост. Первые 19 байтов можно пропустить – это заголовок и резерв под флаги, которые в данный момент не используются. Далее идет набор строк. Каждая строка – это пара (int2, content), где int2 – число колонок. Оно одинаково для всех строк, но дублируется. Последняя строка содержит -1, а за ней ничего нет.

С свою очередь content – это набор пар (int4, field) – длина поля и его содержимого. Если длина -1, то значение null, а содержимого нет. В зависимости от типа поле читают по-разному. Перечислять все типы и их особенности я не буду: для этого есть библиотеки. Радует, что библиотек для бинарного формата Postgres все больше: в Гугле находятся версии для Python, Node.js, Go и так далее. Среди прочих есть и мои реализации: pg-bin на Кложе и модуль org.pg.codec на Джаве в составе pg2.

Возня с бинарным форматом помогла мне понять, как лучше хранить данные, передавать по сети, как ускорить их обработку. В целом, поковыряться с байтами – хорошее дело: развивает кодерские навыки, поддерживает форму.
Совет дня №6

Jan 16, 2026 postgres, sql
Если вы работаете с Postgres, команда COPY должна стать вашим другом. COPY перемещает данные с сервера на клиент и обратно в потоковом режиме. Она подходит для забора больших данных и вставки. COPY эффективнее, чем массовые INSERT и SELECT с пагинацией.

В режиме чтения COPY забирает данные из таблицы или произвольного запроса. Данные передаются в один из трех пунктов назначения: клиенту, в файл или процессу. Процессом может быть выражение, например 'gzip -9 > output.gzip'.

В режиме записи COPY ожидает таблицу. Данные принимаются либо от клиента, либо из файла, либо от процесса.

С помощью COPY можно сделать потоковую обработку. В джаве для этого служит класс CopyManager, который ожидает OutputStream. Этот стрим можно обернуть в PipedInputStream и читать его в другом потоке. Данные могут быть огромны, но вы не израсходуете память – все будет проходить в полете порциями.

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

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

COPY поддерживает три формата данных: текстовый, CSV и двоичный. Первые два содержат данные в виде текста и отличаются разделителями. Их удобно просматривать, но числа занимают больше места, а многие символы экранируются. Двоичный формат компактный, в нем ничего не экранируется. Его структура довольно простая: фиксированный заголовок, потом набор строк. Каждая строка – набор пар (длина, содержимое). Если значение NULL, то длина равна -1, а содержимого нет.

COPY полезен в тестах: вместо того, чтобы вставлять данные штучно, можно хранить файлы CSV с именами таблиц. Специальная фикстура пробегает папку и копирует CSV в нужные таблицы. Файлы можно редактировать в Экселе.

В консоли psql есть команда \copy. Ее синтаксис в точности повторяет запрос COPY. Разница в том, что \copy связывает удаленный сервер с локальной машиной. Например, если выполнить
```
copy users (id, email) to '/path/to/file.csv' with (format csv)
```
, то файл будет создан на сервере Postgres. Если предварить copy обратной чертой, psql запросит с сервера поток и направит в локальный файл /path/to/file.csv. По аналогии работает вставка: \copy читает локальный файл и шлет поток сообщений.

Надеюсь, я смог вас заинтересовать.
Совет дня №5

Jan 15, 2026 postgres, sql
Если в запросе одно из условий – константа, это хороший кандидат на оптимизацию.

Предположим, у нас магазин, и нужно выбрать текущие заказы пользователя. Это примерно такой запрос:
```
select * from orders
where user_id = ? and status = 'active'
```
Из прошлого совета мы знаем, что два индекса на user_id и status не работают. Будет использован только один, скорее всего для user_id, потому что у него высокая селективность (точность). Можно сделать составной индекс (status, user_id), и он будет быстрее обычного user_id.

Есть, однако, еще один вариант: условный индекс по user_id для активных заказов. У оператора create index на конце выражение where:
```
create index idx_users_user_id_active
on orders (user_id)
using btree
where status = 'active'
```
В чем прелесть такого индекса? Он намного меньше аналогов, потому что охватывает не всю таблицу, а ее подмножество. Если магазин появился не вчера, то большая часть заказов находится в статусе “доставлено”, и лишь малая часть активны. Эту часть и охватит индекс.

По условию status = 'active' Postgres определит, что нужно взять именно этот индекс. Фактически условие будет отброшено, потому что значения в индексе уже отфильтрованы по нему.

Скорость такого индекса ошеломительна: запрос может стать быстрее в 10–100 раз – без преувеличений.

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

Jan 15, 2026 postgres, sql
Предположим, в запросе условие по двум полям:
```
select * from items where foo = 1 and bar = 2
```
Разработчик знает про индексы и добавляет их два: один на foo, второй на bar. Логика такая: с одним индексом быстро, с двумя – еще быстрее.

Ан нет: не все так просто. Как правило, Postgres использует только один индекс при обходе, потому что так быстрее. В этом легко убедиться, посмотрев план: там будет индекс либо на foo, либо на bar, но не оба одновременно.

Конечно, бывает, что используются оба индекса. Это возможно, если условия связаны через OR и Postgres выбрал bitmap index scan, используются подзапросы, джоины. Но конкретно в нашем случае индекс будет один.

Если условия соединяются через AND, задайте составной индекс на пару (foo, bar). Он будет быстрее, чем то и другое поле по отдельности.

Порядок полей в составном индексе важен. Желательно располагать их по нарастанию множественности, а кроме того, иметь в виду следующее. Составной индекс может быть использован, если известны его лидирующие компоненты. Например, (foo, bar) сработает, если условие только на foo. По аналогии, индекс (foo, bar, baz) сработает, если заданы foo и bar. Однако для bar и baz он не подхватится.

Планируйте составной индекс так, чтобы охватить как можно больше случаев.
Совет дня №3

Jan 15, 2026 postgres, sql, time
Будьте осторожны с функцией “получить номер недели”. Независимо от языка и платформы, с ней легко прострелить ногу. И когда это случается? Конечно, на стыке годов в предновогоднюю неделю. Лучшее время, чтобы чинить баг.

Дело в том, что номер недели бывает разный. Есть обычный, когда день 2025-12-31 – это неделя 53. А есть ISO-шный, когда 2025-12-31 – это неделя 1. Скажем, в Postgres все способы извлечь неделю являются ISO:
```
select extract(week from '2025-12-31'::date);
-- 1

select to_char('2025-12-31'::date, 'IW');
-- 01
```
В Джаве больше контроля: есть поля weekOfYear и weekOfWeekBasedYear:
```
(.get (java.time.LocalDate/parse "2025-12-31")
      (.weekOfWeekBasedYear java.time.temporal.WeekFields/ISO))
;; 1

(.get (java.time.LocalDate/parse "2025-12-31")
      (.weekOfYear java.time.temporal.WeekFields/ISO))
;; 53
```
Разумеется, их легко перепутать, и файлы уйдут не туда.

Кстати, в корпоративной программе учета часов в декабре нет недели 53. Если работал 29 и 30 декабря, добавляй часы вручную к неделе 52. Подозреваю, что дело в ISO-шном номере 1.

Поздравляю вас с еще одним поводом не расслаблять булки перед праздником.
Совет дня №2

Jan 15, 2026 yyyy, time
Новый год, подарки, “запах мандарин”…, а это значит, пришла пора сделать вот что. Откройте свои проекты и убедитесь, что для форматирования дат используете yyyy-MM-dd, а не YYYY-MM-dd. Буквы года должны быть маленькими!

YYYY возвращает week-based year, а yyyy – обычный календарный. Прелесть в том, стреляет на границе годов. Примеры:
```
(format-date (LocalDate/now) "YYYY-MM-dd")
"2026-12-30"

(format-date (LocalDate/now) "yyyy-MM-dd")
"2025-12-30"
```
Если этого не учесть – мой случай – то за день до нового года файлы уедут в папку с 2026 годом. С чем вас и поздравляю.
Совет дня №1

Jan 15, 2026 postgres, sql
Следите за тем, какие индексы используются, а какие нет. Для этого время от времени проверяйте представление pg_stat_user_indexes. Оно накапливает случаи обращения к индексу. Сохраняются имя схемы, таблицы, индекса, дата последнего обращения, общее число обращений, число прочитанных элементов, число прочитанных строк. Пример:
```
select * from pg_stat_user_indexes
where relname = 'users'
order by last_idx_scan desc nulls last
```
Удалите индексы с малым показателем idx_scan. Сделайте такую проверку регулярной, например раз в месяц.

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