Any team running an agentic pipeline on a periodically-reloaded dataset caches the output of inputs that repeat across periods to save cost. How you store that cache matters. Storing it in the same table as the data is safe, but makes you unable to wipe the data from the previous period before loading the new one. Keeping both on disk to use the cache can work, but if you're in a storage-constrained environment, you can't keep both on disk and have to find a way to remove the old one before loading the new one while still keeping the cache. The fix is to move the cache into a small, orthogonal lookup table keyed on a stable identifier, so it survives the wipe AND takes almost no disk.
This article walks through that pattern: the discovery that made it possible, the two-table shape, and the one operational gotcha you'll hit if you don't watch for it.
The discovery: your inputs are much smaller than your rows
The move to a lookup table starts with noticing something about your data.
A pipeline can process 20 million rows every period and only pass 60,000 distinct inputs to the LLM. The other 19.94 million rows are duplicates of those 60k values, spread across various dates, event types, geographies. Same key, different context.
Once you see that number, the design writes itself. You don't need to store 20 million enrichment results. You need to store 60,000. The 20 million rows can each reference one of those 60k answers.
Concretely:
-
Working table (
data): 20M rows every 2 weeks. ~16 GB. Gets wiped and reloaded when the next period's data arrives. -
Cache table (
enrichment_cache): 60k rows. Two columns, the input key (whatever you feed the LLM) and the enriched output. A few MB.
The compression ratio is doing the work here. A 16 GB table you can't afford to keep two copies of becomes a few-MB table you can afford to keep forever. That's what makes wiping the working table safe: the cache is the durable output of every past LLM call, and it's tiny.
The architecture: a four-step sequence
When the next data arrives, the pipeline runs this sequence:
- Wipe the previous period's data. The cache is untouched because it lives in a different table entirely, so nothing valuable is lost.
-
Load the new data. New rows go into the freshly-empty
datatable via whatever ingest mechanism you use. -
Propagate cache hits with SQL. One statement joins
dataagainstenrichment_cacheon the input key, copying every already-known enrichment onto the new rows. -
Call the LLM for cache misses only, and write to both places. Query
datafor rows still missing enrichment. Those are the genuinely-new inputs. Every LLM response gets written to the row indataAND appended toenrichment_cacheso the input never needs another LLM call.
The SQL for step 3 is the shortest and most important:
UPDATE data d
SET enrichment = c.enrichment
FROM enrichment_cache c
WHERE d.input_key = c.input_key
AND d.enrichment IS NULL;
While this UPDATE syntax works perfectly on smaller datasets, running a bulk in-place update across 20 million rows in production triggers a massive MVCC performance penalty. Postgres creates millions of dead tuples, causing table bloat and heavy disk I/O serialization. To bypass this bottleneck, we eventually migrated this step from an in-place UPDATE to a CTAS (Create Table As Select) and swap architecture during the initial ingestion phase. Part 3 of this series breaks down the mechanics of that zero-bloat swap pattern.
Engineering trade-offs and one gotcha
Why keep the cache in Postgres rather than an in-memory store like Redis?
Any relational database already gives you durable, transactional, indexed key-value storage for free. Adding a separate in-memory cache is another moving part in the deploy, another failure mode to reason about, and another operational surface (memory limits, eviction, replication). If the cache fits comfortably in a relational table and the lookup is an exact match on a stable key, keep infrastructure count low unless a hard constraint forces you to add pieces.
The indexing gotcha.
The cache table is small, so it's tempting to think the join will be trivially fast. It won't be, not at 20M rows on the working side. Without an index on enrichment_cache.input_key, the join has to hash the entire cache table for each batch. That's fine on 60k rows once, but Postgres's planner may still pick a plan that costs you more than you expect at scale, and the equivalent per-row lookup inside step 4 (SELECT ... WHERE input_key = %s) will be a sequential scan every time.
Fix once, forever:
CREATE UNIQUE INDEX enrichment_cache_key_idx ON enrichment_cache (input_key);
A B-tree index on the lookup column turns every cache read into a sub-millisecond index lookup and lets the planner use an index join for the bulk propagate in step 3. This is the one line that separates "cache works in principle" from "cache is fast enough that you notice the pipeline is done."
The point in one sentence
The cheapest LLM call is the one you don't make. The cheapest byte of disk is the one you don't store.
Move the durable, expensive knowledge (LLM answers) into a small, orthogonal lookup table. Let the big working table remain a disposable window on the current period. The wipe becomes safe, the cache stays warm, and the bill collapses.
