There is no best database. There is a best fit for a workload. Ask three questions:
- How does the application read the data? By one key, by any column, as whole documents, or by following relationships?
- What must always be correct? Money and stock need ACID transactions. A count of page views can be a little late.
- How big, and where? Gigabytes on one server, or billions of writes a day over many machines?
Match the workload
| Workload | Good fit | Why |
|---|---|---|
| Orders, payments, stock, accounts | relational | transactions, constraints, joins, any query |
| Product catalogue with different attributes per product | document (or a jsonb column) | each product is read as one piece; the shape varies |
| Sessions, caches, rate limits | key-value | one key per access, very fast, values expire |
| Sensor readings, logs, events at huge volume | wide-column | fast writes spread over many machines; reads by key and time range |
| Social networks, recommendations, fraud rings | graph | queries follow relationships many steps deep |
Graph queries in SQL
A relational database can store a graph: a table of edges, such as follows(follower, followee). Each step of "who follows whom" is one more join. "Friends of friends" needs two copies of the table, three steps need three, and so on. A graph database follows the edges directly, at any depth.
Start relational
Most applications start well with a relational database. PostgreSQL also has jsonb for flexible parts. Many systems use more than one model: PostgreSQL for orders, Redis for sessions, a search engine for text search. Add a second database when a workload really needs it, because each one is more to run, back up and secure.