The short version: one file, one process, and the heaviest page in the app served 315 million requests a day on a M1-laptop. For almost anything you are building, SQLite in WAL mode is enough, and the Postgres container you spun up out of habit was never needed.

The SQLite setup:

const db = new Database('chirp.db');
db.pragma('journal_mode = WAL');       // readers and the writer stop blocking each other
db.pragma('synchronous = NORMAL');     // fsync at checkpoints, not on every commit
db.pragma('busy_timeout = 5000');      // wait for the write lock instead of throwing
db.pragma('foreign_keys = ON');        // off by default, which surprises people
db.pragma('cache_size = -64000');      // 64MB of page cache

Under WAL, across every scenario, zero SQLITE_BUSY errors. Not "few." Zero.

Limits of SQLite:

• Reads stop scaling once anything writes.
• There is only one writer.
• One machine: the recovery time is always the time needed to restore a file.

Reach for Postgres when you have many writers contending on the same rows, when you need read replicas or automatic failover, when you need a real analytics engine over hundreds of millions of rows, or when your team genuinely needs the extension ecosystem. Those are real reasons."

Advantages of SQLite:

• Backups are a file copy. From the shell: sqlite3 chirp.db "VACUUM INTO 'backup-$(date +%F).db'"
• Local development is one file.
• Resetting the database is rm
• Tests get a real database each
• Deploys are a binary and a file
• There is nothing to operate

To optimize SQLite:

• focus on single-core speed
• buy enough RAM to hold the database: wait for GBs
• local NVMe. Never put SQLite on network storage.
• Pay for a dedicated core if you care about p99.
• Use strict tables
• Only INT, INTEGER, REAL, TEXT, BLOB, and ANY are allowed, and ANY is there when you actually want a key-value column. Store your JSON in a TEXT column and use the JSON functions on it.
• Prepare every query on startup. Making it on every request is a way to make SQLite look slow

The hardest part is user acquisition either way. Don't loose time on operations.

The reflex to start with a database server is a habit, not an engineering decision. It made sense when SQLite did lock the whole file on every write. That stopped being true a long time ago, and the tooling caught up: WAL for concurrency, STRICT for type safety, Litestream for replication.

SQLite lessons:

• The SQLite gem supports user defined functions (udfs) and we used it to implement some missing functions in SQLite like regexp, if and stddev so that we wouldn't have to deal with too many sql migration workarounds.
• SQLite doesn't support unsigned bigints. Previously, the mariadb was using unsigned bigints for certain ids, so we had to switch those to bigints for the migration.
• Collation in SQLite is rather weak compared to MariaDB. Lobste.rs used utf8mb4_general_ci in MariaDB, but used NOCASE in SQLite. The downside of NOCASE is that it only supports ASCII characters, not the full UTF case folding.
• Use the preferred Contentless-Delete Tables in SQLite for your full text search tables. These are not the default. I'm constantly surprised by the default choices of SQLite.
  [...]
  Overall lessons:
• I think a key ingredient in making this work was good communication from everyone that participated. I don't think this would have been possible otherwise.
• Migrating the underlying database without having access to the production database is really hard to get right. This was my first underlying database migration without having access to production. One lesson I'll take away from this is that I'll make sure to have realistic dataset sizes before doing another underlying database migration in the future.

Wishes:

• I wish we could say in a test, "Fail if you encounter any full table scans". Which would have caught the perf issues we experienced during the first deploy.
• I wish creating a production like dataset would be much easier than having to manually write something and waiting a week.

Note: In SQLite rowid tables are implemented as B+-Trees where all content is stored in the leaves of the tree, whereas WITHOUT ROWID tables are implemented using ordinary B-Trees with content stored on both leaves and intermediate nodes.

UUIDs take 5 to 16x times slower to insert. UUID without row id in SQLite are only 2 to 3 times slower.

Not random insertion is also useful to write in a database efficiently.

Add Postgres-style NOTIFY/LISTEN semantics to SQLite, with built-in durable pub/sub, task queue, and event streams, without client polling or a daemon/broker.

For a website build

For local development, this has been a big win. Re-rendering all the HTML pages used to take about 15 seconds, but with a warm cache it takes 0.06 seconds. That’s a 200× speedup that I feel every time I hit save, and it’s made working on this site a smoother and more satisfying experience.

An explanation of assertion usage in SQLite. The more I read about it, the more I am willingly to pay 5% runtime to have these checks.

Issues with SQLite:

1. The test suite is not open source
2. External contributions are not welcomed
3. It's written in C, which is nowadays prone to easily-avoidable bugs, hard to maintain and add new features
4. SQLite does not support concurrent writes
5. Columns are weakly typed

Note the SQLite documentation hints Rust as a potential language for a rewrite under conditions listed at the end of the page.

Where Turso is good: scaling (instead of switching to PostgreSQL). Also it's simple to build an extension for it

A nice python project.

PostgresSQL if the service needs close to 99.999% availability, or more than 1 Gpbs of bandwidth or if the database is expected to grow larger than 100-200GB.

256GB means the SQLite DB can fit in RAM, or the migration will be too slow for the sqlite file and block the service.

It's important to note that with the advent of DuckDB, Parquet and Apache Iceberg, tehre are less and less reasons to stuff your main DB with timeseries data and instead only keep some kind of materialized view and send the raw data to S3.

For everything else, SQLite.

The author creates an extension for 3 uuid functions in Rust.

Tradeoffs: big extension size (330KB for simple uuids)

About using SQLite:

As mentioned in an earlier post the two biggest pain points are the "slow" schema changes on 10M+ rows tables locking the entire database for 10+ seconds, and the difficulty to implement automated failover. But it rocks for services that don't need 99.999 % of availability.

After a few years of using both (see Optimizing SQLite for servers for example), I've found that SQLite particularly shines when used for internal services or public services where a small amount of downtime is tolerable.

So, I choose PostgreSQL (preferably with a managed provider) if the service needs (close to) 100% uptime, if the service needs more than 5 Gbps of bandwidth or if the database is expected to grow larger than 200GB. [...] Bascially, all the situations where running on a single server is not possible.

It's important to note that with the advent of DuckDB, Parquet and Apache Iceberg, there is less and less reasons to stuff your main database with useless junk timeseries data, and instead only keep some kind of materialized view and send the raw data to S3. Thus, there are less and less reasons for your main database to be over 200 GB.

Dump the database as SQL statements instead of copying it with indexes. Then compress the resulting txt file.

# Create the backup
sqlite3 my_db.sqlite .dump | gzip -c > my_db.sqlite.txt.gz
# Reconstruct the database from the text file
cat my_local_database.db.txt | sqlite3 my_local_database.db

As complete script example:

# Create a gzip-compressed text file on the server
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"

# Copy the gzip-compressed text file to my local machine
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz

# Remove the gzip-compressed text file from my server
ssh username@server "rm my_remote_database.db.txt.gz"

# Uncompress the text file
gunzip my_local_database.db.txt.gz

# Reconstruct the database from the text file
cat my_local_database.db.txt | sqlite3 my_local_database.db

# Remove the local text file
rm my_local_database.db.txt

There should be better ways though.

My journal is now running on a new site that's pretty much the same on the front-end, except for the fact that it has a chronological list of my journal entries in all their glory (they are paginated 10/page). But at the back-end everything is stored in an SQLite database.

Source code: https://github.com/kevquirk/journal/

Guest Lecture at Saarland University, on June 25th, 2024

SQLite embedded in the browser as WASM executable.

If you look at my previous choices you will see there is in general a move to reducing the number of dependencies. The older and more crusty I get the more I appreciate having a single binary I can just deploy.

https://searchcode.com/

Decoupling storage from compute is the default architecture because it’s a really good idea.

It isn’t because SQLite might lose your data (it won’t), or it doesn’t scale well (it scale’s just fine)

A blazingly fast, open-source backend with type-safe REST & realtime APIs, built-in JS/ES6/TS runtime, SSR, authentication, and admin UI built on Rust, SQLite & V8.

Simplify with fewer moving parts: an easy to self-host single-executable with everything you need to focus on your mobile, web or desktop application. Sub-millisecond latencies eliminate the need for dedicated caches, no more stale or inconsistent data.