Why We Chose SQLite

Update: October 1, 2026

This post reflects our earlier Gleam exploration for Curling IO v3. We ultimately chose Rust and TypeScript. Why We Chose Rust for Version 3 explains that decision and what we kept from the exploration.

About this post

This is a technical architecture note about the database decision for Curling IO v3. It is written for software engineers and operators, and goes deeper into SQLite, Litestream, database isolation, hosting, and recovery tradeoffs than our usual product posts.

Version 3 should be cheaper to operate, easier to restore, and still fast during peak registration and live scoring. That pushed us toward a choice we didn't expect: SQLite.

We assumed PostgreSQL at first. After a decade running Postgres in production, we knew the tooling, the failure modes, and the operational playbook. Then we compared self-hosting Postgres with Litestream-backed SQLite and changed our minds.

What Version 2 Runs On

Curling IO Version 2 runs on Rails backed by PostgreSQL on Crunchy Bridge, a managed Postgres service that has worked well for us. Crunchy Data handles backups, failover, and tuning. We don't think about shared_buffers or autovacuum settings. We don't run pgBackRest or schedule base backups. (Crunchy Data was acquired by Snowflake in 2025, which adds another reason to reduce our dependency on third-party managed services.)

The trade-off is cost, lock-in, and jurisdiction. Crunchy Bridge is AWS-only, so the app servers have to live on AWS too. That's the full stack dependent on a single US cloud provider. We're a 100% Canadian company and we'd rather keep our infrastructure closer to home, especially given the current political uncertainty south of the border.

For Version 3, we're moving to OVH, a French-owned provider with data centers in Quebec and Ontario. All data stays in Canada. Crunchy Bridge doesn't run on OVH, so we can't bring it along. Self-hosting Postgres on OVH would mean taking on everything Crunchy handles today: connection pooling, server tuning (shared_buffers, effective_cache_size, work_mem, max_connections), autovacuum monitoring, and the full backup story (pgBackRest, WAL archiving, scheduled base backups, tested restore procedures, monitoring backup freshness). That's a lot to take on for a small organization.

The Architecture: Database Per Sport

Version 3 uses a database-per-sport architecture. Each sport gets its own SQLite file with an identical schema:

db/
├── curling.db          # All curling club data
├── pickleball.db       # All pickleball club data
└── shared.db           # Cross-sport reference data, jobs, email suppressions

Each sport database is multi-tenant. Every table with tenant data includes an org_id column, and every query filters by it. Adding a new sport means creating a new database file with the same schema. No cross-sport queries, no shared-database contention.

shared.db holds things that span sports: Canadian tax jurisdictions (17 rows covering GST, HST, PST, and QST rates by province), the background job queue, and email suppression lists from Postmark webhooks.

At startup, the server opens one connection per database and holds it for the lifetime of the app:

Connection setup

pub fn open(path: String) -> Result(sqlight.Connection, sqlight.Error) {
  use conn <- result.try(sqlight.open(path))
  use _ <- result.try(sqlight.exec("PRAGMA journal_mode=WAL;", conn))
  use _ <- result.try(sqlight.exec("PRAGMA busy_timeout=5000;", conn))
  use _ <- result.try(sqlight.exec("PRAGMA foreign_keys=ON;", conn))
  Ok(conn)
}

Three PRAGMAs on every connection: WAL mode for concurrent reads, a 5-second busy timeout so writes queue instead of failing immediately, and foreign key enforcement (which SQLite disables by default).

One connection per database. No connection pool. No pool configuration. Each incoming HTTP request gets the sport-specific connection (resolved from the hostname) plus the shared connection.

We also built Marmot for Gleam to generate typed database calls from SQL. We later ported it to Marmot for Rust, where we’ve continued to develop it. The Rust version analyzes SQL against the SQLite schema and generates typed functions backed by rusqlite. Queries live beside the workflows that own them, so we can keep the SQL explicit without hand-writing the parameter binding and row decoding.

Why In-Process SQLite

SQLite runs inside the application process. There's no socket, no TCP round-trip, no serialization between the app and the database. A query is a function call.

Our workload is primarily simple indexed lookups: single-row fetches by primary key, filtered lists by org_id, a handful of line items joined to an order. Our benchmarks show substantial throughput for this kind of workload, and more than a decade of Version 2 traffic gives us a baseline for estimating demand. Even during peak registration or when a provincial championship is posting live scores, the load profile is overwhelmingly reads with occasional write bursts.

The cost follows from the architecture. No separate database server means no database hosting bill. The entire v3 stack runs on a single server for less than what managed Postgres alone costs on AWS.

WAL Mode and Concurrency

WAL (Write-Ahead Logging) is what makes SQLite viable for a concurrent web application. Without it, any write locks the entire database for both readers and writers. With WAL, readers proceed concurrently with writes. On the BEAM, where hundreds of lightweight processes might query simultaneously during a traffic spike, this is essential.

Writes are still serialized. One writer at a time, coordinated by a mutex in the SQLite library. If the write lock can't be acquired within the 5-second busy timeout, SQLite returns SQLITE_BUSY. With our read-heavy workload, write contention hasn't been a practical concern. This is why we use a separate database for background jobs. Job processing writes to shared.db, not the sport databases, so it never contends with registration queries. With SQLite's single-writer model, separating write-heavy workloads into different files is a real architectural consideration, not just organization.

Litestream: The Tipping Point

This is what tipped the scales.

SQLite is a file. Litestream watches that file and continuously replicates its WAL frames to S3-compatible object storage. For us, that's OVH Object Storage in two different geographic locations in Canada. Changes sync every 10 seconds to both.

The configuration is one YAML file:

Litestream restore details

dbs:
  - path: /var/lib/curling-io/databases/curling.db
    replicas:
      - type: s3
        bucket: xxxxx
        endpoint: xxxxx
        sync-interval: 10s
      - type: s3
        bucket: xxxxx
        endpoint: xxxxx
        sync-interval: 10s

Recovery is one command:

litestream restore -o /path/to/curling.db s3://xxxxx/curling.db

That produces a ready-to-use database file. Copy it to the right path, start the app.

Now compare that to self-hosting PostgreSQL backup and recovery:

1. Install pgBackRest
2. Configure a stanza, set archive_mode and archive_command in postgresql.conf
3. Schedule base backups via cron
4. Monitor backup success, WAL retention, and disk usage
5. Periodically test restores (because an untested backup isn't a backup)
6. When disaster strikes: stop PostgreSQL, run pgbackrest restore --type=time --target="...", wait for WAL replay, verify the recovery point, promote to primary

PostgreSQL gives you something Litestream doesn't: exact transaction-level point-in-time recovery. You can roll back to any specific transaction. Litestream gives you "latest snapshot plus WAL frames," which means a worst-case recovery gap of about 10 seconds. For our use case that's more than sufficient.

The operational gap is where it gets decisive. Litestream is one binary, one config file, near-zero ongoing maintenance. PostgreSQL backup is a practice, something you rehearse, monitor, and maintain over the life of the project. Even with perfect tooling, you can't reduce "restore a running server's state" to "download a file and start the app." That gap is architectural. It won't close with better Postgres tooling because the complexity is inherent to backing up a running server process.

The simplicity ceiling for SQLite backup is fundamentally higher than what's possible for any client-server database.

The SQLite Tax

Here's what we pay for that simplicity. Every item below is something PostgreSQL handles natively.

No Real Types

SQLite doesn't have booleans, dates, or enums. Booleans are stored as INTEGER 0/1. Dates are Unix epoch integers. Enums are TEXT strings.

Every boolean parameter needs a db.bool_to_int(value) call. Every boolean decoder needs a != 0 check. All date formatting, timezone conversion, and comparison lives in application code. There's no DATE_TRUNC, no INTERVAL, no AT TIME ZONE.

We use STRICT tables on everything, which enforces that values match declared column types (INTEGER, TEXT, REAL, BLOB). It's a free safety net for anything that bypasses the application layer, but it doesn't help with booleans, dates, or enums. Those stay as application-level concerns, but since Gleam and Parrot are the only source of writes and types are enforced there, it's manageable.

Limited ALTER TABLE

SQLite stores its schema as the original CREATE TABLE text, not as structured system catalogs like PostgreSQL. This means ALTER TABLE is minimal:

• ADD COLUMN, RENAME COLUMN, DROP COLUMN all work
• Changing a column's type, default, or NOT NULL does not
• Adding or modifying CHECK or FOREIGN KEY constraints does not
• Adding UNIQUE to an existing column does not

Changing a column post-launch requires a four-step dance: add the new column, copy the data, drop the old column, rename. PostgreSQL handles this with a single ALTER COLUMN.

No CHECK Constraints

We don't use CHECK constraints anywhere. This is a policy decision, and not one we're thrilled about.

CHECK constraints are embedded in the CREATE TABLE DDL string. Modifying one means a full table rebuild. Adding a new enum value shouldn't require that. So all validation lives in Gleam: union types for enums (with exhaustive pattern matching at compile time), validation functions for business logic, and let assert in decoders to crash fast if bad data gets in.

Write Serialization

As covered above, writes are serialized. Short, indexed transactions keep the writer lock brief, and there is no database network round-trip. That gives us substantial headroom in the benchmarked workload. It doesn't guarantee that writers never wait: transaction duration and simultaneous writes still matter. But it does rule out horizontal scaling to multiple app servers writing to the same database.

What We've Measured

We published a request-shape benchmark comparing Gleam, Rust, and Ruby. It uses synthetic data with production-derived query shapes: about 26 small indexed queries for an admin read request, and a short transaction for an update. The Rust Marmot path measured about 12,900 read sequences and 45,800 update sequences per second on a Ryzen 7 9700X server with 62 GiB of RAM, using WAL and synchronous=NORMAL.

Those are database-workflow timings, not complete HTTP requests or a concurrent load test of the whole application. The fixture has small, fixed-size tables; 10,000 requests doesn't mean 10,000 clubs. It supports the case for short, indexed queries and in-process SQLite, but it doesn't prove capacity at a particular tenant count.

We also have more than a decade of Version 2 usage to work from. Our busiest observed hour was roughly 240,000 requests, about 67 per second averaged across that hour. Shorter spikes can be higher, and request costs vary. Combining that history with the benchmark gives us a basis for capacity estimates. Those estimates remain extrapolations until representative concurrent application tests establish throughput and latency at the proposed scale.

Single Node Only

SQLite is a file on local disk. Multiple app servers can't share it (NFS breaks SQLite's file locking and leads to corruption). We're locked into a single-server architecture. The benchmarks and more than a decade of Version 2 usage give us confidence in this single-server approach. Capacity depends on simultaneous registrations, scoring traffic, and background writes, rather than club count alone.

There are projects that add distributed replication to SQLite (LiteFS, rqlite, dqlite), but they add significant complexity: FUSE filesystems, consensus protocols, additional infrastructure. At that point you're fighting to make SQLite behave like Postgres, and you should probably just use Postgres.

NIF Risk

Our SQLite driver (esqlite) is an Erlang NIF - compiled C code that runs outside the BEAM scheduler's control. A long-running query blocks a scheduler thread. A segfault takes down the entire VM.

In practice, SQLite's C library is near-bulletproof, and our queries return in microseconds. We mitigate the risk by keeping all queries simple (indexed lookups, no full table scans) and pushing anything heavy to background jobs. But the risk surface exists.

SQL Dialect Gaps

Small things that add friction:

• DELETE FROM t WHERE ... LIMIT 1 doesn't work. You need a subquery: DELETE FROM t WHERE id = (SELECT id FROM t WHERE ... LIMIT 1).
• No LISTEN/NOTIFY for real-time push from the database. Change notification goes through application-level pub/sub.
• NULL comparisons need IS ? instead of = ? when the parameter can be NULL.

Why We Accept It

Every tax above is an application-level workaround. Type conversions, date formatting, schema evolution dances, validation in Gleam instead of CHECK constraints. None of them change the architecture. They're annoyances, not obstacles.

Our observed Version 2 usage and the request-shape benchmarks support choosing one server for this read-heavy workload. Write contention, data growth, and latency under concurrent load are still the things to watch.

When We'd Switch to PostgreSQL

We don't expect to need it soon. Vertical scaling is a practical first step while the workload fits on one server. Sustained write contention, unacceptable latency, or a requirement for multiple application servers would be reasons to reassess.

The SQL conversion would be fairly routine: change parameter syntax, map identity columns and booleans, review SQLite-specific statements, and replace the database driver and generated bindings. Our separate database files could map to PostgreSQL schemas, but we'd need to verify isolation and access rules. We expect much of the business logic to carry over.

The rest deserves its own work:

• Set connection-pool limits and timeouts, and review query shapes that would now pay a network round-trip for each statement.
• Check transaction isolation, locking, and read-then-write workflows under concurrent writers. Add bounded retries where conflicts require them.
• If we add application nodes, coordinate durable job claims, prevent duplicate external effects, and carry application notifications across processes.
• Set up backups, replication if needed, monitoring, and tested recovery. Rehearse data transfer and cutover, verify the imported data, and define rollback before switching production traffic.

That's a manageable migration, but it isn't just a dialect swap. We'd need to test the affected workflows and operational procedures before calling it ready.

What's Next

If SQLite stops meeting our needs, PostgreSQL is a credible next step. Keeping SQL beside the workflows that own it makes the conversion easier to scope, but the concurrency and operational changes still need verification.

But so far, Litestream plus in-process SQLite has been one of the best decisions in the v3 rewrite. And there's one more benefit worth its own post: because SQLite runs in-process, every test gets its own in-memory database. No shared state, no cleanup, no flaky tests. That's next.

---

This is Part 5 of the Curling IO Foundation series. Next up: Test Isolation for Free with SQLite.