Blocking versus streaming

Measure your machineNeeds Python with DuckDB, PyArrow

Problem

Some operators stream: they can emit a row as soon as they have read enough input to produce it, so a LIMIT 10 can return almost at once. Others block: a full sort cannot emit its first row until it has seen the last input row, so its time to first row equals most of its total time. Top-N is the hybrid: it must see every row, but keeps only N of them, so it is as fast as a scan and needs almost no memory.

In DuckDB with 20 million orders, run SELECT * FROM orders LIMIT 10 and SELECT * FROM orders ORDER BY amount DESC, streaming the result to the client. Measure the time until the first rows appear and the total time. Then run ORDER BY amount DESC LIMIT 10 and compare, and read the operator from the plan.

The question. How soon does each query deliver its first rows, and which operator does the planner use for the top 10?

Predict

Over 20 million rows, when does the full sort deliver its first row?

Almost at onceOnly after the whole sort, under a secondSeveral seconds

Run it

1Install uv once. It fetches Python and the lab's packages for you.
Terminal
```
curl -LsSf https://astral.sh/uv/install.sh | sh
```

2Get the script. It is the whole lab: read it, then download it.

blocking-versus-streaming.py44 linesDownload

View the code

blocking-versus-streaming.py

# /// script
# requires-python = ">=3.10"
# dependencies = ["duckdb", "pyarrow"]
# ///
# Copyright (c) 2026 Aayush Sharma · SPDX-License-Identifier: MIT
# https://thedataengineering.com/lab-code/LICENSE.txt
"""Blocking against streaming: time to first row.

DuckDB with 20 million orders. For LIMIT 10, a full ORDER BY amount DESC and
ORDER BY ... LIMIT 10 (top-N), measures the time until the first batch of
rows reaches the client, the total time to drain the result, and the peak
memory DuckDB reports for the query.
"""
import argparse, json, time
import duckdb

p = argparse.ArgumentParser(description=__doc__.splitlines()[0])
p.add_argument("--orders", type=int, default=20_000_000, help="Orders.")
p.add_argument("--json", help="Also write the results to this JSON file.")
args = p.parse_args()

c = duckdb.connect(); c.execute("SELECT setseed(0.42)")
c.execute(f"CREATE TABLE orders AS SELECT i AS order_id, (random() * 500)::INT AS amount, repeat('x', 40) AS pad FROM range({args.orders}) t(i)")
def stream(q):
    c.execute("SELECT 1").fetchall()
    t = time.perf_counter(); res = c.execute(q)
    first = res.to_arrow_reader(100_000)
    b = first.read_next_batch(); t_first = time.perf_counter() - t; n = b.num_rows
    for b in first: n += b.num_rows
    total = time.perf_counter() - t
    return t_first, total, n
rows = []
for label, q in (("LIMIT 10", "SELECT * FROM orders LIMIT 10"),
                 ("ORDER BY amount DESC (full sort)", "SELECT * FROM orders ORDER BY amount DESC"),
                 ("ORDER BY amount DESC LIMIT 10 (top-N)", "SELECT * FROM orders ORDER BY amount DESC LIMIT 10")):
    stream(q); tf, tt, n = stream(q)
    plan = c.execute("EXPLAIN " + q).fetchall()[0][1]
    rows.append({"query": label, "first_row_s": tf, "total_s": tt, "rows": n, "operator": "TOP_N" if "TOP_N" in plan else "ORDER_BY" if "ORDER_BY" in plan else "LIMIT"})
for r in rows: print(f"{r['query']:<38} first rows after {r['first_row_s']:7.3f} s   all {r['rows']:>11,} rows after {r['total_s']:7.3f} s   operator {r['operator']}")

if args.json:
    with open(args.json, "w") as f:
        json.dump({"tables": {"queries": rows}}, f)

3Run it in the folder you saved it to, with the defaults first. Then change a knob from the table below and run it again.
Terminal
```
uv run blocking-versus-streaming.py
uv run blocking-versus-streaming.py --help     # every knob
```

Knobs
Knob · default	What it changes
--orders20,000,000	Orders.

Our run

First rows against all rows

First rows against all rows
Query	First rows after	All rows after	Rows	Operator
LIMIT 10	186 µs	188 µs	10	LIMIT
ORDER BY amount DESC (full sort)	744 ms	2.05 s	20M	ORDER_BY
ORDER BY amount DESC LIMIT 10 (top-N)	1.7 ms	1.7 ms	10	TOP_N

Reference runApple M2 · 8 cores · 8 GB

Output · uv run blocking-versus-streaming.py

LIMIT 10                               first rows after   0.000 s   all          10 rows after   0.000 s   operator LIMIT
ORDER BY amount DESC (full sort)       first rows after   0.744 s   all  20,000,000 rows after   2.046 s   operator ORDER_BY
ORDER BY amount DESC LIMIT 10 (top-N)  first rows after   0.002 s   all          10 rows after   0.002 s   operator TOP_N

macOS 26.6.2 · Python 3.13.3 · duckdb 1.5.6 · pyarrow 26.0.0 · run 2026-10-20

What we found

LIMIT 10 returned in 188 µs. The full sort delivered its first rows only after 744 ms, and all 20 million after 2.05 s: the rest of the time was transferring the sorted result. With LIMIT 10 the planner used a TOP_N operator and answered in 1.7 ms.

Read it

A blocking operator makes the first row as expensive as the last. Dashboards and APIs that show a page of results should ask for exactly that page, so the engine can use a top-N operator, instead of sorting everything and discarding most of it on the client.