scale(tracks): plan dedicated infra for the global enriched-track catalog (replica/cache → separate DB → enrichment service)

[thewrz]

Why

The global enriched-track table introduced by #540 (epic #539) is a shared, append-mostly catalog: every unique track ever requested across every event (and, per the design intent, shared so all DJs benefit without re-enriching) accumulates a row, forever. That's the point — but it means this one table grows monotonically and could eventually become the largest, hottest table in the system, with a read/write profile very different from the transactional request/event tables. This is a forward-looking tracking issue to decide when and how to give it dedicated infrastructure, before it's a problem.

Reality check — 1M rows is small for Postgres (don't over-engineer early)

Rough sizing for the #540 row shape (~0.4–0.5 KB/row incl. provenance + heap overhead):

Rows	Heap	+ Indexes (PK, ISRC, sig)	Total on disk
1M	~0.5 GB	~0.15 GB	< 1 GB
10M	~5 GB	~1.5 GB	~6–7 GB
100M	~50 GB	~15 GB	~60–70 GB

• Lookups stay fast and flat. Resolution is an indexed B-tree point lookup on ISRC or dedupe_sig → O(log n), sub-millisecond when cached, a few ms cold. This does not degrade meaningfully from 1M → 100M rows. Postgres routinely runs tables in the hundreds of millions / billions of rows.
• RAM is not "load the whole table". Postgres caches hot pages (shared_buffers + OS page cache). At < 1 GB total, the entire table + indexes fit in RAM on a modest VPS (4–8 GB), so it's effectively fully cached. Even at 10M rows it's a few GB.
• So at 1M rows: a non-event. The honest answer to "how slow / how much RAM at a million songs" is: ~sub-ms indexed lookups and well under a GB — comfortably handled by the current single Postgres instance.

The real inflection points (what actually triggers a split — not raw row count)

1. Workload contention, not size: when the catalog's read/write/vacuum/IO load starts contending with the primary app DB's transactional path (connection saturation, lock/vacuum pressure on the request hot path).
2. Fuzzy matching cost: signature/normalized-title matching at scale wants pg_trgm + GIN indexes (or a dedicated search engine). That's an indexing decision well before it's an infra decision.
3. Multi-tenant / cross-instance sharing: the design implies a catalog shared so "all DJs/instances benefit." A genuinely shared catalog across deployments is the strongest case for extracting it into a dedicated enrichment service (owns the table, exposes an API, independently scalable) rather than a per-instance table.
4. Read scaling: a read replica and/or a cache (Redis or in-process LRU) in front of resolution lookups buys a lot before sharding is ever needed.

Decisions to make (later — this issue tracks them)

• Define concrete trigger metrics (table size, p99 resolution latency, DB CPU/IO, connection pool saturation) that move us from "single shared Postgres" → "read replica + cache" → "separate DB instance" → "dedicated enrichment microservice on its own infra".
• Decide whether the catalog is per-instance or a shared cross-instance service (drives everything else).
• Pick the cheap wins first: pg_trgm/GIN for fuzzy match, a resolution cache, sane vacuum/autoanalyze tuning, partitioning only if/when justified.
• Capacity plan: target VPS/infra at the 10M and 100M row marks.

Related

Tracks the long-term scaling of the table from #540 / epic #539. Not a blocker for shipping the epic. Phase: v3 (Scale).

---

🤖 Filed at maintainer request after the data-contract epic design (Claude Opus 4.8).