Entity-attribute-value (EAV) schemas are a common escape hatch when your data model needs to stay flexible — new fields shouldn't require a migration. They're also a common source of write-path pain once volume grows, and that's exactly what happened here.
The shape of the problem
The schema followed a familiar EAV chain: enrichments → keys → values → anchors. Every time a document was processed, we'd upsert each extracted field individually — one row, one query, repeated for every key on the document.
That's fine at low volume. At higher throughput, it turned into serialized writes fighting over the same rows, and the lock contention showed up as latency spikes exactly when the system was under the most load.
Batching instead of per-row writes
The fix wasn't a schema rewrite — it was changing how we wrote to the existing schema.
-- Before: one upsert per key, in a loop
INSERT INTO values (key_id, anchor_id, value)
VALUES ($1, $2, $3)
ON CONFLICT (key_id, anchor_id) DO UPDATE SET value = EXCLUDED.value;
-- After: one batched upsert per document
INSERT INTO values (key_id, anchor_id, value)
SELECT * FROM UNNEST($1::int[], $2::int[], $3::text[])
ON CONFLICT (key_id, anchor_id) DO UPDATE SET value = EXCLUDED.value;
Converting the per-row loop into a single batched statement using UNNEST cut the number of round trips per document from dozens down to one, and — more importantly — collapsed what used to be many small transactions into one, which removed the lock contention entirely.
Why this mattered beyond performance
The batching also made the write path easier to reason about operationally: one transaction per document means one clear failure mode, one retry boundary, and much simpler observability. What used to be "which of these 40 writes failed?" became "did this document's write succeed or not?"
Sometimes the right fix isn't a new architecture — it's respecting the one you have and being disciplined about how you talk to it.