Skip to main content

01 - DuckDB Instead of Spark: When One Server Is Enough

DuckDB can replace Spark for batch jobs on a single server: measurements on 12 million rows, disk spill, a single writer and the schema for S3.

What do you think about this article?

01 - DuckDB Instead of Spark: When One Server Is Enough
Data Engineering 2027Photo:Lukas BlazekPexels

At a Glance

DuckDB can replace Spark for batch jobs on a single server: measurements on 12 million rows, disk spill, a single writer and the schema for S3.

For a batch job that fits on a single machine, DuckDB can take Spark's place and let you take the cluster off the bill. There is no magic 1 TB threshold, though: what matters is memory, disk space for temporary files and, above all, how many processes need to write to the same data. In this article you generate 12 million order rows, query them with DuckDB and see where the engine holds up and where it stops, with the output of a real run.

Prerequisites. Basic Python and SQL; you do not need to know Spark, although it helps when reading the comparison.

What changes compared with Spark

DuckDB is a library that lives inside your Python process: you open a connection, run SQL and get the results, with no daemons and no ports. The project is distributed under the MIT license, and the non-profit Foundation that holds its intellectual property and trademarks has committed to keeping it that way. The current version as of 2026-11-13 is 2.0.0, marked LTS, released on 2026-10-21 and with end of support scheduled for 2027-10-22.

Spark is something else: a distributed engine under the Apache-2.0 license, now at 4.3.0, that coordinates a driver and many executors. That coordination is valuable when the job does not fit on one machine, and a fixed cost when it does. The choice comes down to a few questions, and these are they:

  • Do the data you work on and the temporary files the computation produces fit on the disk of a single server?
  • How many different processes need to write to the same database at the same time?
  • Does the data arrive continuously and need processing as it arrives, or do you recompute it in batches?
  • Does your team already have a working Spark cluster and someone who knows it?

The first three are technical and have measurable answers, which you will find below: the fourth is organizational and weighs just as much. If you answer "yes, one, none or few, batches", DuckDB gives you fewer moving parts to keep running. If even one answer points the other way, the final section explains why Spark remains the reasonable choice.

A reproducible test data lake

You need real data to read, but without downloading anything: the listing below generates it with DuckDB itself. Every column derives from the row index through the hash() function, so two runs produce the same logical bytes and the figures you read can be reproduced. PARTITION_BY (year, month) writes one directory for each year-month pair, in the year=2024/month=3 style that Spark and other engines recognize (for Spark it is described in the Parquet page of its documentation).

import duckdb, shutil, pathlib

base = pathlib.Path("lake/orders")
base.parent.mkdir(exist_ok=True)
shutil.rmtree(base, ignore_errors=True)

con = duckdb.connect()
con.execute("""
    COPY (
        SELECT
            i                                              AS order_id,
            2022 + (i % 3)::INT                            AS year,
            1 + ((i // 3) % 12)::INT                       AS month,
            (hash(i) % 50000)::INT                         AS customer_id,
            ((hash(i * 7) % 20000) / 100.0)::DECIMAL(10,2) AS amount
        FROM range(12000000) t(i)
    ) TO 'lake/orders' (FORMAT PARQUET, PARTITION_BY (year, month))
""")
n = con.execute("SELECT count(*) FROM read_parquet('lake/orders/**/*.parquet')").fetchone()[0]
files = len(list(base.rglob("*.parquet")))
mb = sum(f.stat().st_size for f in base.rglob("*.parquet")) / 1e6
print(f"rows: {n}")
print(f"parquet files: {files}")
print(f"size on disk: {mb:.0f} MB")

# Output:
# rows: 12000000
# parquet files: 36
# size on disk: 135 MB

The result is 36 Parquet files (three years times twelve months) for about 135 MB: Parquet compresses numeric columns very well, so 12 million rows take up less space than a single CSV of equivalent size. If you drop PARTITION_BY you get a single file: it works, but every query will then have to read everything, as you will see in the next section.

Reading only what you need: partition pruning

The main reason a data lake is organized into directories by year and month is to be able to skip the files that are irrelevant. With hive_partitioning = true DuckDB reads the year and month values from the path and treats them as columns; with filename = true it adds a virtual column saying which file each row comes from, so you can count how many files it really opened.

import duckdb

con = duckdb.connect()
sql = """
    SELECT count(DISTINCT filename) AS files_read, count(*) AS row_count, sum(amount) AS revenue
    FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true, filename = true)
    WHERE year = 2024 AND month = 3
"""
print(con.execute(sql).fetchall())
tot = con.execute("""
    SELECT count(DISTINCT filename)
    FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true, filename = true)
""").fetchone()[0]
print("total files in the lake:", tot)

# Output:
# [(1, 333334, Decimal('33312430.75'))]
# total files in the lake: 36

For March 2024 the query touched one file out of 36 and returned 333,334 rows: the other 35 are discarded before reading, by looking only at directory names. The flip side: if you filter on a column that is not in the path, for example amount, DuckDB has to read the files and rely on the internal statistics of Parquet. The official tuning guide warns that a scan without filter pushdown does useless I/O, and the EXPLAIN plan tells you whether the filter was pushed into the scan.

Analytical queries: CTEs and window functions

Real reporting queries combine an aggregation and a comparison between neighboring rows. Here you compute the monthly revenue of three customers in 2024 and, on top of it, two window functions: rank() orders the customers within each month and the sum with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a rolling three-month sum. The whole computation happens in standard SQL, without opening a context or declaring a job.

import duckdb

con = duckdb.connect()
query = """
WITH monthly AS (
    SELECT customer_id, month, sum(amount) AS total, count(*) AS orders
    FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true)
    WHERE year = 2024 AND customer_id < 3
    GROUP BY customer_id, month
)
SELECT customer_id, month, total, orders,
       rank() OVER (PARTITION BY month ORDER BY total DESC) AS position,
       sum(total) OVER (PARTITION BY customer_id ORDER BY month
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_months
FROM monthly
ORDER BY customer_id, month
LIMIT 6
"""
for row in con.execute(query).fetchall():
    print(row)

# Output:
# (0, 1, Decimal('765.81'), 7, 2, Decimal('765.81'))
# (0, 2, Decimal('174.88'), 4, 3, Decimal('940.69'))
# (0, 3, Decimal('623.49'), 10, 1, Decimal('1564.18'))
# (0, 4, Decimal('642.60'), 7, 2, Decimal('1440.97'))
# (0, 5, Decimal('930.45'), 8, 2, Decimal('2196.54'))
# (0, 6, Decimal('870.10'), 6, 1, Decimal('2443.15'))

Check the first row with the rolling sum by hand: in March customer 0 has 623.49 of revenue and the window adds up the three months 765.81 + 174.88 + 623.49 = 1,564.18, the printed value. If you change ROWS to RANGE the window is defined on values and not on rows, and if some months are missing from the data the window covers a different interval from the one you think: choose the frame by looking at the data, not by copying it.

Memory and disk: the limit is not just RAM

DuckDB's promise is to process more data than fits in memory, moving to disk whatever does not fit (spill). To see it in action the listing sets memory_limit to 300 MB and points temp_directory at a folder of ours; a background thread samples the folder every 20 milliseconds to tell you whether anything was written. Timings change from one run to the next, as the interval at the end of the article also shows.

import duckdb, pathlib, shutil, threading, time

SPILL = pathlib.Path("spill")
LAKE = "lake/orders/**/*.parquet"

def apri(limite):
    shutil.rmtree(SPILL, ignore_errors=True)
    con = duckdb.connect()
    con.execute(f"SET memory_limit = '{limite}'")
    con.execute(f"SET temp_directory = '{SPILL}'")
    con.execute("SET threads = 4")
    return con

def picco_spill(stop, out):
    # samples the size of the temporary-files folder every 20 ms
    while not stop.is_set():
        out[0] = max(out[0], sum(f.stat().st_size for f in SPILL.rglob("*") if f.is_file()))
        time.sleep(0.02)

# 1) aggregation with 12 million groups, with 300 MB of memory
con = apri("300MB")
stop, picco = threading.Event(), [0]
th = threading.Thread(target=picco_spill, args=(stop, picco)); th.start()
t0 = time.perf_counter()
gruppi, totale = con.execute(f"""
    SELECT count(*), sum(s) FROM (
        SELECT order_id, sum(amount) AS s FROM read_parquet('{LAKE}') GROUP BY order_id)
""").fetchone()
secondi = time.perf_counter() - t0
stop.set(); th.join()
print(f"aggregazione: {gruppi} gruppi, somma {totale}, {secondi:.1f} s, picco su disco > 0: {picco[0] > 0}")

# 2) window function that must hold whole partitions in memory
finestra = f"""
    SELECT count(*) FROM (
        SELECT row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS r
        FROM read_parquet('{LAKE}')) WHERE r <= 3
"""
for limite in ("300MB", "400MB"):
    con = apri(limite)
    try:
        print(limite, "->", con.execute(finestra).fetchone()[0], "righe")
    except duckdb.OutOfMemoryException as e:
        print(limite, "->", type(e).__name__)

# Output:
# aggregazione: 12000000 gruppi, somma 1200186947.17, 0.6 s, picco su disco > 0: True
# 300MB -> OutOfMemoryException
# 400MB -> 150000 righe

The aggregation over 12 million groups, the heaviest for the hash table, finishes in under a second with just 300 MB because it spills to disk. The window function on the same memory fails instead with OutOfMemoryException, and passes at 400 MB. The official documentation declares grouping, joins, sorting and windows supported out of memory, but admits limits: more than one blocking operator in the same query, certain aggregations such as string_agg() and those that sort. The boundary between 300 and 400 MB holds for this query, this data and four threads: it is not a rule.

When you run into a memory exception, the remedies come in order of increasing cost; the first three are the ones DuckDB's own message suggests:

  • reduce threads: fewer parallel threads hold less intermediate data together, and the error message lists it first;
  • set preserve_insertion_order = false if you do not need insertion order, so the engine does not have to preserve it;
  • raise memory_limit, remembering that the default value depends on the machine (on mine, with 30 GB of RAM, current_setting('memory_limit') returns 24.1 GiB);
  • rewrite the query so that only one blocking operator works at a time, for example by materializing an intermediate step.

The first remedy costs compute time, the second can break a result that depended on order, the third costs money if the machine is rented, the fourth costs hours of your own work. Start from the first and the second, and measure the difference before deciding.

A single writer: the constraint that decides the choice

The architectural difference that weighs most in production is concurrency. A DuckDB database file can be opened for reading and writing by only one process at a time; threads of the same process can write together, according to the documentation, with optimistic conflict control. The listing opens a file, keeps the connection and then launches a second Python interpreter that tries to open the same file.

import duckdb, subprocess, sys, pathlib

db = pathlib.Path("shared.duckdb")
db.unlink(missing_ok=True)
con = duckdb.connect(str(db))          # process A: opens the file for reading/writing
con.execute("CREATE TABLE t AS SELECT 1 AS x")

# process B: a second interpreter tries to open the same file
child = "import duckdb; duckdb.connect('shared.duckdb')"
r = subprocess.run([sys.executable, "-c", child], capture_output=True, text=True)
print("exit code of the second process:", r.returncode)
msg = r.stderr.strip().splitlines()[-1]
print(msg.replace(str(db.resolve()), db.name).split(" in /")[0])

# Output:
# exit code of the second process: 1
# _duckdb.IOException: IO Error: Could not set lock on file "shared.duckdb": Conflicting lock is held

The second process fails immediately with IOException and a message naming the conflicting lock (I removed the PID and user name from the printout, which change on every run). In read-only mode (access_mode = 'READ_ONLY') several processes can read, but none can write. For writing from several processes, the version 2.0 documentation points to the Quack remote protocol, which turns DuckDB into a client-server database, or to the DuckLake format with PostgreSQL as the catalog. I have not tried them: if your workload requires them, evaluate them in the official documentation before saying that DuckDB is right for you.

Interrupting a query that never ends

An analysis launched by hand can run for hours without anyone noticing. The correct way to stop it from Python is connection.interrupt(), called from another thread: in the listing a threading.Timer invokes it after one second, while the query, a deliberately oversized Cartesian product, is still running. The query ends with InterruptException and the connection remains usable.

import duckdb, threading, time

con = duckdb.connect()
timer = threading.Timer(1.0, con.interrupt)   # after 1 second it interrupts the running query
timer.start()
t0 = time.perf_counter()
try:
    # deliberately huge Cartesian product: on its own it would not finish in a useful time
    con.execute("SELECT count(*) FROM range(100000) a, range(100000) b WHERE (a.range * b.range) % 7 = 3").fetchall()
except duckdb.InterruptException as e:
    print(f"interrupted after ~{time.perf_counter() - t0:.0f} s:", type(e).__name__, "-", e)
finally:
    timer.cancel()
print("the connection is still usable:", con.execute("SELECT 42").fetchone()[0])

# Output:
# interrupted after ~1 s: InterruptException - INTERRUPT Error: Interrupted!
# the connection is still usable: 42

Avoid the methods often found in tutorials: signal.alarm is available only on Unix and the handler can only be installed from the main thread; moreover, the Python handler runs when the interpreter regains control, so inside a long native call it can arrive late (this is how the documentation of the signal module describes it). Remember also to cancel the timer in the finally block, otherwise a surviving timer can interrupt the next query.

Parquet on object storage: the schema to adapt

The real data lake lives on an S3-compatible object storage. DuckDB reads it with the httpfs extension, which authenticates through secrets: the config provider accepts a key and secret written in the statement, while credential_chain uses the standard credentials of the environment (variables, profiles, roles). The ENDPOINT and URL_STYLE fields are for services other than AWS; USE_SSL is true if you do not write it, REGION is us-east-1 and URL_STYLE is vhost for S3 and path for R2 and GCS.

-- Schema to adapt, not executed: it needs a bucket and real credentials.
INSTALL httpfs;
LOAD httpfs;

-- S3-compatible (MinIO or another compatible service): explicit endpoint and path style
CREATE OR REPLACE SECRET lake_s3 (
    TYPE s3,
    PROVIDER config,
    KEY_ID 'test-key',
    SECRET 'test-secret',
    ENDPOINT 'storage.example.org',
    URL_STYLE 'path',
    REGION 'eu-central'
);

-- In production prefer the credentials of the standard chain (environment variables, profile):
-- CREATE OR REPLACE SECRET lake_s3 (TYPE s3, PROVIDER credential_chain);

SELECT year, month, count(*) AS orders, sum(amount) AS revenue
FROM read_parquet('s3://test-bucket/orders/**/*.parquet', hive_partitioning = true)
WHERE year = 2024
GROUP BY year, month;

I did not run this block, because it requires a bucket and credentials: it is a schema to adapt, with syntax and options read from the extension's official page. The commented line is the one you will really use: a key written in the script ends up in the repository. If an error mentions Connection error for HTTP HEAD, the documentation recommends specifying the endpoint explicitly. Since version 2.0 reading Parquet over the network uses asynchronous I/O, so you do not need to raise the threads to fill the bandwidth.

From PySpark to DuckDB: the same aggregation

The most frequent translation is aggregation by key. In PySpark you write it with groupBy, agg and alias; in DuckDB it is a query. The two versions below compute, for each customer, the sum of the amounts and the number of orders in 2024. The Spark version was not run (PySpark is not installed in this environment) and is a schema to adapt; the DuckDB one was run on the test lake.

# Schema to adapt, not executed: PySpark is not installed in this environment.
from pyspark.sql import SparkSession, functions as F

spark = SparkSession.builder.appName("orders-etl").getOrCreate()
result = (
    spark.read.parquet("lake/orders")                 # discovers the year=.../month=... directories
    .filter(F.col("year") == 2024)
    .groupBy("customer_id")
    .agg(F.sum("amount").alias("amount_sum"),
         F.count("order_id").alias("order_count"))
    .orderBy(F.desc("amount_sum"), "customer_id")
    .limit(3)
)
result.show()
import duckdb

con = duckdb.connect()
df = con.execute("""
    SELECT customer_id,
           sum(amount)  AS amount_sum,
           count(*)     AS order_count
    FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true)
    WHERE year = 2024
    GROUP BY customer_id
    ORDER BY amount_sum DESC, customer_id
    LIMIT 3
""").df()
print(df.to_string(index=False))

# Output:
#  customer_id  amount_sum  order_count
#        16683    12474.04          112
#        17495    12461.26          115
#        15857    12354.56          112

Two differences hide errors. The Spark reader discovers partitions from the directories (described in the Parquet page of the Spark documentation), while DuckDB recognizes them on its own when the names are in the key=value style (I verified it by removing the option from the query), but it is better to write hive_partitioning = true to make the intent explicit. Then the names of the result columns: in Spark you choose them with alias, in DuckDB with AS; the old form withColumnRenamed("count(order_id)", ...) depends on the name Spark generates and breaks if the format changes.

Timings measured on my machine

I did not run a comparison with Spark: I did not execute it, and I do not publish other people's numbers. What I can state is how DuckDB scales on the test lake, with the same monthly aggregation repeated seven times for each number of threads, on an AMD Ryzen 5 5600H (12 logical threads), Python 3.14.4 and DuckDB 2.0.0, with a warm file system cache.

import duckdb, platform, time

print("duckdb", duckdb.__version__, "| python", platform.python_version())
con = duckdb.connect()
query = """
    SELECT year, month, count(*) AS ordini, sum(amount) AS fatturato, avg(amount) AS medio
    FROM read_parquet('lake/orders/**/*.parquet', hive_partitioning = true)
    WHERE year >= 2023
    GROUP BY year, month
    ORDER BY year, month
"""
for threads in (1, 4, 12):
    con.execute(f"SET threads = {threads}")
    tempi = []
    for _ in range(7):
        t0 = time.perf_counter()
        con.execute(query).fetchall()
        tempi.append(time.perf_counter() - t0)
    print(f"{threads:>2} thread: {min(tempi)*1000:.0f}-{max(tempi)*1000:.0f} ms su 7 esecuzioni")

# Output:
# duckdb 2.0.0 | python 3.14.4
#  1 thread: 39-44 ms su 7 esecuzioni
#  4 thread: 14-16 ms su 7 esecuzioni
# 12 thread: 12-16 ms su 7 esecuzioni

Going from 1 to 4 threads more than halves the time (from about 50-60 ms to about 20-25 ms); from 4 to 12 the gain is small, because with 135 MB of data the work finishes before all the cores are needed. The intervals of two consecutive runs are similar but not identical: they are measurements of one machine, not a promise. On data ten times larger the curve will be different, and it must be measured again.

When Spark remains the right choice

The conditions in which DuckDB is not enough are those in which a single server is the limit. If the computation exceeds the capacity of one machine even with spill, you need a distributed engine. If several services must write to the same data at a sustained pace without going through the remote protocol or a shared catalog, the single-writer constraint makes itself felt. If you need to process a continuous stream, Spark has Structured Streaming, described in its guide as a scalable and fault-tolerant engine; DuckDB is designed to query data, not to hold the state of a stream.

Then there is the case of code that already exists: rewriting dozens of working jobs just to eliminate a cluster costs more than it saves, if the cluster is there and already being paid for. The reasonable move is to start from a new job or a slow analysis and, after measuring, decide whether to extend. The pipeline that combines DuckDB and Polars, described in the article DuckDB and Polars: a lean pipeline in Python, shows how to compose the pieces; the comparison between the DataFrame libraries is in Polars vs pandas: measurements of your own, and the table format to put on top of the Parquet files in Iceberg, Delta Lake and Hudi: how to choose.

What you can do now

You can generate a partitioned Parquet lake and reproduce it, query it reading one file out of 36, write a query with windows and check it by hand, set the memory limit and recognize when spill is not enough, explain why a second process does not open your file, interrupt a query that does not end, and translate a PySpark aggregation into DuckDB. You also have the schema to point everything at an object storage, to try with your own credentials. To version the results with branches and merges there is lakeFS, with a license to know about first; to see the pieces assembled in a complete pipeline, a minimal data architecture.

Frequently asked questions

Does DuckDB really handle a terabyte of data?

I did not measure it: my tests use 135 MB of Parquet and 12 million rows. The documentation declares support for workloads larger than memory with spill to disk, but with the limits listed above. The right value for you depends on RAM, disk and the shape of the queries, and is found by trying on your own data.

Can I use DuckDB behind a web API?

Yes for reads: one process can serve many requests and several processes can open the file read-only. For writes from several workers you need the Quack protocol or a shared catalog, such as DuckLake with PostgreSQL, because a second process opening for reading and writing gets the lock error shown above.

Is DuckDB safe to use in production from a licensing point of view?

The license is MIT and the DuckDB Foundation, which holds the intellectual property and trademarks, declares that it will stay that way. As of the consultation date (2026-11-13) version 2.0.0 is LTS. Still, pin the version in your requirements file and test it before every upgrade.

Sources

The listings with reported output were run with Python 3.14.4 and DuckDB 2.0.0; each was launched twice, except those with timings, and the output is the real one. The S3 and PySpark schemas were not run. The timing outputs change from one run to the next. The listings that print timings keep the labels of the original run, in Italian, and were not rerun for the English edition.

Try These Tools

Free developer tools related to this topic.

Commenti

Caricamento commenti...

Sign in to comment

New articles and tools on Telegram too

The Telegram channel flags the site's new articles and tools.

Did you like this article?

Explore other content on the blog or discover my projects