DuckDB - Local Analytics Without Running a Database Server

Several projects in the recent link-shortener series touched the same idea: keep the application small, but still give users useful analytics.

That is where DuckDB keeps appearing.

It is not another service you run next to Postgres, Redis, or ClickHouse. It is an embedded analytical SQL engine that an app can carry inside its own process, often backed by a local .duckdb file or used directly over CSV and Parquet.

What is DuckDB?

DuckDB is an open-source analytical database engine.

• DuckDB GitHub Source Code
• DuckDB Website
• DuckDB Documentation
• DuckDB CLI Docs
• DuckDB Python Docs
• License: MIT

I inspected:

repo: https://github.com/duckdb/duckdb
commit: df47ba32d99dd0f4cb21ae4bded9f1105bb9be42
observed latest tag: v1.5.6
tested Python package: duckdb 1.5.6
license: MIT

The shortest practical explanation:

DuckDB is SQLite-shaped operationally, but analytics-shaped logically.

You embed it, import it, or run the CLI. Then you query local tables, CSV, Parquet, Arrow, pandas, and other data sources with SQL.

Why It Matters for Self-Hosters

Self-hosting discussions often jump straight from SQLite to Postgres to ClickHouse.

DuckDB sits in a different spot:

That is why tools like Slite can use SQLite for link metadata and DuckDB for visit analytics. The split is sensible: short-link state is transactional; click history is analytical.

Tech Overview

DuckDB is mostly C++17, with clients and integrations around the core engine.

The repository source layout is what you would expect from a serious database engine:

• src/parser parses SQL.
• src/planner creates logical plans.
• src/optimizer rewrites plans with rules and cost-based decisions.
• src/execution runs physical operators.
• src/storage manages in-memory and on-disk data.
• src/transaction handles commit and rollback.
• tools/shell builds the CLI.
• api_spec generates the C API headers from declarative specs.
• extension manages bundled and external extensions.

The Local Test

I did not build DuckDB from source. That would test my compiler more than the normal user workflow.

Instead, I used the Python package and ran a small link-analytics style smoke test:

uv run --with duckdb --with pyarrow python3

Inside that session, I created a table of click events, aggregated them, exported to Parquet and CSV, then queried those files directly:

import duckdb

con = duckdb.connect()

con.sql("""
CREATE TABLE clicks(
  link VARCHAR,
  country VARCHAR,
  ts TIMESTAMP,
  latency_ms INTEGER
);

INSERT INTO clicks VALUES
 ('/a','ES','2026-10-01 10:00:00',42),
 ('/a','ES','2026-10-01 10:02:00',55),
 ('/b','US','2026-10-02 09:00:00',84),
 ('/a','PL','2026-10-02 12:00:00',38);
""")

print(
  con.sql("""
    SELECT link, count(*) AS clicks, avg(latency_ms)::INTEGER AS avg_ms
    FROM clicks
    GROUP BY link
    ORDER BY clicks DESC
  """).fetchall()
)

Result:

duckdb 1.5.6
rollup [('/a', 3, 45), ('/b', 1, 84)]
parquet_country_counts [('ES', 2), ('PL', 1), ('US', 1)]
csv_rows 4

That is the core appeal. You can keep analytical rows local, export them to a portable columnar format, and query them with SQL without provisioning another database container.

Why Not Just Use Postgres?

Postgres can absolutely do analytics, especially at modest size.

The difference is operational intent.

Postgres is a networked OLTP database that is excellent for shared application state. DuckDB is an embedded OLAP engine that is excellent for scanning columns, joining local data, querying files, and running analytics near the data.

For a web app, a healthy pattern looks like this:

• Postgres, MySQL, or SQLite stores users, teams, links, API keys, billing, auth, and configuration.
• DuckDB stores append-heavy analytical events or reads exported files.
• ClickHouse enters when analytics must scale beyond a single machine or needs many independent writers.

How It Relates to the Link Shortener Series

Yes, DuckDB showed up in the link-shortener work.

The clearest example is Slite .

Slite is related to Sink, but it avoids Cloudflare D1/KV/Analytics Engine/R2 by moving to a local stack:

So DuckDB is not replacing the whole application database. It is replacing the analytics backend.

Other shorteners made different tradeoffs:

• Dub is much heavier because it expects SaaS-grade infrastructure such as MySQL, Redis/QStash, ClickHouse/Tinybird, object storage, and edge hosting.
• ha.mr is more minimal and mostly avoids the dynamic database problem.
• Kutt , Shlink , and YOURLS are more traditional web-app/database shorteners.

DuckDB explains one of the middle paths: keep the shortener deployable on one box, but still make analytics queries comfortable.

Should You Run DuckDB in Docker?

Usually, no.

There are DuckDB containers and you can wrap DuckDB in a container for scheduled jobs, dbt-style transforms, or repeatable pipelines. But for the normal use cases, DuckDB is a binary or library:

duckdb analytics.duckdb

or:

uv run --with duckdb python3 script.py

For this post, I did not add a Home-Lab docker-compose.yml because there is no long-running service to expose on a port. When another app embeds DuckDB, that app owns the container and the volume.

Practical Patterns

Local File Analytics

SELECT country, count(*) AS visits
FROM 'clicks.parquet'
GROUP BY country
ORDER BY visits DESC;

Application-Local Event Store

An app can append events to DuckDB and later run dashboard queries:

SELECT
  link,
  date_trunc('day', ts) AS day,
  count(*) AS visits
FROM clicks
GROUP BY link, day
ORDER BY day DESC;

Browser Analytics

DuckDB-Wasm lets a static browser app run SQL locally over user-provided files. That is useful for privacy-preserving data tools because the file does not need to be uploaded to a backend just to run a query.

FAQ

Is DuckDB self-hosted?

Not in the usual web-app sense. DuckDB is an embedded database engine and CLI, not a dashboard service with a login page. You install it, import it as a library, or use it inside another app.

Which link shorteners were using DuckDB?

From this recent series, Slite is the direct DuckDB example. It uses SQLite for the authoritative short-link state and DuckDB for visit analytics at /data/analytics.duckdb.

The others are useful comparisons because they solve the same redirect/analytics problem with different storage layers:

That comparison is the key lesson: DuckDB is not a generic requirement for link shorteners. It is a good local replacement for a separate analytics database when the app can keep analytical events in one embedded file. Slite uses that pattern. Dub goes the other way with a dedicated SaaS-grade analytics plane. ha.mr avoids the problem almost entirely.

Is DuckDB a replacement for Postgres or MySQL?

Not for general web-app state. Use Postgres/MySQL/SQLite for transactional application data. Use DuckDB when the question is analytical: counts, group-bys, dashboards, joins over files, CSV/Parquet exploration, and reporting.

Is DuckDB a SQLite competitor?

Only partly.

DuckDB and SQLite feel similar operationally because both can be embedded, file-backed, and used without running a separate database server. That is why the comparison is useful.

But they are optimized for different workloads:

So for a URL shortener, SQLite is a good place to store “this short code points to this URL”. DuckDB is a better fit for questions like “how many clicks did each link get by day, country, browser, or referrer?”

That is why Slite using SQLite plus DuckDB makes sense: SQLite owns the application state, DuckDB owns the analytical questions.

Can multiple containers write to the same DuckDB file?

Do not design it that way unless the upstream app explicitly supports it. Treat DuckDB as an embedded database owned by one application process. Multiple readers are a different story, but multiple independent writers are not the default deployment model most self-hosters should assume.

Does DuckDB work with CSV and Parquet?

Yes. This is one of the main reasons it is so practical. You can query files directly from SQL:

SELECT * FROM 'myfile.csv';
SELECT * FROM 'myfile.parquet';

Does DuckDB run in the browser?

Yes. DuckDB-Wasm is the WebAssembly build of DuckDB, so browser apps can run analytical SQL client-side. That does not mean every DuckDB feature behaves exactly like a native server environment, but it is powerful enough for serious local file analysis and static web tools.

Does this need Docker Compose in Home-Lab?

No. I did not add a Home-Lab compose snippet for DuckDB itself because DuckDB is not a daemon that needs a port. If a self-hosted app embeds DuckDB, the compose file belongs to that app and should mount the app’s data volume correctly.

What did the local test prove?

It proved the practical reader workflow: install the Python package, create analytical rows, aggregate them with SQL, export to Parquet/CSV, and query those files back directly. It was not a benchmark and should not be read as a performance claim.

Conclusion

DuckDB is one of those tools that makes more sense after you stop trying to place it in the normal “database server” box.

It is a local analytical SQL engine.

For self-hosters, that means fewer moving parts when a project only needs local dashboards or event rollups. For developers, it means fast SQL over files without standing up infrastructure. For the link-shortener series, it explains why Slite can stay lightweight while still offering analytics: SQLite handles the app state, DuckDB handles the analytical questions.