Postgres

If you do not have a reason to pick something else, pick Postgres. It has transactions, a serious planner, constraints, and enough extension points that you can defer the second database until the design forces it.

What you use it for

The source of truth for entities that change and that must agree with each other: users, orders, tasks, ledgers. A transaction is your concurrency story. Isolation defaults to read committed. When two workers might claim one row, take a row lock explicitly.

WITH next AS (
  SELECT id FROM tasks
  WHERE status = 'open'
  ORDER BY priority DESC, created_at
  FOR UPDATE SKIP LOCKED
  LIMIT 1
)
UPDATE tasks t
SET status = 'claimed', lease_until = now() + interval '15 minutes'
FROM next
WHERE t.id = next.id
RETURNING t.*;

Pair that with CREATE INDEX ON tasks (priority DESC, created_at) WHERE status = 'open'. The index stays small. Closed tasks do not bloat it.

Indexes and plans

B-tree by default. GIN for jsonb containment, arrays, and full text. GiST for ranges and geo. Say “I would EXPLAIN the hot query” and mention what you are looking for: a sequential scan on a big table, a row estimate wildly off from reality, a sort that spills to disk.

INSERT … ON CONFLICT is your idempotent write. Unique constraints are not optional decoration. They are how you make a retry safe.

Scale inside one cluster

Read replicas for read-heavy traffic that tolerates lag. Connection pooling in front, because Postgres wants a modest number of real connections and your app will open thousands. Partition by time for append-only tables so retention is dropping a partition.

Writes still go to one primary. When that primary’s IOPS or CPU is the ceiling, shard by a key you can name, or move an append-only stream to a log. Do not run a distributed lock manager inside Postgres for fun. SKIP LOCKED and advisory locks cover a surprising number of interview problems.

What it should not be

It should not store video. It should not be the only copy of a search corpus you cannot rebuild, unless you accept that search features will stay modest (tsvector is genuinely useful at moderate size). It should not serve a million-QPS key lookup that is really a cache. Put Redis in front and keep Postgres correct.