Best database for a web app: how to choose

What actually matters

1. The shape of your data

Most web application data is relational whether or not you planned it that way. Users have orders, orders have line items, line items refer to products, products belong to categories. You will want to query it from more than one direction: all orders for a user, all users who bought a product, revenue by category by month. A relational database answers all of these from the same normalised tables with joins. That flexibility is the reason it is the default.

A document database fits when the unit you read and write is a self-contained aggregate that is always loaded whole and rarely queried across: a CMS page with nested blocks, a product catalogue where each category has different attributes, an event payload. MongoDB stores each as one document, and an operation on a single document is atomic. The trade-off shows when you need a different access path. Joining collections is possible with $lookup, but the data model was not designed around it, and a single document cannot exceed 16 mebibytes. MongoDB schema design: embed or reference? covers that decision.

You do not have to choose one model for the whole application. PostgreSQL's jsonb and MySQL's JSON type let you keep the relational core and put the variable part in a document column:

-- PostgreSQL
CREATE TABLE products (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category_id bigint NOT NULL REFERENCES categories (id),
  name        text   NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0),
  attributes  jsonb  NOT NULL DEFAULT '{}'
);

CREATE INDEX products_attributes_idx ON products USING GIN (attributes);

SELECT id, name
FROM products
WHERE attributes @> '{"color": "red", "size": "M"}';

This covers most "our data is semi-structured" cases without giving up foreign keys and transactions for the rest of the schema.

2. Consistency requirements

If a mistake costs money or trust, you need multi-statement transactions and constraints enforced by the database: inventory that cannot go negative, payments recorded once, a username that is unique under concurrent sign-ups. PostgreSQL, MySQL with InnoDB and SQLite all provide ACID transactions, foreign keys, unique constraints and CHECK constraints.

MongoDB supports multi-document transactions, and its documentation notes that a well-designed document model makes them unnecessary for many use cases. That is true when the aggregate boundaries are right. It is harder when the business rules span documents, because there are no foreign keys, and schema validation is opt-in per collection.

Redis is not designed for this role. Its persistence options, point-in-time snapshots (RDB) and an append-only file (AOF), are configurable, and common configurations can lose the most recent writes on a crash. That is acceptable for a cache and unacceptable for orders.

3. What your team knows

A team that has run MySQL in production for years knows how to back it up, read its slow query log, add an index to a large table and recover from a failed replica. That knowledge prevents outages. Switching to PostgreSQL for a better feature list replaces it with a learning period during which the mistakes are made on live data. Unless a specific feature is required, the database your team can operate is the right one.

The same applies to the framework. Rails, Django, Laravel, Spring and the Node.js ORMs all support PostgreSQL and MySQL well, and most support SQLite. WordPress requires MySQL or MariaDB. Follow the ecosystem's most used path, because that is where the documentation, plugins and hosting guides are.

4. Hosting and operations

A database you do not have to patch, back up and fail over yourself is worth a lot to a small team. PostgreSQL and MySQL are both offered as managed services by AWS, Google Cloud and Azure, and by many smaller providers. MongoDB has a managed service from its vendor. SQLite has no server to manage at all, but backups and replication become your responsibility, usually through a tool such as Litestream that streams changes to object storage.

Check two things before committing: that point-in-time recovery is included, and that the provider supports the extensions or version you need.

What does not matter at small scale

Benchmark results. On a dataset that fits in memory with correct indexes, PostgreSQL, MySQL and SQLite all answer typical queries in well under the time your application spends rendering the response. Slow pages at this stage come from missing indexes and from an ORM issuing a query per row, not from the engine. The mechanics are in Postgres vs MySQL performance: which is faster, and when?.

Horizontal scalability. Choosing a database for its sharding story before you have enough data to fill one server means paying real costs now (weaker joins, weaker transactions, more operations work) for a problem you may never have. A single PostgreSQL or MySQL primary with read replicas carries most applications for their whole life.

"SQL does not scale." Relational databases run some of the largest sites in the world. The scaling path is well documented: indexes, connection pooling, read replicas, caching, partitioning, then sharding.

Schemaless speed. Skipping migrations feels faster in the first weeks. The schema still exists, in application code and in whatever old documents were written before the last change. A relational migration is that same work done once and enforced.

Decision table by application type

Some notes on the less obvious rows.

Real-time. Redis pub/sub has at-most-once delivery: a subscriber that is disconnected when a message is published never receives it. It is the transport for "notify connected clients now", and the message itself still needs to be written to the primary database so that clients can fetch what they missed. PostgreSQL LISTEN/NOTIFY does a similar job without an extra system for small deployments.

Search. Start with the full-text search in your database. PostgreSQL has tsvector with GIN indexes, MySQL has FULLTEXT indexes and SQLite has the FTS5 extension. Move to Elasticsearch or OpenSearch when you need typo tolerance, tuned relevance ranking, faceted navigation or search across very large text. The search index is then a derived copy: the primary database remains the source of truth and the index can be rebuilt from it.

Caching. Add Redis after you have measured a slow query that cannot be fixed with an index, not before. Every cache adds an invalidation problem. Redis caching patterns: cache-aside, write-through and TTLs explains the options.

SQLite. It is a serious production choice for a single-server application, not only a development convenience. In WAL mode readers and the writer do not block each other, though there is still only one writer at a time. The SQLite project's own guidance is that it suits most low to medium traffic websites and does not suit many machines writing to the same database over a network. A minimal production setup:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
PRAGMA busy_timeout = 5000;

journal_mode is stored in the database file. The other three apply to the connection that runs them, so your application must set them each time it opens a connection. Details are in SQLite in production: WAL mode and knowing when it's enough.

The default, and when it is wrong

Use PostgreSQL unless one of the conditions below applies. The reasons:

• It covers relational data, documents (jsonb), full-text search, geospatial queries (PostGIS) and vector similarity search (pgvector), which delays or removes the need for extra systems.
• Constraints are thorough: foreign keys, CHECK, exclusion constraints, partial unique indexes and deferrable constraints.
• Schema changes are transactional, so a failed migration rolls back cleanly.
• It is released under a permissive license and is not controlled by a single company.
• Managed offerings exist on every major cloud and from several specialised providers.

Use MySQL or MariaDB instead when:

• your team has production experience with it and none with PostgreSQL;
• your framework, CMS or e-commerce platform is built around it (WordPress and much of the PHP ecosystem);
• the workload is overwhelmingly primary-key reads and writes and you want the operational tooling built around MySQL at very large scale.

PostgreSQL vs MySQL: the differences that matter compares the two in detail.

Use SQLite instead when:

• the application runs on one server and can tolerate a single writer;
• it runs on a device, at the edge, or as a desktop or CLI tool;
• it is a prototype and you want nothing to install.

Use MongoDB instead when:

• the data is a set of independent documents with varying structure, read and written whole;
• you have few cross-document rules to enforce;
• you need built-in sharding from an early stage and accept modelling around a shard key.

Check the license too. MongoDB is published under the Server Side Public License, which places conditions on offering it as a service to others.

Do not use as the primary store: Redis (unless the data is disposable or fits a deliberately designed durable configuration) or a search engine.

Habits that matter more than the choice

These apply to every option above and cause more incidents than the choice of engine.

• Back up and test restoring. A backup that has never been restored is an assumption.
• Use migrations from the first commit, in version control, applied the same way in every environment.
• Index for your queries, and check plans for the frequent ones. Foreign key columns usually need an index.
• Pool connections. Application servers and serverless functions can open far more connections than a database handles well. See How do I fix "FATAL: sorry, too many clients already" in PostgreSQL?.
• Choose primary keys deliberately. Should I use UUIDs or auto-increment integers as primary keys? covers the trade-offs.
• Paginate with keysets, not large offsets. Why does OFFSET pagination get slower on every page? shows why.
• Keep transactions short. Never hold one open across an external API call.

Checklist

1. Write down your main entities and how they relate. If you can draw it as tables with foreign keys, you want a relational database.
2. Identify the operations where incorrect data costs money. Those need transactions and constraints.
3. Ask what the team has operated in production. Give that heavy weight.
4. Check what your framework or platform expects.
5. Default to PostgreSQL. Switch to MySQL, SQLite or MongoDB only if a condition in the section above applies.
6. Start with one database. Add Redis when a measured problem needs a cache or queue, and a search engine when database full-text search is no longer enough.
7. Set up backups, migrations and connection pooling before launch.

References

• PostgreSQL: JSON Types
• PostgreSQL: Full Text Search, Preferred Index Types
• PostgreSQL: Row Security Policies
• PostgreSQL License
• MySQL 8.4: The JSON Data Type
• MySQL 8.4: Full-Text Search Functions
• SQLite: Appropriate Uses For SQLite
• SQLite: Write-Ahead Logging
• SQLite FTS5 Extension
• MongoDB: Transactions
• MongoDB: Limits and Thresholds
• MongoDB: Schema Validation
• MongoDB: Server Side Public License
• Redis: Persistence
• Redis: Pub/Sub
• WordPress requirements
• Litestream

Postgres vs MySQL performance: which is faster, and when?

Explains by mechanism where InnoDB or PostgreSQL does less work for lookups, updates, joins, connections and replication, then shows how to benchmark your own workload with pgbench and sysbench.

#internals#mvcc#performance