Databases

Choosing a Database in 2026: A Decision Guide by Workload

When Postgres is enough, and when OLTP, analytics, search, time-series, vector or graph needs justify a specialized database.

·
8 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

Choosing a Database in 2026: A Decision Guide by Workload

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.

Think of it as a ladder:

  1. Postgres full-text search (tsvector, GIN indexes) is good for basic search. Its ts_rank function is not BM25, so ranking quality degrades as corpora grow.

  2. BM25 extensions for Postgres, such as ParadeDB's pg_search and Tiger Data's pg_textsearch (version 1.0 shipped in March 2026), add proper relevance ranking inside Postgres.

  3. 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.

  4. 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 ~~~ T

Cheat 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

  1. Choosing from vendor-run benchmarks. Test on your own data.

  2. Adding a database per buzzword. Each one costs you a sync pipeline and an on-call surface.

  3. Analytics on the primary. Use a replica or CDC.

  4. Ignoring lifecycle and licenses. MySQL 8.0 and Postgres 14 are out of support, and licenses differ.

  5. Treating all releases alike. MySQL innovation releases and non-LTS DuckDB lines are supported only briefly; pick LTS lines for production.

  6. 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!

Tomson Alex

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Tomson Alex's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.