The database market is fragmented. Relational databases (PostgreSQL, MySQL), document databases (MongoDB, Firebase), time-series databases, graph databases, caches, search engines. Each has strengths and many have overlapping capabilities.
Choosing the right database requires understanding what you are optimizing for.
The core trade-offs
Write optimisation vs. read optimisation. Some databases are optimised for fast writes (data ingestion, event streaming). Others are optimised for fast reads (analytics, reporting). Some try to balance. You cannot have both equally fast — you have to choose.
Consistency vs. availability. In a distributed database, you can have consistency (reads always show the latest write), availability (the system always responds), or partition tolerance (the system handles network failures). You can have any two. This is the CAP theorem and it is fundamental.
Query flexibility vs. performance. A database that supports any query (SQL) is slower than a database optimised for specific queries. A document database that lets you query any field is less efficient than one where you know your queries in advance.
Operational burden. Some databases require significant operational work (monitoring, tuning, failover). Others are fully managed. There is a cost to each.
The questions to answer
Before looking at databases, answer these.
What is the main use case? Transaction processing (OLTP), analytics (OLAP), time-series data, full-text search, real-time applications, something else?
What are the access patterns? Do you do lots of random reads? Sequential scans? Aggregations? Point lookups? The access patterns determine which database is efficient.
How much data? Gigabytes, terabytes, petabytes? The scale determines what is feasible.
What consistency do you need? Immediate consistency (traditional ACID), eventual consistency (updates propagate slowly), something in between?
How many writes per second? This determines throughput requirements.
How many reads per second? This determines query throughput.
What is your tolerance for downtime? If you need 99.99% uptime, you need something different than 99% uptime.
The databases and their sweet spots
PostgreSQL. General-purpose relational database. Good for almost everything. Transaction processing, analytics, JSON queries, full-text search. The default choice unless you have a specific reason to choose something else.
MySQL. Similar to PostgreSQL. Slightly simpler, widely supported. Default choice at many companies.
MongoDB. Document database. Good when your data is naturally document-shaped and you want flexible schemas. Slower for complex joins.
Firebase. Fully managed database with real-time sync. Good for web and mobile apps. Expensive at scale.
Elasticsearch. Search and analytics. Good for full-text search and log aggregation. Not good for transactional consistency.
DynamoDB/NoSQL databases. Fast key-value stores. Good for applications that need millions of operations per second. Bad for complex queries.
ClickHouse/TimescaleDB. Time-series and analytics. Good for metrics, events, and large analytical queries. Bad for transactional consistency.
Graph databases (Neo4j). Good for relationship-heavy data (social graphs, recommendations). Bad for everything else.
Redis/Memcached. In-memory caches. Good for session storage, rate limiting, real-time counts. Not a primary database.
The decision framework
Start with relational (PostgreSQL). It handles 90% of use cases adequately. Only move away when you have measured that it does not meet your needs.
Check if you have specific access patterns. Do you only do key-value lookups? DynamoDB might be better. Do you have metrics and events? Time-series database might be better. Do you have complex relationships? Graph database might be better.
Check if scale requires horizontal distribution. PostgreSQL scales vertically (bigger servers). If you need to distribute across multiple servers, something like DynamoDB or a distributed Postgres cluster makes sense.
Check if your schema is flexible. If your data is naturally nested and your schema changes frequently, a document database might be a better fit than a relational database.
Measure before switching. If you are thinking about switching databases, measure on the current one first. Is it actually the bottleneck? Changing databases for a 10% performance improvement is not worth the cost.
What I usually see
Under-engineered: Teams store everything in a single MySQL database and wonder why it is slow. Usually the answer is better indexing or query optimisation, not a new database.
Over-engineered: Teams use DynamoDB for a workload that is 10% of peak scale and pays 2x what a relational database would cost, because they read an article about how fast DynamoDB is.
Mixed up: Teams choose a database because it is trendy, not because it fits their use case. This creates long-term pain.
The honest approach
Choose PostgreSQL unless you have a specific measured reason to choose something else. Measure thoroughly. Optimise queries and schema before considering database changes. Only change databases when you understand the trade-offs you are accepting.
The database market rewards specialisation. Specialised databases are very fast at specific problems and terrible at general problems. A relational database is adequate at many problems, which is often better than specialised and terrible.
A single good database is better than multiple databases you don’t fully understand.