SQLite в продакшене: 200 GPS-трекеров с локальной базой

Логистическая компания «КаргоТрек» обратилась к специалистам itfresh.ru с нетривиальной задачей. Их парк из 200 GPS-трекеров, установленных на грузовых фургонах, должен был собирать телеметрию — координаты, скорость, температуру груза, состояние двигателя — и передавать данные в центральную систему аналитики на PostgreSQL. Проблема состояла в том, что фургоны регулярно оказывались в зонах без мобильного покрытия: промзоны, подземные паркинги, загородные маршруты.

Предыдущее решение на основе очередей MQTT терял до 15% телеметрии при потере связи. Буфер в оперативной памяти устройства вмещал данные за 20 минут, после чего записи затирались. Клиент терял критически важную информацию о маршрутах и условиях перевозки.

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

Выбор SQLite для edge-устройств обусловлен несколькими факторами. Во-первых, база данных представляет собой один файл на диске — нет процесса-демона, нет сетевого стека, нет конфигурации сервера. Для ARM-устройства с 512 МБ RAM и eMMC-накопителем это идеальный вариант.

Во-вторых, SQLite поддерживает полноценный SQL, включая CTE, оконные функции, UPSERT и JSON-операции. Это позволяет выполнять локальную аналитику прямо на устройстве — например, вычислять среднюю скорость за последний час без обращения к серверу.

Мы рассматривали альтернативы:

• LevelDB / RocksDB — key-value хранилища без SQL, сложнее для аналитических запросов на устройстве.
• Realm / ObjectBox — объектные базы с собственным синтаксисом, привязка к конкретным SDK.
• DuckDB — ориентирован на аналитику, но потребляет больше памяти и не предназначен для встраиваемых систем с ограниченными ресурсами.

SQLite при этом показывает впечатляющую производительность: до 240 000 вставок в секунду на обычном оборудовании. Для наших трекеров, генерирующих 1 запись в секунду, это запас в пять порядков.

Главное заблуждение относительно SQLite — якобы база поддерживает только одного клиента. Это не так. В режиме Write-Ahead Logging (WAL) SQLite позволяет одновременное чтение несколькими потоками, пока один поток выполняет запись. Для нашего сценария это критически важно: один поток записывает телеметрию с датчиков, второй отправляет накопленные данные на сервер, третий обслуживает локальный HTTP-API для водителя.

Включение WAL-режима выполняется одной командой при инициализации базы:

-- Включаем WAL-режим (сохраняется между перезапусками)
PRAGMA journal_mode = WAL;

-- Настраиваем размер WAL-файла: автоматический checkpoint после 1000 страниц
PRAGMA wal_autocheckpoint = 1000;

-- Проверяем текущий режим
PRAGMA journal_mode;

В WAL-режиме изменения записываются в отдельный файл (*.wal), а не напрямую в основную базу. Читатели продолжают работать со стабильной версией данных. Checkpoint — операция переноса изменений из WAL в основной файл — выполняется автоматически или по расписанию.

На наших трекерах мы настроили принудительный checkpoint каждые 5 минут, чтобы WAL-файл не разрастался на ограниченном накопителе:

import sqlite3
import threading
import time

def periodic_checkpoint(db_path, interval=300):
    while True:
        time.sleep(interval)
        conn = sqlite3.connect(db_path)
        conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
        conn.close()

checkpoint_thread = threading.Thread(
    target=periodic_checkpoint,
    args=("/data/telemetry.db",),
    daemon=True
)
checkpoint_thread.start()

SQLite из коробки настроен максимально безопасно, что иногда означает «максимально медленно». Для каждого устройства «КаргоТрек» мы подобрали набор PRAGMA-директив, балансирующих между производительностью и надёжностью.

-- Синхронизация с диском: NORMAL вместо FULL
-- В WAL-режиме NORMAL обеспечивает целостность при сбое питания,
-- теряя максимум последнюю транзакцию
PRAGMA synchronous = NORMAL;

-- Размер кэша страниц в памяти: отрицательное значение = килобайты
-- -8000 = 8 МБ кэша (по умолчанию -2000 = 2 МБ)
PRAGMA cache_size = -8000;

-- Размер memory-mapped I/O: 64 МБ
-- Ускоряет чтение, позволяя ОС кэшировать файл через mmap
PRAGMA mmap_size = 67108864;

-- Хранение временных таблиц в памяти
PRAGMA temp_store = MEMORY;

-- Размер страницы 4096 (совпадает с размером блока eMMC)
PRAGMA page_size = 4096;

-- Включаем foreign keys (по умолчанию выключены)
PRAGMA foreign_keys = ON;

-- Автоматическая очистка удалённых страниц
PRAGMA auto_vacuum = INCREMENTAL;

Отдельно стоит упомянуть параметр busy_timeout. При конкурентном доступе один из потоков может получить ошибку SQLITE_BUSY, если база заблокирована записью. Мы выставили таймаут ожидания в 5 секунд:

PRAGMA busy_timeout = 5000;

С этими настройками скорость вставки на ARM-устройстве выросла с 800 записей/с до 12 000 записей/с — более чем достаточно для телеметрии с частотой 1 Гц.

Схема базы на каждом трекере спроектирована для компактности и быстрой синхронизации:

CREATE TABLE telemetry (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ts REAL NOT NULL,              -- Unix timestamp с миллисекундами
    lat REAL NOT NULL,
    lon REAL NOT NULL,
    speed REAL DEFAULT 0,
    heading INTEGER DEFAULT 0,
    temp_cargo REAL,               -- Температура груза
    engine_rpm INTEGER,
    fuel_level REAL,
    synced INTEGER DEFAULT 0,      -- 0 = не отправлено, 1 = отправлено
    created_at TEXT DEFAULT (datetime('now'))
);

-- Индекс для быстрой выборки неотправленных записей
CREATE INDEX idx_telemetry_synced ON telemetry(synced) WHERE synced = 0;

-- Индекс для локальных аналитических запросов по времени
CREATE INDEX idx_telemetry_ts ON telemetry(ts);

-- Таблица событий (резкое торможение, превышение скорости)
CREATE TABLE events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telemetry_id INTEGER REFERENCES telemetry(id),
    event_type TEXT NOT NULL,
    severity TEXT CHECK(severity IN ('info','warning','critical')),
    payload TEXT,  -- JSON с деталями
    synced INTEGER DEFAULT 0
);

Локально на устройстве выполняются аналитические запросы для отображения водителю на планшете:

-- Средняя скорость за последний час
SELECT AVG(speed) as avg_speed,
       MAX(speed) as max_speed,
       COUNT(*) as points
FROM telemetry
WHERE ts > unixepoch() - 3600;

-- Общий пробег за смену (упрощённый расчёт через расстояние Хаверсайна)
WITH pairs AS (
    SELECT lat, lon,
           LAG(lat) OVER (ORDER BY ts) as prev_lat,
           LAG(lon) OVER (ORDER BY ts) as prev_lon
    FROM telemetry
    WHERE ts > unixepoch() - 28800  -- 8-часовая смена
)
SELECT SUM(
    6371 * 2 * ASIN(SQRT(
        POWER(SIN(RADIANS(lat - prev_lat) / 2), 2) +
        COS(RADIANS(prev_lat)) * COS(RADIANS(lat)) *
        POWER(SIN(RADIANS(lon - prev_lon) / 2), 2)
    ))
) as distance_km
FROM pairs
WHERE prev_lat IS NOT NULL;

Механизм синхронизации работает по принципу «отправь и подтверди». Каждая запись имеет флаг synced. Фоновый процесс на устройстве раз в 30 секунд пытается отправить пакет неотправленных записей:

import sqlite3
import requests
import json

BATCH_SIZE = 500
SERVER_URL = "https://api.kargotrack.ru/telemetry/ingest"

def sync_batch(db_path, device_id):
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row

    # Выбираем пакет неотправленных записей
    rows = conn.execute("""
        SELECT id, ts, lat, lon, speed, heading,
               temp_cargo, engine_rpm, fuel_level
        FROM telemetry
        WHERE synced = 0
        ORDER BY ts
        LIMIT ?
    """, (BATCH_SIZE,)).fetchall()

    if not rows:
        conn.close()
        return 0

    payload = {
        "device_id": device_id,
        "records": [dict(r) for r in rows]
    }

    try:
        resp = requests.post(SERVER_URL, json=payload, timeout=10)
        resp.raise_for_status()

        # Помечаем как отправленные
        ids = [r["id"] for r in rows]
        placeholders = ",".join("?" * len(ids))
        conn.execute(f"""
            UPDATE telemetry SET synced = 1
            WHERE id IN ({placeholders})
        """, ids)
        conn.commit()
        return len(ids)

    except requests.RequestException:
        # Нет связи — данные останутся для следующей попытки
        return 0
    finally:
        conn.close()

На серверной стороне данные загружаются в PostgreSQL через COPY-команду для максимальной скорости. Один сервер принимает телеметрию со всех 200 трекеров — около 12 000 записей в секунду в пиковые часы.

На edge-устройствах потеря базы — катастрофа: данные за время офлайна будут утрачены безвозвратно. Мы реализовали двухуровневую защиту.

Уровень 1: встроенная команда .backup

SQLite имеет встроенный механизм горячего бэкапа, который создаёт консистентную копию базы даже во время активной записи:

import sqlite3

def backup_database(source_path, backup_path):
    source = sqlite3.connect(source_path)
    backup = sqlite3.connect(backup_path)

    with backup:
        source.backup(backup, pages=100, progress=backup_progress)

    backup.close()
    source.close()

def backup_progress(status, remaining, total):
    print(f"Скопировано {total - remaining}/{total} страниц")

# Запуск по cron каждые 6 часов
# Бэкап сохраняется на отдельный раздел SD-карты
backup_database("/data/telemetry.db", "/backup/telemetry_backup.db")

Уровень 2: Litestream для непрерывной репликации

Litestream — это стриминговый инструмент репликации SQLite в S3-совместимое хранилище. Он работает как отдельный процесс, отслеживает изменения в WAL-файле и передаёт их в облако в режиме реального времени:

# /etc/litestream.yml
dbs:
  - path: /data/telemetry.db
    replicas:
      - url: s3://kargotrack-backups/devices/device-042/telemetry.db
        region: ru-central1
        endpoint: https://storage.yandexcloud.net
        retention: 720h       # Хранить снапшоты 30 дней
        sync-interval: 60s    # Отправлять WAL каждые 60 секунд
        snapshot-interval: 24h # Полный снапшот раз в сутки

# Запуск Litestream как systemd-сервиса
sudo systemctl enable litestream
sudo systemctl start litestream

# Восстановление базы на новом устройстве
litestream restore -o /data/telemetry.db \
  s3://kargotrack-backups/devices/device-042/telemetry.db

Litestream обеспечивает RPO (Recovery Point Objective) в 60 секунд — при полной потере устройства мы теряем максимум минуту данных. При этом трафик минимален: передаются только дельты WAL-файла, что составляет 5-50 КБ на передачу.

По итогам проекта «КаргоТрек» мы сформулировали критерии выбора SQLite вместо PostgreSQL:

• Встраиваемые устройства — нет ресурсов для серверного процесса СУБД.
• Локальное хранение с периодической синхронизацией — данные сначала сохраняются на устройстве, потом отправляются в центральную базу.
• Один писатель — если приложение имеет единственный процесс записи, WAL-режим обеспечивает достаточную конкурентность.
• Объём данных до 100 ГБ — SQLite стабильно работает с базами такого размера при правильном индексировании.
• Десктопные и мобильные приложения — нулевая конфигурация, файл копируется вместе с приложением.

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

Результаты внедрения в «КаргоТрек»:

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