pgvector: Postgres for AI

This cover targets a pattern almost every team falls into: the instinct to provision a specialized vector database the instant AI search appears on the roadmap. It feels like the 'serious' choice, but it quietly commits you to operating two stateful systems that must always agree with each other.

The purpose of this post is to make the cost of that split concrete, and to show how pgvector sidesteps it by keeping embeddings in the database that already holds your business data.

The fundamental problem with a separate vector store is dual ownership of the truth. A document and its embedding describe the same thing, but they now live in two systems. Every create, update, and delete must be applied to both, in the right order, with the right error handling.

pgvector collapses this to a single store. The embedding is a column on the same row as the text it represents. There is no mirroring to write, no ordering to get right, and nothing to drift out of sync — because there is only one copy of the truth.

This comparison quantifies the 'sync tax.' On the left, a separate vector DB demands dual writes, which introduces partial-failure modes (the Postgres write succeeds but the vector write fails, or vice versa), stale or orphaned vectors when deletes are missed, and a second backup-and-recovery story to maintain.

On the right, pgvector folds all of that into one transactional write against one system. The vector is deleted automatically when its row is, backups capture everything together, and an outage is a single outage rather than a coordination problem between two services.

Production retrieval is rarely a pure similarity question. The real query is almost always 'find the most relevant items that this specific user is allowed to see, within this workspace, matching these constraints.' That blends semantic ranking with relational filtering.

pgvector handles this natively because it is Postgres: you write a WHERE clause and a JOIN, then order by vector distance. Standalone vector stores typically offer only coarse metadata filters that cannot express a genuine join across tables, so you end up either over-fetching and filtering in app code or duplicating relational data into the vector store.

This query is the concrete payoff of the previous slide. It joins `docs` to a `memberships` table to enforce that the requesting user actually belongs to the workspace, adds a recency filter, and only then ranks the surviving rows by cosine distance to the query vector.

This is impossible to express cleanly when your vectors live elsewhere, because the access-control data lives in Postgres. With pgvector the security boundary and the semantic ranking are evaluated in the same query, by the same planner, against the same consistent snapshot of data.

Transactions are an underrated reason to choose pgvector. When you insert a document and its embedding inside one transaction, Postgres guarantees both succeed or both roll back. There is never a moment where the row is visible but its vector is missing, or where a vector points at a row that no longer exists.

That same transactional umbrella extends your existing foreign keys, cascading deletes, and row-level security policies to cover embeddings automatically. The data-integrity machinery you already trust for your core tables now protects your AI features for free.

Beyond correctness, there is the simple matter of operational reuse. Your organization already has runbooks, dashboards, alerts, backup schedules, replication topology, and access controls for Postgres. pgvector inherits every bit of that without adding a new failure domain.

A dedicated vector database, by contrast, is a new thing to provision, secure, monitor, patch, scale, and page someone about at 3am. For most teams the operational savings of not running that second system dwarfs any raw performance edge it might offer.

This bar chart compares the ongoing burden of the two approaches, where lower is better. With pgvector the sync code is zero (none needed), the extra service count is zero, consistency bugs trend toward zero because there is one source of truth, and the operational surface stays flat because it is the same Postgres you already run.

The point is not that pgvector is faster in a benchmark — it is that the recurring engineering cost of the separate-database approach is largely eliminated, and that cost is paid every single day the system runs.

These bullets summarize why the consolidation pays off: one source of truth removes a whole class of bugs, transactional consistency comes for free, you can filter and join before ranking, you reuse Postgres operations and security, and you operate fewer services overall.

Taken together they explain why so many teams that started with a separate vector DB later migrate back to pgvector. The dedicated system's headline performance rarely justifies the steady drag of keeping two stores aligned.

Honesty matters, so this comparison names the cases where a dedicated vector database genuinely wins. If you are storing billions of vectors, need single-digit-millisecond p99 latency at massive scale, are building a product whose entire value is vector search, or require heavy sharding, a purpose-built system earns its keep.

For the far more common case — millions of vectors tightly coupled to relational data, on a team already running Postgres, where filters and joins matter — pgvector is the better default. The decision should be driven by measured requirements, not by what sounds impressive.

The mistake this slide calls out is reaching for a specialized vector store reflexively, before any measurement justifies it. Teams routinely take on dual-write complexity and consistency bugs for a workload that Postgres would have served comfortably.

The disciplined approach is to start with pgvector and only graduate to a dedicated system when you hit a concrete, measured wall — a scale or latency ceiling you have actually tried and failed to tune past. Premature specialization is just a more expensive way to be wrong.

That closes the 'why it matters' post. The throughline is consolidation: fewer systems, one source of truth, and the reuse of everything Postgres already gives you.

The next post goes underneath the SQL to explain how pgvector actually finds neighbors quickly — the difference between exact and approximate search, and how the IVFFlat and HNSW indexes work.