Start with the questions
Before naming a database, write down the requests it must answer and the state changes it must protect. Does a user retrieve one document? Join orders with customers? Search a sentence? Find semantically similar passages? Record expected data growth, update frequency and a concrete consistency requirement.
Our example product has accounts, paid subscriptions and a searchable article library. A relational database can own accounts and subscription state. A search index can serve article discovery. A vector index may support semantic retrieval. That does not require three sources of truth: the application can retain canonical records in one durable store and derive indexes.
Match the access shape
| Store style | Useful starting point | Important trade-off |
|---|---|---|
| Relational | Constraints, joins, transactions | Schema and query design |
| Document | Aggregate-shaped records | Cross-document invariants |
| Key-value | Known-key lookup and temporary state | Limited query shape |
| Search | Text relevance and filtering | Index lag and rebuilds |
| Vector | Similarity over embeddings | Relevance, filters and evaluation |
These are starting points rather than exclusive capabilities. PostgreSQL supports JSON alongside relational tables; its JSON documentation explains the available representations. A JSON column does not remove the need to decide which fields deserve constraints and indexes.
Turn a product feature into access patterns
Imagine a support product with tickets, comments, full-text search and recommended related articles. Writing a ticket requires a durable owner, status and history. Opening it needs a bounded set of records. Searching needs ranked text matches. Recommendations may need vector similarity. Listing those requests makes the choice more concrete than asking whether SQL or documents are modern. Each feature has an access pattern and a failure cost; the entire product does not need to inherit one storage style automatically.
Begin with a representative data sample and a short list of important queries. Include the uncomfortable cases: a tenant with unusually many tickets, a long comment history, a changed permission and a deleted document that must disappear from search. Record which relationships and uniqueness rules need enforcement. A relational database with JSON fields can handle many mixed shapes without adding a second operational system. A specialized search or vector index becomes easier to justify when its query behavior provides a capability that the authoritative store cannot deliver within the actual product constraints.
Separate authoritative records from derived indexes
The authoritative store is where the product decides what exists and what the user may access. A search index is usually a derived view optimized for retrieval. Treating those roles explicitly gives deletion, permissions and rebuilds a clearer design. A result returned by a derived index may need authorization against current records, especially when access has changed since indexing. Similarity ranking alone should never imply that a user is permitted to view a document.
A document aggregate can simplify loading one coherent object, but an ever-growing embedded comment list can make updates and transfers expensive. Relational joins can express shared relationships, but poorly bounded queries can also become costly. Compare modeling choices with the request patterns rather than with slogans. For every additional store, write down who operates backups, restores, schema or mapping changes, monitoring and resynchronization. A specialized engine may improve one query while adding a permanent coordination problem. Its value must exceed that operational cost, especially for a small team that already has a reliable primary database.
Decide consistency explicitly
For an account balance, stale reads can be unacceptable. For a search result, a short indexing delay may be acceptable. Describe that difference per workflow. If a record is deleted, determine when derived search results stop exposing it. Authorization must remain correct even while an index is behind.
A second store means another failure mode. Design what happens when it is unavailable: fall back to a simpler query, return a clear unavailable state, or keep serving a bounded stale result. Do not silently treat a missing index update as a successful permanent write.
Count operational cost
Compare backup restoration, client support, monitoring, upgrades, migrations and the team's familiarity. Managed services can reduce infrastructure work, while introducing limits and recurring spend. An inexpensive instance is not inexpensive if restoring it takes days.
Use one realistic query fixture and one failure drill before committing to a complex architecture. Rebuild a derived index from canonical data. Restore a backup into an isolated environment. Check that your main transaction still enforces its invariant under concurrent requests.
A sensible default is the simplest store that satisfies the current workload and leaves a clear extraction path. Add specialized stores when measured query or product needs justify their operating cost. Compare OLTP and OLAP and caching strategies before adding another database merely to make reads faster.
Validate the failure path before committing to a platform
A selection exercise should include more than a successful demonstration query. Load representative documents or rows, include skewed tenants, and observe the expensive path. Then restore a backup into a separate environment and rebuild any derived indexes from authoritative records. A platform that looks simple during insertion may be difficult to recover when identifiers, permissions or mappings evolve. Record the recovery steps and the dependencies needed to perform them.
During an index outage, decide whether the application can still open known records, accept writes or show a useful degraded search state. Avoid making every business operation depend synchronously on every specialized system. For asynchronous updates, expose and monitor lag so that stale search results are diagnosable. Keep a clear boundary between query experiments and permanent architecture: a benchmark on synthetic uniform data is evidence about that sample, not proof for all future workloads. The final choice should explain the most important invariant, the hardest query, the recovery process and the next scale boundary the team will re-evaluate.
