← Notes

Disk-backed tables: near-zero overhead persistence

Setup

The benchmark uses the same aggregate workload as the in-memory benchmarks, but the table is created with CREATE DISK TABLE. 5,000 rows, 500 iterations. Measured as wall-clock latency through the PostgreSQL wire protocol.

CREATE DISK TABLE sales (
    id INT, region TEXT, product TEXT,
    quantity INT, amount NUMERIC(10,2)
) DIRECTORY '/tmp/mskql_bench/sales';
-- 5,000 rows inserted (auto-persisted to disk)

SELECT region, SUM(amount)
FROM sales
GROUP BY region;
-- repeated 500 times

Problem

In-memory tables are volatile. Restarting the mskql process loses all data. For use cases that require persistence—development databases, long-running analytics servers, any deployment where data outlives the process—the in-memory-only model is insufficient.

Cause

Adding persistence typically introduces I/O overhead on both reads and writes. Disk-based databases use buffer pools, page caches, and write-ahead logs to manage the memory-to-disk boundary. Each of these layers adds latency and complexity.

Fix

The disk I/O layer (diskio.c / diskio.h) implements a simpler model:

• Write path: On INSERT, UPDATE, or DELETE, the modified column data is written to the disk directory. Writes are append-oriented and use the same columnar layout as in-memory storage.
• Read path: On startup, the disk directory is scanned and the table is loaded into memory. After loading, all reads are from in-memory column arrays—identical to regular tables.
• No WAL, no buffer pool. The disk layer persists the current state directly. There is no write-ahead log and no page cache to manage.

The CREATE DISK TABLE ... DIRECTORY '...' syntax is the only user-facing change. Once created, disk tables behave identically to in-memory tables for all query operations. The persistence is transparent.

Result

dk/mem ratios across all disk-eligible batch workloads:

The near-unity dk/mem ratio confirms that read performance is identical: once loaded, disk tables are in-memory tables. The slight variation (±0.2×) is within run-to-run measurement noise. Write-heavy workloads show slightly higher overhead due to the synchronous disk persist on each mutation, but read-dominated analytical workloads are unaffected.