Picking the wrong database is one of the most expensive architectural mistakes a team can make. Unlike a library swap, migrating production data is a multi-month project. In 2026 the landscape has stabilized considerably — there are fewer surprising new entrants and more clarity on which database wins which workload. This guide gives you a decision framework, not a vendor pitch.
What changed in 2026
- Postgres ate more of the market. pgvector 0.8 handles vector search at scale; full-text search improved; JSONB performance is excellent. The "use Postgres first" default is stronger than ever.
- Serverless databases matured. Neon (serverless Postgres), PlanetScale (serverless MySQL), Turso (embedded SQLite/libSQL), and Supabase are production-ready and cost-effective for variable-traffic workloads.
- Vector databases consolidated. Pinecone, Weaviate, and Qdrant are the surviving specialist options. Most teams embedding < 10 M vectors use pgvector and skip the specialist entirely.
- The NewSQL wave quieted. CockroachDB and YugabyteDB serve their niche (geo-distributed Postgres-compatible) but are not general replacements for Postgres.
The core decision matrix
| Workload |
Primary choice |
When to add a specialist |
| General web app |
Postgres |
Almost never |
| JSON-heavy, flexible schema |
Postgres (JSONB) |
MongoDB if schema truly unknown and changes frequently |
| Sub-ms cache / session store |
Redis |
Redis |
| Full-text search |
Postgres (tsvector) |
Elasticsearch / Typesense if >100 M docs |
| Embeddings / vector search |
pgvector |
Pinecone/Qdrant if >10 M vectors or >1k QPS |
| Time-series / metrics |
TimescaleDB (Postgres ext.) |
InfluxDB if you have a dedicated metrics team |
| Event streaming |
Kafka |
SQS/PubSub for simpler fan-out |
| Graph data |
Postgres (recursive CTEs) |
Neo4j if deeply recursive graph queries dominate |
| Mobile / embedded |
SQLite |
Turso (libSQL) for edge sync |
How to pick: the five questions
1. What is the shape of your data?
Rows with known columns → relational. Nested, variable-shape documents → document or JSONB. Pure key-value → Redis or DynamoDB. Streams of events → Kafka or a message broker.
2. What are your dominant query patterns?
Point lookups by primary key → almost any DB works. Complex joins, aggregations → relational wins. Vector similarity search → pgvector or specialist. Time-range queries → TimescaleDB. Graph traversal → Neo4j.
3. What is your expected data volume?
< 10 GB: any DB, Postgres is fine
10 GB – 1 TB: Postgres with good indexing and partitioning
1 TB – 10 TB: Postgres with read replicas; consider Citus for sharding
> 10 TB: evaluate data warehouse (BigQuery, Snowflake) for analytics;
keep hot OLTP on Postgres
4. What are your consistency requirements?
Strong consistency, multi-row transactions → relational (Postgres, MySQL). Eventual consistency acceptable → DynamoDB, Cassandra, CouchDB. Distributed strong consistency → CockroachDB (with added ops complexity).
5. What is your team's operational capacity?
No dedicated DBA → use a managed service (Supabase, Neon, RDS, Cloud SQL). Dedicated infrastructure team → self-hosted Postgres with pgBackRest and Patroni is cost-effective at scale.
Postgres first: a worked example
Before reaching for MongoDB for a "flexible schema," try this in Postgres:
-- Store structured fields + a JSONB blob for variable attributes
CREATE TABLE products (
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
category TEXT NOT NULL,
price_cents INT NOT NULL,
attributes JSONB DEFAULT '{}'
);
-- Index a JSONB key for fast lookups
CREATE INDEX idx_products_brand ON products ((attributes->>'brand'));
-- Query: find all red Nike products
SELECT name, price_cents
FROM products
WHERE category = 'shoes'
AND attributes->>'brand' = 'Nike'
AND attributes->>'color' = 'red';
This handles most "we need a document store" use cases. Add a GIN index on attributes for arbitrary key queries.
When Postgres is not enough
Redis: response time must be < 1 ms (session store, rate limiting, leaderboards). Redis keeps everything in memory; do not store data you cannot afford to lose unless you enable AOF persistence.
Kafka: you need durable, replayable event streams with multiple independent consumers. A job queue (BullMQ, Celery) is often enough; reach for Kafka when you need replay and consumer group isolation.
Pinecone / Qdrant: you have >10 M high-dimensional vectors (1536-dim OpenAI embeddings) and need <50 ms latency at >500 QPS. Below that threshold, pgvector with HNSW indexing is competitive:
-- pgvector HNSW index (introduced in pgvector 0.5, improved in 0.8)
CREATE INDEX ON embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
Common mistakes
Picking the database your last job used. Every workload is different. The database that worked for a social network may be wrong for a financial ledger.
Under-indexing. 90% of "our database is slow" problems are missing indexes, not wrong database choice. Profile before migrating.
Over-normalizing or over-denormalizing. A schema that requires 7-table JOINs for a common query is as bad as a flat denormalized table that duplicates 50 columns.
Not modeling writes. Teams optimize for read queries but ignore write patterns. A schema with heavy UPDATE contention on hot rows needs a different design, not a different database.
What to skip
- Exotic databases for standard CRUD apps — if your app is users, sessions, and records, Postgres is correct.
- Running multiple database types in a new project — start with one; add specialists only when a specific limit is hit.
- Self-hosting a database cluster without a DBA — a misconfigured primary with no replica is worse than paying for managed.
FAQ
Is MongoDB still relevant?
Yes, for genuinely document-oriented workloads with highly variable, deeply nested schemas at high write volume. But Postgres JSONB handles the majority of "we want a document store" cases without operational overhead.
Should I use DynamoDB for a new project?
Only if you are already deep in the AWS ecosystem and need key-value scale with single-digit millisecond latency at virtually unlimited throughput. The query model is restrictive; you pay for simplicity in scale with complexity in data modeling.
What database is best for AI applications?
Postgres with pgvector for most cases. Add Pinecone or Qdrant if you exceed pgvector's scale limits. See the vector search comparison below.
How do I migrate if I pick the wrong database?
Write an ETL that reads from the old store, transforms to the new schema, and writes to the new store. Run both in parallel, verify consistency, then cut over. Expect weeks to months of engineering time for production-scale data.
Where to go next