Choosing the Right Database
Every database decision starts with the shape of your data. Structured data with relationships → relational (PostgreSQL). Flexible, evolving schemas → document (MongoDB, Firestore). High-speed key lookups and caching → key-value (Redis, DynamoDB). Analytics over millions of rows → columnar (BigQuery, Redshift). Semantic search and AI retrieval → vector (pgvector, Pinecone). Relationship traversal → graph (Neo4j, Neptune). Time-series metrics → InfluxDB, TimescaleDB.
Most applications use more than one. A common production stack: PostgreSQL as the system of record, Redis as the cache and session store, and pgvector or Pinecone for semantic search. Start with the simplest option that fits your data shape. Add a second database only when the first one demonstrably cannot handle the use case — not because it looks impressive on a diagram.
Check your understanding
Structured data with relationships — first pick?Show answerHide answer
Answer
A relational engine (e.g. PostgreSQL) as system of record unless constraints force otherwise.When add a second database engine?Show answerHide answer
Answer
When the primary cannot meet a measured need (cache latency, vector search, analytics) — not for diagram aesthetics.Give one example multi-store stack.Show answerHide answer
Answer
Postgres for records, Redis for cache/sessions, and pgvector or a vector service for semantic search — only if each earns its ops cost.