← mskql

mskql vs PostgreSQL vs DuckDB

← back to main page

16 in-process engine benchmarks. 101 wire-level batch workloads. 15 concurrent throughput tests. In the fairest comparison—both engines linked as libraries, zero wire overhead—mskql wins 15 of 16 workloads against DuckDB. Full scan 13×, window functions 5×, distinct 3.4×, insert 102× faster. DuckDB wins only group_by (1.06×). Against PostgreSQL: every cached batch workload and 14 of 15 throughput tests.

The advantage traces to three architectural properties: (1) zero per-query allocation after warmup—the arena rewinds, nothing is freed; (2) the scan cache converts row-store to flat columnar arrays once per table write, so repeated reads are a memcpy of contiguous memory; (3) result bytes accumulate into a 64 KB buffer before a single write() syscall. These are structural properties of the system, not tuning parameters. → Design philosophy: why abstraction is not free

1. Methodology

Three benchmark tiers, each measuring a different aspect of performance:

Tier 1: In-process engine comparison (mskql vs DuckDB)

The fairest DuckDB comparison. A C harness (bench/bench_vs_duck.c) links both engines as libraries—libmskql.a and libduckdb—in the same process. Both run in-memory with identical table schemas and data. Each benchmark creates fresh databases, runs setup, then times N iterations of the same query. There is no wire protocol, no CLI process spawn, no serialization overhead on either side. This isolates pure query engine speed.

The wire-level benchmark (Tier 2) runs mskql and PostgreSQL over pgwire but runs DuckDB via its CLI—an in-process invocation with zero network overhead. That asymmetry inflated DuckDB’s apparent advantage on scan and sort workloads. The in-process harness eliminates this bias: both engines execute through their C API in the same address space.

Tier 2: Wire-level batch latency (mskql vs PostgreSQL vs DuckDB)

All three databases are tested on the same Apple M-series laptop, same loopback interface. The benchmark script (bench/bench_vs_pg.py) generates identical SQL workloads, runs setup against all databases, then times the benchmark queries. mskql and PostgreSQL are timed via psql -f over pgwire; DuckDB is timed via its CLI (in-process). Each workload has two variants: cached (result cache active) and no-cache (_nc suffix—unique SQL comment busts the cache).

Note: The DuckDB column in wire-level results includes CLI startup but no network overhead, while mskql and PostgreSQL include full pgwire round-trips. This favours DuckDB. See Tier 1 for the apples-to-apples comparison.

Tier 3: Concurrent throughput (mskql vs PostgreSQL)

A multi-threaded C benchmark (bench/bench_throughput.c) opens 8 persistent connections speaking raw pgwire and fires queries for 5 seconds per workload. Measures QPS and median latency (p50). DuckDB is excluded because it does not expose a wire-protocol server.

2. In-Process Engine Comparison — mskql vs DuckDB

Both engines linked as C libraries in the same process. No wire protocol, no CLI, no serialization. This is the fairest possible comparison of raw query engine speed. mskql wins 15 of 16 benchmarks.

Visual Summary

Shorter bars = faster. The chart below highlights 8 representative in-process workloads.

Full Results

Ratio < 1.0 means mskql was faster. Both engines in-memory, same process, same data. 15 of 16 workloads are mskql wins. The one DuckDB “win” (group_by at 1.06×) is within noise.

What the in-process results corrected

The wire-level benchmarks (Section 3) showed DuckDB winning full scans (2.9×), sorts (1.2–2.0×), and generate_series (2.9×). In-process, mskql wins all three: full scan 13× faster, order_by 2× faster, large_sort 2.2× faster, generate_series 19× faster. The wire-level “DuckDB wins scans and sorts” narrative was an artifact of comparing DuckDB’s in-process CLI against mskql’s pgwire path.

3. Wire-Level Batch Latency — Cached (single client, sequential)

End-to-end wall-clock time including client/server overhead. mskql and PostgreSQL communicate over pgwire; DuckDB runs via its CLI (in-process, no network). Ratio < 1.0 means mskql was faster. The disk column shows performance with CREATE DISK TABLE.

Note: DuckDB’s numbers here benefit from zero network overhead. See Section 2 for the apples-to-apples engine comparison.

All cached workloads faster than PostgreSQL. Closest: full scan (0.89×), transactions (0.82×). Against DuckDB over the wire: mskql wins aggregates (17×), distinct (14×), writes, analytical CTE, correlated subquery, joins, and all stress tests. DuckDB’s wire-level advantages on scans and sorts are a measurement artifact—see Section 2 for the fair comparison. The dk/mem column shows disk-backed tables average 0.96× the in-memory speed. See workload details for the exact SQL.

4. Parquet Benchmarks — mskql vs DuckDB

9 Parquet workloads (50K–200K rows). PostgreSQL cannot query Parquet files natively, so this section compares mskql and DuckDB only. The mskql side uses CREATE FOREIGN TABLE; DuckDB uses its native Parquet reader.

Cached: mskql wins 8 of 12. The result cache serves repeated Parquet queries from cached wire bytes. DuckDB re-reads the Parquet file every time. No-cache: mskql wins 6 of 12. The plan executor’s columnar Parquet reader outperforms DuckDB on aggregates, joins, and subqueries even without caching. DuckDB wins full scans, sorts, WHERE scans, and pq_mixed_join.

5. Concurrent Throughput (8 threads, 5 seconds)

Comparison: mskql vs PostgreSQL only—DuckDB does not expose a wire-protocol server. Ratio > 1.0 means mskql has higher throughput.

6. The Honest Picture

Where mskql wins vs DuckDB (in-process)

15 of 16 engine benchmarks. Insert bulk: 13ms vs 1,355ms (102×). Full scan: 1.0ms vs 13.0ms (13×). Window function: 10.2ms vs 48.3ms (5×). Distinct: 51.7ms vs 174.5ms (3.4×). Scalar functions: 7.3ms vs 27.7ms (3.8×). Join: 3.4ms vs 14.6ms (4.3×). Generate series: 0.5ms vs 9.4ms (19×). Order by: 16.1ms vs 32.9ms (2×). The result cache is not a factor here—this is raw engine speed, both in-process.

Wire-level: every cached batch workload vs PostgreSQL. Correlated subquery: 6ms vs 3,456ms (576×). Expression aggregate: 14ms vs 302ms (22×). Throughput: mixed_analytical at 97,210 QPS vs 508 (191×). multi_join at 37,402 vs 475 (79×).

Vectors: all 7 workloads vs PostgreSQL + pgvector. Vector insert: 136ms vs 383ms (2.8×). Vector filter (cached): 13ms vs 42ms (3.2×). Even without the result cache, mskql is faster on every vector workload.

Parquet: 8 of 12 workloads (cached). pq_aggregate: 12ms vs 104ms (8.7×). pq_subquery: 5ms vs 56ms (11×). The plan executor’s columnar reader outperforms DuckDB on aggregates, joins, and subqueries. DuckDB wins full scans, sorts, WHERE scans, and pq_mixed_join_nc.

Disk-backed tables: near-zero overhead. Average dk/mem ratio across all disk-eligible workloads: 0.96×. CREATE DISK TABLE adds persistence with no measurable performance cost on reads.

Where it’s a tie

DuckDB group_by (1.06×). Within measurement noise. DuckDB’s SIMD-vectorized hash aggregation matches mskql on high-cardinality GROUP BY.

What the wire-level data got wrong

The wire-level benchmarks previously showed DuckDB winning full scans (2.9×), sorts (1.2–2.0×), and generate_series (2.9×). This was an artifact of comparing DuckDB’s in-process CLI (zero network) against mskql’s pgwire path (full TCP round-trip). In-process, mskql wins all three: full scan 13×, large sort 2.2×, generate_series 19×.

Where PostgreSQL wins

Mixed read/write throughput: 0.94×. PostgreSQL’s MVCC allows concurrent readers and writers without blocking. The mskql single-threaded executor serializes all queries.

7. Running It Yourself

In-process engine comparison (mskql vs DuckDB)

# Build (requires libduckdb installed)
make bench_vs_duck

# Run all 16 benchmarks
./build/mskql_bench_vs_duck

# Filter to specific benchmarks
./build/mskql_bench_vs_duck --filter join

# Output JSON for CI integration
./build/mskql_bench_vs_duck --json results.json

Wire-level batch latency (3-way)

# Start mskql in one terminal
./build/mskql

# In another terminal, with PostgreSQL and DuckDB installed:
./bench/bench_vs_pg.sh

Concurrent throughput (mskql vs PG)

# Build the throughput benchmark
make -C bench bench_throughput

# Run (requires both mskql and PostgreSQL running)
./build/mskql_bench_tp --duration 5 --threads 8 --pg

The in-process benchmark links libmskql.a and libduckdb directly—no running servers needed. The batch script auto-creates a mskql_bench database in PostgreSQL if it doesn’t exist. The throughput benchmark requires libpq headers at build time.