Database types and how to choose one

HOW IT FITS TOGETHER

KEY TERMS

Access-pattern-first design

Write the queries before the schema. Relational modelling tolerates unknown future queries; DynamoDB, Cassandra and Redis do not, because their keys are the only fast path. If you cannot list the patterns yet, that alone is an argument for a relational store.

Normalisation vs aggregates

Relational designs store each fact once and join at read time. Document and key-value designs duplicate data so one read returns the whole aggregate. Duplication is fast to read and expensive to keep consistent when the shared fact changes.

Partition key

In DynamoDB, Cassandra, Redis Cluster and sharded MongoDB, the partition or shard key decides which node owns a record. Queries that include it hit one partition; queries that do not fan out or need a secondary index. A low-cardinality or time-only key creates hot partitions.

OLTP vs OLAP

OLTP touches few rows per query with many concurrent writers, so row storage and B-trees win. OLAP scans many rows but few columns, so columnar storage, compression and vectorised execution win. Running heavy analytics on the OLTP primary steals its I/O and buffer cache.

Approximate nearest neighbour

Vector indexes such as HNSW and IVFFlat find close embeddings without comparing every row, at the cost of recall. In pgvector, a WHERE filter is applied after the index scan, so a selective filter can return fewer rows than LIMIT unless iterative scans (0.8.0+) are enabled.

Polyglot persistence tax

Every extra store adds backups, upgrades, monitoring, access control, a sync pipeline and a new way to be inconsistent. Postgres extensions (pgvector, TimescaleDB, full-text search, JSONB) often cover a pattern well enough to defer that tax.

Source of truth

Exactly one system owns each fact; caches, search indexes and analytics copies are derived from it and can be rebuilt. Writing to two stores from application code without a transaction or outbox guarantees they will eventually disagree.

IN YOUR STACK

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')),
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now()
);
-- Matches the access pattern: one customer, newest first.
CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC);

-- Latest 20 orders with item counts, keyset-paginated.
SELECT o.id, o.status, o.total_cents, o.created_at, count(i.id) AS items
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = $1
  AND o.created_at < $2          -- cursor: created_at of the last row seen
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 20;

import { MongoClient, ObjectId } from "mongodb";

type Order = {
  _id?: ObjectId;
  customerId: string;
  status: "pending" | "paid" | "shipped";
  totalCents: number;
  createdAt: Date;
  items: { sku: string; qty: number; priceCents: number }[]; // embedded: read with the order
};

const client = new MongoClient(process.env.MONGODB_URI!);
const orders = client.db("shop").collection<Order>("orders");
await orders.createIndex({ customerId: 1, createdAt: -1 });

export async function recentOrders(customerId: string) {
  return orders.find({ customerId }).sort({ createdAt: -1 }).limit(20).toArray();
}

export async function placeOrder(order: Omit<Order, "_id">) {
  return orders.insertOne(order); // single-document writes are atomic
}

import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, PutCommand, QueryCommand } from "@aws-sdk/lib-dynamodb";

const db = DynamoDBDocumentClient.from(new DynamoDBClient({}));
const Table = "shop";

// PK groups a customer's items in one partition; SK sorts them by type and time.
export async function placeOrder(customerId: string, orderId: string, totalCents: number) {
  const createdAt = new Date().toISOString();
  await db.send(new PutCommand({
    TableName: Table,
    Item: { PK: `CUSTOMER#${customerId}`, SK: `ORDER#${createdAt}#${orderId}`, orderId, totalCents, status: "paid" },
  }));
}

export async function recentOrders(customerId: string) {
  const { Items } = await db.send(new QueryCommand({
    TableName: Table,
    KeyConditionExpression: "PK = :pk AND begins_with(SK, :prefix)",
    ExpressionAttributeValues: { ":pk": `CUSTOMER#${customerId}`, ":prefix": "ORDER#" },
    ScanIndexForward: false, // newest first
    Limit: 20,
  }));
  return Items ?? [];
}

# Recent-orders index per customer: sorted set scored by epoch milliseconds.
ZADD customer:123:orders 1791710400000 o_789
# Keep only the newest 100 entries.
ZREMRANGEBYRANK customer:123:orders 0 -101
EXPIRE customer:123:orders 86400

# Order summary as a hash.
HSET order:o_789 status paid total_cents 4200 customer_id 123
EXPIRE order:o_789 86400

# Read path: newest 20 ids, then fetch each summary (pipeline these).
ZRANGE customer:123:orders 0 19 REV
HGETALL order:o_789

// The plain access pattern works, but is not why you would pick a graph.
MATCH (c:Customer {id: $customerId})-[:PLACED]->(o:Order)
RETURN o.id, o.status, o.totalCents, o.createdAt
ORDER BY o.createdAt DESC
LIMIT 20;

// Where graphs earn their keep: products bought by customers who bought what I bought.
MATCH (c:Customer {id: $customerId})-[:PLACED]->(:Order)-[:CONTAINS]->(p:Product)
      <-[:CONTAINS]-(:Order)<-[:PLACED]-(other:Customer)
      -[:PLACED]->(:Order)-[:CONTAINS]->(rec:Product)
WHERE other <> c
  AND NOT EXISTS { (c)-[:PLACED]->(:Order)-[:CONTAINS]->(rec) }
RETURN rec.sku, count(DISTINCT other) AS buyers
ORDER BY buyers DESC
LIMIT 10;

CREATE TABLE order_events
(
    order_id    String,
    customer_id UInt64,
    status      LowCardinality(String),
    total_cents Int64,
    created_at  DateTime64(3)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (customer_id, created_at);

-- Monthly revenue for one customer over a year...
SELECT toStartOfMonth(created_at) AS month, count() AS orders, sum(total_cents) / 100 AS revenue
FROM order_events
WHERE customer_id = 123 AND created_at >= now() - INTERVAL 12 MONTH
GROUP BY month ORDER BY month;

-- ...and the query it exists for: every customer, every order, in seconds.
SELECT status, quantile(0.95)(total_cents) AS p95_cents, count() AS n
FROM order_events GROUP BY status;

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE products ADD COLUMN embedding vector(1536);
CREATE INDEX products_embedding_hnsw ON products USING hnsw (embedding vector_cosine_ops);

-- Products semantically similar to the customer's last order, in stock only.
SET hnsw.iterative_scan = relaxed_order;  -- keep scanning when the filter discards rows
SELECT p.sku, p.name, p.embedding <=> $1 AS cosine_distance
FROM products p
WHERE p.in_stock
  AND p.id NOT IN (SELECT product_id FROM order_items WHERE order_id = $2)
ORDER BY p.embedding <=> $1
LIMIT 10;

WHEN TO REACH FOR WHAT

WHERE IT BITES

• Choosing a NoSQL store for scale you do not have, then rebuilding joins, constraints and transactions in application code.
• Designing a DynamoDB or Cassandra table before listing access patterns, then discovering the new screen needs a scan.
• Dual-writing to the database and a search index or cache from the request handler; a crash between the two leaves them permanently diverged.
• Using Redis as the only copy of durable data while it runs with async replication and default persistence settings.
• Running analytics queries on the OLTP primary until a dashboard refresh becomes the incident.
• Adding a dedicated vector database before checking whether pgvector, or plain full-text search, already meets recall and latency targets.

WHEN IT BREAKS IN PRODUCTION

• An endpoint gets slower as results grow
• The database spikes when a hot cache key expires
• The UI shows old data after a save
• The team wants microservices too early
• Requests hang waiting for a database connection

SOURCES

• Amazon DynamoDB: Best practices for using sort keys (opens in new tab)Checked 2026-10-11
• MongoDB: Data modeling (opens in new tab)Checked 2026-10-11
• ClickHouse: MergeTree table engine (opens in new tab)Checked 2026-10-11
• pgvector README (opens in new tab)Checked 2026-10-11
• Redis: Scale with Redis Cluster (opens in new tab)Checked 2026-10-11
• SQLite: Appropriate uses for SQLite (opens in new tab)Checked 2026-10-11