← mskql

mskql vs PostgreSQL vs DuckDB

← back to main page

93 batch workloads. 15 concurrent throughput workloads. Three databases on the same machine. Faster than PostgreSQL on every cached batch workload and 13 of 15 throughput tests. Against DuckDB, wins 61 of 93 workloads—aggregates 8×, expression aggregates 10×, correlated subqueries 166× faster than PG. DuckDB wins full scans (4.3×) and sorts (4.7×). PostgreSQL wins mixed read/write (0.94×).

The advantage on cached analytical workloads 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—on a 5,000-row result, 5 syscalls instead of 5,000. These are structural properties of the system, not tuning parameters. → Design philosophy: why abstraction is not free

1. Methodology

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 for each test, runs setup against all databases, then times the benchmark queries. Each timing measures a single psql -f invocation (mskql, PostgreSQL) or duckdb CLI invocation (DuckDB) containing all iterations of that workload. Port assignment: mskql on 5433, PostgreSQL on 5432 with default configuration, DuckDB in-process via its CLI.

Each workload has two variants: cached (result cache active) and no-cache (_nc suffix—each query has a unique SQL comment appended to bust the result cache). This isolates the effect of mskql’s result cache from raw execution speed.

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 not included in throughput tests because it does not expose a wire-protocol server.

Visual Summary — Selected Workloads

Shorter bars = faster. The chart below highlights 10 representative workloads across all three databases.

2. Batch Latency — Cached (single client, sequential)

Ratio < 1.0 means mskql was faster. All 28 workloads with PostgreSQL data: every one is an mskql win. Against DuckDB: wins on aggregates, joins, CTEs, subqueries; DuckDB wins full scans, sorts, wide output.

All 28 cached workloads faster than PostgreSQL. Closest: index lookups (0.96×), transactions (0.86×). Against DuckDB: mskql wins aggregates, distinct, writes, analytical CTE, correlated subquery, joins. DuckDB wins scans, sorts, window functions, generate_series, scalar functions. See workload details for the exact SQL.

3. 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 7 of 9. The result cache serves repeated Parquet queries from cached wire bytes. DuckDB re-reads the Parquet file every time. No-cache: DuckDB wins 8 of 9. DuckDB’s native columnar Parquet reader is faster on cold reads. On the mskql side, Parquet is read row-by-row via PLAN_PARQUET_SCAN—no columnar pushdown yet.

4. 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.

5. The Honest Picture

Where mskql wins

vs PostgreSQL: every cached batch workload. Correlated subquery: 22ms vs 3,665ms (166×). Expression aggregate: 33ms vs 323ms (10×). Throughput: mixed_analytical at 94,525 QPS vs 496 (190×). multi_join at 36,171 vs 462 (78×). The result cache and zero-allocation executor dominate on repeated analytical queries.

vs DuckDB: aggregates, writes, correlated subqueries, analytical CTEs, joins. Expression aggregate: 33ms vs 249ms (8×). Aggregate: 35ms vs 199ms (6×). Insert+delete: 2,654ms vs 15,972ms (6×). Bulk update: 55ms vs 85ms (1.5×). Analytical CTE: 20ms vs 32ms (1.6×). Correlated subquery: 22ms vs 38ms (1.7×).

Vectors: all 7 workloads vs PostgreSQL + pgvector. Vector insert: 138ms vs 418ms (3×). Vector filter (cached): 13ms vs 45ms (3.5×). Even without the result cache, mskql is faster on every vector workload—the columnar float[] storage and zero-copy scan path outperform pgvector’s row-based storage.

Parquet (cached): 7 of 9 workloads. pq_aggregate: 26ms vs 73ms (2.8×). pq_subquery: 18ms vs 41ms (2.3×). The result cache makes repeated Parquet queries faster than DuckDB’s native reader.

Where DuckDB wins

Full scans: 4.3× faster. DuckDB’s columnar storage reads contiguous column data with SIMD vectorization. The mskql row store converts to columnar via the scan cache—fast, but not as fast as native columnar.

Sorts: 4.7× faster. DuckDB’s sort is SIMD-optimized for columnar data. The mskql plan executor sorts via multi-column radix sort on column blocks.

Window functions: 1.9–2.6× faster. DuckDB’s window function implementation is heavily optimized for columnar processing.

Parquet (cold): 8 of 9 workloads. DuckDB reads Parquet natively with columnar pushdown. The mskql Parquet reader scans row-by-row via PLAN_PARQUET_SCAN.

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.

6. Running It Yourself

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 batch script auto-creates a mskql_bench database in PostgreSQL if it doesn’t exist. DuckDB runs in-process via its CLI. The throughput benchmark requires libpq headers at build time.