ATX AnalyticsTable Performance | 100 Million Rows in Under a Second

ATX AnalyticsTable performance: 100 million rows, measuredA DuckDB benchmark on a laptop with 16 GB of RAM: fifteen aggregations (sums, group-bys, filters, a million-group ranking, medians, joins and a window function) against tables of 1, 10, 50 and 100 million rows, with the median time, the SQL and the method for each.

The machine

Intel(R) Core(TM) i7-4610M CPU @ 3.00GHz, 4 logical cores, 15.9 GB RAM, Windows_NT 10.0.19045 x64; DuckDB v1.5.1 with 4 threads.
Method: Fifteen aggregate queries against synthetic sales tables. Database closed and reopened before timing. Median of warm runs; run 1 is firstMs. Dataset: sales(sale_id, order_date, customer_id, product_id, region, channel, qty, amount) + products(500) + customers(1M).

Median time per query, by table size

Row count — 1 million rows: 1.5 ms; 10 million rows: 1.9 ms; 50 million rows: 1.6 ms; 100 million rows: 1.8 ms. Answered from table metadata; the floor of every other number.
Sum / avg / min / max of one column — 1 million rows: 6.6 ms; 10 million rows: 36 ms; 50 million rows: 171.2 ms; 100 million rows: 345.5 ms. A full scan of one column, four aggregates.
Group by region (5 groups) — 1 million rows: 8 ms; 10 million rows: 50 ms; 50 million rows: 222.8 ms; 100 million rows: 478.1 ms. Low-cardinality string key.
Group by product (500 groups) — 1 million rows: 13.1 ms; 10 million rows: 74.8 ms; 50 million rows: 307.1 ms; 100 million rows: 616.3 ms. Medium cardinality, three aggregates.
Monthly totals (72 groups) — 1 million rows: 39.1 ms; 10 million rows: 308 ms; 50 million rows: 1.50 s; 100 million rows: 3.09 s. Date truncation in the grouping key.
Region x channel x year (90 groups) — 1 million rows: 37.6 ms; 10 million rows: 273.5 ms; 50 million rows: 1.24 s; 100 million rows: 2.35 s. Three keys, three aggregates.
Filtered aggregate (one year, one region) — 1 million rows: 7.3 ms; 10 million rows: 49.7 ms; 50 million rows: 230.6 ms; 100 million rows: 483.5 ms. Selective predicate on date and region.
Top 10 customers (1M groups) — 1 million rows: 80.7 ms; 10 million rows: 716.3 ms; 50 million rows: 4.28 s; 100 million rows: 12.10 s. One group per customer: the hash table is the cost.
Distinct customers — 1 million rows: 58.1 ms; 10 million rows: 448 ms; 50 million rows: 3.06 s; 100 million rows: 7.86 s. COUNT(DISTINCT) over a 1M-value key.
Approximate median and p95 — 1 million rows: 68.9 ms; 10 million rows: 723.8 ms; 50 million rows: 3.34 s; 100 million rows: 6.94 s. Streaming sketch; cheap at any size.
Exact median — 1 million rows: 39.4 ms; 10 million rows: 499.5 ms; 50 million rows: 2.23 s; 100 million rows: 4.49 s. Has to hold every value: the expensive one.
Pivot-style: year x 5 regions — 1 million rows: 43.8 ms; 10 million rows: 383 ms; 50 million rows: 1.87 s; 100 million rows: 3.86 s. Five FILTERed sums per year.
Join products (500 rows), group by category — 1 million rows: 11.5 ms; 10 million rows: 66.9 ms; 50 million rows: 346.7 ms; 100 million rows: 639.3 ms. Tiny build side.
Join customers (1M rows), group by segment and country — 1 million rows: 225.4 ms; 10 million rows: 748.1 ms; 50 million rows: 3.01 s; 100 million rows: 7.06 s. Large build side.
Monthly totals with running total — 1 million rows: 49.8 ms; 10 million rows: 363 ms; 50 million rows: 1.37 s; 100 million rows: 2.89 s. Window over the 72 aggregated rows.

Loading the data

1 million rows built in 1.40 s (9.2 MB on disk).
10 million rows built in 11.81 s (91.8 MB on disk).
50 million rows built in 66.50 s (458 MB on disk).
100 million rows built in 137.19 s (916.5 MB on disk).

Related guides

ATX Institute on LinkedIn

This site needs JavaScript for the full experience. Every page above is reachable without it.