
Picture two teams asking the same question. A five-person startup is building a SaaS app. A 500-person retailer is building a real-time analytics platform. Both ask, "Which database should we use?" The right answers differ, because the workload differs. This guide is organized around workloads, not trends.
Short answer: start with PostgreSQL, and add a specialized database only when a measured requirement on your own data says you must. Every extra system brings sync pipelines, monitoring, backups and new failure modes.
Decision flowchart
Start from your dominant workload and follow the branch. The cheat sheet near the end gives the same guidance as a table.
flowchart LR
Start["Dominant workload"] --> W0["Tiny, local or single-process"]
Start --> W1["Application transactions"]
Start --> W2["Reports and aggregations"]
Start --> W3["Text search"]
Start --> W4["Time-stamped metrics"]
Start --> W5["Embeddings"]
Start --> W6["Cache or sessions"]
Start --> W7["Relationship traversal"]
W0 --> R0["SQLite, or DuckDB if analytical"]
W1 --> Q1{"Multi-region writes, or writes beyond one node?"}
Q1 -- Yes --> R1a["Distributed SQL"]
Q1 -- No --> Q1b{"Already on MySQL?"}
Q1b -- Yes --> R1b["MySQL 8.4 or 9.7 LTS"]
Q1b -- No --> R1c["PostgreSQL"]
W2 --> Q2{"Scale and concurrency?"}
Q2 -- "One machine" --> R2a["DuckDB"]
Q2 -- "Real-time, many users" --> R2b["ClickHouse"]
Q2 -- "Org-wide, governed" --> R2c["Snowflake, BigQuery or Databricks"]
W3 --> Q3{"Need facets, typo tolerance or independent scale?"}
Q3 -- No --> R3a["Postgres full-text or a BM25 extension"]
Q3 -- Yes --> R3b["Elasticsearch or OpenSearch"]
W4 --> Q4{"Heavy ingest?"}
Q4 -- No --> R4a["Postgres or TimescaleDB"]
Q4 -- Yes --> R4b["InfluxDB 3, QuestDB or ClickHouse"]
W5 --> Q5{"Independent scale, or targets unmet after tuning?"}
Q5 -- No --> R5a["pgvector"]
Q5 -- Yes --> R5b["Dedicated vector database"]
W6 --> R6["Redis or Valkey, or DynamoDB for managed key-value"]
W7 --> R7["SQL recursive queries, Apache AGE or Neo4j"]By company stage (opinion)
These are my rules of thumb, not measured facts.
Stage | Suggested shape |
|---|---|
1 to 5 engineers | One managed Postgres (or SQLite for single-node apps). Rent anything you would otherwise operate yourself. |
5 to 50 engineers | Postgres plus a read replica. Add analytics (DuckDB or a warehouse) via CDC, and try search and vector extensions before new systems. |
50+ engineers, several teams | Multiple engines are normal. A platform team standardizes a short list, and each workload picks from it. |
OLTP (transactional apps)
Option | Pick it when | Watch for |
|---|---|---|
PostgreSQL 19 | Default for relational data, transactions, JSON and extensions | 19 was released November 19, 2026; version 14 reached end of life on November 12, 2026 |
MySQL | You already run MySQL or its ecosystem | 8.0 reached end of life in April 2026. Supported lines are 8.4 LTS and 9.7 LTS; other 9.x innovation releases are short-lived |
SQLite | Embedded, edge, single-node or small apps | Its own guidance: under roughly 100K hits/day is fine (a conservative estimate), but use client/server for many networked clients or write-heavy sites |
Distributed SQL (Spanner, CockroachDB, YugabyteDB, TiDB, Aurora DSQL) | Multi-region active-active writes or write scale beyond one node | Cost, complexity and compatibility gaps. Aurora DSQL went GA in May 2025. Self-hosted CockroachDB moved to a single Enterprise license in November 2024 (free under $10M revenue, source-available rather than open source) |
MongoDB 9.0 (released September 29, 2026) | Document-shaped data and existing Mongo skills | SSPL licensing for the server and its search component |
Hard limits rarely decide this. Postgres documents unlimited database size and a 32 TB limit per table (per partition for partitioned tables), and practical limits such as performance and disk space usually bite first.
The case against defaulting to Postgres: your team is already expert in another engine; you want zero-ops key-based access at scale (DynamoDB-style); you need multi-region active-active writes; or your platform standardizes on a vendor stack. Those are good reasons. "Postgres is popular" is not a reason either way.
Key-value, caching and graph
Redis and Valkey are the usual choices for caches, sessions, rate limits and queues. Redis 8 (May 2025) is available under a tri-license of RSALv2, SSPLv1 and AGPLv3; versions up to 7.2 were BSD. Valkey is the Linux Foundation fork that continues from Redis 7.2.4, backed by major cloud providers. Both can persist to disk, but most teams keep the system of record in their primary database (opinion).
DynamoDB is AWS's managed key-value and document store. It suits predictable key-based access with little operations work, but you design around your access patterns rather than ad hoc queries.
Graph: for a few relationship queries, start with SQL recursive queries. Apache AGE (Apache 2.0) adds openCypher graph queries inside Postgres. Choose a native graph database such as Neo4j when traversal is the core workload. GQL (ISO/IEC 39075:2024) is the first international graph query language standard. Native SQL/PGQ property-graph queries were committed to Postgres 19 during development but reverted before release, with a later retry expected.
Analytics
DuckDB 2.0 (an LTS release on October 21, 2026, supported until at least October 2027) runs in-process and excels at Parquet/CSV files, notebooks and pipelines on one machine. It now also has a client-server mode (the Quack protocol; by default it listens on localhost, with a TLS reverse proxy recommended for remote access). Files created by 2.0 cannot be read by older versions, so test before switching production.
ClickHouse suits large event, log and metrics data that needs fast queries under high concurrency. You can self-host it or use ClickHouse Cloud.
Cloud warehouses and lakehouses (Snowflake, BigQuery, Databricks) fit organization-wide analytics with many teams, governance and shared compute.
Postgres itself is fine for modest reporting, ideally on a read replica.
Rule: do not run heavy analytics on your OLTP primary. Replicate with change data capture (CDC) instead.
A typical shape keeps transactions in Postgres and sends analytics elsewhere:
flowchart LR
App["Application"] --> Primary
subgraph PG["PostgreSQL"]
Primary[("Primary: transactions, JSON, extensions")]
Replica[("Read replica")]
Primary -- "streaming replication" --> Replica
end
Primary -- "CDC" --> Analytics["Analytics engine: DuckDB, ClickHouse or warehouse"]
Replica --> Reports["Light reporting"]
Analytics --> Dash["Dashboards and heavy queries"]Convergence trend: analytics vendors now sell Postgres. Snowflake Postgres became generally available on February 24, 2026. Databricks Lakebase reached general availability on AWS (January 22, 2026), Azure and Google Cloud. ClickHouse's managed Postgres was in public beta as of July 2026. Keeping Postgres for transactions and attaching an analytics engine is increasingly a supported, low-glue path.
Search
Think of it as a ladder:
Postgres full-text search (
tsvector, GIN indexes) is good for basic search. Itsts_rankfunction is not BM25, so ranking quality degrades as corpora grow.BM25 extensions for Postgres, such as ParadeDB's
pg_searchand Tiger Data'spg_textsearch(version 1.0 shipped in March 2026), add proper relevance ranking inside Postgres.Elasticsearch or OpenSearch are the dedicated engines. Elastic announced an AGPLv3 license option (alongside SSPL and Elastic License 2.0) in August 2024; OpenSearch is Apache 2.0 and moved to the Linux Foundation in September 2024. Dedicated engines typically still lead in analyzers, typo tolerance and relevance tooling.
MongoDB Search and Vector Search became generally available for Community Edition on June 30, 2026, if your data already lives in Mongo.
Move up the ladder when relevance tuning, faceting, typo tolerance, or search load that must scale independently of your main database becomes a real requirement.
Time series
Postgres + TimescaleDB (the company is now called Tiger Data) keeps relational and time-series data together in SQL, with partitioning, compression and continuous aggregates.
InfluxDB 3 is a ground-up rewrite on Apache Arrow and Parquet, queried with SQL and InfluxQL. Moving from 1.x or 2.x is a migration, not an upgrade.
QuestDB or ClickHouse are worth testing for very high ingest or heavy analytical queries.
Vendor benchmarks in this category disagree by large margins, so run your own with your real cardinality, retention and query mix. For moderate volumes, partitioned Postgres is often enough.
Vectors (embeddings and RAG)
pgvector adds a vector type to Postgres. Search is exact by default, with approximate HNSW and IVFFlat indexes available. It also supports half-precision, binary and sparse vectors. Since version 0.8.0, iterative index scans address filtered queries that return too few rows. HNSW queries faster than IVFFlat but builds slower and uses more memory.
Dedicated vector databases (Qdrant, Milvus, Weaviate, Pinecone) make sense when vector search needs to scale or be isolated independently from your transactional workload, or when recall and latency targets are still unmet after tuning.
Document and search engines (MongoDB, Elasticsearch, OpenSearch) also offer vector search, which helps when your data already lives there.
Hybrid search (keyword plus vector) is common in RAG apps, so favor a system that does both next to your data.
Managed or self-hosted? (opinion)
Managed services cost more per unit but save engineer time, so they usually win for small teams. Self-host when you have operations capacity, data-residency or compliance needs, or a scale where the savings are real. To limit lock-in, favor engines with many managed providers: Postgres-compatible services now come from cloud vendors and from Snowflake, Databricks and ClickHouse, among others. Check licenses (AGPL, SSPL, source-available) before you embed or resell an engine.
Escalation ladders
For search, vectors and time series, the pattern is the same: stay in Postgres as long as measurements allow, then move right.
flowchart TB
subgraph S["Search"]
direction LR
S1["Postgres full-text"] --> S2["BM25 extension"] --> S3["Elasticsearch or OpenSearch"]
end
subgraph V["Vectors"]
direction LR
V1["pgvector exact search"] --> V2["pgvector HNSW or IVFFlat index"] --> V3["Dedicated vector database"]
end
subgraph T["Time series"]
direction LR
T1["Partitioned Postgres"] --> T2["TimescaleDB"] --> T3["InfluxDB 3, QuestDB or ClickHouse"]
end
S ~~~ V
V ~~~ TCheat sheet
Workload | Default | Switch when |
|---|---|---|
OLTP | PostgreSQL | Multi-region writes need distributed SQL; embedded use needs SQLite; an existing MySQL fleet stays on 8.4 or 9.7 LTS |
Analytics | DuckDB (one node) or a warehouse | High-concurrency real-time data needs ClickHouse; organization-wide analytics needs a warehouse |
Search | Postgres full-text, then a BM25 extension | Relevance tooling, faceting or independent scaling needs Elasticsearch or OpenSearch |
Time series | Postgres or TimescaleDB | Very high ingest needs InfluxDB 3, QuestDB or ClickHouse |
Vectors | pgvector | Independent scale or isolation needs a dedicated vector database |
Cache / key-value | Redis or Valkey | Managed key-value at scale may favor DynamoDB |
Graph | SQL recursive queries, then Apache AGE | Traversal-heavy workloads need a native graph database |
Mistakes to avoid
Choosing from vendor-run benchmarks. Test on your own data.
Adding a database per buzzword. Each one costs you a sync pipeline and an on-call surface.
Analytics on the primary. Use a replica or CDC.
Ignoring lifecycle and licenses. MySQL 8.0 and Postgres 14 are out of support, and licenses differ.
Treating all releases alike. MySQL innovation releases and non-LTS DuckDB lines are supported only briefly; pick LTS lines for production.
Treating a cache as the system of record without a plan for loss or rebuild.
Validate in a day
Load a representative sample of your data, replay your ten most important queries plus a realistic write load, and record p95/p99 latency, cost and operational effort. If the default meets your targets, stop there.
FAQ
Is Postgres really enough for most apps? For many applications, yes. It documents no limit on database size, and practical limits come from performance and hardware. Add a specialized engine when a measured requirement demands it.
Do I need a separate vector database? Usually not at the start. Try pgvector first, and move when vector search must scale independently or recall and latency targets remain unmet after tuning.
MySQL or Postgres for a new project? With no existing MySQL investment, Postgres is the usual default. If you run MySQL, stay on a supported line: 8.4 LTS or 9.7 LTS.
Can DuckDB replace a data warehouse? For single-machine analytics on files, often yes. For many concurrent users, governance and shared data, a warehouse or ClickHouse still fits better. DuckDB 2.0's server mode narrows the gap, so test your case.
Verdict
Default to PostgreSQL, keep analytics, search and vector workloads close to it until measurements say otherwise, and choose specialized engines for the workloads that outgrow it. The 2026 trend helps you: Postgres keeps absorbing capabilities, and analytics vendors now integrate with it rather than replace it.
Sources: PostgreSQL 19 release announcement; PostgreSQL limits; SQLite appropriate uses; DuckDB 2.0.0 announcement; Snowflake Postgres GA notice; Databricks Lakebase release notes; ClickHouse Cloud changelog; MySQL 8.0 release notes; Cockroach Labs license announcement; Amazon Aurora DSQL GA; Redis licensing; Elastic's AGPL announcement; Linux Foundation on OpenSearch; MongoDB Search GA for Community Edition; pgvector 0.8.0 announcement.
Comments(0)
Join the discussion by logging into your account.
No comments yet. Be the first to comment!