1536-dim embeddings, cosine, ivfflat index created back when the table had maybe 20k rows. We are at 820k now and the same query that used to be 40ms is 3.1s at p95. Nothing about the query changed. 4 vCPU / 16 GB managed instance, no other load worth mentioning.
Before you rebuild anything, run EXPLAIN ANALYZE and confirm the index is being used at all. If your query filters by tenant alongside the vector ordering, the planner may decide a sequential scan is cheaper, or use the vector index and then throw most of the results away at the filter, leaving you with three rows out of a requested ten. Both look like "vector search got slow" from the application.
Partial indexes per tenant solve the filter problem cleanly and stop scaling at a few hundred tenants, at which point you have a few hundred indexes and every autovacuum takes an age. Fine for enterprise-shaped customer lists, not for self-serve.
3.1s smells like disk. 820k x 1536 floats is around 5 GB of raw vectors before the index, on a 16 GB box that is also holding your normal working set. Check the buffer cache hit rate during a slow query — if you have fallen out of RAM, no index parameter will save you and the fix is either a bigger box or narrower vectors.
Following on: halve the width. Either an embedding model that supports shorter outputs, or store as halfvec. Roughly half the memory for a small recall cost, and it moves the point where you fall out of cache a long way to the right.
@frosting_fixer · last mo. · 3 replies
Two things, and the second one is the real one.
First,
listswas sized for a 20k table. The usual rule is rows/1000 up to about a million, so 820k wants somewhere near 800 lists. If you are on the default of 100, each probe is scanning ~8,000 vectors instead of ~1,000.Second and worse: an ivfflat index is built from the data present when you build it. The centroids came from your 20k rows. Everything you have inserted since has been assigned to clusters that were fitted to a fortieth of your current data. Reindexing is not optional maintenance here, it is the whole mechanism.
Given you have to rebuild anyway, build hnsw instead. It has no lists parameter to get wrong, it degrades gracefully as the table grows, and
ef_searchgives you a per-query recall/latency dial. You pay with a slower build and more memory.Reply
Report
@slowtrainjo · last mo.
Rebuilt as hnsw, m=16, ef_construction=64. Build took 26 minutes on 820k rows. p95 is 41ms at ef_search=40 and 96ms at 200, and recall on our eval set went from 0.71 to 0.92. That is a strictly better index in every column, which does not happen often.
Reply
Report
@airflow_amir · last mo.
Raise
maintenance_work_membefore you build a big hnsw index. Ours failed twice at the default and the error message is not especially forthcoming about why.Reply
Report