Choosing a Datastore
What each datastore family is genuinely better at, and why the choice follows from the access patterns rather than from familiarity.
QueryCommand({ TableName, KeyConditionExpression, ExpressionAttributeValues, ScanIndexForward, Limit })There is no general-purpose best datastore; there are stores that make certain access patterns cheap by making others expensive. The choice is a consequence of how the data will be read, not of how it will be drawn.
The families, and what each is actually good at
- Relational — unknown future queries, joins, multi-row transactions, constraints enforced by the engine. The default until something specific rules it out.
- Document — one aggregate read and written whole, with a shape that varies per record.
- Key-value — lookups by a key you always have, at very low latency; Redis adds data structures and expiry.
- Wide-column — enormous write volume and range reads inside a partition; Bigtable and Cassandra store rows sorted by key, so the sort order is chosen at design time.
- Graph — traversals of unbounded depth, where SQL would need a recursive join per hop.
- Search — ranked relevance over text, as a derived index rather than a source of truth.
- Time-series — append-only measurements, queried as downsampled ranges, with old data expiring.
Resisting familiarity
Familiarity is a real engineering input: an operable store beats an optimal one. The failure is not choosing what you know, it is choosing it before writing down the access patterns, then discovering the main query cannot be served. Write the queries first, including the ones with a sort and a filter, and check each has an index or a key that answers it.
Example
import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, QueryCommand } from "@aws-sdk/lib-dynamodb";
// DocumentClient is the shorthand layer: it takes and returns plain JS values.
// The longhand client wants typed attributes, { S: "CUSTOMER#7" } instead of
// "CUSTOMER#7". Same service, same API shape, less ceremony.
const ddb = DynamoDBDocumentClient.from(new DynamoDBClient({}));
// The access pattern was decided FIRST: "the 20 most recent orders for one
// customer". The key schema exists to serve it:
// pk = CUSTOMER#<id> partition key
// sk = ORDER#<iso time> sort key, so rows are stored in time order
export async function recentOrders(customerId) {
const out = await ddb.send(
new QueryCommand({
TableName: "app",
KeyConditionExpression: "pk = :pk AND begins_with(sk, :prefix)",
ExpressionAttributeValues: {
":pk": `CUSTOMER#${customerId}`,
":prefix": "ORDER#",
},
ScanIndexForward: false, // descending sort key: newest first
Limit: 20,
})
);
return out.Items;
}
// What this schema cannot answer: "all orders over $500 last week, any
// customer". That needs its own global secondary index, or it becomes a Scan.
// Items are capped at 400 KB, so the order body cannot be unbounded either.When to use it
- Deciding whether a new feature belongs in the existing Postgres or needs its own store.
- Writing down access patterns before designing a DynamoDB key schema in an interview.
- Explaining why a feed query is cheap in Cassandra and expensive in a relational table.
- Choosing where session, counter and leaderboard data should live when it is all currently in one database.
More examples
The query a key-value store cannot do for you
Reach for relational whenever the questions are not all known yet, which is most products. In a document or key-value store this query becomes several round trips plus aggregation in application memory, and each new question is a new denormalised copy of the data to maintain.
-- Four tables, one answer, no application loop, and the engine chooses the
-- plan. This is what you give up when you leave the relational model.
SELECT c.country,
date_trunc('week', o.placed_at) AS week,
COUNT(DISTINCT o.id) AS orders,
SUM(oi.quantity * p.price) AS revenue
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
WHERE o.placed_at >= now() - interval '90 days'
AND p.category = 'audio'
GROUP BY c.country, week
HAVING SUM(oi.quantity * p.price) > 10000
ORDER BY revenue DESC;Wide-column: the key is the query plan
Use this when writes are relentless and reads are always "the latest N within one partition": chat history, event logs, per-device telemetry. The compensation for huge write throughput is that an unplanned query is refused rather than merely slow, which is arguably friendlier than DynamoDB quietly scanning.
-- Cassandra. The primary key is a design decision about reads, not an id.
CREATE TABLE messages_by_room (
room_id uuid,
bucket date, -- partition bucket: keeps one partition bounded
sent_at timeuuid,
sender_id uuid,
body text,
PRIMARY KEY ((room_id, bucket), sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);
-- Served straight off disk in stored order, no sort step:
SELECT * FROM messages_by_room
WHERE room_id = ? AND bucket = ? LIMIT 50;
-- Rejected outright rather than being slow - no partition key given:
-- SELECT * FROM messages_by_room WHERE sender_id = ?;
-- InvalidRequest: ALLOW FILTERING requiredKey-value with structure: a leaderboard in Redis
Good for anything that is a ranking, a recent-items list or a counter read far more often than it is reconciled. Redis 6.2 folded the REV, BYSCORE, BYLEX and LIMIT options into ZRANGE and deprecated the five commands it now replaces — ZREVRANGE, ZRANGEBYSCORE, ZREVRANGEBYSCORE, ZRANGEBYLEX and ZREVRANGEBYLEX; both forms still work, so you will meet each in existing code.
# A sorted set keeps members ordered by score, so the read is O(log N + M)
# with no sort and no index to maintain.
ZADD leaderboard:2026-10 1850 user:42
ZINCRBY leaderboard:2026-10 50 user:42 # increment in place
# Top 10, highest first. Since Redis 6.2 this is spelled with ZRANGE:
ZRANGE leaderboard:2026-10 +inf -inf BYSCORE REV LIMIT 0 10 WITHSCORES
# The older spelling of exactly the same query, deprecated in 6.2:
ZREVRANGEBYSCORE leaderboard:2026-10 +inf -inf LIMIT 0 10 WITHSCORES
ZREVRANK leaderboard:2026-10 user:42 # one member's rank
EXPIRE leaderboard:2026-10 5184000 # the month's board, 60 days
Discussion