100000 TPS over a billion rows: the unreasonable effectiveness of SQLite
9 days ago
- #Benchmarking
- #Database Performance
- #SQLite
- SQLite's single-writer model and embedded nature allow it to achieve high transaction per second (TPS) rates, outperforming network databases like Postgres in certain scenarios.
- Benchmarks show SQLite achieving up to 44096 TPS without network latency, compared to Postgres's 13756 TPS under similar conditions.
- Network latency significantly impacts Postgres performance, reducing TPS from 13756 to 348 when simulating real-world conditions with serializable transactions and additional queries.
- SQLite's ability to batch transactions dynamically, using features like SAVEPOINT for nested transactions, further increases its TPS to 186157 and 121922 with fine-grained rollback.
- Adding concurrent reads to the mix, SQLite maintains a high TPS of 102545, demonstrating its effectiveness in mixed read-write environments.
- The article highlights the challenges of Amdahl's law and network latency with traditional databases, advocating for SQLite's efficiency in high-throughput scenarios.
- Further reading and resources are provided for scaling SQLite, including handling replicas, backups, and the limitations of single-machine workloads.