Store & query transaction history for hundreds of millions of accounts

Question

IT Interview Practice · Accepted Answer

**Partition the ledger by account and time, keep recent data hot and old data cold, and treat rows as an append-only immutable ledger — so a single account's history is a small, indexed slice, never a scan of billions of rows.** The access pattern is always "one account, a date range", so partition to match it.

## Architecture

```text
Write ─▶ Ledger table (append-only, immutable)
          partition by (account_hash, month)
              │
     ┌────────┴─────────┐
  HOT tier            COLD tier
  last 3 mo           older
  Postgres/SSD        S3/Parquet, Glacier
     │                    │
  index-seek        keyset paginate ──▶ API (account+range)
```

## How it works

- **Partition by account + time.** Hash the account id into N buckets, then range-partition by month. A query for one account/month hits exactly one partition — the DB prunes the rest. This bounds every read regardless of total size.
- **Immutable ledger.** Never `UPDATE` a transaction; corrections are new reversing rows. This gives an audit trail, safe replication, and lets you cache aggressively (rows never change).
- **Hot/cold tiering.** Keep the last ~3 months on fast SSD/Postgres for low-latency reads; roll older partitions to **S3/Parquet** (queried via Athena/Trino) or Glacier. 95% of queries hit recent data, so most storage can be cheap.
- **Keyset pagination.** Page with `WHERE (ts, id) < (:last_ts, :last_id) ORDER BY ts DESC LIMIT 50` — not `OFFSET`. Offset scans and discards skipped rows; keyset seeks the index directly, O(1) per page.

## Interview perspective

The interviewer probes **partition-key choice** and **pagination**. Good answers say "partition by account"; great answers explain **why keyset beats OFFSET** at deep pages, why the **ledger is immutable** (auditability, no lock contention), and how **tiering** cuts cost. A trap is a single giant table with a composite index — it works until the index no longer fits in RAM and every read becomes disk-bound.

## Trade-offs & failure modes

- **Partition key is hard to change.** Pick account+time up front; re-partitioning 100s of millions of rows later is a migration project.
- **Cross-account queries suffer.** "Total volume across all accounts" now fans out across partitions — push those to an OLAP rollup instead.
- **Cold reads are slow.** Fetching a 3-year-old statement from Glacier takes minutes; set user expectations or pre-warm on request.
- **Hot partitions.** A whale account or the current month can be a hotspot; sub-partition or cache it.
- **When not to:** millions of rows (not hundreds of millions) fit in one indexed Postgres table — don't build tiering you won't need. Reach for this at the scale where a single table's index stops fitting in memory.

Architecture

How it works

Interview perspective

Trade-offs & failure modes

Related questions