If you’ve been doing data work on a laptop with PostgreSQL, you’ve already felt the friction. You have a 2 GB CSV from the client. You spin up Postgres, create a schema, write a COPY statement, wait for the import, then you can query it. Twenty minutes gone and you haven’t written a single analytical query yet.
DuckDB is a different mental model entirely. It’s not "a lighter Postgres." It’s an in-process OLAP engine designed from scratch for the read-heavy, schema-flexible, file-first workflows that analysts actually live in. There’s no server, no daemon, no TCP socket. It’s a library that you embed — or a single binary you run — and it speaks SQL that’s genuinely more expressive than what you’re used to.
Official repo: https://github.com/duckdb/duckdb
This isn’t a "DuckDB is great, use it" post. This is a practical rundown of ten specific capabilities that DuckDB has and PostgreSQL doesn’t — with code you can run today.
Setting up in 30 seconds
# Install the CLI
curl -fsSL https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.gz \
| gunzip > /usr/local/bin/duckdb && chmod +x /usr/local/bin/duckdb
# Or via Python (most analysts end up here anyway)
pip install duckdb
That’s it. No initdb, no pg_hba.conf, no port binding. You either use the CLI (duckdb mydb.duckdb) or the Python API. Both work against the same .duckdb file, and an in-memory database is just duckdb.connect() with no arguments.
1. Query a CSV file without importing it
This one sounds like a parlor trick until you’ve actually needed it at 11pm before a client call.
-- DuckDB: no import, no schema, no ceremony
SELECT region, SUM(revenue) AS total
FROM read_csv_auto('sales_export_2024_Q4.csv')
GROUP BY region
ORDER BY total DESC;
DuckDB sniffs the delimiter, quotes, encoding, and column types automatically. It handles malformed rows, different newline conventions, and multi-file globs:
-- Query all monthly exports at once
SELECT month, COUNT(*) FROM read_csv_auto('exports/2024_*.csv') GROUP BY month;
PostgreSQL has COPY FROM but it requires a pre-existing table with the right schema. You cannot SELECT directly from a CSV path. Full stop.
Gotcha: read_csv_auto type inference can surprise you on columns that look numeric but have occasional "N/A" strings. Use read_csv('file.csv', types={'revenue': 'VARCHAR'}) when you need explicit control.
2. Read and write Parquet natively
Parquet is the de facto format for analytical data. Every data warehouse, every Spark job, every dbt artifact outputs Parquet. PostgreSQL has no native support for it — you need an external loader or a foreign data wrapper that is anything but trivial to set up.
-- Read a Parquet file
SELECT * FROM 'warehouse_snapshot.parquet' LIMIT 100;
-- Write query results to Parquet
COPY (
SELECT user_id, COUNT(*) AS events
FROM 'events/*.parquet'
GROUP BY user_id
) TO 'user_summary.parquet' (FORMAT PARQUET);
DuckDB reads Parquet columnar — it only deserializes the columns you SELECT, which means on a wide table with 200 columns, a query touching 5 columns reads roughly 2.5% of the file bytes. On a laptop with an SSD, this makes 10–50 GB datasets queryable without breaking a sweat.
3. Query S3, HTTPS, and remote filesystems without a pipeline
This alone has replaced entire data pipelines in my workflows.
-- Install the extension once
INSTALL httpfs;
LOAD httpfs;
-- Set your credentials (or use IAM role / env vars)
SET s3_region = 'eu-central-1';
SET s3_access_key_id = 'AKIA...';
SET s3_secret_access_key = '...';
-- Query S3 directly, no download
SELECT customer_segment, AVG(order_value)
FROM 's3://my-data-lake/orders/2024/**/*.parquet'
GROUP BY customer_segment;
DuckDB uses HTTP range requests — it fetches only the row groups it needs based on Parquet metadata. A 100 GB file might require only 300 MB of network transfer for a selective query.
Gotcha: S3 request costs are real. A query that scans many small files will generate thousands of GET requests. Always prefer fewer large Parquet files partitioned well. Use EXPLAIN to see how many files DuckDB is touching before you run something expensive.
4. Parallel columnar execution — out of the box, no tuning
PostgreSQL’s planner is brilliant for OLTP. It’s mediocre for analytical queries on large local datasets because its execution model is row-at-a-time. DuckDB uses vectorized columnar execution with automatic CPU parallelism.
-- DuckDB uses all your cores automatically
SELECT
date_trunc('month', event_date) AS month,
event_type,
COUNT(*) AS cnt,
COUNT(DISTINCT user_id) AS unique_users
FROM 'events.parquet'
GROUP BY 1, 2
ORDER BY 1, 2;
On a 4-core laptop with a 50M-row Parquet file, this takes 1–3 seconds. The equivalent query in PostgreSQL on an imported table often takes 30+ seconds because it’s single-threaded by default and operates row-by-row through a heap.
You can tune parallelism if needed:
SET threads = 8; -- match your core count
SET memory_limit = '8GB';
But you won’t need to — the defaults are already sane for a laptop.
5. PIVOT and UNPIVOT as first-class SQL syntax
Every analyst who’s ever written a CASE WHEN cascade to pivot rows into columns knows the pain. PostgreSQL has no PIVOT syntax. You either write ten CASE WHEN statements or you use a crosstab extension that requires tablefunc and syntax nobody can remember cold.
-- DuckDB: proper PIVOT
PIVOT sales
ON quarter
USING SUM(revenue)
GROUP BY product_line;
Output: one row per product line, one column per quarter. Clean, readable, no extension required.
The reverse works just as naturally:
-- UNPIVOT wide table back to long format
UNPIVOT monthly_kpis
ON jan, feb, mar, apr, may, jun
INTO NAME month VALUE amount;
Gotcha: Dynamic pivot (where you don’t know the column values at query time) requires a slightly different syntax with PIVOT ... ON col IN (...). DuckDB supports this but you need to enumerate the values or build the query dynamically in Python.
6. Lambda functions and list operations in SQL
DuckDB has a proper array/list type with functional-style operations that have no equivalent in standard PostgreSQL array functions.
-- Filter a list inline
SELECT list_filter([1, 2, 3, 4, 5], x -> x > 3);
-- [4, 5]
-- Transform a list
SELECT list_transform(['alice', 'bob', 'carol'], s -> upper(s));
-- ['ALICE', 'BOB', 'CAROL']
-- Reduce a list
SELECT list_reduce([1, 2, 3, 4], (a, b) -> a + b);
-- 10
This sounds academic until you’re working with JSON columns that contain arrays — event sequences, tag lists, feature vectors — and you need to filter or transform them without unnesting, joining back, and re-aggregating.
-- Extract only error-type events from a JSON array column
SELECT
session_id,
list_filter(
json_extract_string(events, '$[*].type'),
e -> e LIKE '%error%'
) AS error_events
FROM sessions_json;
PostgreSQL has array_agg, unnest, and JSON operators, but the functional composition DuckDB offers here is genuinely more expressive.
7. Struct and nested data without a schema migration
In PostgreSQL, adding a nested structure means either a JSONB column (with limited query ergonomics) or a normalized table. DuckDB has a native STRUCT type that you can create inline and query with dot notation.
-- Create struct inline
SELECT {'name': 'Alice', 'scores': [95, 87, 91]} AS student;
-- Access fields with dot notation
SELECT student.name, student.scores[2] AS second_score
FROM (
SELECT {'name': 'Alice', 'scores': [95, 87, 91]} AS student
) t;
More practically, DuckDB automatically infers struct schemas when reading nested JSON or Parquet with nested schemas:
SELECT
payload.user.id,
payload.user.email,
payload.metadata.source
FROM read_json_auto('webhook_events.json');
Gotcha: The STRUCT type is strict once defined. If a field is missing from some records in your JSON, use TRY_CAST or check nullability carefully. read_json_auto with union_by_name=true helps when records have slightly different shapes.
8. ASOF JOIN — time-series alignment with no boilerplate
This is one of those features where PostgreSQL forces you to write a lateral join with ORDER BY and LIMIT 1 that’s both slow and hard to explain to colleagues. DuckDB has ASOF JOIN built in.
Use case: you have a table of stock prices (sampled every minute) and a table of trades (happening at arbitrary timestamps). You want to join each trade to the most recent price snapshot.
SELECT
t.trade_id,
t.symbol,
t.trade_at,
p.price AS price_at_trade,
t.quantity * p.price AS notional_value
FROM trades t
ASOF JOIN prices p
ON t.symbol = p.symbol AND t.trade_at >= p.sampled_at;
DuckDB finds the last prices row where sampled_at <= trade_at for each symbol. No subquery, no lateral, no window function workaround. This pattern appears constantly in IoT, finance, and telemetry data.
9. The Python API is a first-class citizen, not an afterthought
PostgreSQL Python drivers (psycopg2, asyncpg) treat the database as a remote service — you serialize Python objects to SQL strings and deserialize result rows back into Python. There’s a boundary.
DuckDB’s Python API has zero-copy integration with Pandas and Arrow:
import duckdb
import pandas as pd
df = pd.read_parquet('large_dataset.parquet')
# Query the DataFrame directly — no copy, DuckDB reads the Arrow buffer
result = duckdb.sql("""
SELECT
category,
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95
FROM df
GROUP BY category
""").df() # returns a DataFrame
The DataFrame lives in Python memory. DuckDB queries it in place via Arrow IPC. You can mix file sources and in-memory DataFrames in the same query:
duckdb.sql("""
SELECT a.user_id, b.segment
FROM df AS a
JOIN 's3://my-bucket/segments.parquet' AS b
ON a.user_id = b.user_id
""")
Gotcha: When you do .df() on a large result set, you’re pulling everything into Pandas memory. If your result is multi-GB, use .arrow() or write directly to Parquet instead.
10. Built-in export to multiple formats, including partitioned Parquet
PostgreSQL’s COPY TO outputs CSV or a binary Postgres-specific format. That’s it. Exporting to partitioned Parquet (the format that every downstream tool expects for a well-organized data lake) requires an external tool.
-- Write query output as Hive-partitioned Parquet
COPY (
SELECT
year,
month,
region,
product_id,
SUM(revenue) AS revenue
FROM 's3://raw-data/transactions/**/*.parquet'
GROUP BY 1, 2, 3, 4
) TO 'output/' (
FORMAT PARQUET,
PARTITION_BY (year, month),
COMPRESSION 'zstd'
);
DuckDB creates output/year=2024/month=01/data.parquet, output/year=2024/month=02/data.parquet, and so on — the Hive partition layout that Spark, Athena, BigQuery, and Trino all consume natively.
You can also export to JSON, CSV with custom delimiters, and (with the Arrow extension) Feather/IPC format.
When NOT to use DuckDB
This section matters. DuckDB is an OLAP engine. It’s not a replacement for PostgreSQL across the board.
- Concurrent writes from multiple processes: DuckDB supports single-writer access. PostgreSQL’s MVCC handles dozens of concurrent writers gracefully. If your use case involves multiple processes writing simultaneously, PostgreSQL wins.
- Row-level access control and multi-tenant applications: PostgreSQL’s role system, row security policies, and connection pooling are production-grade. DuckDB has minimal access control.
- Long-running transactional workloads: DuckDB optimizes for bulk reads and writes. High-frequency small INSERTs/UPDATEs will be slower than in a row-store.
- Replication and HA: PostgreSQL has streaming replication, logical replication, pg_bouncer, patroni. DuckDB has none of that. It’s not a server.
The framing I use: PostgreSQL is the right database for your application. DuckDB is the right database for your analysis. They complement each other perfectly — use PostgreSQL’s FOREIGN DATA WRAPPER or just export a snapshot, and analyze it in DuckDB without touching production.
Putting it together: a real analyst workflow
import duckdb
con = duckdb.connect('analysis.duckdb')
# Load S3 data and write a clean local copy once
con.execute("""
INSTALL httpfs; LOAD httpfs;
SET s3_region = 'eu-central-1';
CREATE OR REPLACE TABLE events AS
SELECT * FROM 's3://my-datalake/events/2024/**/*.parquet';
""")
# Now iterate locally at full speed
result = con.execute("""
WITH cohorts AS (
SELECT
user_id,
date_trunc('week', MIN(event_date)) AS cohort_week
FROM events
WHERE event_type = 'signup'
GROUP BY user_id
)
SELECT
cohort_week,
date_diff('week', cohort_week, date_trunc('week', e.event_date)) AS weeks_since,
COUNT(DISTINCT e.user_id) AS retained_users
FROM events e
JOIN cohorts c USING (user_id)
GROUP BY 1, 2
ORDER BY 1, 2
""").df()
result.to_parquet('cohort_retention.parquet')
S3 is hit once. Everything after that runs locally against the DuckDB file, and re-running the cohort query on 50M events takes under 5 seconds on a modern laptop.
The bottom line
DuckDB won’t replace your PostgreSQL instance. But for every analytical task you’re currently doing against imported CSVs, temporary tables, or ad-hoc Python Pandas chains — it deserves to be your default tool.
The ten features above aren’t edge cases. They’re the actual daily friction points of data analysis work, and DuckDB removes them cleanly. The install is a single binary. The SQL is standard. The performance is genuinely remarkable for a library that runs in your process.
Start with pip install duckdb and point it at a CSV you already have. The mental shift happens within the first ten minutes.