Why I still default to PostgreSQL even after Rails 8 pushed SQLite

The Rails 8 SQLite story is one of the most interesting bets the framework has made in a decade. Solid Queue, Solid Cache, Solid Cable, all backed by SQLite by default in development. The deployment story (Kamal, single-server, on-disk database) is genuinely good. The vibes are excellent.

I’ve shipped a SQLite-backed Rails app to production. I’d do it again for the right project. I am also going to keep defaulting new apps to PostgreSQL, and I want to make the case for why without being a Postgres maximalist about it.

This is the post I would have wanted to read before I made the wrong call once.

The pitch, summarized fairly

The SQLite-on-Rails-in-production argument is roughly:

1. Most apps don’t need the scale they design for. SQLite handles tens of thousands of concurrent users on one server.
2. Operational simplicity is a feature. No separate database process. No connection pool tuning. No “the database is up but the app can’t reach it.”
3. Backups are a file copy. Replication is Litestream streaming to S3. Disaster recovery is fundamentally simpler.
4. Cost is dramatically lower. You’re not paying for a managed Postgres instance. You’re paying for a VPS.
5. Latency is better. Local disk reads beat network round-trips to a database server, every time.

Every one of those points is true. The SQLite movement isn’t selling snake oil. The reason I still default to Postgres is that the constraints SQLite imposes on your app’s design are real and you don’t always know which ones you’ll hit until you’ve already shipped.

Where SQLite breaks down at scale

1. Single-writer

This is the big one. SQLite serializes writes through one writer at a time. Reads are concurrent (with WAL mode), writes are not. For a read-heavy app this is fine — most web traffic is reads — but the moment you have a workload that does sustained writes, you hit the wall.

What “sustained writes” looks like in practice:

• A background job system processing thousands of jobs per minute, each updating job state
• An analytics endpoint logging every page view to a database table
• A real-time feature with high write volume (presence, typing indicators stored in DB, live counters)
• A bulk import that runs nightly

You can mitigate with batching, with a separate database file for high-write tables, with Litestream acting as a write buffer. None of these are difficult, but they’re all things you don’t have to think about with Postgres because Postgres has MVCC and concurrent writers as table stakes.

The single-writer constraint also has a sharp edge: when it bites, it doesn’t gradually degrade. You hit lock-wait timeouts and the app starts erroring. The transition from “fine” to “broken” is faster than I’d like.

2. No advisory locks

This one took me a while to articulate. Postgres has pg_advisory_lock — a lightweight, app-level mutex that lives in the database and goes away when the connection closes. I use them for:

• “Only one worker should run this nightly aggregation.” Take an advisory lock keyed to the job name. If the lock is held, exit.
• “Serialize this user’s actions across replicas.” Lock on user ID. The other request waits.
• Cross-process coordination without standing up Redis or ZooKeeper.

SQLite doesn’t have these. You can build the equivalent with a locks table and INSERT … ON CONFLICT, and it works, but it’s now your code to maintain and you’ve introduced exactly the kind of tricky concurrency primitive you wanted to avoid writing.

3. JSONB

Postgres’s JSONB columns are one of those features that you don’t appreciate until you have it. Indexed lookups into JSON documents, GIN indexes on ? and @> operators, full path queries, the ability to use JSON as a flexible-schema escape hatch in an otherwise relational model.

SQLite has JSON1, which is fine for storing and reading JSON, but the indexed query story is much weaker. You can index expressions over JSON paths, but it’s per-path and rebuilding the index when your access patterns change is more painful than CREATE INDEX … USING GIN.

For an app that uses a JSON column as a settings bag or an event payload, this doesn’t matter. For an app where JSON is a primary access pattern (event sourcing, flexible product attributes, document-shaped models), Postgres is meaningfully better.

4. Full-text search

SQLite’s FTS5 is good. I want to be clear about that — for a single-table search use case, FTS5 is excellent and probably faster than Postgres’s tsvector for the same workload because it’s purpose-built and lives in the same file.

What FTS5 doesn’t do as well: trigram similarity (Postgres has pg_trgm for “did you mean” suggestions), multilingual stemming with the breadth Postgres offers via tsearch_data, ranking with custom weights across multiple columns. For real product search you probably want Elastic or Meilisearch anyway, but for the in-between case — “I want better than LIKE '%foo%' but I don’t want a search service yet” — Postgres covers more ground.

Where SQLite legitimately wins

I want to be honest about this list because it’s longer than people expect:

• Small monoliths. Internal tools, admin dashboards, side projects, B2B apps with a few hundred users. SQLite is right.
• Single-server deployments. If your whole app fits on one VPS, SQLite removes a moving part.
• Embedded apps. Anything that ships to a customer’s machine — desktop tools, electron apps with a Rails backend, on-prem deployments. SQLite is the only sensible answer.
• Prototype speed. No database to set up means rails new to running app in seconds. Useful for spikes and demos.
• Read-heavy workloads with low write rate. Marketing sites with a CMS, documentation portals, content-publishing apps. The single-writer thing doesn’t bite.
• Apps with strict budget constraints. A $5 VPS running SQLite is cheaper than the cheapest managed Postgres tier by an order of magnitude.

These are real wins. None of them are marketing fluff.

The honest tradeoff

The line I land on, after going back and forth on it: SQLite’s ergonomics are real, but Postgres is still the right default for any app you expect to run for five years.

The reason is asymmetry. If you start on Postgres and never need its advanced features, you’ve paid a small operational cost and gotten back a database you’d be using anyway. If you start on SQLite and hit one of the four limits above two years in, the migration is meaningful work — you have to stand up Postgres, dual-write, cut over, and you’ll learn things about your app’s locking assumptions that you didn’t want to learn under deadline.

Migrations are easier in one direction than the other. Going Postgres → SQLite means dropping features. Going SQLite → Postgres means adding features and rewriting the code that worked around their absence. The first is rare. The second I’ve seen happen multiple times.

For a side project I expect to run for a year and shut down: SQLite. For an internal tool with five users: SQLite. For anything I’m starting that I think might still be running in 2031: Postgres.

What changed my mind, partially

The Rails 8 work on SQLite — the litestream integration, the WAL-by-default tuning, the Solid stack — has materially raised the floor of what SQLite can handle in a Rails app. I would not have considered SQLite for a 50-user B2B SaaS three years ago. I would consider it now.

What hasn’t changed: the four constraints above are properties of SQLite’s design, not its tooling. You can paper over single-writer with batching and queues, but the constraint is still there. You can build your own advisory-lock equivalent, but you’re now maintaining concurrency code. The Rails 8 defaults make these constraints less painful; they don’t remove them.

A heuristic

When someone asks me which database to use for a new Rails app, I ask three questions back:

1. How many concurrent writers do you expect? If the answer involves “thousands,” Postgres.
2. Do you need any of: JSONB indexed queries, advisory locks, trigram search, pub/sub from the DB? Any yes, Postgres.
3. Do you expect this app to outlive its current scope? Yes, lean Postgres. No, SQLite is genuinely fine.

Most production B2B SaaS apps end up in Postgres after that filter. Most internal tools, side projects, and content-driven sites end up in SQLite. That distribution feels right.

What I’d skip

The framing where SQLite is for “small” and Postgres is for “real” apps. Both are real. The SQLite movement has earned its credibility. I just think the default for an unknown future is the database with more headroom, and that’s still Postgres.

The Rails 8 push gave us a third option I didn’t have before: pick SQLite deliberately, with eyes open, for an app where it actually fits. That’s a strict improvement over the world where SQLite was assumed to be a development-only toy. It’s just not the same as making it the default for everything.

I’ll keep rails new --database=postgresql. And I’ll stop arguing with people who pick the other answer for the right reasons.