TL;DR:

  • SQLite is a strong default for local data storage on edge devices: it requires no server process, has a tiny footprint (~600KB), handles concurrent reads well, and is extremely reliable under power loss with WAL mode enabled
  • For devices with very limited RAM or flash storage, use WAL mode with a journal size limit and synchronise to the cloud periodically rather than on every write
  • SQLite works well for sensor history, configuration storage, and event queues at the edge; it’s not the right choice for high-write-rate telemetry where you need sub-millisecond latency

Edge devices — Raspberry Pi gateways, Jetson inference modules, industrial panel PCs, smart building controllers — often need to store data locally. Sometimes for offline resilience, sometimes for local analytics, sometimes simply as a staging buffer before syncing to the cloud. The question of which database to use is less exciting than it might seem: for the vast majority of edge use cases, SQLite is the right answer.

Why SQLite Works Well at the Edge

SQLite is a file-based embedded database, not a client-server database. There’s no daemon to run, no port to expose, no configuration to manage. The entire database is a single file. The library itself is around 600KB. It runs on hardware with as little as 4MB of flash.

Compare that to PostgreSQL (hundreds of megabytes, requires a server process, complex configuration) or MySQL. For edge devices where you’re managing resources carefully, SQLite’s simplicity is a genuine advantage.

Reliability under power loss: Edge devices get their power cut. Industrial environments see brownouts and unexpected shutdowns constantly. SQLite in WAL mode (Write-Ahead Logging) handles this well. A write either completes fully or doesn’t appear at all — the database doesn’t end up in a corrupted intermediate state. This isn’t guaranteed with naive file I/O or with some other embedded databases.

Read concurrency: Multiple processes on the same device can read from a SQLite database simultaneously without blocking each other. If you have a sensor collection process, a local dashboard web server, and a sync agent all running on the same gateway, they can all query the local SQLite database without needing a central server to coordinate them.

SQL: It’s a real SQL database. Filtering, joining, aggregating, indexing — all available. This matters when your edge application needs to do meaningful local data processing before deciding what to send upstream.

Configuration for Edge Environments

The defaults aren’t always right for edge hardware. A few pragmas to set when opening the database:

import sqlite3

def open_db(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path)
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")  # safe with WAL; faster than FULL
    conn.execute("PRAGMA cache_size = -2000")     # 2MB page cache
    conn.execute("PRAGMA wal_autocheckpoint = 100")  # checkpoint every 100 pages
    conn.execute("PRAGMA temp_store = MEMORY")
    conn.execute("PRAGMA mmap_size = 67108864")  # 64MB mmap (if RAM allows)
    return conn

WAL mode: Critical for edge devices. WAL separates reads from writes and provides much better behaviour under concurrent access and power interruptions. Always use it.

synchronous = NORMAL: With WAL mode, NORMAL provides durability within a reasonable window (data is safe after a crash, though there’s a small window where data written in the last milliseconds before a power cut might be lost). If you need absolute write durability at the cost of throughput, use FULL.

cache_size: Negative values are in kibibytes. On a Raspberry Pi with 8GB RAM, a 2MB cache is fine. On a device with 256MB total RAM, you might want 512KB or less.

Handling High Write Rates

SQLite handles most edge write loads comfortably. A Raspberry Pi can sustain several thousand inserts per second for small rows. If you’re storing sensor readings at 1Hz from a handful of sensors, SQLite is more than fast enough.

Where it struggles is high-rate telemetry with thousands of writes per second on slow flash storage. eMMC and SD card write latency is the bottleneck, not SQLite itself.

For high write rates, batch writes in a transaction rather than committing each row individually:

def batch_insert(conn: sqlite3.Connection, readings: list[dict]):
    with conn:  # transaction context manager
        conn.executemany(
            "INSERT INTO sensor_readings (ts, sensor_id, value) VALUES (?, ?, ?)",
            [(r["ts"], r["sensor_id"], r["value"]) for r in readings]
        )

A thousand individual inserts takes ~1 second. A thousand inserts in a single transaction takes ~10 milliseconds. Batching writes is the single biggest performance improvement available for SQLite on write-heavy workloads.

Data Retention and Storage Limits

Edge devices have limited storage. Left unchecked, a SQLite database can grow without bound as historical data accumulates. Build retention in from the start:

def prune_old_readings(conn: sqlite3.Connection, retention_days: int = 30):
    cutoff = int(time.time()) - (retention_days * 86400)
    conn.execute(
        "DELETE FROM sensor_readings WHERE ts < ?",
        (cutoff,)
    )
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")

Run this on a schedule (daily is usually sufficient). After deleting rows, the WAL file doesn’t shrink automatically — the wal_checkpoint(TRUNCATE) call truncates it.

For devices with very limited storage, consider SQLite’s max_page_count pragma to hard-limit database size:

# Limit database to 512MB
conn.execute("PRAGMA max_page_count = 131072")  # 131072 * 4096 bytes = 512MB

Syncing to the Cloud

A common pattern on edge devices is local-first storage: the device stores data in SQLite, a sync agent periodically reads and uploads it to the cloud, then marks rows as synced.

CREATE TABLE sensor_readings (
    id INTEGER PRIMARY KEY,
    ts INTEGER NOT NULL,
    sensor_id TEXT NOT NULL,
    value REAL NOT NULL,
    synced INTEGER NOT NULL DEFAULT 0
);

CREATE INDEX idx_unsynced ON sensor_readings(synced) WHERE synced = 0;

The sync agent selects unsynced rows, uploads them in batches, then marks them synced:

def sync_readings(conn, upload_fn):
    rows = conn.execute(
        "SELECT id, ts, sensor_id, value FROM sensor_readings WHERE synced = 0 LIMIT 1000"
    ).fetchall()
    if not rows:
        return
    
    upload_fn([{"ts": r[1], "sensor_id": r[2], "value": r[3]} for r in rows])
    
    ids = [r[0] for r in rows]
    conn.execute(
        f"UPDATE sensor_readings SET synced = 1 WHERE id IN ({','.join('?' * len(ids))})",
        ids
    )
    conn.commit()

This pattern gives you local resilience (data persists even if the network is down) plus eventual consistency with the cloud, without needing a more complex message queue.

When SQLite Is Not the Right Choice

SQLite works well for edge devices with moderate write rates, local analytics, and offline-first requirements. It’s not the right tool if:

  • You need sub-millisecond write latency for very high-rate telemetry (consider a time-series database like TimescaleDB or InfluxDB, or simply writing to flat files with periodic aggregation)
  • Multiple devices need to share a single database over the network (SQLite is local-only; use a proper client-server database for shared access)
  • You’re running on microcontrollers with kilobytes of RAM (SQLite has a minimum memory footprint that’s still too large for some constrained MCUs; consider a key-value store like NVS on ESP32)

For everything else on Linux-based edge hardware, SQLite is reliable, fast enough, and simple. Start with it.