Sqlite on a ramdisk with compression still faster than on a NVMe SSD

Yes, absolutely. Running SQLite on a compressed RAM disk (like ZRAM on Linux or a compressed third-party RAM drive on Windows) will comfortably outpace an NVMe SSD in almost all scenarios.

The mechanics behind why this holds true break down into three primary vectors: latency, bandwidth, and OS caching behaviors.

1. Latency: The Unbridgeable Gap

The most critical factor for SQLite performance is transactional latency (especially for serial, unbatched INSERT or UPDATE operations).

• NVMe SSD: Even the fastest Gen 5 NVMe drives operate at a hardware latency floor of roughly 10 to 50 microseconds (\mu s).
• RAM Disk: Accessing system memory happens in nanoseconds—typically around 10 to 100 nanoseconds (ns).

Because RAM is roughly 1,000× to 5,000× faster in pure latency than flash memory, SQLite doesn't have to wait for the flash controller to clear cells or handle physical page mapping.

2. Bandwidth vs. CPU Compression Overhead

You might wonder if the CPU overhead of compressing/decompressing pages on the fly slows the RAM disk down. In reality, modern compression algorithms (like ZSTD or LZ4) are so fast that they actually help performance rather than hurt it.

• Throughput: A standard DDR4 or DDR5 memory channel pushes between 25 GB/s and 80+ GB/s. Top-tier NVMe SSDs max out around 7 to 14 GB/s.
• The Compression Win: Algorithms like LZ4 can decompress data at multiple gigabytes per second per CPU core. By compressing the RAM disk, you are transferring fewer physical bytes across the memory bus. The CPU cycles spent decompressing are easily offset by the reduction in footprint and the blistering speed of RAM.

3. The Elephant in the Room: The OS Page Cache

There is a catch to look out for. If your SQLite database is small enough to fit into RAM anyway, operating system file caching can level the playing field.

If you run SQLite on an NVMe SSD with PRAGMA synchronous = OFF; and PRAGMA journal_mode = WAL;, the OS will aggressively cache reads and buffer writes in system RAM anyway. In a benchmark of a fully-cached SSD database versus a RAM disk database, you might see nearly identical speeds because both are secretly running out of RAM.

When the Compressed RAM Disk Dominates:

1. Massive Datasets: If the database is 100 GB and you only have 32 GB of RAM, the NVMe SSD will be forced to constantly drop pages and hit the actual physical flash (thrashing). A compressed RAM disk using ZRAM might compress that 100 GB down to 30 GB, keeping the entire database inside physical memory and avoiding disk I/O entirely.
2. Strict Persistence (synchronous = FULL): If you force SQLite to safely commit every single transaction to the physical media via fsync, the NVMe SSD will stall out waiting for the hardware write cycle. The RAM disk will instantly return a "write complete" success because its "hardware" is just memory.

⚠️ The Data Volatility Warning:

Never forget that RAM disks are completely volatile. If your server loses power or crashes, an SQLite database on a RAM disk vanishes instantly. If you use this setup, ensure you are frequently backing up or replicating the database to that NVMe SSD.

(Source: Google Gemini)