Contents
For most web products the primary database should be relational — usually PostgreSQL: it provides transactions, relationships, flexible queries and can store JSON. NoSQL databases are worth adding alongside it for specific tasks: Redis for caching and queues, column stores for analytics, search engines for full-text search, graph databases for complex relationships.
Here are the kinds of databases, how they differ and how to choose for a given task.
How does SQL differ from NoSQL?
SQL databases store data in tables with a strict schema and relationships and support transactions with consistency guarantees. NoSQL is an umbrella term for databases with other data models: documents, key-value pairs, columns or graphs. They trade some guarantees or query flexibility for scaling or speed on particular tasks.
| Type | Examples | Data model | Used for |
|---|---|---|---|
| Relational | PostgreSQL, MySQL | Tables and relations | The product's core data |
| Document | MongoDB | JSON documents | Flexible structures, catalogues with varied attributes |
| Key-value | Redis | Keys and values in memory | Cache, sessions, counters, queues |
| Column | ClickHouse | Columns for analytics | Reports over billions of rows |
| Graph | Neo4j | Nodes and edges | Recommendations, social connections |
| Search | Elasticsearch, Meilisearch | Inverted index | Full-text search |
Why is a relational database a sensible default?
A relational database is a sensible default because almost every product's data is connected: users, orders, products, payments. Transactions ensure money is not charged without an order, and SQL lets you ask a new question of the data without rewriting code.
What a relational database provides:
- transactions — several changes succeed together or not at all;
- integrity — foreign keys and constraints prevent contradictory data;
- flexible queries — joins, grouping and reports without precomputed structures;
- JSON when needed — PostgreSQL can store and index documents.
For choosing between the two popular options, see PostgreSQL vs MySQL, and for more on transactions, transactions and isolation levels.
Photo: Unsplash
When is a document database genuinely better?
A document database is better when data naturally forms independent documents with varying structure, they rarely need joining and the schema changes often — for example, an event log from different devices or content made of arbitrary blocks.
Signs a document database fits:
- A document is read and written as a whole, not assembled from several tables.
- Relationships between documents are rare and simple.
- Structure varies a lot from record to record.
- Strict transactions across documents are not needed.
Once orders, stock and payments appear, the document model gets in the way: relationships have to be maintained by hand and integrity checked in code.
Which NoSQL databases are added alongside the main one?
Specialised stores are added alongside the main relational database for tasks where it is inefficient. The relational database remains the source of truth; the others hold derived copies or temporary data.
- Redis — cache, sessions, rate limiting, queues; see Redis in a web project;
- ClickHouse — analytics over large event volumes; see ClickHouse for analytics;
- a search engine — search with typo tolerance and facets; see site search;
- a vector database — semantic search and AI features; see vector databases.
How do you avoid choosing wrong?
Choose a database by how data will be read and changed, not by fashion. Describe the main entities, their relationships and the most frequent queries — the answer usually becomes obvious.
- do not pick NoSQL just for "future scaling" — relational databases scale further than most products need;
- do not store money and stock where there are no transactions;
- do not add a new database while the existing one copes: every database means backups, monitoring and expertise;
- validate the choice against real queries and volumes.
In short
- Default to a relational database — most often PostgreSQL.
- NoSQL covers different data models: documents, key-value, columns, graphs.
- Document databases suit independent documents with varying structure.
- Redis, ClickHouse, search and vector databases are added alongside for specific tasks.
- Choose by how data is read and changed, not by fashion.
FAQ
Is NoSQL faster than SQL?
Not in general. Specialised databases are faster at their own tasks — key-value stores for caching, for example. But for a typical application a well-designed relational database with indexes performs just as well and offers stronger guarantees.
Can PostgreSQL store JSON?
Yes, the jsonb type lets you store documents, index fields inside them and query them. It covers many scenarios that used to require a document database.
Is it hard to change the database later?
Moving between similar relational databases is manageable. Switching between different data models, such as from a document store to a relational one, almost always means rewriting a large part of the code.
