Choosing Between Postgres and SQLite for a New Rust Service
Both are excellent, and the difference is architecture. SQLite is a library inside your process, and Postgres is a server your process talks to. Pick by how many writers and machines you need.
The default answer for a new backend is Postgres, and that is usually right. It is not always right. A growing number of services run on SQLite in production because they fit its shape: one node, modest writes, large reads. The choice depends on a handful of questions that you can answer before writing code. Versions in mind: Postgres 17, SQLite 3.46, SQLx 0.8, Rust 1.90.
The core difference
| | SQLite | Postgres | |---|---|---| | Architecture | library in your process, one file | server process, network protocol | | Writers | one at a time | many concurrent (MVCC) | | Readers | many, in-process, no network | many, over connections | | Access from several machines | not supported on shared file systems | built for it | | Setup | none | install, configure, secure, monitor | | Types | dynamic typing, few types | rich types: arrays, JSONB, ranges, enums | | Extensions | limited (FTS5, JSON, R-Tree) | pgvector, PostGIS, pg_cron and more | | Backups | copy the file, Litestream | pg_dump, base backups, WAL archiving, managed | | HA and failover | external tooling | replicas, managed failover |
Everything else follows from these rows.
Questions that decide it
1. How many application instances will write? One instance: SQLite works. Two or more behind a load balancer: Postgres. Zero-downtime deploys with overlapping instances also point to Postgres, since two processes writing the same file during a rollout is a hazard.
2. How write-heavy is it? SQLite's single writer sustains thousands of small transactions per second on an SSD, which covers most applications. Hundreds of concurrent long-running write transactions do not fit.
3. Do other systems need to query the data? BI tools, workers on other machines and analysts connect to Postgres natively. With SQLite you export or replicate.
4. Which features do you need? Full-text search exists in both, with different strengths. Vector search, geospatial queries, row level security and listen/notify are Postgres strengths (pgvector, RLS, LISTEN/NOTIFY).
5. Who operates it? A solo developer or small team may prefer no database server. A team with a platform group or a managed Postgres has the opposite situation.
6. What is the growth path? If you expect to need multiple nodes within a year, starting on Postgres avoids a migration. If the service will stay small, SQLite avoids a permanent operational cost.
How the Rust code differs
The connection model changes the design. SQLite wants one writer connection plus reader connections (SQLite in production). Postgres wants a pool sized to the server (pool sizing). With SQLx, you pick a different feature and type for the pool:
# SQLite
sqlx = { version = "0.8", features = ["runtime-tokio", "sqlite", "migrate"] }
# Postgres
sqlx = { version = "0.8", features = ["runtime-tokio", "postgres", "migrate"] }SQL dialect differences appear quickly:
| Concern | SQLite | Postgres | |---|---|---| | Placeholders | ? or ?1 | $1 | | Auto IDs | INTEGER PRIMARY KEY | bigserial or identity | | Timestamps | text or integer | timestamptz | | Upsert | ON CONFLICT (supported) | ON CONFLICT | | Booleans | integers 0 and 1 | boolean | | ALTER TABLE | limited | extensive | | Type enforcement | flexible unless STRICT | strict |
Use STRICT tables in SQLite to get type checking closer to Postgres.
Keeping the door open
- Put data access behind a repository trait so only one module knows SQL (repository pattern).
- Avoid dialect-specific tricks in shared queries. Test the same migrations on both if you plan to switch.
- Use UUIDs or application-generated IDs, which move between systems easily (UUIDs versus integers).
- Store timestamps in UTC in a consistent format (timestamps and zones).
- Keep schema changes in versioned migrations.
Moving from SQLite to Postgres
Export tables as CSV, load with COPY (bulk loading with COPY), convert text timestamps and 0/1 booleans, recreate constraints and indexes, and run the application's tests against Postgres. pgloader automates much of it. Dual-write is rarely necessary at this scale, and a short maintenance window is simpler.
A checklist
Pick SQLite if: single node, writes under a few hundred per second, simple deployment valued, data under tens of gigabytes, team small.
Pick Postgres if: more than one app instance, other systems need the data, advanced features, regulatory needs for replication, or a managed service is easier for your organization.
When unsure and the cost of a wrong guess is low, choose the one you can operate confidently. Both are mature. For related design choices, see SQLx versus Diesel versus SeaORM.
