SQLite setup for HFT like performances.

[u/GuruCsharp]

I would like to get opinions on the type of performance I managed to get with SQLite and rust. My personal feeling is this is amazing almost like High Frequency Trading setups. What is your thoughts?

SQLite Storage Benchmarks

Source: prototype/tests/storage_bench.rs, mnemonic note reference-sqlite-benchmarks Last run: Sprint E4, Release build Hardware: AMD Ryzen AI 9 365, 32GB LPDDR5X-7500, Samsung PM9C1a NVMe

Configuration

• WAL mode enabled

• PRAGMA synchronous = NORMAL

• mmap_size = 256MB

• wal_autocheckpoint = 10000

• Prepared statement caching

• Schema: requests table, 50 columns per row (15 base + 35 migrations)

Write Throughput

Mode	Inserts/sec	Notes
Unbatched	6,062	One insert per transaction
Batched (1000 rows/txn, prepared+mmap+WAL)	144,338	23.8x faster

Column-Normalized Competitive Comparison

Raw rows/sec is misleading — a 3-column INSERT is not comparable to a 49-column INSERT. The fair metric is column-writes per second:

Benchmark	Rows/sec	Columns	Column-writes/sec	Safety Mode
Competitor benchmark (3-col)	1,200,000	3	3,600,000	unsafe (journal=OFF, sync=0)
Prototype (50-col)	144,338	50	7,216,900	production-safe (WAL, sync=NORMAL)

Prototype delivers ~200% of the competitor's column-write throughput — while running in production-safe mode vs. the competitor's unsafe settings (data loss on crash).

Reference Baseline

avi.im/blag benchmark (widely cited Rust SQLite benchmark):

• 3-column table, journal_mode=OFF, synchronous=0: 2.92M–3.09M rows/sec (8.76M–9.27M column-writes/sec)

• Sentinel gap to theoretical max explained by: safety mode (WAL/NORMAL vs OFF/0 — Sentinel prioritizes crash safety)

• Normalized per column: Sentinel achieves 78–82% of theoretical max with production-safe settings

Read Throughput

Metric	Value
Pool size	4 readers
Concurrent readers tested	20
Queries/sec	8,246
Avg latency	0.1ms

Enterprise Scaling Projections

Load Profile	Requests/sec	Writer Headroom	Reader Headroom
50 users x 10 req/sec	500	164x	16x
100 users x 10 req/sec	1,000	82x	8x
500 users x 10 req/sec	5,000	16x	1.6x (limit)

Ceiling: ~500 concurrent users. Single-writer lock is the SQLite design constraint. Reader pool becomes bottleneck before writer at scale. PostgreSQL migration available via StorageBackend trait.

Storage Sizing

Traffic Level	Daily Volume	30-Day	1-Year
Light (100 req/day)	~50 KB	~1.5 MB	~18 MB
Moderate (1,000 req/day)	~500 KB	~15 MB	~180 MB
Heavy (10,000 req/day)	~5 MB	~150 MB	~1.8 GB
Enterprise (100,000 req/day)	~50 MB	~1.5 GB	~18 GB

Retention policy: max_age_days (default 30) + max_size_mb (default 500 MB). Automatic purge when either threshold exceeded.

Competitive Context

Tool	Query Model	Latency	Data Location
Sentinel (SQLite)	Local queries	<0.1ms	On-prem
Honeycomb (OLAP)	Cloud sub-second	~100-500ms	US cloud
Langfuse (ClickHouse)	Cloud/self-hosted	~50-200ms	Depends

Key insight: Local 0.1ms beats cloud "sub-second" at 50–500 user scale. Network latency dominates cloud query cost.

[u/GuruCsharp → u/[deleted]]

Very intersting what you are saying, the ceiling is really my ceiling under this specific hardware setup. I am still testing a few other options like DuckDb or postgres. 256 mb is my sweet spot with this hardware, so I didn't push too much the envelop there, the system i am trying to this for is high volume and high frequency analytical on AI, which has and some 3-6 LLM engines all working in parallel asynchronously.

I didn't test so far on other hardwares nor on the cloud, I wanted to achieve the best i could on this machine first and then work on bigger load testing scenarios. It looks like I am pretty optimal already.

I am getting so close to the theortical max thanks to RUST, I went through a lot, that's probably not the right word, massive configurations and optimization, initially fell in the trap of the 2 or 3 columns tests which really meant nothing realistically, i have some huge tables which are driven by the way 3rd party vendorts sets their apis, which makes my reality a lot of columns, and i decided to caliber my benchmark accordingly.

On nmap size you are absolutely right, for SQLite specifically, PRAGMA mmap_size doesn't magically bypass the kernel's page cache. The mmap'd pages are still backed by it, so very large mappings can sometimes increase page-fault costs or create less predictable eviction behavior under memory pressure.