7 Postgres Tools Every AI Engineer Should Know

Most AI stacks add a vector database on day one and a second datastore to keep in sync forever after. A short list of Postgres extensions removes that decision for a large class of workloads.

We build Statewave, an open-source memory runtime for AI agents that runs on Postgres and nothing else. No separate vector service. That constraint forced us to learn which extensions genuinely carry AI workloads and which are resume padding. Below are seven, ordered by how often they earn their install.

Our own dependency list is one extension long, which is the first useful signal in this post: vector is the only one we require. Two entries below are not extensions at all, full-text search ships in Postgres core, and PgBouncer is a connection pooler, but both earn their place on an AI workload, so they are here.

1. pgvector

pgvector is what makes the rest of the argument possible. It adds a vector column type plus distance operators for cosine, L2, and inner product.

CREATE EXTENSION IF NOT EXISTS vector; ALTER TABLE memories ALTER COLUMN embedding TYPE vector(1536);

Index choice is the part worth internalizing, because we shipped the wrong one first. Our initial migration built an IVFFlat index:

CREATE INDEX ON memories USING ivfflat (embedding vector_cosine_ops);

IVFFlat partitions vectors into lists and probes a subset at query time. It builds fast and uses little memory. Recall, though, depends on lists and probes being tuned against your actual row count, and a corpus that grows past what you tuned for silently returns worse neighbors. Nothing errors. Results just get quietly less relevant.

We moved to HNSW:

CREATE INDEX ix_memories_embedding ON memories USING hnsw (embedding vector_cosine_ops);

HNSW builds a navigable graph. Better recall at the same latency, stable as the corpus grows, and no list-count tuning. Cost is real: build time and memory. On a small corpus the migration finishes in under a second, but HNSW build is the slow step at scale, so our migration sets statement_timeout = '20min' to survive large tables.

Use IVFFlat when your corpus is static and you need a fast build. Use HNSW for anything that grows. If you inherited an IVFFlat index and nobody has retuned lists since the table doubled, that is worth checking today.

2. Built-in tsvector and GIN (no extension required)

Semantic search alone fails on exact tokens. A user asking for error code SW-4021 needs lexical matching, and embeddings will happily return five semantically adjacent errors instead.

Postgres ships full-text search in core. A generated column plus a GIN index gives you lexical retrieval with zero dependencies:

ALTER TABLE memories ADD COLUMN content_tsvector tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED; CREATE INDEX ix_memories_content_tsvector ON memories USING gin (content_tsvector);

GENERATED ALWAYS AS ... STORED means Postgres maintains the column on every write. No trigger to forget, no backfill job to schedule.

We run this alongside vector search and fuse the two, which is what "hybrid retrieval" means in practice. Including it here because half the teams that install a vector database do so to solve a problem that lexical search solves better.

3. pgcrypto

Small but load-bearing. pgcrypto supplies gen_random_uuid() for primary keys generated server-side, so you are not round-tripping to the application for an ID.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

On Postgres 13 and newer, gen_random_uuid() is in core and you may not need the extension at all. Check your version before adding it. Its digest and encryption functions matter separately if you are storing anything sensitive in a memory layer, which for agent memory you usually are.

4. pg_trgm

pg_trgm does trigram matching for fuzzy string comparison. Entity resolution is the AI-specific use: deciding that "Acme Corp", "ACME Corporation", and "acme corp." are the same subject before you write three separate memory rows about them.

CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX ON subjects USING gin (name gin_trgm_ops); SELECT name, similarity(name, 'acme corp') AS score FROM subjects WHERE name % 'acme corp' ORDER BY score DESC;

Embeddings are the wrong tool here. Two spellings of the same company name are lexically close and semantically identical, so cosine similarity gives you no separation between the right match and every other company in your table.

5. pg_stat_statements

pg_stat_statements tells you why your agent feels slow.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements; SELECT query, calls, mean_exec_time, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

AI workloads have a specific failure shape: one vector query looks fine at 40ms, then you discover the agent issues it eleven times per turn. Mean latency stays healthy while total time balloons. total_exec_time catches that; a p99 dashboard does not.

6. pg_cron

pg_cron covers the background work agent memory needs. Compiling raw events into typed facts, expiring memories past their validity window, and recomputing scores are all jobs that should not run on the request path.

CREATE EXTENSION IF NOT EXISTS pg_cron; SELECT cron.schedule('expire-memories', '0 * * * *', $$UPDATE memories SET status='expired' WHERE valid_until < now() AND status='active'$$);

We run compilation as durable jobs in the application layer rather than in pg_cron, because our jobs call an LLM and need retry semantics Postgres should not own. Honest rule: pg_cron is right for deterministic SQL maintenance and wrong for anything that makes a network call.

7. PgBouncer (a pooler, not an extension)

Included deliberately, because the thing that breaks first when you scale an AI service is not the vector index. It is connection count.

Each API replica holds a pool. Ten replicas at fifteen connections each is 150 logical connections crowding max_connections, and Postgres connection overhead is not free. Raising max_connections postpones the problem and makes it worse.

PgBouncer in transaction mode decouples logical client connections from physical backends: a pool of 30 to 60 backend connections comfortably serves 5 to 15 replicas. Transaction mode is the right setting when sessions are short. If you use prepared statements, PgBouncer 1.21 and later support them in transaction mode via max_prepared_statements; on older versions, avoid holding them across transactions.

One thing to know before you switch: pg_stat_activity then shows PgBouncer's identity rather than your replicas. Use SHOW POOLS and SHOW CLIENTS for the per-replica picture.

What we would actually install

For a new AI service on Postgres, in order:

1. vector with an HNSW index. Non-negotiable.
2. Built-in tsvector plus GIN. Free, and it fixes the exact-token failures embeddings cannot.
3. pg_stat_statements. Install before you need it, because the query pattern you need to diagnose is one you have to catch in the act.
4. pg_trgm once you have real user-entered entity names.
5. PgBouncer at the point where replica count times pool size approaches max_connections.

pgcrypto and pg_cron are situational. Check your Postgres version for the first and your job semantics for the second.

Broader point: a memory layer for AI agents can run on Postgres with one extension and no separate vector service. We wrote up the storage decisions in more detail, and the IVFFlat-to-HNSW migration is readable in the repo linked at the top if you would rather check it than take our word for it.

What did we miss? If you are running something in production that earned its place on an AI workload, name it below.