Ты в точку попал — если требования прыгают каждую неделю ("а теперь по моделям", "а теперь по водителям", "а теперь по пробегу"), то предрасчёт только по "актив → площадка → день" может оказаться бесполезным, и придётся либо всё перестраивать, либо держать сырые интервалы.
Но тут как раз фишка в том, что если хранить сырые интервалы в обычной таблице (как в статье), а предвычисленные снэпшоты по дням — в key-value, то ты получаешь и то, и другое:
Сырые интервалы — для гибкости, когда начальник придумал новый срез (добавляешь поле в таблицу или отдельную табличку — и всё).
Предвычисленные битмапы — только для самого горячего запроса ("где был список активов на дату"), который обычно 90% нагрузки и должен летать.
Обновляешь битмапы раз в сутки батчем — и никаких проблем с новыми требованиями, потому что сырые данные остаются на месте. А если новый срез тоже стал горячим — просто добавляешь ещё один слой предрасчёта (битмапы по новому измерению).
По времени расчётов — да, в реляционке оно предсказуемое, но в key-value для типичного запроса оно просто на порядок меньше и тоже предсказуемое (один get + пару микросекунд на битмапы).
Короче, я не за "бросаем всё в NoSQL и в кучу", а за гибрид: сырые данные в Postgres/CH (где удобно), а горячие ежедневные снэпшоты — в key-value для скорости. Так и начальник доволен (новые срезы быстро добавляются), и пользователи не ждут по 10 мс на каждый отчёт :)
А автор как раз показал крутой инструмент для сырых данных — респект ему за это! Просто я добавил вариант, как выжать ещё больше скорости из самого частого сценария. Всё от задачи, как ты и сказал 👍
Ок, понял, ты прав насчёт ACID и конкурентных обновлений — если история меняется онлайн с разных сторон, то PostgreSQL с exclusion constraint'ами реально надёжнее всего, и спорить глупо.
Но в 90% случаев с учётом спецтехники/ОС обновления приходят батчами раз в день/неделю из 1С или ERP, а днём только читают тоннами. Вот тут key-value с предвычисленными битмапами просто рвёт PostgreSQL:
Чтение: <1 мс на 1000 активов против 5–15 мс в твоём тесте.
Размер: 25–30 МБ против 150–200+ МБ с индексами.
Сложные вопросы типа «кто был на площадках 10–15 за месяц» — пара операций над битмапами, без подзапросов и window-функций.
Короче, если у вас обновления не онлайн и не критичны по секундам — делайте предрасчёт в key-value, будет в разы быстрее и легче. Если же реально конкурентная запись — оставайтесь на Postgres, там всё честно и без сюрпризов.
Крутая статья, спасибо за детальный разбор с PostgreSQL и daterange — это реально полезный подход для реляционных баз, особенно когда нужно гарантировать целостность данных на уровне БД. Но давай честно: SQL здесь мягко говоря не самый оптимальный вариант, особенно если задача — максимизировать скорость чтения и минимизировать место, а не мучаться с обновлениями и индексами. Я бы предложил перейти на key-value базу данных с предвычисленными данными по дням и использованием Roaring Bitmaps для сжатия множеств активов. Это как раз то, что позволит выжать максимум производительности без компромиссов.
Давай сравню наглядно твой подход с PostgreSQL (на основе твоих тестов) и альтернативный:
По времени исполнения запросов:
- В PostgreSQL: для 333 активов на дату — 3 мс (с BTree-индексом), но это с join'ами, сканированием интервалов и ORDER BY/LIMIT. Если список активов вырастет до 1000, или добавятся фильтры — время легко уйдёт в 5-10 мс+ из-за overhead'а индексов.
- В key-value с Roaring: один точечный lookup по ключу (дню) — <1 мс даже для 1000 активов. Почему? Данные предвычислены: загружаешь один blob (~2-3 КБ), десериализуешь в массив из 100 битмапов, и для каждого актива просто проверяешь contains() в O(1). Нет join'ов, нет сканирования — чистая скорость, как в кэше.
По объёму занимаемого места:
- В PostgreSQL: 1.27 млн записей + индексы (GIST и BTree) — легко 100-200 МБ+ на диске, особенно с overhead'ом на строки, даты и проверки целостности. Индексы сами по себе жрут место и замедляют вставки.
- В key-value с Roaring: вся история (~9500 дней) укладывается в <30 МБ. Каждый день — один ключ с сжатым blob'ом из 100 Roaring Bitmaps (по 20-30 байт на площадку, т.к. в среднем 10 активов на ней). Сжатие Roaring бьёт любые таблицы: нет дублирования дат, нет строк — чистые биты.
По сложности более сложных фильтров:
- В PostgreSQL: базовый запрос на дату — ок, но если добавить фильтры (например, "активы на площадках X и Y за период" или "пересечение с другими условиями")? Придётся городить подзапросы, window functions или даже материализованные views — это усложняет код, замедляет (время вырастет в разы) и требует тюнинга индексов. Целостность круто, но для чтения-ориентированной задачи это overkill.
- В key-value с Roaring: фильтры — это нативные операции над битмапами! Например, пересечение (intersection) двух площадок — bitmap1 & bitmap2 в O(N), где N маленькое. Хочешь активы на нескольких площадках за день? Просто загрузи данные дня и сделай bitwise ops. Для периода — загрузи несколько дней (параллельно, если нужно) и union/intersect. Это проще в коде, быстрее (миллисекунды) и масштабируемо на аналитику без перестройки индексов. Плюс key-value даёт преимущества в поиске: атомарные get'ы без блокировок, лёгкая репликация и кэширование, а Roaring позволяет делать сложные set-операции (union, difference) на лету, чего в SQL без костылей не добьёшься.
В общем, если твоя задача — не частые обновления, а быстрые чтения (как в аналитике или отчётах), то SQL здесь выглядит архаично: медленнее, жирнее и сложнее в расширении.
Мне кажется лучше даже не начинать проек с sql базой. За кажущейся легкостью в начале потом настает час расплаты и много боли. Лучше юзать самую простую бд типа ключ значение и хорошо думать как все индексировать на лету. UUID - dense id связи, roaring bitmap и т.д. "Отшардировать" потом будет тоже проще
А почему не используете ZFS в своей системе? Она позволяет делать "моментальные снэпшоты" с минимальным потреблением ресурсов (чем-то ее работа аналогична гиту). Клиент сможет делать снэпшоты хоть каждую минуту. А для сохранности сами данных, вроде как можно пул настроить так, что репликация будет работать по сети, причем с минимальным потреблением трафика (отправляется только то что изменилось). + сквозная шифрока и компрессия работает. Если я что-то упускаю - было бы интересно почитать ваше мнение.
llm рано или поздно сольет депозит также как и человек, т.к он похож на человека.
Ты в точку попал — если требования прыгают каждую неделю ("а теперь по моделям", "а теперь по водителям", "а теперь по пробегу"), то предрасчёт только по "актив → площадка → день" может оказаться бесполезным, и придётся либо всё перестраивать, либо держать сырые интервалы.
Но тут как раз фишка в том, что если хранить сырые интервалы в обычной таблице (как в статье), а предвычисленные снэпшоты по дням — в key-value, то ты получаешь и то, и другое:
Сырые интервалы — для гибкости, когда начальник придумал новый срез (добавляешь поле в таблицу или отдельную табличку — и всё).
Предвычисленные битмапы — только для самого горячего запроса ("где был список активов на дату"), который обычно 90% нагрузки и должен летать.
Обновляешь битмапы раз в сутки батчем — и никаких проблем с новыми требованиями, потому что сырые данные остаются на месте. А если новый срез тоже стал горячим — просто добавляешь ещё один слой предрасчёта (битмапы по новому измерению).
По времени расчётов — да, в реляционке оно предсказуемое, но в key-value для типичного запроса оно просто на порядок меньше и тоже предсказуемое (один get + пару микросекунд на битмапы).
Короче, я не за "бросаем всё в NoSQL и в кучу", а за гибрид: сырые данные в Postgres/CH (где удобно), а горячие ежедневные снэпшоты — в key-value для скорости. Так и начальник доволен (новые срезы быстро добавляются), и пользователи не ждут по 10 мс на каждый отчёт :)
А автор как раз показал крутой инструмент для сырых данных — респект ему за это! Просто я добавил вариант, как выжать ещё больше скорости из самого частого сценария. Всё от задачи, как ты и сказал 👍
Ок, понял, ты прав насчёт ACID и конкурентных обновлений — если история меняется онлайн с разных сторон, то PostgreSQL с exclusion constraint'ами реально надёжнее всего, и спорить глупо.
Но в 90% случаев с учётом спецтехники/ОС обновления приходят батчами раз в день/неделю из 1С или ERP, а днём только читают тоннами. Вот тут key-value с предвычисленными битмапами просто рвёт PostgreSQL:
Чтение: <1 мс на 1000 активов против 5–15 мс в твоём тесте.
Размер: 25–30 МБ против 150–200+ МБ с индексами.
Сложные вопросы типа «кто был на площадках 10–15 за месяц» — пара операций над битмапами, без подзапросов и window-функций.
Короче, если у вас обновления не онлайн и не критичны по секундам — делайте предрасчёт в key-value, будет в разы быстрее и легче. Если же реально конкурентная запись — оставайтесь на Postgres, там всё честно и без сюрпризов.
Крутая статья, спасибо за детальный разбор с PostgreSQL и daterange — это реально полезный подход для реляционных баз, особенно когда нужно гарантировать целостность данных на уровне БД. Но давай честно: SQL здесь мягко говоря не самый оптимальный вариант, особенно если задача — максимизировать скорость чтения и минимизировать место, а не мучаться с обновлениями и индексами. Я бы предложил перейти на key-value базу данных с предвычисленными данными по дням и использованием Roaring Bitmaps для сжатия множеств активов. Это как раз то, что позволит выжать максимум производительности без компромиссов.
Давай сравню наглядно твой подход с PostgreSQL (на основе твоих тестов) и альтернативный:
По времени исполнения запросов:
- В PostgreSQL: для 333 активов на дату — 3 мс (с BTree-индексом), но это с join'ами, сканированием интервалов и ORDER BY/LIMIT. Если список активов вырастет до 1000, или добавятся фильтры — время легко уйдёт в 5-10 мс+ из-за overhead'а индексов.
- В key-value с Roaring: один точечный lookup по ключу (дню) — <1 мс даже для 1000 активов. Почему? Данные предвычислены: загружаешь один blob (~2-3 КБ), десериализуешь в массив из 100 битмапов, и для каждого актива просто проверяешь contains() в O(1). Нет join'ов, нет сканирования — чистая скорость, как в кэше.
По объёму занимаемого места:
- В PostgreSQL: 1.27 млн записей + индексы (GIST и BTree) — легко 100-200 МБ+ на диске, особенно с overhead'ом на строки, даты и проверки целостности. Индексы сами по себе жрут место и замедляют вставки.
- В key-value с Roaring: вся история (~9500 дней) укладывается в <30 МБ. Каждый день — один ключ с сжатым blob'ом из 100 Roaring Bitmaps (по 20-30 байт на площадку, т.к. в среднем 10 активов на ней). Сжатие Roaring бьёт любые таблицы: нет дублирования дат, нет строк — чистые биты.
По сложности более сложных фильтров:
- В PostgreSQL: базовый запрос на дату — ок, но если добавить фильтры (например, "активы на площадках X и Y за период" или "пересечение с другими условиями")? Придётся городить подзапросы, window functions или даже материализованные views — это усложняет код, замедляет (время вырастет в разы) и требует тюнинга индексов. Целостность круто, но для чтения-ориентированной задачи это overkill.
- В key-value с Roaring: фильтры — это нативные операции над битмапами! Например, пересечение (intersection) двух площадок — bitmap1 & bitmap2 в O(N), где N маленькое. Хочешь активы на нескольких площадках за день? Просто загрузи данные дня и сделай bitwise ops. Для периода — загрузи несколько дней (параллельно, если нужно) и union/intersect. Это проще в коде, быстрее (миллисекунды) и масштабируемо на аналитику без перестройки индексов. Плюс key-value даёт преимущества в поиске: атомарные get'ы без блокировок, лёгкая репликация и кэширование, а Roaring позволяет делать сложные set-операции (union, difference) на лету, чего в SQL без костылей не добьёшься.
В общем, если твоя задача — не частые обновления, а быстрые чтения (как в аналитике или отчётах), то SQL здесь выглядит архаично: медленнее, жирнее и сложнее в расширении.
Мне кажется лучше даже не начинать проек с sql базой. За кажущейся легкостью в начале потом настает час расплаты и много боли. Лучше юзать самую простую бд типа ключ значение и хорошо думать как все индексировать на лету. UUID - dense id связи, roaring bitmap и т.д. "Отшардировать" потом будет тоже проще
А почему не используете ZFS в своей системе? Она позволяет делать "моментальные снэпшоты" с минимальным потреблением ресурсов (чем-то ее работа аналогична гиту). Клиент сможет делать снэпшоты хоть каждую минуту. А для сохранности сами данных, вроде как можно пул настроить так, что репликация будет работать по сети, причем с минимальным потреблением трафика (отправляется только то что изменилось). + сквозная шифрока и компрессия работает. Если я что-то упускаю - было бы интересно почитать ваше мнение.