Когда база захлёбывается от телеметрии, первым делом не добавляйте индекс и не увеличивайте число consumers. Снимите четыре ряда на одной временной шкале: сообщения на входе, строки после преобразования, подтверждённые записи в базе и глубину очереди. Если вход выше commit rate, backlog растёт математически; нужно менять запись или объём данных, а не маскировать отставание.

Надёжная схема отделяет быстрый приём от более медленной базы ограниченным durable buffer. Consumer собирает небольшие batches, записывает их одной транзакцией и подтверждает исходные сообщения только после успешного commit. Лимит очереди, политика повтора и retention задаются заранее, иначе краткий пик превращается в заполненный диск.

Начните с бюджета потока, а не с названия СУБД

devices = 12_000
samples_per_device_per_minute = 6
rows_per_sample = 4

rows_per_second = devices * samples_per_device_per_minute * rows_per_sample / 60
# 4 800 rows/s до повторов и служебных записей

bytes_per_second = rows_per_second * measured_row_bytes
backlog_growth = incoming_rows_per_second - committed_rows_per_second
drain_seconds = queued_rows / (committed_rows_per_second - live_incoming_rows_per_second)

Это условный расчёт: measured_row_bytes берут из реального batch с учётом индексов, WAL и служебных колонок, а не из длины JSON. Отдельно считайте сообщения и строки — одно MQTT-сообщение может раскрыться в несколько измерений. Добавьте повторы QoS, поздние данные и retransmit после восстановления связи.

Одна диаграмма показывает место насыщения

СтадияОсновной сигналПризнак узкого места
BrokerPublish rate, queued, droppedОчередь растёт до consumer
Decoder/validatorMessages/s, reject rate, CPUBroker отстаёт при свободной БД
Batch writerRows/batch, flush interval, retriesМного маленьких транзакций
PostgreSQLCommit latency, WAL, locks, I/OBatch готов, commit замедляется
IndexesРазмер и write amplificationInsert дорожает с каждым лишним индексом
DashboardsQuery duration и scanned rowsЧтение конкурирует с ingest
Retention jobDeleted rows и lock timeОчистка создаёт длинные блокировки

Собирайте метрики с одинаковыми timestamps. Средняя загрузка CPU не объясняет минутный стоп диска, а средний insert latency скрывает хвост. Для базы смотрите p95/p99 commit, WAL rate, checkpoints, активные запросы и I/O; для конвейера — возраст самой старой непринятой записи, а не только количество сообщений.

Пакетная запись уменьшает цену одной строки

Отправлять каждый sample отдельной транзакцией дорого: на каждую строку повторяются сетевой round trip, parse/plan и commit. PostgreSQL рекомендует для массовой загрузки COPY, который оптимизирован для большого числа строк; в постоянно работающем сервисе часто используют bounded batches и prepared inserts, если COPY неудобен для текущего протокола. Размер batch выбирают измерением задержки и памяти, а не универсальным числом.

  1. Собирать batch до лимита строк, байт или короткого времени — срабатывает первое условие.
  2. Проверять schema version и типы до открытия транзакции.
  3. Записывать batch с idempotency key вида device_id + sequence или другой бизнес-идентичностью.
  4. Подтверждать сообщения broker только после commit.
  5. При временной ошибке повторять batch с jitter, не меняя его идентификаторы.
  6. После постоянной ошибки отправлять запись в ограниченный quarantine, не блокируя весь поток.

QoS 1 допускает повторную доставку, поэтому повтор после неясного результата commit должен быть безопасным. Уникальный индекс помогает дедупликации, но сам тоже увеличивает цену вставки; включайте в него только поля, которые действительно определяют одну запись, и измеряйте write amplification.

Сырые данные и отчёты живут в разных слоях

ПотребностьПодходящий слойПочему
Последние значения устройстваОтдельная current-state таблица/кешНе сканирует всю историю
Подробная история за короткий срокПартиции или time chunksЗапись и поиск ограничены окном
График за месяцМинутные/часовые агрегатыМеньше строк для каждого просмотра
Архив для редких расследованийХолодное хранилищеНе конкурирует с горячим ingest
Удаление по срокуDetach/drop старой партицииНе выполняет огромный row-by-row DELETE

PostgreSQL partitioning разделяет логическую таблицу на физические части и позволяет быстро убирать старое окно через detach или drop partition. Выигрыш зависит от запросов и размера таблицы; сотни слишком мелких пустых партиций могут увеличить planning overhead. Границы создают заранее и проверяют путь для поздних timestamps.

В Timescale hypertable время разбивается на chunks; continuous aggregates инкрементально поддерживают сводные окна. Это вариант реализации, а не обязательная миграция. Если обычный PostgreSQL с range partitions и материализованными сводками укладывается в SLO, смена технологии сама по себе ничего не добавит.

Retention начинается с требований к данным

  • Для каждого типа измерения задайте срок сырого ряда и срок агрегатов.
  • Зафиксируйте, нужны ли поздние события после закрытия временного окна.
  • Отделите юридически значимые события от технической телеметрии.
  • Проверьте, что backup и replica не удерживают объём, который уже удалён из primary.
  • Создавайте следующую time partition до наступления её диапазона.
  • Следите за свободным местом с запасом на WAL, compaction и восстановление.

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

Нагрузочный тест должен закончиться исчезновением backlog

Сценарий состоит из стабильной нагрузки, короткого пика и периода восстановления. Генератор использует распределение payload и reconnect, похожее на парк устройств, но синтетические device IDs. Во время пика допускается контролируемый рост очереди; после него committed rate должен превысить live input, возраст oldest message — вернуться к baseline, а dashboards — оставаться в своём SLO.

Для точного разбора сохраните схему таблиц и индексов, размер реальной записи, rates по стадиям, batch policy, очередь и её лимиты, retention, PostgreSQL version и графики WAL/I/O. Payload с координатами, серийными номерами и ключами устройств замените синтетическими значениями. По этим данным можно решить, нужен ли новый writer, partitioning, агрегаты или отдельное хранилище.

Проверенные источники