We Dropped Our Vector Database: pgvector Served 40M Embeddings Fine

What the second database really cost

The bill was eleven invoices and about four thousand dollars a month, plus a sync worker that kept waking someone up at night. A second vendor was holding a list of numbers that belonged beside rows the company already owned. Approving that invoice eleven times was easy. Saying out loud what it meant, in a meeting, produced the silence you get when everyone already knows and nobody wants to go first.

Still, the money wasn't the main cost. The seam between the two systems was. Two databases mean two sources of truth, which means a sync worker, which means a dead letter queue, which means an engineer awake at 3 a.m. reading JSON on a phone. This is the architecture as it ran in practice, not as drawn in the design doc:

app ──> postgres        (rows, permissions, deletes)
   │
   └────> vector store    (embeddings)
              ▲
              │
        sync worker ──> DLQ ──> me, awake, coffee going cold

When a user deleted a document, Postgres dropped the row inside the same transaction. The vector store dropped its copy whenever the worker caught up, so for roughly ninety seconds, deleted content was still searchable. That isn't a latency issue. It's a support ticket with a legal review attached.

One table, one index, no worker

The replacement was a single table with a single index and no background process:

create extension vector;

create table docs (
  id bigserial primary key,
  team_id int not null,
  body text,
  emb vector(768)
);

Querying it is what won the team over, since it's plain SQL:

select id, body
from docs
where team_id = 42
order by emb <=> $1
limit 10;

An embedding can't drift away from its row when it lives inside that row. A whole category of bugs vanished, and so did the runbook written for them.

Migrating 40 million rows while the product stayed up

Migration write-ups usually skip this stage, though it took most of the time. The backfill didn't use OFFSET. Deep pagination across forty million rows degrades into a sequential scan, and the job gets slower as it advances, which is the reverse of what a long job needs. The workers walked the primary key in ranges and checkpointed the last id finished:

select id, body from docs
where emb is null and id > $1
order by id
limit 2000;

Each batch held two thousand rows, eight workers ran at once, and a deliberate pause between batches kept replication lag in check. Replica lag was the number being watched, not throughput, because the replicas were handling live traffic the entire time.

The index was built last. Creating HNSW before the load forces the graph to be rewritten continuously as data arrives, and the build then never completes on a timetable you'd tolerate.

set maintenance_work_mem = '16GB';
set max_parallel_maintenance_workers = 7;

create index concurrently on docs
using hnsw (emb_h halfvec_cosine_ops)
with (m = 16, ef_construction = 64);

The failure nobody's dashboard showed

Search went live on Postgres and the dashboards looked great: flat latency, zero errors. Nobody celebrated, because nothing seemed to have happened. Four days later a customer reported that a document they'd definitely uploaded wasn't showing up in results.

The cause was that hnsw.ef_search had never been set. It sat at its default, a candidate list too narrow for a graph of this size, and recall had slipped into the low nineties. Every dashboard measured whether the query returned. None measured whether it returned the right thing.

The fix was setting it to 100, then trying a range of values to find where recall levelled off. After that came the check that should have been there from day one: a frozen batch of test queries, each with a known right answer, and an alert if the agreement rate drops.

set hnsw.ef_search = 100;

Recall is a setting, not a property of the database. If you keep one line from all this, keep that one.

Measured results

The test ran on forty million rows at 768 dimensions with sixteen virtual CPUs, and replicas serving reads.

                        vector store     pgvector
  p50 latency              18 ms           24 ms
  p95 latency              41 ms           57 ms
  recall at 10             0.985           0.981
  filtered p95            190 ms           61 ms
  index size                 n/a           45 GB
  monthly cost            $4,100            $280

Unfiltered queries run six milliseconds slower. Inside a model call of nine hundred milliseconds, that gap goes unseen. The filtered case matters more, because it's where the real argument sits. Searches in a real product are filtered by team, permission or date. In the old setup you'd pull a candidate set bigger than needed out of one system and verify it in the other. With a single database, the planner can weigh the filter and the sort order in one go and choose how to run them.

Objections you're probably forming

HNSW doesn't handle heavy update churn well. That's true. These documents are written once and read constantly, so the graph stays stable, but if you rewrite embeddings every hour, think carefully about it.

Index builds are expensive. Also true. This one took a little over an hour, and building concurrently kept writes flowing throughout.

Very selective filters can badly hurt graph traversal. True again, and the honest advice is to test your own filter shapes instead of trusting any table, including this one.

When to stay where you are

Keep your current setup if you're beyond a hundred million vectors and still growing, if you need single-digit-millisecond latency on unfiltered search, or if nobody on the team wants to think about Postgres memory settings. This route swaps a vendor invoice for your own attention.

For everyone else, the database already ships a feature they're paying someone to rent.

What was removed

Twenty-four hundred lines of synchronization code are gone, along with a service, an alert channel and a runbook. The best infrastructure decision of the previous year turned out to be a deletion. Go read your own invoice. You probably know what it says.