I picked DuckDB over SQLite for SahamLens. Here’s what the trade-off cost me.
DuckDB turned out to be a better fit than SQLite for SahamLens because the core workload is analytical: scanning historical market data, computing indicators, and preparing research outputs locally.
May 20, 2026·11 min read·sahamlens, duckdb, sqlite, local-first, database-architecture
I picked DuckDB over SQLite for SahamLens. Here’s what the trade-off cost me.
SahamLens is a local-first trading intelligence platform built for Indonesian stock investors. One of the earliest architectural decisions in the project was the storage layer: whether to use SQLite, DuckDB, or a more conventional server database such as PostgreSQL.
I ended up choosing DuckDB for the analytical core of the product. At the time, that choice was driven less by novelty and more by workload shape. SahamLens spends most of its time reading historical market data, computing indicators over multi-year windows, scanning groups of tickers, and feeding the results into dashboards, portfolio views, and AI-assisted research workflows. That is much closer to an analytical workload than to a traditional CRUD-heavy web application.
DuckDB fit that profile well. It also introduced trade-offs that I would not have had with SQLite.
This post is about why I chose DuckDB, where it was the right decision, and where it made the architecture more complicated than the simpler option would have.
The decision was not “DuckDB vs PostgreSQL.” It was “analytical local engine vs general embedded store.”
At first glance, SQLite looked like the obvious choice.
It is embedded, zero-configuration, stable, easy to package, and well understood. For local-first applications, SQLite is often the default answer because it removes operational overhead and works well as a lightweight application database.
DuckDB looked less conventional. It is also embedded and easy to distribute, but it is designed primarily as an analytical engine rather than as a general-purpose transactional store. That distinction matters because SahamLens is not just storing user settings or a few application records. The core of the platform revolves around analytical queries on market data.
The application needs to support workflows such as:
loading historical OHLCV data for multiple IDX tickers
computing indicators such as moving averages, RSI, MACD, and rolling volume statistics
scanning watchlists across multi-month or multi-year windows
comparing signal conditions across groups of stocks
preparing structured summaries for AI-assisted research features
querying journal, watchlist, and portfolio-related data alongside market-derived features
Those are not especially complex workloads from a data engineering perspective, but they are much closer to “small analytical warehouse on a laptop” than to “user table plus settings table plus a few forms.”
Once I looked at the product through that lens, DuckDB became a serious candidate rather than a novelty choice.
Why DuckDB matched the workload better
The strongest argument for DuckDB was not a single benchmark result. It was the fact that its execution model matched the shape of the work SahamLens performs most often.
On this page
SahamLens does a lot of read-heavy analysis over tabular time-series data. The system repeatedly scans price history, computes derived columns, filters by indicator conditions, and joins those results into higher-level views used by the dashboard and research workflows. This is the kind of workload where columnar execution and vectorised processing can make a noticeable difference, especially compared with a database optimized more broadly for row-oriented transactional usage.
In practical terms, DuckDB was a good fit for several reasons.
1. Analytical queries are a first-class use case
DuckDB is built for analytical scans over structured datasets. That maps well to stock market data, where most operations involve reading many rows, computing rolling metrics, aggregating by ticker or timeframe, and producing derived outputs rather than updating individual rows at high frequency.
For SahamLens, that matters more than raw insert throughput. The product is not a brokerage backend processing orders. It is an investor workspace that needs to query, transform, and interpret historical market data quickly enough that analysis feels immediate.
2. SQL features were useful for indicator pipelines
Another reason DuckDB fit well is that it provides a SQL environment that is comfortable for analytical transformations. Window functions, aggregation, and table-oriented transformations are central to many indicator and screening workflows.
That matters because a lot of financial analysis logic can be expressed more clearly as data transformations than as deeply nested application loops. Once the data is in a structured analytical engine, tasks such as ranking, rolling calculations, partitioned windows, and summary extraction become much easier to express and iterate on.
3. It simplified the local-first architecture
SahamLens is intentionally local-first. That means I wanted the analytical layer to run on the user’s machine without requiring a separate database server, container, or networked dependency.
DuckDB preserved that deployment simplicity while still giving me an engine oriented toward analytical work. I did not have to choose between “lightweight local setup” and “database that handles scans and transformations well.” DuckDB gave me both in one component.
What I actually gained from the choice
The most visible benefit was query speed on the analytical core.
Once historical market data was loaded locally, scanning ticker groups, computing indicator-related views, and preparing derived outputs became fast enough that the analysis workflow felt interactive rather than batch-like. That mattered because SahamLens is not a back-office reporting tool. It is a daily-use research workspace, and even modest latency becomes noticeable if the user repeatedly waits for scans, dashboards, or AI-prep pipelines to finish.
The benefits showed up in several parts of the system:
indicator and watchlist scans became fast enough to run as part of normal UI workflows
dashboard reads felt closer to local analytics than to server-backed reporting
the LLM summarisation pipeline could read structured market context from the same local store without additional export steps
journal and portfolio-related features could coexist with the analytical layer without introducing a separate data processing stage
Even without obsessing over microbenchmarks, the qualitative difference was clear: the analytical core felt like something I could build more features on top of, rather than something I had to work around.
Where DuckDB made the architecture harder
DuckDB was a strong fit for the analytical side of SahamLens, but it was not a free win. The trade-off was that I was no longer using the most conservative and frictionless option for an embedded application database.
There were three areas where that cost showed up.
1. Concurrency assumptions became more important
SQLite is extremely forgiving as an embedded application store because it has a mature operational story for many small application patterns. DuckDB can absolutely be used inside local applications, but it is not trying to be a drop-in answer for every concurrency pattern a web-style app might create.
That mattered during development, especially when a dev server, hot reload, background analysis tasks, and bridge processes were all touching the same local database lifecycle.
The most annoying version of this showed up in development rather than production: repeated connection churn created by the dev environment caused instability around connection management. The root issue was not that DuckDB “cannot work” in that setup, but that the application needed to be more deliberate about how connections were opened, reused, and closed.
The fix was to move toward a singleton-style connection pattern in the local analysis bridge rather than letting the surrounding environment create ad hoc connections during reload-heavy development loops.
That is not an impossible problem, but it is exactly the kind of problem I would have preferred not to think about during early product iteration.
2. The ecosystem path is narrower than SQLite’s
SQLite has decades of examples, wrappers, migration patterns, debugging threads, and battle-tested assumptions across almost every stack. If something strange happens with SQLite in a local app, there is a good chance someone has already hit it, documented it, and posted a workaround.
DuckDB’s ecosystem is growing quickly, but it is still narrower. In practice, that means more time reading documentation carefully, more time validating assumptions around integration details, and occasionally more time dealing with behavior that is entirely reasonable once understood but not as familiar as the SQLite equivalent would have been.
For a solo project, this cost is manageable. For a team trying to minimize infrastructure-related surprises, it is a real factor.
3. I had to think harder about what belongs in the analytical store
Choosing DuckDB pushed me to be explicit about workload boundaries inside the application.
Some data in SahamLens is clearly analytical:
historical price data
derived indicators
market scans
signal summaries
LLM-ready research context derived from market data
Other data is more application-oriented:
journal entries
portfolio notes
user preferences
workflow state
lightweight metadata around reviews or saved analysis
DuckDB can store both, but the decision forced me to ask whether every table truly belonged in the same engine or whether I was mixing analytical and application concerns simply because the local-first setup made it convenient.
That is not necessarily a downside. In some ways, it was a healthy architectural constraint. But it did add design overhead compared with “put everything in SQLite and move on.”
The real trade-off: DuckDB made the analytical path easier and the application path more opinionated
Looking back, I do not think the trade-off can be summarized as “DuckDB is faster but less convenient.” That is directionally true, but it misses the more important distinction.
DuckDB made the analytical path of SahamLens much more natural. It encouraged me to think in terms of datasets, scans, transformations, rolling windows, and queryable derived outputs. That was exactly the right mental model for the market-analysis part of the product.
At the same time, it made the application path more opinionated. Connection handling, workload boundaries, and integration patterns needed a little more care than they would have with SQLite. I could not treat the database as a generic invisible storage box and forget about it. The choice surfaced architectural questions earlier.
That was sometimes inconvenient, but it also forced clarity about what SahamLens actually is. It is not just a web app with some charts. It is a local analytical workspace with an application layer around it.
Why I did not just use PostgreSQL
A reasonable question is why I did not avoid the embedded-database trade-off entirely and just use PostgreSQL.
The answer is that PostgreSQL solves a different problem than the one I was trying to solve in the first version of SahamLens.
I did not want:
a separately managed local database server
a heavier setup burden for a single-user analytical tool
more operational surface area than the product actually needed
the local-first experience to depend on a service that feels like infrastructure rather than part of the app
PostgreSQL would make more sense if SahamLens evolved into a collaborative, server-backed multi-user platform with shared accounts, background jobs, and a stronger transactional backend. For the current shape of the product, that would have been too much system for the job.
When I would choose SQLite instead
If I were building a different kind of local-first product, I would absolutely pick SQLite.
There are at least two conditions that would push me in that direction.
1. The workload becomes more transactional than analytical
If the core of the product shifted toward frequent small writes, event logging, chat-like interactions, or heavy mutation of application state, SQLite would become much more attractive. In that world, the main value of the database is not analytical scanning performance but operational simplicity and broad compatibility.
2. The app becomes collaboration-heavy or multi-process in a way that complicates the local engine
If the architecture started to rely on multiple contributors, more aggressive background workers, or a larger number of simultaneously active write paths, then I would re-evaluate whether the current local analytical design was still the right fit. At that point, either SQLite or a more explicit client-server database would deserve another look depending on the product direction.
What I learned from the decision
The main lesson was that local-first storage decisions should start with workload shape, not with popularity or familiarity.
SQLite is a great default for local applications, but “local app” is not a workload category. A note-taking app, a journaling tool, a stock analysis workspace, and a collaborative offline editor can all be local-first while needing very different storage behavior.
For SahamLens, the important question was not “what embedded database do people usually use?” It was “what kind of work will this application ask its database to do every day?”
Once the answer was framed that way, DuckDB made sense.
Closing thoughts
I do not think DuckDB is the universal answer for local-first apps, and I would not recommend using it just to be unconventional. But for SahamLens, where the core loop revolves around historical market data, indicator computation, scanning, and local analytical workflows, it was the right trade-off.
The choice did cost me some simplicity. I had to be more deliberate about connection handling, more careful about workload boundaries, and more willing to debug a less conventional local stack than I would have with SQLite.
But the payoff was that the analytical core of the product feels aligned with the tool’s actual purpose. SahamLens is supposed to behave like a personal market research workspace, not just a CRUD app with stock data attached to it. DuckDB helped push the architecture in that direction.