Choosing a database: relational unless you have a reason

Start relational

PostgreSQL and MySQL handle transactions, complex queries, JSON documents, full-text search and geospatial data competently. For most business applications the relational default is correct and remains correct at considerable scale.

Modern relational databases absorbed many features that once justified specialised stores, which makes the case for adding another system weaker than it was.

Reasons to choose otherwise

• Document stores — genuinely variable schemas where relational modelling fights the data.
• Key-value — caching and session state at high throughput.
• Time series — heavy append-only metric workloads with time-window queries.
• Graph — queries traversing many relationship hops.
• Search engines — relevance ranking and faceted search.

Every additional store is operational cost

A second database means another thing to back up, monitor, patch, secure, scale and hold expertise in. It also raises the question of consistency between stores, which is where subtle bugs live.

Adding one is justified when a specific workload genuinely does not fit; it is not justified by a general preference.

Model for how you query

Normalise until it hurts, denormalise until it works. Start with a clean model, measure the queries that matter, then denormalise deliberately where profiling justifies it.

Indexes are the cheapest performance work available and the most commonly neglected. Most slow queries in production are a missing index rather than a wrong database.

Planning for growth

Most databases outlive the assumptions they were designed under, so a few habits keep future options open without over-engineering now.

• Avoid business logic in the database where it is hard to test and version.
• Keep schema migrations in version control and reversible.
• Add indexes deliberately and review them — unused indexes cost write performance.
• Monitor query performance continuously rather than investigating after complaints.

Read replicas, connection pooling and query optimisation address most scaling needs, and they should be exhausted before considering architectural change.