SQLite vs DuckDB vs PostgreSQL on Edge Gateways: Choosing an Embedded Database for IoT Telemetry and Local Analytics (2026)
On a typical industrial gateway, the biggest database decision is not which engine you pick. It is the order of the columns in your primary key. In our test, one SQLite schema wrote about 2.5 MB of write-ahead log per one-second batch of 500 readings. The same rows with the key columns swapped wrote about 38 KB. That is a 68-fold difference in flash wear from a one-line change. The sqlite vs duckdb debate usually skips this, and so does the question of whether PostgreSQL belongs on a gateway at all. It matters more in 2026 because gateways now do real work: local alarms, rollups, offline buffering for hours or days, and Parquet exports for the lakehouse. All of it runs on eMMC or SD flash with a finite write budget. You will leave with a clear split of jobs between the three engines, verified durability semantics, worked sizing numbers, tested Python code, and a decision matrix.
What this covers: current versions (SQLite 3.54, DuckDB 2.0 LTS, PostgreSQL 18 and the PostgreSQL 19 release candidate), SQLite WAL mode and checkpointing, synchronous=NORMAL vs FULL, single-writer concurrency, DuckDB’s columnar engine and process model, PostgreSQL’s footprint on ARM, flash write amplification, power-loss safety, sizing math, store-and-forward sync, failure modes and a decision matrix.
Context and Background
An edge gateway sits between field devices and the cloud. It polls PLCs over Modbus or OPC UA, subscribes to MQTT, normalises readings, and forwards them upstream. Our IIoT edge gateway architecture guide covers the full software stack. This post is about one layer of it: the local database.
For years, that layer was an afterthought. Gateways buffered a few minutes of data in RAM or in a broker queue and pushed everything to a cloud time-series database. Three things changed. First, sites now expect hours or days of offline operation, because cellular and VPN links fail. Second, local analytics moved to the edge: vibration features, energy rollups and OEE counters are computed on site to cut bandwidth. Third, the analytics stack itself changed. Columnar files and in-process engines made it practical to run real SQL analytics on a four-core ARM board.
Three engines dominate the shortlist. SQLite is the embedded transactional default. It is a single C library that reads and writes one file, and the SQLite project lists version 3.54.1, released on 2026-10-16, as the latest release. DuckDB is the embedded analytical engine: columnar storage, vectorised execution, and native Parquet support. DuckDB 2.0.0 LTS shipped on 2026-10-21, with support promised until at least October 2027. PostgreSQL is the full client-server relational database. PostgreSQL 18.7 is the latest minor release of the current major version. PostgreSQL 19 reached release candidate 1 on 2026-10-15, with general availability planned for 2026-11-19, two days after this post.
Most comparisons treat these as rivals for one slot. That framing is wrong for gateways. They solve different problems, and the interesting design question is how to split the work between them. For the cloud side of the same pipeline, see our comparison of PostgreSQL 18 vs TimescaleDB vs ClickHouse for IoT. Here we stay on the box, where RAM is measured in gigabytes, storage is flash, and the power cord can be pulled at any moment.
SQLite vs DuckDB vs PostgreSQL: The Edge Gateway Reference Architecture
For an edge gateway, SQLite vs DuckDB is not either-or. Use SQLite in WAL mode as the transactional hot store for ingest, alarms and the store-and-forward outbox. Use DuckDB for heavy local analytics and Parquet export, reading SQLite read-only. Reserve PostgreSQL for gateways that must serve many concurrent clients.

Figure 1: A reference architecture for an embedded database on an IoT gateway. SQLite owns writes; DuckDB owns analytics; the outbox owns delivery.
Figure 1 shows the data path. A collector batches readings once per second and commits them to a SQLite database in WAL mode. A rollup job folds raw readings into one-minute aggregates and writes each result into an outbox table in the same transaction. An uplink process drains the outbox over MQTT and advances a cursor only after the broker acknowledges. Separately, DuckDB attaches the SQLite file read-only, runs the heavy queries, and writes compressed daily Parquet files for bulk upload. The local HMI and alarm logic read SQLite directly.
Why SQLite owns the write path
The write path on a gateway has three properties. It is a steady stream of small inserts. It must survive power loss without corruption. And it has exactly one natural writer: the collector. SQLite fits all three.
SQLite’s write-ahead log documentation states the concurrency rule plainly: “writers and readers can run at the same time,” but “there can only be one writer at a time.” On a gateway, a single writer is not a limitation; it is the design. The collector, the rollup job and the retention job can all share one write lock because each holds it for milliseconds.
Durability is where SQLite needs precise configuration. In WAL mode with synchronous=NORMAL, the SQLite documentation says the database “is always consistent” but “does lose durability.” A transaction committed just before a power cut “might roll back following a power loss or system crash.” With synchronous=FULL, WAL mode is fully ACID, and SQLite fsyncs the WAL on every commit. The choice is a trade: NORMAL fsyncs only at checkpoints, so it saves one flush per commit at the cost of losing the last few commits on power loss.
For telemetry, NORMAL is usually right, provided your upstream design tolerates it. The collector can re-read the last seconds from the PLC, or the device can retransmit. For the outbox cursor, losing a recent ack update means re-sending a message, which an idempotent receiver absorbs. If you hold data that cannot be regenerated, such as a manual operator entry or a batch genealogy record, use FULL for that database or that connection.
Why DuckDB owns local analytics
DuckDB is built for the opposite workload. It stores data in columns, compresses each column, and processes vectors of values per operator call. A query that scans one column of fifty million rows touches only that column. That is why DuckDB beats a row store by a wide margin on aggregations, percentiles and window functions over long time ranges.
The process model is the catch. DuckDB’s concurrency documentation describes two in-process modes. In read-write mode, “one process can both read and write to the database.” In read-only mode, “multiple processes can read from the database, but no processes can write.” Inside the one writer process, multiple threads can write concurrently under MVCC and optimistic concurrency control, and “appends will never conflict, even on the same table.” Writing from multiple processes requires the Quack remote protocol, which DuckDB 2.0 ships in a more mature form, or DuckLake with a PostgreSQL catalog.
DuckDB also handles tiny commits poorly compared with SQLite. In our test, inserting 500 rows per call through Python executemany took about 0.4 seconds per batch. Inserting the same batch from a DataFrame took under 3 milliseconds. Your numbers will differ, but the shape is reliable: DuckDB wants bulk inserts, not row-at-a-time statements. That makes it a poor first landing zone for a one-second sensor stream and a strong second stage.
Where PostgreSQL fits, and where it does not
PostgreSQL is a server. It runs a postmaster process, one backend process per connection, and background workers for WAL writing, checkpointing, autovacuum and asynchronous I/O. The defaults are modest by server standards: shared_buffers “is typically 128 megabytes,” work_mem defaults to 4 MB, and max_wal_size defaults to 1 GB, according to the PostgreSQL 18 configuration reference. It runs well on ARM64 Linux and is packaged by every major distribution.
What PostgreSQL buys you on a gateway is multi-client concurrency. If ten processes, a historian, a SCADA client and a web UI all write and query at once, PostgreSQL’s row-level locking and MVCC handle it cleanly. It also offers mature replication, roles and network access control. Its edge costs are real. It needs a service supervisor, upgrades are major-version migrations, and autovacuum adds background writes to flash. Its row format also carries more per-row overhead than SQLite, as the sizing section shows.
The honest answer for most single-purpose gateways is that PostgreSQL is more database than the box needs. On a site server or a large industrial PC acting as an edge aggregator for dozens of lines, it often becomes the right answer.
The Decision Matrix: SQLite vs DuckDB vs PostgreSQL at the Edge
The table below compares the three engines on the dimensions that matter on a gateway. Versions are the latest stable releases verified on the publication date.
| Criterion | SQLite 3.54 | DuckDB 2.0 LTS | PostgreSQL 18.7 |
|---|---|---|---|
| Architecture | In-process library, one file | In-process library, one file | Client-server, multi-process |
| Storage layout | Row-oriented B-tree | Columnar, compressed row groups | Row-oriented heap plus indexes |
| Write concurrency | One writer, many readers, any process | One read-write process; many read-only processes | Many concurrent writers |
| Best write pattern | Small, frequent transactions | Large bulk appends | Many concurrent transactions |
| Analytical scans | Adequate on small ranges | Excellent, vectorised and parallel | Good, parallel query |
| Power-loss behaviour | Consistent in WAL mode; FULL is durable | WAL plus checkpoint; tune for bulk | WAL with full-page writes; durable by default |
| Parquet support | None built in | Native read and write | Via extensions or external tools |
| Memory default | About 2 MB page cache per connection | 80% of physical RAM | 128 MB shared buffers plus per-backend memory |
| Operational burden | None; a file | None; a file | Service, roles, vacuum, upgrades |
| Best gateway role | Hot store, outbox, config | Rollups, features, Parquet export | Multi-client site aggregator |
Two rows deserve emphasis. First, the memory defaults point in opposite directions. SQLite’s default page cache is tiny: the PRAGMA reference says the default suggested cache size of -2000 limits it to about 2 MB. DuckDB’s default memory_limit is 80% of physical RAM, which is a poor default on a shared gateway. Second, the write-pattern rows explain the whole architecture. SQLite likes small commits; DuckDB likes big ones. Put them in sequence, and each gets the load it is good at.
Deeper Analysis: Sizing, Flash Wear and Working Code
This section works through capacity, then the write-path mechanics that drive flash wear, then runnable code for both engines.
Worked sizing math
Take an illustrative gateway with 500 tags sampled at 1 Hz. That is 500 rows per second, or 43.2 million rows per day. Each row holds a millisecond timestamp, a tag ID, a double value and a quality code.
We measured on-disk bytes per row on one million synthetic rows: Gaussian values rounded to three decimals, 500 tags, SQLite 3.53.1 and DuckDB 2.0.0 on Linux x86-64. Treat these as illustrative. Real data compresses differently, especially for DuckDB and Parquet, where slowly changing values compress far better than random noise.
| Format (our synthetic test) | Bytes per row | Per day at 500 rows/s | 7-day raw retention |
|---|---|---|---|
| SQLite, WITHOUT ROWID, time-first key | about 26 to 29 | about 1.1 to 1.25 GB | about 8 to 9 GB |
| DuckDB native file | about 4.5 | about 195 MB | about 1.4 GB |
| Parquet with zstd | about 2.9 | about 125 MB | about 0.9 GB |
| PostgreSQL heap plus PK index (estimated, not measured) | about 80 to 90 | about 3.5 to 3.9 GB | about 25 to 27 GB |
The PostgreSQL row is an estimate from documented page layout, not a measurement. The PostgreSQL storage page layout specifies a 23-byte tuple header and a 4-byte line pointer per row. Add the data with alignment padding: a bigint timestamp, an integer tag, a double precision value and a smallint quality code come to roughly 26 bytes. The header and data together round up to 56 bytes, plus 4 for the line pointer. A composite primary key B-tree adds another 20 to 30 bytes per row. Ordering columns from widest to narrowest trims some padding, but PostgreSQL will still use roughly three times SQLite’s space per row.
The conclusion is simple. On a 32 GB eMMC with room for the OS and logs, SQLite comfortably holds a week of raw 1 Hz data for 500 tags. PostgreSQL would struggle with the same retention. DuckDB or Parquet holds months of the same data. That is the economic case for rolling raw data out of SQLite into columnar files after a short hot window.
How SQLite WAL mode actually writes
To reason about flash wear, you need to know what bytes hit the disk. SQLite WAL mode never modifies the main database file during a transaction. Instead, the writer appends whole page images, called frames, to the -wal file. Each frame is one database page plus a 24-byte header. A commit marks the last frame as a commit frame and updates the wal-index in the -shm shared-memory file.

Figure 2: The SQLite WAL write path. Readers pick a snapshot from the wal-index; checkpoints copy pages back and can reset the WAL only when no reader still needs old frames.
Figure 2 shows the full cycle. A reader records the last valid commit frame when it starts. It then reads newer pages from the WAL and older pages from the main file, which gives it a stable snapshot. A checkpoint later copies the newest version of each page into the main file. The SQLite documentation says automatic checkpoints run “when the WAL file reaches a threshold size of 1000 pages,” which is about 4 MB with the default 4096-byte page. Automatic checkpoints are always PASSIVE, meaning they never wait on readers.
The trap is in the reset. A checkpoint can only restart the WAL from the beginning when no reader is still using old frames. The documentation is blunt: if there is always at least one active reader, “no checkpoints will be able to complete and hence the WAL file will grow without bound.” On a gateway, a dashboard holding a long-running read transaction can do exactly this. We return to it in the failure-modes section.
Two settings bound the damage. PRAGMA journal_size_limit truncates the WAL file after a checkpoint resets it; the default is -1, meaning no limit. PRAGMA wal_checkpoint(TRUNCATE) forces a checkpoint that waits for readers and then truncates the file to zero bytes. Our test code runs it during quiet periods.
One more fact matters for upgrade planning. On 2026-03-03, the SQLite developers found a rare WAL-reset race that could corrupt a database. It affects WAL databases with two or more connections writing or checkpointing at the same instant. The WAL page says it likely affects every release from 3.7.0 through 3.51.2, and is fixed in 3.51.3 and later, with backports to 3.44.6 and 3.50.7. Gateways shipping an old distro SQLite should check their version. Python’s sqlite3 module uses whatever library it was linked against, so check sqlite3.sqlite_version, not the Python version.
The primary key decides your flash wear
Now combine frames with B-tree layout. Every insert lands in a leaf page chosen by the primary key. A one-second batch of 500 rows touches some set of leaf pages, and every touched page is written to the WAL as a full 4 KB frame.
If the key is (tag_id, ts_ms), the batch’s 500 rows land in 500 different places in the tree, one per tag. Each touched leaf becomes a WAL frame. If the key is (ts_ms, tag_id), the 500 rows all sort after every existing row. They append to the tail of the tree and fill a handful of consecutive leaves.

Figure 3: Why key order dominates write amplification. Measured WAL bytes per one-second commit of 500 rows in our test.
We measured this directly. After warming each table with 300 seconds of data, we disabled autocheckpoint and committed 60 more one-second batches of 500 rows. The tag-first WITHOUT ROWID table wrote about 2.56 MB of WAL per commit, which is about 621 frames, or 5,116 bytes of WAL for each 30-byte row. The time-first table wrote about 38 KB per commit, roughly 9 frames, or 75 bytes per row. A plain rowid table with no index wrote about 27 KB per commit. Ingest time followed the same pattern; in our larger demo, switching to the time-first key cut ingest time by roughly an order of magnitude.
Translate that into flash wear. At one commit per second, the tag-first layout writes about 221 GB of WAL per day. Checkpoints then rewrite pages into the main file as well. The time-first layout writes about 3.3 GB of WAL per day. Consumer microSD cards and small eMMC parts have limited endurance, so a 68-fold difference decides whether the card lasts years or months. These are illustrative figures from one synthetic test, but the mechanism is exact: B-tree page writes scale with distinct pages touched, not rows written.
What about per-tag queries? A time-first key still serves them. A query for one tag over the last hour scans one hour of rows, about 1.8 million at our rate, and filters by tag. That is fine for an HMI trend. If you need fast per-tag history, do not add a (tag_id, ts_ms) secondary index on the raw table; it reintroduces the scattered writes. Serve long per-tag history from the one-minute rollups, which are 60 times smaller, or from DuckDB.
The same logic explains why batching matters even with a good key. A transaction per row at 500 rows per second would write at least one frame per commit, 500 frames per second. Batching per second writes about 9. With synchronous=FULL, it also replaces 500 fsyncs per second with one.
PostgreSQL’s write path on flash
PostgreSQL has its own version of the same physics. Its WAL records changes, and after each checkpoint, the first change to any page writes the whole 8 KB page image into WAL. The documentation for full_page_writes explains why: a torn page after a crash could hold “a mix of old and new data.” Turning it off “might lead to either unrecoverable data corruption, or silent data corruption.” Keep it on. You can reduce its cost with wal_compression, which supports pglz, lz4 and zstd when compiled in, and with a longer checkpoint_timeout, which defaults to five minutes.
PostgreSQL also has a cleaner middle ground than SQLite’s NORMAL. With synchronous_commit=off, a crash may lose recently acknowledged commits, up to three times wal_writer_delay, which is 600 ms with the 200 ms default. The documentation says this creates no “risk of database inconsistency.” That is the right setting for high-rate telemetry inserts on PostgreSQL, and it can be set per session or per transaction. Our deep-dive on write-ahead logging, fsync, group commit and torn pages covers these mechanics in detail.
Finally, PostgreSQL 18 introduced asynchronous I/O. The io_method setting defaults to worker, with io_uring available only in builds compiled with liburing support. On a gateway, this mostly helps read-heavy queries, not the insert path.
SQLite ingest, rollup, outbox and retention in Python
The script below implements the left half of Figure 1 using only the Python standard library. We tested it with Python 3 linked against SQLite 3.53.1. It ingests 200 tags at 1 Hz for ten minutes, rolls up to one-minute aggregates, drains an outbox, enforces retention and truncates the WAL.
import sqlite3, time, random, json
DB = "telemetry.db"
def open_db(path=DB):
con = sqlite3.connect(path, isolation_level=None, timeout=5.0)
con.execute("PRAGMA journal_mode=WAL") # persistent once set
con.execute("PRAGMA synchronous=NORMAL") # consistent; may lose last commits on power cut
con.execute("PRAGMA busy_timeout=5000") # wait up to 5 s for the write lock
con.execute("PRAGMA wal_autocheckpoint=1000") # pages (the default, stated explicitly)
con.execute("PRAGMA journal_size_limit=67108864") # truncate WAL to 64 MiB after checkpoints
con.executescript("""
CREATE TABLE IF NOT EXISTS raw (
ts_ms INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
value REAL NOT NULL,
quality INTEGER NOT NULL DEFAULT 192,
PRIMARY KEY (ts_ms, tag_id) -- time-first: each batch appends to the B-tree tail
) WITHOUT ROWID, STRICT;
CREATE TABLE IF NOT EXISTS rollup_1m (
minute_ms INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
n INTEGER, vmin REAL, vmax REAL, vsum REAL,
PRIMARY KEY (minute_ms, tag_id)
) WITHOUT ROWID, STRICT;
CREATE TABLE IF NOT EXISTS outbox (
seq INTEGER PRIMARY KEY AUTOINCREMENT,
payload TEXT NOT NULL
) STRICT;
CREATE TABLE IF NOT EXISTS sync_cursor (
id INTEGER PRIMARY KEY CHECK (id = 1),
acked_seq INTEGER NOT NULL
) STRICT;
INSERT OR IGNORE INTO sync_cursor VALUES (1, 0);
""")
return con
def ingest_batch(con, rows):
"""rows: list of (ts_ms, tag_id, value). One transaction per batch."""
con.execute("BEGIN IMMEDIATE") # take the write lock up front, fail fast on contention
try:
con.executemany(
"INSERT OR REPLACE INTO raw(ts_ms, tag_id, value) VALUES (?,?,?)", rows)
con.execute("COMMIT")
except Exception:
con.execute("ROLLBACK")
raise
def rollup(con, since_ms, until_ms):
"""Fold raw rows in [since, until) into 1-minute aggregates and enqueue them."""
con.execute("BEGIN IMMEDIATE")
try:
con.execute("""
INSERT OR REPLACE INTO rollup_1m
SELECT (ts_ms / 60000) * 60000, tag_id,
count(*), min(value), max(value), sum(value)
FROM raw WHERE ts_ms >= ? AND ts_ms < ?
GROUP BY 1, 2""", (since_ms, until_ms))
rows = con.execute("""
SELECT minute_ms, tag_id, n, vmin, vmax, vsum FROM rollup_1m
WHERE minute_ms >= ? AND minute_ms < ?""", (since_ms, until_ms)).fetchall()
con.execute("INSERT INTO outbox(payload) VALUES (?)", (json.dumps(rows),))
con.execute("COMMIT")
except Exception:
con.execute("ROLLBACK")
raise
def drain_outbox(con, send, limit=50):
"""Store-and-forward: send in order, advance the cursor only after an ack."""
acked = con.execute("SELECT acked_seq FROM sync_cursor").fetchone()[0]
batch = con.execute("SELECT seq, payload FROM outbox WHERE seq > ? ORDER BY seq LIMIT ?",
(acked, limit)).fetchall()
for seq, payload in batch:
if not send(seq, payload): # broker or cloud said no: stop, retry later
break
con.execute("UPDATE sync_cursor SET acked_seq = ? WHERE id = 1", (seq,))
con.execute("DELETE FROM outbox WHERE seq <= (SELECT acked_seq FROM sync_cursor)")
def enforce_retention(con, keep_ms, now_ms, chunk=5000):
"""Delete old raw rows in small chunks so no single transaction bloats the WAL."""
cutoff = now_ms - keep_ms
while True:
con.execute("BEGIN IMMEDIATE")
cur = con.execute("""DELETE FROM raw WHERE (ts_ms, tag_id) IN (
SELECT ts_ms, tag_id FROM raw WHERE ts_ms < ? LIMIT ?)""", (cutoff, chunk))
con.execute("COMMIT")
if cur.rowcount < chunk:
break
if __name__ == "__main__":
import os
for f in (DB, DB + "-wal", DB + "-shm"):
if os.path.exists(f): os.remove(f)
con = open_db()
t0 = 1_763_337_600_000 # fixed epoch ms for a reproducible demo
tags, seconds = 200, 600 # 200 tags at 1 Hz for 10 minutes
start = time.perf_counter()
for s in range(seconds):
ts = t0 + s * 1000
ingest_batch(con, [(ts, t, random.gauss(50, 5)) for t in range(tags)])
print(f"ingested {tags*seconds} rows in {time.perf_counter()-start:.2f}s")
rollup(con, t0, t0 + seconds * 1000)
print("rollup rows:", con.execute("SELECT count(*) FROM rollup_1m").fetchone()[0])
drain_outbox(con, send=lambda seq, p: True)
print("outbox left:", con.execute("SELECT count(*) FROM outbox").fetchone()[0])
enforce_retention(con, keep_ms=5 * 60_000, now_ms=t0 + seconds * 1000)
print("raw rows kept:", con.execute("SELECT count(*) FROM raw").fetchone()[0])
con.execute("PRAGMA wal_checkpoint(TRUNCATE)")
print("wal bytes after truncate:", os.path.getsize(DB + "-wal"))
On our test machine, it printed 120,000 rows ingested in about 0.15 seconds, 2,000 rollup rows, an empty outbox, 60,000 raw rows kept and a zero-byte WAL. With the tag-first key, the same ingest took between 1.2 and 2.1 seconds across runs.
A few design choices deserve explanation. BEGIN IMMEDIATE takes the write lock at the start of the transaction rather than at the first write. That avoids the classic deadlock where two connections both start as readers, both try to upgrade, and one gets SQLITE_BUSY without the busy handler helping. STRICT tables reject type mismatches, which catches collector bugs early. Retention deletes in chunks of 5,000 rows, so no single transaction writes a huge burst into the WAL.
The rollup and the outbox insert share one transaction. That is the transactional outbox pattern: either the aggregate exists and is queued for delivery, or neither happened. There is no window where a rollup is computed but never sent.
Store-and-forward semantics
The outbox gives at-least-once delivery, not exactly-once. If the gateway loses power after the broker acknowledges message 41 but before the cursor update reaches disk, message 41 is sent again after reboot. With synchronous=NORMAL, that window covers every commit since the last checkpoint, so expect several resends after a hard power cut. The fix belongs on the receiver: make writes idempotent by keying on gateway ID plus sequence number, or on gateway ID, tag and minute.
Pair this with the broker’s guarantees. MQTT QoS 1 is itself at-least-once, so the same deduplication handles both sources of duplicates. Our guide to MQTT store-and-forward on edge gateways compares this database outbox with broker-level persistence and bridging. The database outbox wins when the payload is derived from local state, as rollups are, because the derivation and the enqueue commit together.
Size the outbox for your longest expected outage. One-minute rollups for 500 tags produce 500 rows per minute, about 720,000 rows per day. At roughly 60 bytes each as JSON, that is around 43 MB per day of backlog. That number is illustrative, but it shows that rollups, not raw data, are what you should buffer for multi-day outages.
DuckDB edge analytics and Parquet export
The second script implements the right half of Figure 1. It attaches the SQLite file read-only through DuckDB’s sqlite extension and computes one-minute statistics, including a 95th percentile, which SQLite has no built-in function for. It then writes a zstd-compressed Parquet dataset partitioned by day. If the extension cannot be installed, for example on an air-gapped gateway without a local extension repository, it falls back to reading through Python’s driver. We tested it with DuckDB 2.0.0.
import duckdb, os, shutil
# Run sqlite_edge.py first, or point SQLITE_DB at any gateway database with a `raw` table.
SQLITE_DB = "telemetry.db"
con = duckdb.connect("analytics.duckdb")
con.execute("SET memory_limit = '512MB'") # leave headroom on a 2 GB gateway
con.execute("SET threads = 2") # fewer threads, smaller per-thread state
con.execute("SET temp_directory = 'duck_spill'") # put spill files on the data volume
con.execute("SET preserve_insertion_order = false")
# Read the SQLite file directly. Try the sqlite extension; fall back to the Python driver.
try:
con.execute("INSTALL sqlite; LOAD sqlite;")
con.execute(f"ATTACH '{SQLITE_DB}' AS edge (TYPE sqlite, READ_ONLY)")
src = "edge.raw"
except Exception as e:
import sqlite3, pandas as pd
print("sqlite extension unavailable, using pandas bridge:", type(e).__name__)
raw_df = pd.read_sql_query("SELECT ts_ms, tag_id, value FROM raw",
sqlite3.connect(SQLITE_DB))
con.register("raw_df", raw_df)
src = "raw_df"
# Columnar, vectorised rollup: 1-minute stats plus a percentile SQLite has no built-in for.
con.execute(f"""
CREATE OR REPLACE TABLE rollup_1m AS
SELECT time_bucket(INTERVAL '1 minute', make_timestamp(ts_ms * 1000)) AS minute,
tag_id,
count(*) AS n,
min(value) AS vmin,
max(value) AS vmax,
avg(value) AS vavg,
quantile_cont(value, 0.95) AS p95
FROM {src}
GROUP BY ALL
""")
print(con.execute("SELECT count(*), round(avg(p95), 2) FROM rollup_1m").fetchone())
# Export a partitioned, zstd-compressed Parquet snapshot for the uplink.
shutil.rmtree("export", ignore_errors=True)
con.execute("""
COPY (SELECT *, strftime(minute, '%Y-%m-%d') AS day FROM rollup_1m)
TO 'export' (FORMAT parquet, COMPRESSION zstd, PARTITION_BY (day))
""")
files = [os.path.join(d, f) for d, _, fs in os.walk("export") for f in fs]
print(len(files), "parquet file(s),", sum(os.path.getsize(f) for f in files), "bytes")
print(con.execute("SELECT count(*) FROM read_parquet('export/**/*.parquet')").fetchone())
The settings at the top are the important part. DuckDB’s default memory_limit is 80% of RAM, and threads defaults to the number of CPU cores. On a gateway that also runs a broker, a collector and a container runtime, those defaults invite the kernel’s OOM killer. DuckDB’s own environment guidance says it “requires a minimum of 125 MB of memory per thread” and recommends 1 to 4 GB per thread. That is why two threads and 512 MB is a sensible start on a 2 GB board.
Why attach SQLite instead of copying everything into DuckDB? Because the read-only attach respects the single-writer rule on both sides. The collector keeps writing to SQLite. DuckDB reads a consistent snapshot through SQLite’s normal reader path, and it never holds a write lock. Keep the DuckDB query short, though. A long scan is a long SQLite read transaction, which can starve checkpoints.
For a deeper look at how the 2.0 release changes DuckDB on industrial workloads, see our DuckDB 2.0 vs 1.5 benchmarks for IIoT. Before you choose Parquet codecs and encodings for the uplink, read our comparison of zstd vs LZ4 vs Snappy and Gorilla encoding for IoT telemetry.
Trade-offs, Gotchas, and What Goes Wrong
Every engine has failure modes that only show up in the field. Figure 4 maps the common ones to their mitigations.

Figure 4: Failure modes seen on gateway databases and the mitigation for each. Most are configuration problems, not engine bugs.
SD card corruption. WAL mode keeps the database consistent only if the storage honours flush requests. The SQLite corruption guide warns that “most consumer-grade mass storage devices lie about syncing.” It also notes that some flash controllers can damage files during power loss, even files not being written. No database fixes a card that lies. Use industrial-grade eMMC or SSD with power-loss protection, or add a supercapacitor or small UPS that gives the OS time to sync. Mount data on its own partition so a corrupted filesystem does not take the OS with it.
Deleted or orphaned WAL files. After a crash, the -wal file contains committed transactions not yet checkpointed. Deleting it, or copying only the .db file during a backup, loses data and can corrupt the database. Back up with VACUUM INTO or the online backup API, never with cp on a live database.
Unbounded WAL growth. A reader that never ends a transaction blocks checkpoint resets. Common culprits are a Python connection left inside an open transaction, a dashboard holding a cursor, or a long DuckDB scan through the SQLite attach. Monitor the -wal file size. Keep read transactions short, and schedule wal_checkpoint(TRUNCATE) in quiet windows.
Locking errors. SQLITE_BUSY and “database is locked” usually mean a second writer, a missing busy_timeout, or a deferred transaction trying to upgrade. Funnel writes through one process. In DuckDB, a second process that opens the file for writing fails to acquire the file lock. The fix is architectural: one writer process, or Quack server mode, not retries.
Network filesystems. SQLite WAL needs shared memory on one host, and the documentation states “WAL does not work over a network filesystem.” DuckDB’s guidance likewise advises against read-write use of its native format on NFS or SMB.
Out-of-memory on aggregation. DuckDB spills large joins, sorts and high-cardinality aggregations to its temporary directory once intermediates exceed the memory limit. Certain aggregates, such as string_agg(), and stacks of blocking operators can still fail. On Linux, an over-limit process may simply die with “Killed,” visible in dmesg. Set memory_limit explicitly, reduce threads, and put temp_directory on a volume with room.
PostgreSQL bloat. Retention by DELETE leaves dead tuples for autovacuum, which writes again. On PostgreSQL, partition by day and drop old partitions instead.
Practical Recommendations
Start from the workload, not the engine. If one process writes and the gateway mostly forwards data, SQLite alone is enough. Add DuckDB when you need percentiles, windows, feature extraction or Parquet output over more than a few hours of data. Choose PostgreSQL only when several independent applications must write concurrently, or the box is really a site server.
Treat the primary key as a storage-endurance decision. Put time first in the raw table’s key, batch commits per second or per poll cycle, and keep secondary indexes off the raw table. Measure WAL bytes per commit on your own data before you ship. The test takes a few minutes and can change the hardware budget.
Decide durability per dataset. Raw telemetry that can be re-polled tolerates synchronous=NORMAL. Operator entries, batch records and configuration deserve FULL, or a separate small database set to FULL. Make the cloud receiver idempotent so that either way, resends are harmless.
Upgrade SQLite deliberately. Confirm the linked library version on the device, and stay at 3.51.3 or later to pick up the WAL-reset fix. For DuckDB, note that 1.4 LTS support ends on 2026-11-17, and that pre-2.0 releases cannot open database files created by 2.0. Treat DuckDB files as rebuildable caches and Parquet as the durable artefact.
A short checklist for a new gateway build:
- SQLite 3.51.3 or later, WAL mode,
busy_timeout,BEGIN IMMEDIATEfor writes. - Raw table keyed
(ts_ms, tag_id),WITHOUT ROWID,STRICT, no extra indexes. - One writer process; rollup and outbox insert in one transaction.
- Chunked retention deletes and a scheduled TRUNCATE checkpoint.
- DuckDB with an explicit
memory_limit, two threads and a spill directory. - Daily zstd Parquet exports as the durable analytical record.
- Industrial flash with power-loss protection, and data on its own partition.
Frequently Asked Questions
Is DuckDB a replacement for SQLite on an IoT gateway?
No, not for the write path. DuckDB is an analytical engine optimised for large scans and bulk appends, while SQLite is optimised for small, frequent transactions. In our test, small per-second inserts through Python executemany were orders of magnitude slower in DuckDB than bulk DataFrame inserts. DuckDB also allows only one read-write process on a file unless you run Quack server mode. Use SQLite as the hot store and outbox, and let DuckDB read it for analytics and Parquet export.
Is SQLite WAL mode safe against power loss?
WAL mode keeps the database consistent after power loss, provided the storage honours flush requests. With synchronous=NORMAL, the last commits before the cut may roll back, but the file is not corrupted. With synchronous=FULL, committed transactions are durable. The weak link is usually hardware: SQLite’s documentation warns that many consumer storage devices acknowledge syncs they have not completed. On cheap SD cards, no setting guarantees safety. Use industrial flash with power-loss protection or a supercapacitor.
Can PostgreSQL run on a Raspberry Pi or ARM gateway?
Yes. PostgreSQL runs on ARM64 Linux and is packaged by major distributions, and its default shared_buffers is a modest 128 MB. The question is whether you need it. PostgreSQL adds a server process, a backend per connection, autovacuum, roles and major-version upgrades. It also stores roughly three times more bytes per telemetry row than SQLite, by our estimate. Choose it when many applications write concurrently or when the gateway acts as a site-level aggregator for several lines.
How much telemetry can SQLite store on a gateway?
It depends on row width and key design, but a narrow telemetry row takes about 26 to 29 bytes in our synthetic test. At 500 tags and 1 Hz, that is roughly 1.1 to 1.25 GB per day, so a week fits easily on 32 GB of flash. SQLite’s file-size limits are far beyond gateway storage. The practical limits are flash endurance and query speed on long ranges, which is why you roll old data into one-minute aggregates and Parquet files.
Why does my SQLite WAL file keep growing?
A checkpoint can copy pages back to the database while readers are active, but it can only reset the WAL when no reader still uses old frames. If some connection always holds a read transaction open, the WAL grows without bound. Common causes are an idle connection left inside a transaction, a dashboard cursor, or a long analytical scan. Keep reads short, set journal_size_limit, and run PRAGMA wal_checkpoint(TRUNCATE) during quiet periods.
Should I use DuckDB or SQLite to buffer data during network outages?
Use SQLite. Store-and-forward needs small, durable, ordered writes plus an acknowledgement cursor, which is a transactional workload. A SQLite outbox table, written in the same transaction as the rollup it carries, gives at-least-once delivery with a simple restart story. Make the cloud receiver idempotent by keying on gateway and sequence number. Use DuckDB alongside it to produce compressed Parquet files for bulk backfill once the link returns.
Further Reading
- PostgreSQL 18 vs TimescaleDB vs ClickHouse for IoT: the cloud-side time-series database decision that receives this gateway’s uplink.
- DuckDB 2.0 vs 1.5 benchmarks for IIoT workloads: what the new LTS release changes for industrial analytics.
- IoT telemetry compression: zstd vs LZ4 vs Snappy and Gorilla encoding: how to pick codecs for the Parquet uplink.
- Parquet vs ORC vs Lance vs Vortex columnar file formats: the file format choice behind the export step.
- SQLite write-ahead logging documentation: the primary source for checkpoints, concurrency and the WAL-reset bug.
- DuckDB 2.0.0 LTS release announcement: version support windows, Quack server mode and storage compatibility.
References
- SQLite. “SQLite Home Page,” latest release 3.54.1 (2026-10-16). https://sqlite.org/
- SQLite. “Release History,” 3.54.0 and 3.54.1 notes. https://sqlite.org/changes.html
- SQLite. “Write-Ahead Logging.” https://sqlite.org/wal.html
- SQLite. “Pragma statements supported by SQLite.” https://sqlite.org/pragma.html
- SQLite. “How To Corrupt An SQLite Database File.” https://sqlite.org/howtocorrupt.html
- DuckDB. “Announcing DuckDB 2.0.0 LTS,” 2026-10-21. https://duckdb.org/2026/10/21/announcing-duckdb-200
- DuckDB. “Concurrency,” documentation v2.0. https://duckdb.org/docs/2.0/connect/concurrency
- DuckDB. “Configuration Overview,” documentation v2.0. https://duckdb.org/docs/2.0/configuration/overview.html
- DuckDB. “Environment,” performance guide v2.0. https://duckdb.org/docs/2.0/guides/performance/environment.html
- DuckDB. “Memory Management in DuckDB,” 2024-07-09. https://duckdb.org/2024/07/09/memory-management
- PostgreSQL Global Development Group. “Versioning Policy.” https://www.postgresql.org/support/versioning
- PostgreSQL Global Development Group. “PostgreSQL 19 RC 1 Released!,” 2026-10-15. https://www.postgresql.org/about/news/postgresql-19-rc-1-released-3409/
- PostgreSQL 18 documentation. “Write Ahead Log” settings. https://www.postgresql.org/docs/18/runtime-config-wal.html
- PostgreSQL 18 documentation. “Resource Consumption.” https://www.postgresql.org/docs/18/runtime-config-resource.html
- PostgreSQL 18 documentation. “Database Page Layout.” https://www.postgresql.org/docs/18/storage-page-layout.html
By Riju — about
