two databases, one binary: duckdb for reads, sqlite for state
a polyglot-persistence split for a small analytics service — columnar duckdb for events, row sqlite for state, and a non-blocking batch-insert buffer
the tracking endpoint has exactly one job: say 202 and get out of the way. a beacon fires on someone else’s website, my server catches it, and the visitor is already three paragraphs into an article before my response even lands. nobody is waiting on that request — the whole point of a sendBeacon is that the page has already moved on. so the afternoon i read a flame graph and found the ingest handler parked inside a duckdb INSERT, holding a request open while the disk did its thing, i felt a very specific kind of stupid. i’d taken a fire-and-forget pixel and taught it to block on fsync.
fixing that is most of this post. but the fix only makes sense on top of a decision most tutorials would have talked me out of: i put two different databases in one small rust binary, on purpose.
the rotating-salt piece was the privacy side of a privacy-first analytics tool i’m building — the hash-not-a-cookie identity model, the salt that rots every twenty-four hours. i promised at the end of it i’d write the storage side. this is that. and the storage side opens with the split: duckdb for the events, sqlite for everything else.
one database is the tidy answer. it’s the wrong one.
the tidy answer is one postgres and a shrug. i love that answer. it’s one connection pool, one backup story, one set of credentials, one thing to reason about at 3am. and for a lot of products it’s correct.
it’s wrong here because the two things this service stores are not remotely alike. look at what they actually do:
- the events. append-only, millions of tiny rows, never updated, never deleted one-at-a-time. they’re read exclusively through big analytical sweeps —
count(*),count(distinct visitor_hash),group byday over a date range. that is textbook OLAP, and it wants a column store. - the state. accounts, sites, api config, the rotating daily salt, goal definitions. a few thousand rows that get read and written by primary key — “give me the account for this email”, “give me the current salt”. small, pointy, transactional. that is textbook OLTP, and it wants a row store.
force one engine to serve both and one of the two workloads always runs against the grain. a row store doing count(distinct) over ten million rows reads every column of every row to look at one. a column store doing SELECT * WHERE id = ? is paying vectorised-scan overhead to fetch a single tuple. polyglot persistence is just refusing to pick which of your two workloads gets to be the slow one. so i didn’t pick. duckdb ↗
— which describes itself as a “columnar-vectorized query execution engine” built for “analytical query workloads, also known as online analytical processing (OLAP)” — takes the events. sqlite ↗
, which the docs call “almost always a better solution” for “device-local storage with low writer concurrency”, takes the state.
two handles, two workloads
the split is visible in the app state. two pools, side by side, and a comment explaining why they’re both wrapped the way they are:
server/src/state.rs
/// Shared application state for all axum routes.
///
/// `DuckDB` and `SQLite` connections are not `Sync`, so they live behind
/// a `Mutex` to satisfy axum's `Send + Sync + Clone` requirement.
#[derive(Clone)]
pub struct AppState {
pub config: Arc<Config>,
pub duck: Arc<Mutex<DuckDbPool>>,
pub sqlite: Arc<Mutex<SqlitePool>>,
pub buffer: Arc<EventBuffer>,
pub sessions: Arc<Mutex<SessionTracker>>,
// ...
}the rationale isn’t buried in a design doc; it’s in the doc-comment on each wrapper, which is where i want load-bearing decisions to live. duckdb’s says what it’s for:
core/src/db/duckdb.rs
/// Wrapper around a `DuckDB` connection for analytics event storage.
pub struct DuckDbPool {
pub(crate) conn: duckdb::Connection,
}and sqlite’s says what it’s for — note the list, because it’s the whole “state” half in one line:
core/src/db/sqlite.rs
/// Wrapper around a `SQLite` connection for metadata storage
/// (accounts, sites, salts, sessions, goal definitions).
pub struct SqlitePool {
pub(crate) conn: Connection,
}“salts” in that list is the same rotating salt from the last post — the one thing i do persist to identify a visitor for a day. it lives in the row store because the access pattern is a single-row lookup by recency, get_current_salt, not an aggregation. same for get_account_by_email: point queries against a b-tree, which is exactly what sqlite is fast at and exactly what a column store is bad at.
the events table, by contrast, is a wide append target — every column the analytics side might one day group by, typed for a column store:
core/src/db/duckdb.rs
"CREATE TABLE IF NOT EXISTS events (
site_id BIGINT NOT NULL,
event_type TINYINT NOT NULL,
visitor_hash VARCHAR NOT NULL,
session_hash VARCHAR NOT NULL,
timestamp TIMESTAMP NOT NULL,
url_path VARCHAR NOT NULL,
// ... referrer, utm_*, country_code, browser, os, device_type
props JSON
);"and the reason it’s shaped like that is the read side. this is the query duckdb was built for — bucket by time, count rows, count distinct visitors, group, order — the kind of thing that touches three columns out of twenty and wants all the values of each column packed together:
core/src/analytics/timeseries.rs
let sql = format!(
"SELECT CAST(date_trunc('{trunc}', timestamp) AS VARCHAR) AS ts,
COUNT(*) AS pageviews,
COUNT(DISTINCT visitor_hash) AS visitors
FROM events
WHERE site_id = ?1
AND timestamp >= ?2::TIMESTAMP
AND timestamp < ?3::TIMESTAMP
GROUP BY date_trunc('{trunc}', timestamp)
ORDER BY date_trunc('{trunc}', timestamp)"
);the write path can’t wait for the disk
now back to the flame graph. the ingest handler does the privacy work from the last post — extract ip, compute the salted hash, enrich — and then, at the very end, it has to get the event into duckdb. the version that made me feel stupid did an INSERT right there. the version that shipped does this:
server/src/routes/ingest.rs
// 9. Submit to event buffer
if let Err(e) = state.buffer.submit(processed).await {
tracing::error!(error = %e, "failed to submit event to buffer");
return StatusCode::BAD_REQUEST;
}
// 10. Return 202 Accepted
StatusCode::ACCEPTEDsubmit doesn’t touch the database. it drops the finished event onto a channel and returns, and the handler answers 202 immediately. the actual disk write happens somewhere else, later, in a batch, on a task that no http request is waiting on. the request’s critical path stops at “handed it off.”
500 events or 5 seconds, whichever comes first
the buffer is a bounded tokio mpsc channel ↗ with a background task draining it. three constants set the whole policy:
core/src/ingestion/buffer.rs
/// Default batch size for flushing events to `DuckDB`.
const DEFAULT_BATCH_SIZE: usize = 500;
/// Default flush interval.
const DEFAULT_FLUSH_INTERVAL: Duration = Duration::from_secs(5);
/// Channel capacity: allow some backpressure before blocking senders.
const CHANNEL_CAPACITY: usize = 10_000;submit is a one-liner over the sender, and it’s honest about the one way it can fail:
core/src/ingestion/buffer.rs
pub async fn submit(&self, event: ProcessedEvent) -> Result<(), AppError> {
self.tx
.send(event)
.await
.map_err(|_| AppError::Internal("event buffer channel closed".into()))
}the drain loop is where the “whichever comes first” lives. it’s a tokio::select! ↗
racing two things — the next event off the channel against a sleep timer — and it “returns when the first branch completes”, exactly the primitive this wants:
core/src/ingestion/buffer.rs
async fn flush_loop(
mut rx: mpsc::Receiver<ProcessedEvent>,
duck: DuckDbPool,
batch_size: usize,
flush_interval: Duration,
) {
let mut buffer: Vec<ProcessedEvent> = Vec::with_capacity(batch_size);
loop {
let deadline = tokio::time::sleep(flush_interval);
tokio::pin!(deadline);
// Collect events until batch is full or the timer fires.
loop {
tokio::select! {
event = rx.recv() => {
if let Some(e) = event {
buffer.push(e);
if buffer.len() >= batch_size {
break;
}
} else {
// Channel closed — flush remaining and exit.
if !buffer.is_empty() {
flush_batch(&duck, &mut buffer);
}
info!("event buffer shutting down");
return;
}
}
() = &mut deadline => {
break;
}
}
}
if !buffer.is_empty() {
flush_batch(&duck, &mut buffer);
}
}
}read the two ways out of the inner loop and you have the whole behaviour. events arrive, get pushed onto a Vec; hit 500 and break to flush a full batch. no traffic for five seconds and the deadline branch fires, breaking to flush whatever partial batch exists so a quiet site’s three pageviews don’t sit in memory forever. and the None arm — the channel closed because every Sender was dropped — is the graceful drain: flush what’s left, log, and return instead of spinning. that arm is why a clean shutdown doesn’t silently eat the last partial batch.
the batch insert itself is a prepared statement executed once per row inside one call:
core/src/db/duckdb.rs
pub fn insert_events_batch(&self, events: &[ProcessedEvent]) -> Result<(), AppError> {
if events.is_empty() {
return Ok(());
}
let mut stmt = self.conn.prepare(
"INSERT INTO events (/* ... 23 columns ... */) VALUES (/* ... */)",
).map_err(|e| AppError::DuckDb(e.to_string()))?;
for event in events {
// ...
stmt.execute(duckdb::params![/* ... */])
.map_err(|e| AppError::DuckDb(e.to_string()))?;
}
Ok(())
}i’ll flag now, and again in the honest section, that duckdb’s docs say the appender ↗
is the “recommended way to load bulk data” — it caches rows and writes them in one shot. a prepared-INSERT loop is not that. at 500 rows every five seconds it’s nowhere near the bottleneck, so i left it; it’s the first thing i’d change if throughput ever mattered.
the one place a request can still block
i want to correct my own comment, because “load-shedding” is what i wish that channel did and “backpressure” is what it actually does. a bounded tokio channel does not drop when it’s full — the docs are explicit ↗
: “once the buffer is full, attempts to send new messages will wait until a message is received.” so if 10,000 events pile up unflushed, submit(...).await stops returning instantly and starts waiting, and the ingest handler is briefly back to blocking — the exact thing i built this to avoid, just moved to the tail of the distribution. that’s a deliberate trade: 10,000 events of slack is a lot of runway, and past it i’d rather push back than balloon memory. but it is backpressure, not shedding, and pretending otherwise would be a lie i’d eventually debug at 3am.
the honest part: what i cut
-
events are dropped on insert error. there is no dead-letter queue. the flush path says so out loud, and that comment is doing real work:
core/src/ingestion/buffer.rs
if let Err(e) = duck.insert_events_batch(buffer) { error!(error = %e, count, "failed to flush event batch"); // Events are lost on error. In production you might want a // dead-letter queue or retry logic here. } buffer.clear();a batch that fails to insert is logged and cleared — gone. for analytics that’s a defensible call; a lost pageview is a rounding error, not a lost invoice. for anything with money or receipts in it, this line would be malpractice. the fix is a dlq or a bounded retry, and it’s a comment instead of code because i decided approximate counts were fine. that’s a bet, stated as one.
-
duckdb is single-writer, so the batch loop is the only writer — by design. duckdb allows one writing process at a time ↗ . the buffer task owns its own connection (the server opens a second handle purely for it) and it is the sole thing that ever writes events. that’s not a limitation i’m tolerating, it’s the invariant the design leans on: funnelling every write through one serialized loop is exactly how you keep a single-writer engine happy. the read handle in
AppStateonly ever runsSELECTs. -
the in-flight buffer is process memory, so a crash loses it. up to ~10,000 events in the channel plus up to 500 in the flush
Veclive nowhere but ram.kill -9between flushes and they evaporate. clean shutdowns drain (that’s theNonearm); a hard crash does not. same amnesiac-by-default posture as the in-memory session map from the last post, and the same honest caveat: fine for counting readers, unacceptable for billing them. -
right-tool-per-workload beat forcing one engine to do both. the whole split could have been a single postgres and i’d have spent the year apologising to one of the two workloads. two embedded files in one binary — no extra service, no network hop — turned out to be less operational weight than the “simpler” single database, not more.
none of these are “it crashes” bugs. they’re the honest gap between a fast, non-blocking write path and a durable one — and for this product, fast-and-approximate is the correct point on that line, as long as i say so.
lessons learned
- match the store to the access pattern, not to the org chart. the instinct to run one database is really an instinct to have one thing to operate. two embedded engines with zero moving parts call that bluff: i got the column store the analytics reads wanted and the row store the state writes wanted, and the binary is still a single
scp. - put the batching policy in constants and the timing in
select!. “500 or 5 seconds” is twoconsts and a two-branch race. i can reason about the worst-case latency (five seconds) and the worst-case memory (10,500 events) by reading, not by profiling, because the policy is data and the mechanism is boring. - write the tradeoff into the code, then don’t quietly outgrow it. “events are lost on error” and “allow some backpressure” are both comments that told me exactly where the sharp edges are. one of them turned out to undersell what the code does — which is the whole reason i keep them honest.
this is the same reflex that made me leave go for rust on the hot path: a borrow checker watching a buffer that hands ownership across a channel and drains it on shutdown is worth a lot when “drop the event” and “block the request” are one refactor apart.
next: what happens when the single writer is the bottleneck — swapping the prepared-INSERT loop for duckdb’s appender, and measuring whether it was ever worth the sentence i spent apologising for it.
the rest of my work is on github ↗ if you want to poke around. this one’s still a work in progress, like everything here.