SahamLens runs on DuckDB, not SQLite. The 14× speedup on analytical queries was the easy part. The concurrent connection bug in the dev server was not.
SahamLens shipped its first working sprint in May 2026. The core decision that shaped the whole architecture: DuckDB over PostgreSQL for local storage. I almost went with SQLite. Here is why I did not, and what the trade-off cost me.
SQLite was the default answer. Embedded, zero-config, battle-tested. Every local-first app tutorial reaches for it. DuckDB was the contrarian answer — an analytical database designed for columnar workloads, not a general-purpose embedded store.
SahamLens is a trading analysis tool. The core workload is: ingest daily OHLCV data for 50–200 IDX tickers, compute rolling indicators (MA, RSI, MACD) over 1–5 year windows, and surface results in a dashboard. That is an analytical workload, not a transactional one.
The first benchmark told the story. Computing a 200-day moving average over 3 years of daily data for 100 tickers:
sqlite3): 4.2 secondsDuckDB's columnar storage and vectorised execution are built for exactly this query shape. The 14× speedup was not a surprise — it was the expected outcome of using the right tool.
The SQL interface was also a win. DuckDB speaks standard SQL with window functions, QUALIFY, and PIVOT — all of which I use in the indicator engine. SQLite's window function support is limited and its PIVOT is nonexistent.
DuckDB is not a general-purpose database. Concurrent writes from multiple processes are not supported — one writer at a time. For SahamLens this is fine: the data ingestion script runs, then the dashboard reads. No concurrent writes.
The Python ecosystem for DuckDB is younger than SQLite's. I hit one undocumented behaviour in the COPY statement for Parquet ingestion that cost me half a day. SQLite would not have had that problem.
The indicator engine runs in under 500ms for a full 100-ticker scan. The trade journal queries are instant. The LLM summariser pipeline reads from DuckDB and writes results back in the same transaction — no intermediate files.
The local-first constraint was the right call. My portfolio data never leaves my machine. The analysis runs offline. The only network calls are the data ingestion (IDX price feed) and the LLM API (Anthropic). Everything else is local.
Two signals would flip me back.
A team of more than one. DuckDB's single-writer constraint becomes a coordination problem with multiple contributors. SQLite's WAL mode handles concurrent writes gracefully.
A transactional workload. If the core use case were insert-heavy (logging every user action, storing chat messages), SQLite's row-oriented storage would win. DuckDB is not optimised for high-frequency small inserts.
Until those signals appear, DuckDB stays. The 14× speedup on the analytical core is not a marginal win — it is the difference between a tool I reach for and one I avoid.