The need for analytics usually starts with the same sentence: "We need to be able to query this data properly." What follows is predictable. A few meetings, an architecture diagram, a cloud warehouse pricing page, then the phrase "let's build a pipeline first." Months later there is a working warehouse, but nobody has answered the question that started it all.
Yet most of those first questions can be answered by a few gigabytes sitting on a single disk, on a single machine. DuckDB lives exactly in that gap: a columnar analytical engine with no server, running inside your process. What makes it interesting to me isn't how fast it is, it's how plainly it tells you where its limit is. Plenty of tools oversell their capacity; few document their boundary honestly.
This article answers two questions at once: when does DuckDB genuinely remove the need for a warehouse, and at what point does it say "stop here"?
An analytical engine without a server
DuckDB runs as a library inside your application. There is no service to keep alive, no port to listen on, no user table to manage. In that sense it resembles SQLite; but while SQLite is row-based and designed for transactional load, DuckDB is a vectorized engine that scans columns. "How many requests came from each category this month" and "update this one record" are two different worlds, and they are not built with the same ease.
As I write this, the current version is v1.5.5 (22 July 2026). The long-term support line is v1.4.0 "Andium" — released on 16 September 2025, with community support ending a year later, on 16 September 2026. I'm putting these dates up front for a reason; we'll get to how they turn into a production decision.
Querying without moving the data
If I had to pick a single feature that makes DuckDB worth trying, I'd pick its ability to query data without importing it into its own format first. The classic flow says "load, then query." Here the file itself acts as the table:
-- CSV: delimiter, quoting and type inference are detected automatically
SELECT category, count(*) AS n
FROM read_csv('logs/2026-08-*.csv')
GROUP BY category
ORDER BY n DESC;
-- Parquet: a glob pattern turns a folder full of files into one table
SELECT date_trunc('day', ts) AS day, avg(duration_ms)
FROM read_parquet('warehouse/events/**/*.parquet')
GROUP BY 1;
Note the CSV side: read_csv figures out the configuration with its own sniffer, so you don't need a separate automatic variant. The read_csv_auto habit you see in older guides isn't what today's documentation puts forward; the current path is read_csv directly.
Remote storage connects with the same logic, but there's an outdated-method trap here. The SET s3_access_key_id = ... examples that have circulated for years are now history; current documentation marks that as the "deprecated S3 API" and points you to a secret definition instead:
INSTALL httpfs; LOAD httpfs;
CREATE OR REPLACE SECRET secret (
TYPE s3,
PROVIDER credential_chain
);
SELECT * FROM read_parquet('s3://bucket/events/2026/08/*.parquet') LIMIT 10;
The credential_chain provider pulls credentials from the AWS SDK's own chain: profiles, SSO, assumed roles, instance metadata. Not embedding a key in SQL is reason enough on its own.
⚠️ Don't reach for persistent secrets casually
The
CREATE SECRETabove is temporary and lives only for that session.CREATE PERSISTENT SECRET, by default, writes to~/.duckdb/stored_secrets, and the documentation says it outright: persistent secrets are stored on disk in unencrypted binary format. DuckDB also has no user or role-based access control, which means filesystem permissions are your only line of defence for both the database file and the secret directory. On a shared machine, that is the bill for the comfort of having "no user table to manage".
The same approach works against a live PostgreSQL. This is what most teams actually wanted before they built a warehouse — reading a table from the operational database for a heavy analytical query:
ATTACH 'dbname=app user=readonly host=10.0.60.20' AS pg (TYPE postgres, READ_ONLY);
SELECT count(*) FROM pg.public.orders WHERE created_at >= '2026-08-01';
That READ_ONLY isn't a preference for me, it's a reflex. And the old postgres_attach() function is explicitly marked deprecated in the documentation; anything written today should use ATTACH.
ℹ️ The connection string will look familiar
ATTACHaccepts either a libpq connection string or a PostgreSQL URI as input. So your existing habits withPGHOST,PGUSERandPGPASSWORDcarry over as they are; there is no separate credential system to learn.
What it looks like day to day
Whether a tool fits into daily work is decided less by its feature list than by the question "how many keystrokes sit between two questions?" DuckDB is comfortable here: one command in the terminal, one line inside Python.
import duckdb
con = duckdb.connect("analytics.db")
con.execute("SET memory_limit = '4GB'")
df = con.sql("""
SELECT strftime(ts, '%Y-%m') AS month, count(*) AS requests
FROM read_parquet('access/*.parquet')
GROUP BY 1 ORDER BY 1
""").df()
Getting the result straight into a DataFrame largely removes the risk of an analysis turning into a data-migration project. For anyone who prefers the terminal, the command line client matured noticeably in v1.5: a dynamic prompt showing the current context, a pager for long results, improvements to .tables and DESCRIBE for inspecting tables, plus a _ shortcut for reusing the previous result. The client can now also be installed with pip install duckdb-cli.
The same release touched the engine itself: checkpointing is no longer blocking, meaning reads don't stop while a write is in progress. The project's own announcement reports roughly a 17% throughput improvement on a TPC-H workload. I won't present that number as my own measurement — what happens with your data can only be seen with your data. But the direction is right: long-running writes no longer stalling readers reduces the most irritating friction in a single-machine analytics setup.
For anyone dealing with semi-structured data, v1.5's VARIANT type is also worth noting. Instead of storing JSON as text and parsing it on every query, it keeps typed, binary data. If your event records keep changing shape, being able to work without freezing the schema upfront is a real relief.
Memory: generous defaults, a disk safety net
The first fear in local analytics is always the same: "What if the data doesn't fit in memory?" DuckDB's answer is spilling to disk. The documented defaults are quite generous too: memory_limit is 80% of the machine's RAM, and threads matches the CPU core count.
I never leave those two at their defaults on a shared machine. If an analysis job running next to a CI runner quietly takes every core and three quarters of the memory, your problem is no longer the query, it's your relationship with the neighbours:
SET memory_limit = '6GB';
SET threads = 4;
SET temp_directory = '/var/tmp/duckdb_spill.tmp/';
SET preserve_insertion_order = false;
That last line is less known but useful: it lifts the requirement to preserve result order for queries without ORDER BY, which lets the system re-order results and reduce memory usage. temp_directory is where intermediate results are written when blocking operators — GROUP BY, JOIN, ORDER BY, window functions — exceed the memory limit. It defaults to a location next to the database file; if your fast disk lives elsewhere, moving it can make a measurable difference.
The real wall: how many writers at once
So far everything looks almost too good. Now let's talk about the limit, because this is what decides the architecture.
DuckDB's concurrency model is documented as two options for in-process mode: either a single process reads and writes, or multiple processes read only (access_mode = 'READ_ONLY'). In that mode there is no combination of the two. Inside a single process things are comfortable: thanks to MVCC and optimistic concurrency control, threads can write in parallel and appends to the same table never conflict; but if two threads try to edit the same row, the second one fails with a conflict error.
The reason for this design is documented as well: caching data in RAM instead of going back and forth to disk on every query, in other words preserving analytical speed. So this isn't a shortcoming, it's a deliberate trade-off.
Can the wall be crossed? I stressed "in-process" for a reason. The documentation describes a separate path for writing from multiple processes: the Quack remote protocol, which turns DuckDB into a client-server database. The same page states its maturity plainly; Quack is in beta as of DuckDB v1.5.2 and is expected to mature with v2.0 in the autumn of 2026. For concurrent reads and writes, the option currently pointed at as intended for production is a different one: a DuckLake setup with PostgreSQL as the catalog. So the wall is coming down, but it hasn't come down yet. I wouldn't build a nightly reporting line on top of a beta protocol, and I also know this paragraph will need rewriting in six months.
What does that mean in practice? If you're thinking of pointing a twenty-person team's live dashboard directly at a single .duckdb file, close that dashboard before you open it. But this arrangement works perfectly well: a single writer job runs at night, prepares the data, publishes the result as Parquet, and dashboards and analysts open that output read-only. One writer, many readers. The classic way to violate this limit is well known too: two scheduled jobs try to write the same file, one waits for the other, then one of them dies. That should sound familiar. My experience with SQLite's locking behaviour taught the same lesson: in embedded databases, the number of writers sets a ceiling long before data size does.
This blog's own content is served from a SQLite-based database as well; comments, reactions and post records all live there. In a setup like that, the right reflex when analytics enters the picture is not to push the operational database harder, but to separate reads. That is exactly where DuckDB is attractive: it makes that separated read side substantially stronger without a warehouse bill.
Three details need settling upfront when you build this, otherwise they all get asked at once on the first bad night. First, publishing has to be atomic: write the Parquet output to a temporary directory and rename it when the job finishes, or readers will see a half-written folder. Second, backups: a .duckdb file is a single artifact and easy to back up, but a copy taken while the writer is running may not be consistent; take it after the job completes. Third, detection: if the writer job dies quietly nobody notices, so monitor the freshness of the output with a separate check.
What I like about this arrangement is that it borrows the disciplined part of warehouse architecture rather than the complicated part: it's clear who produces the data, the output is versioned, and consumers are isolated from production. You don't need a distributed system for that; a scheduled job and an output folder cover most teams' needs. The cost of buying complexity early usually shows up not on the invoice, but in a stack nobody fully understands.
Version policy: where it bites quietly
Now back to those dates from the beginning, because this is the most overlooked part of taking DuckDB into production.
On the storage format the project is conservative: every version from v1.0 through v1.5 creates files with storage version 64 by default, corresponding to the v1.0.0 format. Anyone who wants newer features can deliberately move forward with the STORAGE_VERSION option introduced in v1.2.0:
ATTACH 'analytics.db' (STORAGE_VERSION 'v1.2.0');
In the compatibility table, v1.4.x maps to storage version 67 and v1.5.x to 68. Backward compatibility is the intended behaviour; forward compatibility is provided on a best-effort basis. If different machines on your team run different DuckDB versions, take that "best effort" phrase seriously.
In practice: pin the version, and write down where you pinned it. If the CI step running the analysis job, the developer's laptop and the scheduled job don't share a version, one day someone won't be able to open a file another one produced. Failures like this are never loud; they show up at month end as "why is the report empty today?" Black boxes spend their worst nights without telling anyone.
The support calendar genuinely requires a decision right now, as we move into autumn 2026:
| Version | Codename | Released | End of support |
|---|---|---|---|
| 1.4.0 LTS | Andium | 2025-09-16 | 2026-09-16 |
| 1.5.0 | Variegata | 2026-03-09 | 2026-09-01 |
Starting with v1.4.0, every other release becomes long-term support, and community support for LTS versions currently lasts a year after release. Now look at the real surprise in that table: the problem isn't the LTS line. Community support for v1.5.0 ends on 1 September 2026, only days after this article. The LTS line that's easy to criticise holds out two weeks longer, until 16 September. Whichever line you're on today, you have to open an upgrade window in September.
The calendar shows 1.5.6 for 16 September 2026 and 2.0.0 for the autumn, and the project marks both as tentative. If it were me, I'd pin the version today, put the September window on the calendar now, and knowingly accept the uncovered days until the next release lands. For teams that must stay on an LTS past its community support, there's a paid door as well: DuckLabs offers support for expired LTS versions. The decision isn't "which line is safer", it's "when do I open the upgrade window".
Two breaking changes in v1.5 can affect existing queries, one quietly and one loudly. The quiet one: date_trunc() on a DATE now returns a TIMESTAMP instead of a DATE. The loud one: the arrow lambda syntax (x -> x + 1) now throws a warning, and the warning text asks you to migrate before DuckDB's next release; the recommended form is the Python-style lambda x: x + 1. The announcement also states that DuckDB 2.0 will disable the arrow syntax by default, so there's no point in letting those warnings pile up.
DuckLake: when a pile of files becomes a table
Teams that hit the single-writer limit usually reach for a warehouse first. There's an intermediate stop: DuckLake, a lakehouse format offered as a DuckDB extension. The data stays in Parquet files while the catalog and version information live in a SQL database, which brings capabilities like snapshots and time travel.
The specification has reached version 1.0 and is supported by DuckDB v1.5.2 and later. If you have more than one writer and the question "which report was produced from which data?" has reached the table, this is where to look before the cloud warehouse.
A decision framework
The checklist I run through is short:
- Is there exactly one writer? If not, DuckDB alone isn't the right answer.
- Can readers open it read-only? If they can, sharing the same file is fine.
- Where does the data live? If it can stay on Parquet/CSV/S3/PostgreSQL, don't copy it; DuckDB's real gain comes from not moving data.
- Are memory and core limits written down explicitly? On a shared machine, defaults upset the neighbours.
- Are the version and storage version pinned? Everyone on the team should be on the same line, with the upgrade date on the calendar.
- What happens if this workload grows tenfold tomorrow? If the answer is "more RAM in the same machine," carry on; if it's "more users," the plan has to change.
Conclusion
Calling DuckDB a "small warehouse" does it a disservice, because it doesn't shrink the problem — it puts the problem in the right place. A significant share of warehouse decisions come not from a technical necessity but from uncertainty about who owns the data. Before committing questions answerable by a single machine and a single writer process to a monthly bill, it's worth checking how many people actually ask those questions, and how often.
My measure is this: if a cron job and a handful of SQL queries cover your analytics needs, every infrastructure layer you didn't build is profit. And if they don't, DuckDB tells you early and in writing. The most expensive kind of tool is the one that reveals its limit only in production.
Top comments (2)
𝙏𝙝𝙚 𝙨𝙞𝙣𝙜𝙡𝙚 𝙬𝙧𝙞𝙩𝙚𝙧 + 𝙢𝙪𝙡𝙩𝙞𝙥𝙡𝙚 𝙧𝙚𝙖𝙙𝙚𝙧𝙨 setup makes a lot of sense 😄
I was actually wondering about this when I first came across DuckDB for analytics. And honestly, good that you covered the limitations too — all that glitters is not gold 😅
Not everything needs a warehouse from day one. Sometimes keeping it simple just works better.
Exactly 😄 That trade-off is what I find most useful about DuckDB. The single-writer limitation may look restrictive at first, but once you design around it intentionally, the architecture becomes surprisingly clean.
And yes — “not everything needs a warehouse from day one” is pretty much the whole point. Complexity is easy to add later; removing it after a system grows around it is much harder. 😅
Thanks for reading!