Architecting lean LLM caching: how to drop a 20M-row table without losing your AI memory

Short summary

A detailed engineering pattern for caching LLM enrichment outputs in a small orthogonal Postgres lookup table, enabling safe wipes of large working tables between data periods. A 20M-row pipeline with only 60k distinct LLM inputs shrinks cache from 16GB to a few MB. Covers SQL propagation, MVCC bloat mitigation via CTAS swap, and why Postgres beats Redis for this use case.

• •Separate LLM cache into a tiny lookup table keyed on input identifier to survive working-table wipes
• •20M rows with 60k distinct inputs yields a few-MB cache instead of 16GB
• •Use CTAS-and-swap instead of bulk UPDATE to avoid MVCC bloat on large datasets

Generated with AI, which can make mistakes.