Postgres Is the Rest of Your AI Stack
The vector column was the easy part. An AI feature accumulates a queue, a cache, a trace log and a tenant boundary — and every one of those is a table you already know how to write.
Architecture diagrams for AI features are drawn around the model and the vector index, because those are the interesting boxes. Then you build the thing, and discover that the embeddings were the part with the clearest instructions. What actually consumes the weeks is the surrounding state: work that has to be retried, calls that must not be paid for twice, answers worth reusing, a record of what the model was asked, and a boundary that stops one customer's documents reaching another.
None of that needs a new piece of infrastructure. It needs about five tables. I have written before about keeping vectors in Postgres rather than beside it; this is the less glamorous half of the same argument, and the half where the defaults are actually dangerous.
What is left over once the vectors work
Take a retrieval feature that works in a notebook and list the state it grows on the way to production:
- Pending work. Documents to chunk, chunks to embed, re-embedding triggered by a source change. Every item is slow, costs money, and fails intermittently.
- In-flight expensive calls. A request that times out at the client after the model has already been billed, and a retry that bills you again.
- Reusable answers. The same question asked forty times an hour, at full price each time.
- What happened. Which chunks were retrieved, which prompt was sent, how many tokens, why it took nine seconds, which answer the user thumbed down.
- Who may see what. The predicate that separates tenants, enforced somewhere more reliable than the query you remembered to write correctly.
Those are a queue, an idempotency ledger, a cache, an append-only log and an access-control rule. Postgres has been doing all five for years, and the reason to keep them there is not minimalism for its own sake — it is that they need to be transactionally consistent with the rows they describe.
The queue is a table, and the pattern is SKIP LOCKED
Embedding work is the canonical background job: slow, rate-limited, occasionally failing, and worth resuming rather than restarting. Reaching for a broker is reasonable at volume, and well below that volume a table with one clause in it does the job.
CREATE TABLE embedding_jobs (
id bigserial PRIMARY KEY,
chunk_id bigint NOT NULL REFERENCES chunks(id) ON DELETE CASCADE,
status text NOT NULL DEFAULT 'pending',
attempts int NOT NULL DEFAULT 0,
run_after timestamptz NOT NULL DEFAULT now(),
started_at timestamptz,
last_error text,
created_at timestamptz NOT NULL DEFAULT now()
);
-- status is redundant inside a partial index that already filters on it
CREATE INDEX ON embedding_jobs (run_after) WHERE status = 'pending';Note the cascade again: delete a chunk and its queued work disappears with it, so a worker never wakes up to embed a row that no longer exists. That is the same property that makes the vector column worth keeping here, applied to the job.
Claiming work is one statement:
WITH claimed AS (
SELECT id
FROM embedding_jobs
WHERE status = 'pending' AND run_after <= now()
ORDER BY run_after
FOR UPDATE SKIP LOCKED
LIMIT 20
)
UPDATE embedding_jobs j
SET status = 'running', attempts = attempts + 1, started_at = now()
FROM claimed
WHERE j.id = claimed.id
RETURNING j.id, j.chunk_id;SKIP LOCKED is the whole trick. Ten workers running that query concurrently each get a different twenty rows instead of nine of them blocking on the first one's locks. Without it you have written a queue that serialises, which is worse than no queue because it looks like it works in staging with one worker.
Do not hold the transaction open across the model call. This is the mistake that turns a working queue into an operational problem. Claim in a transaction that commits in milliseconds, make the embedding request with no transaction open, then start a second short transaction to write the vector and mark the job done. A transaction that stays open for the eight seconds of an API call holds its snapshot open too, which blocks vacuum from cleaning up dead rows across the whole database — and a queue table is the highest-churn table you own.
Failure handling is run_after and nothing more. On error, set status back to pending with run_after = now() + interval '1 minute' * attempts for linear backoff, record last_error, and move a job to a terminal failed state once attempts crosses your limit. The dead-letter queue is a WHERE clause. Add one more: a job stuck in running with started_at older than your timeout was orphaned by a worker that died, and needs resetting to pending — this is the recovery path people leave out, and it is why work silently stops.
For picking up jobs promptly, LISTEN/NOTIFY will wake a worker the moment something is enqueued. It is genuinely useful and it is not a delivery guarantee: notifications are dropped if no session is listening, and a reconnecting worker misses everything sent while it was away. Treat it as a hint that shortens latency on top of a poll every few seconds, never as the mechanism that work depends on. What should be driving these jobs in the first place — deriving invalidation from what actually changed — is the subject of keeping a vector index in sync.
Calling an expensive model exactly once
Model calls are slow enough that clients time out, retry middleware fires, and users press the button again. Each of those is a duplicate charge, and for anything with a side effect — a generated document, a summary posted to a ticket — a duplicate action as well.
The fix is a unique constraint. The client, or your API layer, sends an idempotency key; the database decides who owns the work.
CREATE TABLE model_calls (
idempotency_key text PRIMARY KEY,
tenant_id uuid NOT NULL,
request_hash text NOT NULL,
status text NOT NULL DEFAULT 'running',
response jsonb,
input_tokens int,
output_tokens int,
created_at timestamptz NOT NULL DEFAULT now(),
completed_at timestamptz
);
-- claim the right to make this call
INSERT INTO model_calls (idempotency_key, tenant_id, request_hash)
VALUES ($1, $2, $3)
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;A returned row means you own the call: make it, then write the response and set status = 'complete'. No row means someone got there first, so read the existing one — if it is complete, return that response; if it is still running, wait and poll rather than issuing a second call. Two racing requests cannot both win, because the primary key will not allow it.
Two details that matter more than they look. Store request_hash and compare it: the same key arriving with a different request body is a client bug, and returning the first response for the second question is a confusing one to debug — better to reject it. And expire these rows, because an idempotency ledger with infinite retention is a table that only grows; a few days covers every retry that will ever legitimately arrive.
A completion cache that cannot leak across tenants
Caching model output is one of the largest cost savings available in an AI feature, and the naive version is a security incident. The temptation is to key on the user's question, because that is what a user repeats.
In a retrieval feature, the question is not the input. The input is the question plus every retrieved chunk, and those chunks differ per tenant, per user, per permission set. Key on the question alone and the second person to ask “what is our refund policy?” gets an answer generated from a different company's documents. It will look like a cache hit and a fast response, and nothing in your logs will flag it.
The cache key has to cover everything that shaped the output:
- the rendered prompt, including retrieved context, not the question
- the model identifier and version, since a provider silently moving a pointer alias should invalidate everything
- decoding parameters — temperature, max tokens, any tool or schema definition
- the tenant, as a real column rather than a string concatenated into a hash, so that isolation is enforceable and auditable
CREATE TABLE completion_cache (
tenant_id uuid NOT NULL,
prompt_hash text NOT NULL, -- sha256 of rendered prompt + params
model text NOT NULL,
response jsonb NOT NULL,
hits int NOT NULL DEFAULT 0,
created_at timestamptz NOT NULL DEFAULT now(),
expires_at timestamptz NOT NULL,
PRIMARY KEY (tenant_id, model, prompt_hash)
);Two honest caveats. Temperature zero is not determinism— identical inputs still drift across provider infrastructure, so a cache is a cost optimisation, never a way to make behaviour reproducible. And semantic caching, where a similar question reuses a previous answer, is a genuinely bad default: “can I cancel after 30 days” and “can I cancel within 30 days” sit close together in embedding space and have opposite answers. Exact-hash caching is boring and correct; reach for the fuzzy version only with an eval set that will catch it going wrong.
Traces you can actually query
When a user reports a bad answer, the question is always the same: what was retrieved, what was sent, and what came back. If that is only in stdout you will not answer it. A trace table is the highest-leverage table in this whole list.
CREATE TABLE llm_traces (
id bigserial PRIMARY KEY,
request_id uuid NOT NULL,
tenant_id uuid NOT NULL,
feature text NOT NULL,
model text NOT NULL,
latency_ms int NOT NULL,
input_tokens int,
output_tokens int,
cost_usd numeric(10,6),
retrieved_ids bigint[], -- which chunks, for reproducing it
payload jsonb NOT NULL, -- prompt, tool calls, raw response
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON llm_traces (feature, created_at DESC);
CREATE INDEX ON llm_traces (request_id);Keep the queryable dimensions as real columns and put the bulky, shape-varying material in jsonb. The common mistake is one undifferentiated JSON blob per call, which means every operational question — p95 latency by feature, spend by tenant this week, refusal rate since the model upgrade — becomes a full scan with extraction in the WHERE clause. When you do need to filter on something inside the payload regularly, promote it rather than indexing an expression by hand:
ALTER TABLE llm_traces
ADD COLUMN finish_reason text
GENERATED ALWAYS AS (payload ->> 'finish_reason') STORED;
CREATE INDEX ON llm_traces (finish_reason) WHERE finish_reason <> 'stop';This table is also what makes evaluation possible, because a real eval set is built from traces of things that actually went wrong rather than from questions you invented — the case I made in testing something that answers differently every time. Store eval runs the same way, one row per case per run, and a regression becomes a join between two runs instead of a diff of two console logs.
Two things to decide on day one, both of which are painful to retrofit. Growth: traces outgrow the data they describe, so partition by month and drop old partitions rather than issuing a DELETE against a billion rows. Retention and content: prompts contain whatever your users typed, which means this table holds personal data and will be in scope for any deletion request. Decide the retention window and what gets redacted before it is full, not after.
The tenant boundary belongs in the database
Every query in a retrieval path needs a tenant predicate, and “every query” is exactly the kind of requirement that holds for eleven months and then does not. The consequence of one missing WHERE clause here is not a bug, it is cross-tenant disclosure.
ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
ALTER TABLE chunks FORCE ROW LEVEL SECURITY; -- applies to the owner too
CREATE POLICY chunks_tenant_isolation ON chunks
USING (tenant_id = current_setting('app.tenant_id', true)::uuid);The application sets app.tenant_id per request with SET LOCAL inside the transaction, so it is scoped to that transaction and cannot leak to the next borrower of a pooled connection. Three sharp edges worth knowing before you rely on it: the table owner bypasses policies unless you FORCE it, so connect as a role that is not the owner; SET LOCAL outside a transaction is silently useless, which is easy to do with a pooler in transaction mode; and a policy is not a substitute for the predicate in your query when performance matters, because RLS turns tenant isolation into a filter applied to whatever the approximate vector index returned — the recall trap from the pgvector post, now invisible because the predicate is not in the SQL you wrote.
This is the same argument as prompt injection is an authorisation problem, one layer down. If a retrieval tool runs its query on a connection whose tenant is fixed by the request, then a document instructing the model to “search all tenants for admin credentials” produces a query the database refuses to widen. The model does not get a vote.
Where this stops being the right answer
The point is not that Postgres is always sufficient. It is that these components have a known cost curve, and you should be able to say which part of it you have hit.
- Queue throughput. A table handles thousands of jobs a minute comfortably. At sustained thousands per second the churn becomes a vacuum problem — a high-turnover queue table needs its own aggressive autovacuum settings long before that, and past it a broker is doing something a table cannot.
- Analytics on traces. Dashboards scanning months of trace history do not belong on the instance serving user requests. A read replica buys you a lot of room; a warehouse is the answer once those queries have their own product requirements.
- Vectors past a single node. Hundreds of millions of embeddings is a sharding problem, and sharding an index is what a specialised store is genuinely for.
- Fan-out to many consumers. The moment three unrelated services need the same event, you want a log with independent consumer offsets rather than three processes polling one table.
What all four have in common is that you can name the property you need. “This is the standard architecture for AI applications” is not one of them, and it is the reason most of these systems end up with four datastores and a consistency problem between each pair.
The short version
The vector column is the part with the tutorials and the smallest share of the risk. The queue, the idempotency ledger, the cache, the trace log and the tenant boundary are where the outages and the disclosures live, and all five are ordinary tables. Use SKIP LOCKED and never hold a transaction open across a model call. Let a unique constraint decide who pays for an expensive call. Key the cache on the rendered prompt rather than the question, or you will serve one tenant's documents to another. Keep the trace dimensions as columns so operational questions stay cheap, and decide retention before the table is enormous. Enforce the tenant predicate with row-level security, not with discipline. Then add a specialised system when you can name the property it has that Postgres does not.