Why We Built Analytics on PostgreSQL + DuckDB (Architecture Deep-Dive)

The problem with one database for analytics

Web analytics has two very different workloads. Writes are tiny, frequent, and transactional — one row per pageview, thousands per minute. Reads are huge aggregations — "count distinct visitors per day over the last 90 days, grouped by country." A single database tuned for one is bad at the other.

Row-store databases like PostgreSQL are excellent at writes but slow at wide aggregations, because answering "sum over 5 million rows" means walking 5 million rows. Column-store (OLAP) engines flip that: they read only the columns you ask for, in compressed blocks, and aggregate at memory bandwidth.

So we use both.

The dual-database design

• PostgreSQL is the source of truth for all writes: tracking events, sessions, users, sites, teams. It is durable, transactional, and battle-tested.
• DuckDB is an embedded columnar OLAP engine that serves every analytics read. It runs in-process (no separate server), and it is astonishingly fast at the GROUP BY / COUNT DISTINCT queries dashboards live on.

A background sync streams new rows from PostgreSQL into DuckDB. The dashboard never queries PostgreSQL directly — it only reads from DuckDB.

Tracking script ──▶ POST /api/track ──▶ PostgreSQL (writes)
                                            │  sync (keyset cursor)
                                            ▼
Dashboard ◀── GET /api/analytics ◀──────  DuckDB (reads)

Why DuckDB specifically

DuckDB is "SQLite for analytics": a single file, zero-config, in-process, columnar. For a self-hosted tool that matters enormously — there is no extra service to run, no cluster to manage. You ship one binary dependency and get column-store performance.

On a single node, a 90-day KPI aggregation reads only the columns it needs rather than whole rows, which is what makes this shape of query cheap on a columnar engine. Actual latency depends on dataset size, hardware, and query shape.

The sync pipeline (and how we made it correct)

The naive sync — "copy every row newer than the last timestamp" — has two classic bugs:

1. Duplicates on crash. If the process dies mid-sync and the high-water mark was only written at the end, the next run re-copies rows.

2. Lost rows at the boundary. If many rows share the same millisecond timestamp, a timestamp > last cursor can skip some.

We fixed both with a keyset cursor on the monotonic event id (the incremental sync guide walks through the implementation): page by id > last_id ORDER BY id, and persist the cursor after every batch. A re-run resumes exactly where it left off — idempotent, and no boundary gaps. Bulk inserts (one multi-row INSERT per batch) make it fast.

Hot + cold for scale (appsv2)

For very large datasets, the v2 layout adds a hot/cold tier: the last 30 days live in a DuckDB hot table (RAM-speed), and older data is exported to Parquet files (optionally on S3 or Cloudflare R2). A transparent UNION ALL view stitches them together, so queries still just say FROM events and get the full history — recent data fast, historical data cheap.

Frequently asked questions

Why not just use PostgreSQL for both reads and writes?

You can, and for small sites you should. The split earns its keep when dashboard queries scan wide date ranges — a row store reads every column of every row to aggregate one, while a columnar engine reads only the columns named. Below a few million events the difference rarely justifies the extra moving part.

Does the sync mean my dashboard shows stale data?

Slightly, by design. The sync runs on an interval, so analytics reads trail live writes by that interval. Realtime counters bypass DuckDB and read fresh state, so the live visitor count stays current while historical aggregates lag by a sync cycle.

What happens if the sync breaks?

Writes continue into PostgreSQL, which is the source of truth, so no data is lost — the dashboard simply stops advancing. Because the sync is idempotent and checkpointed, restarting it resumes from the last committed cursor rather than replaying everything.

Is DuckDB safe to use with concurrent readers?

Multiple readers in one process are fine, which is the pattern here — one instance, pooled connections. DuckDB is not designed for multiple processes writing the same file concurrently, which is why every write goes to PostgreSQL and only the sync worker writes DuckDB.

At what scale does this design stop working?

When the working set stops fitting comfortably on one machine. The usual next step is tiering cold partitions to Parquet rather than replacing DuckDB — see DuckDB + Parquet cold storage.

What this buys you

• Fast dashboards at any scale, on a single node
• No separate analytics database server to operate
• Clean separation: durable writes in PG, fast reads in DuckDB
• A read-only SQL editor that lets power users query their own data directly

It is the kind of architecture that used to require a data warehouse — running in one open-source container you can self-host. Further reading: why columnar storage is faster, DuckDB vs ClickHouse vs Postgres, and Parquet cold storage.

Curious how it feels? The InsightsTrack live demo runs this exact stack with sample data.