What's new in SQLite
What’s new in SQLite#
It’s exciting
Disclaimer: This is my personal learning note, and it may contain inaccurate information. I would appreciate it if you could point that out. Thanks!
SQLite is a great OLTP database. SQLite excels at handling high reads and low-write applications.
High Concurrency
SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But some applications require more concurrency, and those applications may need to seek a different solution — https://www.sqlite.org/whentouse.html
Need to scale up vertically instead of horizontally in term of space.
Who uses SQLite in Server#
Expensify
Chisel Strike
remotive.io
Tailscale
k3s
CAP Theorem recap#
You cannot choose all three.
Streaming Backup#
From the CAP theorem, this approach sacrifices Availability where there is a single point of failure in the system.
Other databases in this category are just databases deployed in a single instance (for example, a single AWS RDS instance) and have a backup schedule.
Good-old cron job
Litestream
This video explains why a single server with a backup is good enough for most applications.
Advantage over the cronjob is it offers incremental backup and seamless integration to S3-compatible storage.
Works out of the box.
Reference:
Strongly-consistent Distributed DB#
From the CAP theorem, this approach sacrifices Partitioning where if there is not enough node in the quorum, it will reject the operation.
Other databases in this category are CockroachDB, Yugabyte (with synchronous replication), etc.
To scale the read request, it’s fairly easy; just add a new replica.
However, because each database transaction requires consensus from all replicas, the more replica you have, the higher the latency for the write operation.
Most of the implementations are software that sit on top SQLite that handle consensus between other nodes.
LiteFS
Ensuring consistency during split-brain.
Chisel Store
dqlite
Not a drop-in replacement for SQLite and require its own library and only available in Go.
A layer on top of SQLite. Single-threaded and using event-loop.
rqlite
A layer on top of SQLite.
Same as statement-based replication in other databases. Not a drop-in replacement for SQLite and require its own library.
Able to spin up new read replicas with non-voting power and able to choose consistency level which speeds up the query.
Read your own write https://github.com/rqlite/rqlite/blob/master/DOC/READ_ONLY_NODES.md#querying-a-read-only-node
Leader-less Distributed DB#
From the CAP theorem, this approach sacrifices Consistency. The database can scale horizontally, but each node sees its data (or its world) differently (hence eventual consistency).
Other databases in this category are Yugabyte Multi-DC setup, AWS Aurora with multi-writer feature, etc.
Marmot
Masterless database. It means you can insert the data from any node and scale horizontally on both reads and write. All reads and writes are local but the writes are propagated to all nodes via NATS broker.
However, I wonder how does it handle:
- split-brain issues if something is wrong with the NATS broker
- conflict. I assume the last write wins.
Marmot is also cannot store the data more than its available space so you’ll have to scale up your machine if you want to store more data