Choosing & Scaling a Database

Cover this or you fail the question

• How will this data be read? Lookups by primary key, range scans, aggregations, full-text search and similarity search all suit different engines, and the read path decides the choice far more than the write path does.
• What consistency does the operation actually require? A balance needs strong consistency; a view count does not. Answering this per operation rather than globally is the mark of a considered answer.
• What is the read-to-write ratio and the absolute volume? Read-heavy workloads scale with replicas and caches, which is comparatively easy; write-heavy workloads eventually need partitioning, which is not.
• How large will it get, and how fast? A dataset that fits in memory behaves fundamentally differently from one that does not, and this changes the answer more than most candidates expect.

Common trap: Choosing on the data's shape — 'it is JSON so we need a document store'. Relational databases handle documents well, and the shape rarely decides anything on its own.

Cover this or you fail the question

• It supports query patterns you have not thought of yet, which matters because early products change their access patterns constantly and a schema optimised around today's queries becomes an obstacle.
• Real transactions across multiple rows remove an entire class of application-level correctness problems that otherwise have to be solved by hand.
• Constraints and foreign keys keep bad data out at the storage layer rather than relying on every writer being correct forever.
• It is the best-understood operational surface there is: backup, restore, failover and tuning are well documented, and hiring for it is easy.
• Modern relational engines cover more ground than their reputation suggests — indexed document columns, full-text search, and vector similarity through extensions — which frequently removes the need for a second store.

What separates a solid answer

• A wide-column or key-value store when write volume and horizontal scale exceed what one primary can take and the access pattern is predictable lookups by key. The trade is giving up flexible querying and cross-row transactions, which is a real cost rather than a formality.
• A document store when documents genuinely vary in structure per record and are read whole. Choose it for that reason, not because the data arrives as JSON.
• A time-series database for append-only measurements queried by time range, where the specialised compression and retention handling are a large practical difference.
• A search engine when the requirement is relevance-ranked full-text search rather than exact matching — worth noting it is usually a secondary index alongside a primary store rather than the source of truth.
• A vector database, or vector support in an existing store, for similarity search over embeddings. For moderate volumes the extension in a relational database is usually enough and avoids a second system.
• A graph database when queries traverse relationships several hops deep and those traversals are the main workload, rather than an occasional join.

What separates a solid answer

• Fix the queries first. A missing index or a pattern issuing one query per row in a loop is extremely common, costs nothing to fix relative to any infrastructure change, and is often worth more than an order of magnitude.
• Add memory. A database serving its working set from memory behaves completely differently from one reading disk, and this is frequently the cheapest large improvement available.
• Add caching in front for repeated reads, with the caveat that a cache introduces invalidation and a cold-start failure mode that must be designed for.
• Add read replicas for read-heavy workloads, accepting replication lag and handling the case where a user must not read their own write from a stale replica.
• Partition only when write volume genuinely exceeds one primary. It is last because it is the change that constrains everything afterwards — cross-partition queries and transactions become hard or impossible, and the partition key is very expensive to change later.

The senior signal

• The partition key is close to permanent. Choosing one that distributes load evenly and keeps common queries within a single partition is the decision that determines whether the system works at scale, and a key that concentrates traffic on one partition cannot be fixed by adding capacity.
• Polyglot persistence — several specialised stores — is often proposed and consistently underestimated. Each store adds operational surface, its own failure modes, and the problem of keeping data consistent between them. The honest default is one store until a specific requirement justifies a second.
• Consistency choices propagate into the product. Eventual consistency is a fine engineering decision and it means the interface has to say what a user sees during the window, which is a design conversation rather than an implementation detail.
• Managed versus self-run is a real decision with a clear default: the parts of database operations most likely to be done badly under pressure — failover, point-in-time recovery, version upgrades — are exactly the parts a managed service performs identically every time.
• Backups are not a backup strategy until a restore has been performed. An untested backup is a belief, and the restore time is a number the business needs before an incident rather than during one.