SQLite, PostgreSQL, and the smallest useful database choice

The smallest useful choice

The SQLite versus PostgreSQL question is often argued as if one database proves engineering seriousness. A better question is smaller: what is the smallest database choice that honestly supports the app's durability, concurrency, deployment, and operational needs?

SQLite is not a toy. PostgreSQL is not always overengineering. They optimize for different shapes of work.

For a Rails app, the right answer depends less on ideology and more on writes, hosting, backups, extensions, team familiarity, and whether multiple processes need to coordinate through the database.

SQLite

SQLite is embedded. The database is a file, the server process disappears, and deployment can be extremely simple. For small apps, prototypes, local tools, read-heavy sites, and single-node deployments, that simplicity is a real advantage.

It also reduces operational surface. There is no separate database daemon to patch, monitor, or network. Backups can be straightforward when the write pattern is understood.

The trade-off is concurrency and ecosystem expectations. Multiple app processes, long writes, background jobs, and platform tooling can make the file-based model harder to operate well.

PostgreSQL

PostgreSQL is a server database with strong concurrency, rich indexing, extensions, roles, replication options, observability, and a large operational ecosystem. It is a natural default for many Rails apps because Rails teams and hosting providers understand it well.

That comes with more moving parts: provisioning, credentials, backups, upgrades, connection limits, memory settings, and monitoring. For tiny projects, that may be more system than the app needs.

PostgreSQL earns the complexity when the app needs concurrent writes, multiple services, advanced queries, full-text search, JSON indexing, background workers, or an easy path to managed production hosting.

Rails operational fit

Rails supports both, but the surrounding assumptions differ. Many gems, deployment guides, and production runbooks assume PostgreSQL. SQLite can work well, but the team should understand how migrations, jobs, connection pooling, backups, and deploy restarts interact with it.

The database is part of the runtime contract. A single Puma process and a small SQLite database is one contract. Multiple web dynos, job workers, and analytics queries are another.

Changing databases later is possible, but not free. SQL differences, constraints, indexes, date behavior, and locking assumptions can leak into application code.

Decision

Choose SQLite when the application is small, deployment simplicity is the main constraint, writes are modest, and the team is willing to keep the architecture single-node or carefully controlled.

Choose PostgreSQL when concurrency, hosted operations, richer indexing, extensions, or future growth are already part of the realistic plan.

The best database choice is the one whose failure modes the team is willing to own. Minimal does not mean fragile. Serious does not mean large by default.

Migration pressure

A common mistake is to choose SQLite while quietly planning for PostgreSQL semantics. If the roadmap already assumes multiple app servers, background workers, advanced indexing, and hosted read replicas, PostgreSQL may be the simpler honest choice from day one.

The reverse mistake is also common: choosing PostgreSQL for a tiny app because it sounds serious, then spending more time on database operations than on product behavior. The database should fit the actual pressure, not the imagined status of the project.

When the database stops being small

SQLite is excellent when the application and the database live in the same trust and deployment boundary. The warning signs appear when that boundary changes: multiple app servers, long background jobs, heavy concurrent writes, operational reporting, or permissions that need to live in the database.

PostgreSQL also changes how failure is handled. A web dyno can restart while the database keeps accepting connections from other workers. Backups, replicas, extensions, roles, and observability become separate operational tools instead of application-local details.

-- PostgreSQL makes database-side guarantees explicit.
ALTER TABLE invoices
ADD CONSTRAINT total_cents_non_negative
CHECK (total_cents >= 0);

CREATE INDEX CONCURRENTLY index_invoices_on_account_and_issued_at
ON invoices (account_id, issued_at DESC);

The minimal-postgres argument is not that every side project needs a database server on day one. It is that once the app is networked, multi-process, or operationally important, PostgreSQL gives you a boring place to put guarantees that should not depend on one process behaving well.

Operational threshold

The threshold is usually operational before it is technical. SQLite can handle serious work, but the moment the team needs separate database credentials, independent backups, concurrent worker fleets, read replicas, extension support, or production observability, PostgreSQL starts buying clarity.

The migration cost is also part of the choice. Moving later is possible, but every raw SQL fragment, type assumption, locking expectation, and deployment script becomes a place to check. For projects likely to grow into a networked service, starting with minimal PostgreSQL can be less work than proving later that SQLite assumptions were harmless.

That does not make SQLite a toy. It means the decision should include deployment shape, not only benchmark numbers. A single-user local app and a multi-worker web service have different definitions of simple.