Two-table LLM caching pattern cuts 20M-row storage to a few MB in AI pipelines

Engineering teams running agentic AI pipelines on large, periodically-refreshed datasets face a dilemma: wiping old data risks destroying costly LLM-generated cache results stored in the same table. A proposed solution separates the cache into a small, standalone lookup table keyed on a stable input identifier, keeping it intact when the main data table is wiped and reloaded. This works because a 20-million-row working table may contain only around 60,000 distinct LLM inputs, meaning enrichment results can be stored once and referenced by all duplicate rows. During each new data load cycle, a SQL join propagates cached results to matching rows, and the LLM is called only for genuinely new inputs. The approach avoids large in-place UPDATE penalties in PostgreSQL by eventually using a Create Table As Select and swap pattern to prevent table bloat.