Compare SQLite and PostgreSQL for Rails apps: performance, scalability, concurrency, deployment, and when to use each.
• 21 min read
• 21 min read
If you have ever run rails new and paused at the database prompt, you already know this decision matters. SQLite and PostgreSQL are the two most common choices for Rails applications, and both are excellent tools. The problem is that a lot of advice online jumps straight to "just use PostgreSQL" without explaining why, which leaves beginners assuming SQLite is a toy and PostgreSQL is the only serious option.
That's not accurate. The right database depends on your application's size, concurrency needs, deployment setup, and how it's likely to grow. A personal blog and a multi-tenant SaaS platform don't have the same database requirements, even if both are built with Rails.
This article covers how the two databases differ architecturally, how each behaves inside a Rails app, when each makes sense in production, and how to think through the decision using real scenarios instead of generic rules of thumb.
SQLite is a lightweight, file-based relational database. Instead of running as a separate server process, it's embedded directly into your application, with the entire database, tables, indexes, and data, living in a single file on disk.
Because there's no separate server to install or manage, Rails apps talk to SQLite through a simple library call rather than a network connection. This is why SQLite is called an embedded database: it runs inside the same process as your Rails app rather than alongside it.
For local development, this convenience is hard to beat. Clone a repository, run bin/setup, and your database is ready, no credentials, no server to start, no ports to configure.
Rails 8 leans into this further. With built-in SQLite backed adapters for caching, background jobs, and Action Cable, SQLite has moved well beyond a "development only" tool and become a legitimate option for certain production workloads.
PostgreSQL is a client-server relational database. Unlike SQLite, it runs as its own process, usually on a dedicated server or managed service, and your Rails application connects to it over a network connection, even if that connection is to localhost.
This client-server model is a big part of why PostgreSQL is the default choice for production web applications. Multiple application servers can connect to the same instance at once, each handling its own concurrent requests, while PostgreSQL coordinates access to the data.
PostgreSQL also brings a deep set of advanced SQL features: rich indexing, native JSON support, full text search, extensions for things like geospatial data, and strong support for concurrent transactions through MVCC (multi-version concurrency control), covered later on. For Rails developers, it's long been the "safe default" for applications expected to scale, thanks to this combination of concurrency support and feature depth.
The fundamental difference comes down to architecture. SQLite is embedded; PostgreSQL is client-server. That single distinction explains almost every other difference between them.
With SQLite, your Rails process reads and writes directly to a database file. There's no separate connection handshake and no network layer, which makes it fast for single-process access but introduces file-level locking constraints when multiple processes want to write at once.
With PostgreSQL, your Rails process opens a network connection to a running database server, which manages many simultaneous connections, coordinates locking at a much finer grain, and handles concurrent writes from multiple clients without blocking the entire database.
This difference cascades into deployment (a file to copy versus a service to run), operational complexity (almost none versus meaningful setup), and scalability (great for a single server versus built for many concurrent clients from the start).
Active Record, Rails' ORM, abstracts away most database differences. You define models, write migrations, and query with methods like where and joins, and Active Record translates that into the correct SQL for whichever adapter you're using. Switching between SQLite and PostgreSQL in config/database.yml is often just a matter of changing the adapter:
# SQLite
development:
adapter: sqlite3
database: storage/development.sqlite3
# PostgreSQL
development:
adapter: postgresql
database: myapp_development
username: myapp
host: localhost
Migrations, models, validations, and most queries work identically across both databases. But "abstracts away" doesn't mean "makes identical." The underlying database still affects locking behavior, concurrency limits, available data types, and how certain queries perform under load. Active Record hides the syntax differences, not the physics.
For local development, SQLite is genuinely convenient. There is no database server to install or start, no credentials to manage, and no risk of your local setup drifting out of sync with a running service. Rails ships with SQLite as the default adapter for new applications for exactly this reason: a fresh clone followed by bin/setup gets a working database with zero extra configuration.
Backups during development are trivial too. Since the entire database is one file, you can copy it, version it, or reset it by deleting the file and re-running migrations, which also makes SQLite a great fit for demos, prototypes, and CI. For many solo developers and small teams, SQLite in development removes a whole category of setup friction.
Some teams prefer running PostgreSQL locally even during development, and the reasoning is straightforward: production parity. If production runs PostgreSQL, developing against SQLite means your queries aren't tested against the same engine that will run them in production, which matters more once you use PostgreSQL-specific features like JSONB columns, array types, or full text search.
The trade-off is simplicity versus consistency. Running PostgreSQL locally, often through Docker, adds setup overhead in exchange for catching database-specific bugs before they reach production. Whether it's worth it depends on how much your app leans on PostgreSQL-specific behavior.
SQLite is often unfairly dismissed as unsuitable for production, but that's not accurate. It works well for small to medium applications that are read-heavy, run on a single server, and don't require heavy concurrent write traffic.
A few production considerations matter here:
None of this means SQLite is unsuitable for all production applications; it means production use requires understanding these constraints. For a deeper walkthrough of configuring SQLite for real production traffic, CodeCurious has a complete guide to optimizing SQLite for Rails 8 production.
PostgreSQL earns its reputation as a production workhorse because it was built for exactly this environment: many concurrent connections, simultaneous writes from multiple sources, and large datasets that need to stay performant over time.
It handles multiple application servers connecting at once without the locking constraints SQLite faces, supports replication for read scaling and high availability, and offers mature tooling for backups, monitoring, and point-in-time recovery.
Advanced indexing strategies (partial indexes, GIN indexes for JSONB, and more) and a large ecosystem of extensions give PostgreSQL room to grow with an application's needs, rather than requiring a database migration later. For applications expecting meaningful growth, PostgreSQL is often the safer default precisely because it removes a class of scaling problems before they happen.
There is no single benchmark that settles "SQLite vs PostgreSQL performance" once and for all, and you should be skeptical of anyone who claims otherwise. Performance depends heavily on workload shape, not just which database you picked.
For read-heavy, single-server workloads, SQLite can be extremely fast since there's no network round trip between your app and the database. For write-heavy workloads with many concurrent writers, PostgreSQL's MVCC model generally performs better because it doesn't serialize writes the way SQLite's locking model can.
Query design and indexing usually matter more than the engine itself. A poorly indexed query on PostgreSQL can easily be slower than a well-indexed one on SQLite, and vice versa. Treat "which database is faster" as the wrong question; the better one is which database's concurrency model matches your application's actual read and write patterns.
This is arguably the most important practical difference for a growing Rails app.
SQLite traditionally locks the entire database file during a write, meaning only one write can happen at a time. WAL mode improves this by letting reads continue while a write is in progress, but writes are still serialized, so several Puma workers writing at once will queue behind each other.
PostgreSQL uses MVCC, letting multiple transactions read and write concurrently without blocking each other in most cases, since each works with its own consistent snapshot of the data.
In practice, a Rails app with a handful of background workers and moderate write traffic will likely be fine on SQLite with WAL mode enabled. An app with dozens of concurrent workers, or multiple app servers writing simultaneously, will feel PostgreSQL's concurrency advantage much more directly.
Both databases fully support transactions, and Active Record's transaction API works the same way regardless of which one you use:
ActiveRecord::Base.transaction do
order.update!(status: "paid")
inventory.decrement!(:stock, order.quantity)
end
If any statement inside the block raises an error, Active Record rolls back the entire transaction. This behavior is identical across both databases.
Where they differ is what happens when transactions overlap. Under concurrent load, PostgreSQL's MVCC lets multiple transactions proceed independently with less contention, while SQLite's locking model means concurrent write transactions are more likely to wait on each other. The atomicity guarantee is the same; behavior under simultaneous load is not.
Indexes work conceptually the same way in both databases: they let the database look up rows without scanning an entire table. Both support single-column indexes, composite indexes across multiple columns, and unique indexes to enforce data integrity.
add_index :orders, :customer_id
add_index :orders, [:status, :created_at]
add_index :users, :email, unique: true
Foreign key columns should almost always be indexed, since Active Record associations frequently query on them, regardless of which database you're running.
Choosing a database doesn't replace the need for good indexing. A missing index will slow down queries on PostgreSQL just as much as on SQLite. For a deeper look at how indexing interacts with query performance in Active Record, CodeCurious has a guide on optimizing Active Record queries in Rails worth reading alongside this one.
Active Record does a remarkable job of making SQLite and PostgreSQL feel similar day to day. Models, associations, validations, migrations, scopes, and most queries work the same regardless of adapter.
class Order < ApplicationRecord
belongs_to :customer
has_many :line_items
end
The differences show up around the edges: database-specific SQL functions, certain data types, and constraints that one database supports and the other doesn't. PostgreSQL, for example, supports array columns and JSONB natively, while SQLite has more limited equivalents.
If you want to keep the option of switching databases open, avoid heavy reliance on database-specific SQL in scopes or raw queries. For a closer look at how associations translate into actual SQL joins, see CodeCurious's guide on how Active Record associations work in Rails.
Both databases cover the basics well: strings, text, integers, booleans, dates, and times all map cleanly through Active Record migrations in either database.
Where they diverge is in more specialized types. PostgreSQL has native support for JSONB, array columns, and UUID as a first-class type, along with binary data handling suited for larger payloads. SQLite supports JSON and can store similar data, but with fewer native query operators and less mature tooling around it.
None of this means every Rails application needs these advanced features. A straightforward CRUD app will not notice the gap, but an app doing heavy JSON querying, working with array data, or relying on UUID primary keys will feel PostgreSQL's broader feature set more directly.
PostgreSQL's JSONB type stores JSON data in a binary format that supports indexing and efficient querying, useful for applications storing semi-structured data like user preferences or event payloads. Array types let you store lists of values in a single column without a join table. Full text search built into PostgreSQL can handle basic search without an external service for smaller datasets, and extensions like PostGIS add geospatial capabilities.
These features are genuinely useful, but they are not requirements for most Rails apps. If you don't need flexible JSON querying, arrays, or built-in search, treat them as nice to have rather than decisive.
As a Rails application grows, database choice starts to interact with how the application itself scales. Vertical scaling, giving your single server more CPU and memory, works reasonably well with both databases up to a point.
Horizontal scaling, running multiple application servers, is where the databases diverge. Background jobs, connection pooling, and read-heavy versus write-heavy workloads all put different pressure on the database layer depending on which engine you're running. PostgreSQL generally becomes more attractive once an app needs multiple app servers or heavier concurrent database activity, since it was designed for that pattern from the start. SQLite can still participate, but with more careful architecture around where the database file lives.
The core challenge with SQLite in a horizontally scaled setup is that it's a single file. Multiple application servers all need access to that same file, and network filesystems generally don't provide the locking guarantees SQLite needs for safe concurrent writes.
This doesn't mean SQLite can't participate in multi-server deployments, but the architecture needs to account for it, whether that's a single write server with read replicas synced through tools like Litestream, or restricting SQLite to single-server deployments entirely. In general, SQLite fits best where its database file can be reliably and exclusively accessed by the process writing to it, rather than shared across many independent app servers.
PostgreSQL was designed with this scenario in mind. A centralized database server accepts connections from as many application servers as needed, coordinated through pooling tools like PgBouncer. Read replicas offload read-heavy traffic from the primary, and built-in replication supports high availability setups where a standby server can take over if the primary fails.
For Rails apps that need to run on multiple app servers, whether for redundancy or traffic volume, PostgreSQL's client-server model is a much more natural fit than coordinating access to a single file.
Backup strategy differs meaningfully between the two databases.
For SQLite, a safe backup means copying the database file (and any associated WAL file) using a method that accounts for in-progress writes, rather than a naive copy that could grab an inconsistent state. Tools like Litestream can stream continuous replication of the file to remote storage for durability.
For PostgreSQL, you have more established options: logical backups (pg_dump), physical backups, and point-in-time recovery. Managed services typically handle much of this automatically.
Whichever database you choose, having backups isn't the same as having a recovery plan. Regularly test restoring from them, not just creating them, since a backup you've never restored is an assumption, not a guarantee.
SQLite's deployment story is simple: your database is a file that lives alongside your application. There is no separate service to provision, no credentials to rotate, and minimal infrastructure to reason about, though you do need persistent storage on platforms that otherwise treat filesystems as ephemeral.
PostgreSQL requires a running database server, self-managed or through a managed provider, along with credentials, network connectivity, and typically connection pooling as traffic grows.
For small teams and solo developers, this difference is not trivial. SQLite can meaningfully lower the barrier to shipping and maintaining a Rails application, especially early on.
SQLite has a cost advantage that is easy to overlook: there is no separate database service to pay for, since it lives on the same server as your application. PostgreSQL, especially through managed services, adds a recurring cost on top of application hosting, but that cost buys automated backups, easier scaling, replication, and monitoring, which is often well worth it once traffic or data justifies it.
The honest framing is that SQLite reduces infrastructure cost, while PostgreSQL's cost is usually justified by capabilities that apps actually need. Neither is universally cheaper in the abstract.
Security for SQLite is largely a matter of filesystem security: proper file permissions, controlling server access, and securing backups like any other sensitive file.
PostgreSQL's security model is broader because it is a networked service: authentication, role-based permissions, network access rules, credentials, and SSL/TLS all come into play, though managed providers typically handle much of this baseline for you.
In both cases, security depends more on how you deploy and configure things than on which database you picked. A poorly configured PostgreSQL server can be less secure than a well-protected SQLite file, and vice versa.
A common setup is using SQLite for local development and testing, with PostgreSQL in production, and for a lot of apps this works fine. Active Record smooths over most differences, and SQLite's speed makes for a fast local test suite.
The risk appears when your app relies on PostgreSQL-specific behavior, JSONB queries, array operations, specific SQL functions, that SQLite either doesn't support or handles differently. Tests passing locally against SQLite don't guarantee the same behavior in production against PostgreSQL, so if you're using database-specific features, it's worth testing against PostgreSQL in CI rather than relying entirely on local SQLite tests.
Applications often start on SQLite and move to PostgreSQL as they grow, typically triggered by increased traffic, more concurrent writes, larger datasets, a move to multiple application servers, or a need for PostgreSQL-specific features. At a high level, the process looks like this:
Complexity depends heavily on your application's size and how much database-specific behavior it relies on. A straightforward CRUD app migrates far more easily than one leaning on SQLite-specific SQL.
This direction is less common, but teams do it to simplify a smaller app's deployment, reduce infrastructure, or go more local-first.
The challenge is compatibility: PostgreSQL-specific features like JSONB queries, array columns, or certain SQL functions don't have exact equivalents in SQLite, so any code relying on them needs to be reworked.
Rails 8 has meaningfully improved SQLite's story for production use. Built-in adapters for Solid Cache, Solid Queue, and Solid Cable mean an entire Rails app, web requests, background jobs, and caching, can run on SQLite without introducing Redis or a separate job queue.
Combined with WAL mode and sensible busy timeout configuration, this makes SQLite a genuinely viable production option for a meaningful slice of Rails applications, particularly smaller ones on a single server.
That said, Rails 8 does not change SQLite's fundamental concurrency model. It makes SQLite easier to run in production; it does not make it suitable for every workload. Applications expecting heavy concurrent write traffic or a multi-server architecture still tend to be better served by PostgreSQL, Rails 8 or not.
Solid Queue, Rails' database-backed background job framework, can run on SQLite, and Rails 8 supports this out of the box. Jobs are stored as rows in the database, and worker processes poll for and claim available jobs, which introduces write concurrency: your web requests and job workers may both be writing to the same database. WAL mode and appropriately configured busy timeouts help workers wait for locks rather than fail outright.
The practical takeaway is to evaluate your app's combined workload, web traffic plus job volume, together rather than in isolation. A low-traffic app with a handful of workers will likely be fine on SQLite; a high-throughput job system alongside heavy web traffic deserves more scrutiny.
A practical framework, not a universal rule:
Choose SQLite when:
Choose PostgreSQL when:
These are guidelines meant to inform a decision, not absolute rules that apply identically to every project.
"SQLite is only for development." Not true, WAL mode and Rails 8's Solid stack make it a legitimate production option for many apps.
"PostgreSQL is always faster." Not universally; performance depends on workload, indexing, and query design more than the engine.
"SQLite cannot be used in production." It can, with the right configuration and understanding of its concurrency constraints.
"PostgreSQL is always more expensive." Not necessarily; SQLite reduces infrastructure costs, while PostgreSQL's cost buys capabilities that matter more as an app grows.
"Switching databases later is always easy." Depends heavily on how much database-specific behavior your app relies on.
"Active Record makes all databases behave exactly the same." It smooths over syntax, not underlying locking behavior.
"SQLite does not support transactions." It does, fully, including rollbacks.
"PostgreSQL is necessary for every Rails application." Many small applications run perfectly well on SQLite.
A few patterns show up repeatedly:
Avoiding these mistakes usually comes down to deciding deliberately, based on your application's real requirements, rather than defaulting to habit.
Work through these questions before choosing:
Your answers should guide the decision far more than general opinions about which database is "better."
Personal blog: SQLite, low traffic and simple deployment matter more than advanced features.
Small SaaS application: Either works early on; SQLite keeps things simple, PostgreSQL makes sense if growth is imminent.
Internal company dashboard: SQLite usually suffices, since traffic is limited to internal users.
High-traffic API: PostgreSQL, given the concurrent connection and write demands.
E-commerce application: PostgreSQL, due to concurrent order and inventory updates under load.
Content-heavy website: SQLite works well for read-heavy content with modest write activity.
Background-job-heavy application: Depends on volume; light workloads suit SQLite, heavy ones favor PostgreSQL.
Multi-server Rails application: PostgreSQL is the better architectural fit.
Prototype or MVP: SQLite, for speed and simplicity while validating an idea.
Small production app with modest traffic: SQLite is a reasonable, cost-effective choice.
None of these are universal rules, but they reflect how the architectural differences above tend to play out in practice.
Is SQLite good for Rails production? Yes, for many small to medium apps with modest write concurrency, especially with WAL mode enabled.
Is PostgreSQL better than SQLite for Rails? Depends on the app. PostgreSQL suits high-concurrency and multi-server workloads better, but that doesn't make it universally "better."
Should I use SQLite or PostgreSQL with Rails 8? Either can work well. Rails 8's Solid stack strengthened SQLite's production story, but PostgreSQL still fits apps expecting significant scale.
Can Rails use SQLite in production? Yes, and Rails 8 supports this directly, including database-backed caching and background jobs.
Why do Rails developers use PostgreSQL? Mainly its concurrency model, advanced features, and maturity in multi-server production environments.
Is SQLite faster than PostgreSQL? Neither is universally faster; it depends on workload, indexing, and whether you're optimizing for reads or writes.
Can SQLite handle multiple users? Yes, especially with WAL mode, though heavy concurrent write traffic favors PostgreSQL.
Can I migrate a Rails app from SQLite to PostgreSQL? Yes, it's well-established, though complexity depends on app size and database-specific behavior.
Should I use PostgreSQL in development if production uses PostgreSQL? It helps catch database-specific bugs earlier, especially with PostgreSQL-specific features.
What database is best for a Rails application? No single answer; it depends on expected size, concurrency needs, and deployment architecture.
SQLite and PostgreSQL are both strong choices for Rails applications, and the right one depends on your workload, expected concurrency, scaling plans, feature needs, and deployment architecture, not on which database sounds more impressive.
SQLite can be an excellent choice for smaller applications, prototypes, internal tools, and even certain production workloads, especially now that Rails 8 supports it so thoroughly. PostgreSQL remains the stronger choice for applications expecting meaningful growth, high write concurrency, or advanced database features.
Rather than defaulting to whichever database is more popular, look honestly at what your application actually needs today and where it's realistically headed. That's a far more reliable guide than any general rule about which database is "better."