How to Choose a Database: A Workload-Based Selection Guide

Choose a database by its workload (the shape of the data, the queries you must answer, the consistency you need, the scale you will actually reach and who will operate it), not by popularity. For most new applications the right answer is PostgreSQL as the system of record, Redis or Valkey for caching and short-lived state, and object storage for files. Add a specialized store for search, columnar analytics, time series, graph or vector workloads only when a measured workload outgrows Postgres, and keep it explicit which store owns which data.

The rest of this guide is the reasoning behind that default, a decision matrix by workload, worked SQL for the cases where teams usually go wrong, and the production failures that tend to decide the matter.

Key takeaways

• Start from your top queries and writes, not from a product list. Write them down before comparing databases.
• PostgreSQL is the default system of record. Relational data, transactions, JSONB, full-text search, geospatial and vector search through extensions, in one system.
• Redis or Valkey is for speed, not truth. Caches, sessions, rate limits and counters, in front of a durable database.
• Specialize when a workload is measured, not imagined. Search, analytics, time series and vector stores earn their place when Postgres demonstrably struggles.
• More than one database means data synchronization. Use a transactional outbox or change data capture, never two independent writes.
• Test with your own data volume and queries, and restore a backup before you trust the choice.

Five questions before any benchmark

1. What shape is the data? Highly related entities, self-contained documents, key-value pairs, append-only events, time series, a graph?
2. What are the top queries and writes? List the ten most frequent and the ten most important, with expected rates. Relational databases forgive a list you got wrong. Key-value stores do not.
3. What consistency do you need? Money, inventory and permissions need transactions and strong consistency. A like counter or an activity feed can lag.
4. What scale will you actually reach? Plan for the next two years with real numbers from your product, not for a hypothetical hundredfold growth.
5. Who operates it? A managed service your team understands beats a theoretically better system that nobody knows how to back up, upgrade or fail over.

These answers matter more than any published benchmark, because benchmarks measure someone else's workload on someone else's schema.

The decision matrix by workload

PostgreSQL as the default

PostgreSQL is the default because it is the most forgiving choice when requirements change, and requirements always change. It enforces correctness in the schema, answers questions you did not plan for, and covers most specialized needs through extensions (PostGIS, pgvector, TimescaleDB, pg_trgm, Citus).

Use the schema to enforce what must never be wrong:

CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint      NOT NULL REFERENCES customers (id),
  status      text        NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'canceled')),
  total_cents bigint      NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX orders_customer_recent_idx ON orders (customer_id, created_at DESC);

JSONB, done correctly

JSONB lets one table hold flexible documents next to relational columns. It is also where a classic bug hides: ->> returns text, so comparing it with a quoted number compares strings, and '9' > '100' is true.

CREATE TABLE events (
  id         uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  type       text        NOT NULL,
  payload    jsonb       NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
 
SELECT id, payload
FROM events
WHERE type = 'purchase'
  AND payload @> '{"items": [{"category": "electronics"}]}'
  AND (payload->>'amount')::numeric > 100;

The GIN index with jsonb_path_ops serves the @> containment test. When a JSON field becomes something you filter or sort on regularly, promote it to a real column so it gets a type, constraints and an ordinary index:

ALTER TABLE events
  ADD COLUMN amount numeric GENERATED ALWAYS AS ((payload->>'amount')::numeric) STORED;
CREATE INDEX CONCURRENTLY events_amount_idx ON events (amount);

Adding a stored generated column rewrites the table, so on a large table schedule it like any heavy migration.

Postgres as a job queue

For moderate background work, a table and SKIP LOCKED replace a separate queue, with the benefit that enqueueing happens in the same transaction as the business write:

WITH next_job AS (
  SELECT id
  FROM jobs
  WHERE status = 'queued' AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
UPDATE jobs
SET status = 'running', started_at = now()
FROM next_job
WHERE jobs.id = next_job.id
RETURNING jobs.id, jobs.payload;

Where PostgreSQL strains

• Write throughput on one primary. Postgres scales reads with replicas; writes go to one primary. Partitioning, batching and Citus extend this a long way before distributed SQL is needed.
• Large analytical scans. Row storage reads whole rows. Heavy dashboards on the primary slow down the application.
• Multi-region writes. A single primary means one region takes the writes.
• Many short-lived connections. Each connection is a server process. Serverless functions without a pooler (PgBouncer or the provider's pooler) exhaust connections quickly.
• Update-heavy tables. Frequent updates create dead tuples; autovacuum needs tuning on hot tables.

None of these are reasons to avoid Postgres at the start. They are the signals that tell you which specialized store to add, and when.

Document databases: MongoDB

MongoDB fits when data is aggregate-oriented (read and written as a whole document), attributes vary widely between records, and the team thinks in documents. Schema-less does not mean schema-free: validate at the database.

db.createCollection("products", {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["sku", "name", "price"],
      properties: {
        sku: { bsonType: "string" },
        name: { bsonType: "string" },
        price: {
          bsonType: "object",
          required: ["amount", "currency"],
          properties: {
            amount: { bsonType: "decimal" },
            currency: { enum: ["USD", "EUR", "GBP", "INR"] },
          },
        },
        variants: { bsonType: "array", maxItems: 200 },
      },
    },
  },
});
 
db.products.createIndex({ sku: 1 }, { unique: true });

Model around the reads. Embed what is read together and bounded in size (variants of a product). Reference what grows without bound (reviews, orders, events) in its own collection, because a BSON document has a hard 16 MB limit and large documents are slow long before that. Multi-document transactions exist, but if your design depends on them everywhere, or $lookup appears in most queries, the data was relational all along.

DynamoDB and other key-value stores

DynamoDB rewards teams that know their access patterns up front. You design keys for the queries, not tables for the entities. A common single-table layout stores a customer's orders under one partition key with a sortable sort key:

aws dynamodb query \
  --table-name app \
  --key-condition-expression "PK = :pk AND begins_with(SK, :prefix)" \
  --expression-attribute-values '{":pk": {"S": "CUSTOMER#123"}, ":prefix": {"S": "ORDER#2026-"}}'

The trade-offs are real. Items are capped at 400 KB. There are no ad hoc queries or joins; a new access pattern means a new index and often a backfill. Scans are slow and costly. A partition key with low cardinality or skewed traffic creates hot partitions. In exchange you get predictable performance at very large scale with no servers to run. For AWS-specific scaling trade-offs, see scaling startups on AWS.

Redis and Valkey: speed, not truth

Redis is an in-memory store for data that must be fast and can be rebuilt: caches, sessions, rate limits, counters, leaderboards, locks. After Redis changed its license in 2024, the Linux Foundation started Valkey as an open-source fork; it is compatible for most uses and is offered as a managed service by several clouds.

Cache-aside is the common pattern. The production details are the key version, TTL jitter, and protection against many callers rebuilding the same hot key at once:

import Redis from "ioredis";
 
const redis = new Redis(process.env.REDIS_URL!);
const TTL_SECONDS = 300;
 
export async function getProduct(id: string): Promise<Product | null> {
  const key = `product:v2:${id}`;                   // bump the version when the shape changes
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);
 
  // one caller rebuilds a hot key; the others wait briefly and re-read
  const gotLock = await redis.set(`${key}:lock`, "1", "PX", 5000, "NX");
  if (!gotLock) {
    await new Promise((resolve) => setTimeout(resolve, 50));
    const retry = await redis.get(key);
    if (retry) return JSON.parse(retry);
  }
 
  const product = await db.product.findUnique({ where: { id } });
  if (product) {
    const jitter = Math.floor(Math.random() * 60);   // spread expiries so keys do not expire together
    await redis.set(key, JSON.stringify(product), "EX", TTL_SECONDS + jitter);
  }
  if (gotLock) await redis.del(`${key}:lock`);
  return product;
}

Two settings decide whether Redis behaves. Persistence (RDB snapshots, AOF) reduces data loss on restart but does not make Redis a database of record. The eviction policy must match the use: allkeys-lru suits a pure cache, while sessions, queues and locks need noeviction or a separate instance, or Redis will quietly delete them under memory pressure.

Analytics: columnar engines

Transactional databases store rows; analytical engines store columns. A query that sums one column over a large table reads only that column, compressed, with vectorized execution. That is why ClickHouse, DuckDB, BigQuery and Snowflake answer aggregations that would crush an OLTP primary.

CREATE TABLE events
(
    event_date  Date,
    event_time  DateTime64(3),
    tenant_id   UInt32,
    event_type  LowCardinality(String),
    user_id     UInt64,
    properties  String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_time);

In ClickHouse the ORDER BY key is the most important design decision: put the columns you filter on most first. Feed the analytical store from the transactional one with change data capture or scheduled loads, never with dual writes from the application. The surrounding pipeline is covered in the modern data engineering stack.

Search, time series, vector and graph

For vector search specifically, including index types and hybrid search, see our guide to vector databases and semantic search. For shallow graphs, a recursive CTE goes surprisingly far:

WITH RECURSIVE reach AS (
  SELECT followee_id AS user_id, 1 AS depth
  FROM follows
  WHERE follower_id = 42
  UNION
  SELECT f.followee_id, r.depth + 1
  FROM follows AS f
  JOIN reach AS r ON f.follower_id = r.user_id
  WHERE r.depth < 3
)
SELECT DISTINCT user_id FROM reach;

Distributed SQL

When one primary truly cannot take the writes, or the product needs low-latency writes in several regions, distributed SQL databases (CockroachDB, YugabyteDB, Google Spanner) keep the relational model while spreading data across nodes with consensus replication. You pay in write latency for consensus, in cost, and in schema design for data locality. Most teams that consider it early would be better served by indexing, partitioning, read replicas and caching on Postgres, which postpone the need for a long time.

Using more than one database without corrupting data

Writing to the database and then publishing an event from application code is a dual write. If the process dies between the two, the stores disagree and nothing notices. The transactional outbox fixes this by writing the event in the same transaction as the data:

BEGIN;
INSERT INTO products (id, name, description, category)
VALUES ($1, $2, $3, $4);
INSERT INTO outbox (aggregate_id, event_type, payload)
VALUES ($1, 'product.created', jsonb_build_object('id', $1, 'name', $2, 'category', $4));
COMMIT;

A relay then publishes outbox rows to the broker or search index. Change data capture tools such as Debezium can read the outbox table directly; a simple polling relay also works:

async function relayOutbox(): Promise<number> {
  return db.transaction(async (tx) => {
    const rows = await tx.query(
      `SELECT id, event_type, payload FROM outbox
       WHERE published_at IS NULL
       ORDER BY id
       LIMIT 100
       FOR UPDATE SKIP LOCKED`
    );
    for (const row of rows) {
      await broker.publish(row.event_type, row.payload, { messageId: String(row.id) });
    }
    await tx.query(`UPDATE outbox SET published_at = now() WHERE id = ANY($1)`, [rows.map((r) => r.id)]);
    return rows.length;
  });
}

Delivery is at least once: a crash after publishing and before the update republishes the batch. Consumers must be idempotent, keyed on the message ID. For multi-tenant systems, where this pattern meets tenant isolation, see SaaS architecture patterns.

Test with your workload before committing

A short, honest test beats any comparison article, including this one.

1. Load realistic volume. Generate or copy data at the size you expect in a year. Empty tables make every database look fast.
2. Replay your top queries. With PostgreSQL, a custom pgbench script exercises them under concurrency:

\set customer_id random(1, 1000000)
SELECT id, status, total_cents
FROM orders
WHERE customer_id = :customer_id
ORDER BY created_at DESC
LIMIT 20;

pgbench -n -c 32 -j 8 -T 300 -P 10 -f top_query.sql appdb

3. Read the plans. Run EXPLAIN (ANALYZE, BUFFERS) on every top query and fix sequential scans you did not expect.
4. Fail it over. Kill the primary in staging and time how long the application takes to recover.
5. Restore a backup. Time a full restore and a point-in-time recovery. A backup you have not restored is a hope, not a backup.

What goes wrong in production

• Missing indexes found at scale. Queries that were instant on ten thousand rows time out on ten million. Review plans for every new query path.
• N+1 queries from an ORM. One page load becomes hundreds of round trips. Log query counts per request.
• Connection exhaustion. Serverless functions or autoscaled workers open more connections than the database allows. Put a pooler in front.
• Migrations that lock tables. Building an index without CONCURRENTLY, or a table rewrite at peak traffic, blocks writes. Set lock_timeout in migrations and schedule rewrites.
• JSONB as a dumping ground. No constraints, inconsistent keys, and text comparisons where numbers were meant.
• Unbounded arrays in documents. Reviews embedded in a product grow until writes slow down and the document approaches the size limit.
• A new access pattern on DynamoDB. The query the product now needs has no key to serve it, and the fix is a new index plus a backfill.
• Redis treated as durable. A restart or an eviction policy meant for caches deletes sessions or queued jobs.
• A cache stampede after a deploy or flush. Every key misses at once and the database takes the full load. Jitter and rebuild locks prevent it.
• Analytics on the primary. One heavy dashboard query degrades the whole application. Move it to a replica or a columnar store.

Frequently Asked Questions

SQL or NoSQL: which should I choose?

Choose by access pattern, not by category. If your data has relationships, needs transactions across records, or will be queried in ways you cannot fully predict, a relational database such as PostgreSQL is the safer choice. NoSQL stores fit when access patterns are known and narrow, such as key lookups at very large scale in DynamoDB or whole-document reads in MongoDB, and they trade query flexibility for that fit.

Is PostgreSQL better than MongoDB?

Neither is better in general; they optimize for different shapes of work. PostgreSQL handles relational data, transactions, ad hoc queries and JSON documents in one system, which makes it the safer default when requirements are still moving. MongoDB fits aggregate-oriented data that is read and written as whole documents with varied attributes, especially for teams already fluent in document modeling.

When should I use Redis?

Use Redis, or its open-source fork Valkey, for data you can afford to lose or rebuild and need very fast: caches, sessions, rate limits, counters, leaderboards, short-lived locks and lightweight pub/sub. Keep the system of record in a durable database and treat Redis as a fast layer in front of it. Configure the eviction policy deliberately, because the right setting for a cache is wrong for sessions or queues.

What database should a startup use?

For most startups, a managed PostgreSQL instance is the right first database, with Redis or Valkey added for caching when it is needed and object storage for files. This covers relational data, JSON, full-text search, geospatial queries and vector search through extensions, with one system to operate. Add specialized databases later, when a measured workload outgrows Postgres.

Can PostgreSQL replace a document database?

For many applications, yes. JSONB columns store and index documents, support containment queries, and can live alongside relational columns that carry constraints and joins. A dedicated document database still makes sense when nearly all data is document-shaped, documents are large and highly variable, or the team is built around document modeling.

When do you need a separate analytics database?

When analytical queries start scanning large portions of your data and competing with transactional traffic on the primary, or when dashboards become too slow even with rollup tables. Columnar engines such as ClickHouse, DuckDB, BigQuery or Snowflake read only the columns a query needs and compress well, which makes large aggregations far cheaper. Feed them from the transactional database through change data capture or batch loads.

Need a second opinion on your data layer?

We pick databases the way we pick any dependency we will be on call for: boring where possible, specialized where a measured workload earns it, and always with a restore we have actually run. If you are choosing a data layer, or living with one that no longer fits, talk to the engineers who would do the work.

• Software Development practice
• Cloud Architecture practice
• Talk to an engineer