Когда база захлёбывается от телеметрии, первым делом не добавляйте индекс и не увеличивайте число 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 после восстановления связи.
Одна диаграмма показывает место насыщения
| Стадия | Основной сигнал | Признак узкого места |
|---|---|---|
| Broker | Publish rate, queued, dropped | Очередь растёт до consumer |
| Decoder/validator | Messages/s, reject rate, CPU | Broker отстаёт при свободной БД |
| Batch writer | Rows/batch, flush interval, retries | Много маленьких транзакций |
| PostgreSQL | Commit latency, WAL, locks, I/O | Batch готов, commit замедляется |
| Indexes | Размер и write amplification | Insert дорожает с каждым лишним индексом |
| Dashboards | Query duration и scanned rows | Чтение конкурирует с ingest |
| Retention job | Deleted 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 выбирают измерением задержки и памяти, а не универсальным числом.
- Собирать batch до лимита строк, байт или короткого времени — срабатывает первое условие.
- Проверять schema version и типы до открытия транзакции.
- Записывать batch с idempotency key вида device_id + sequence или другой бизнес-идентичностью.
- Подтверждать сообщения broker только после commit.
- При временной ошибке повторять batch с jitter, не меняя его идентификаторы.
- После постоянной ошибки отправлять запись в ограниченный 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, агрегаты или отдельное хранилище.