Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The Case Against PGVector

alex-jacobs.com

Post reply on HN
Print view View original

71–80 of 144 posts

Re: The Case Against PGVector

#71

Post by morshu9001 »

Fripplebubby wrote: post #61
simonw wrote: post #38

Earlier quoted context omitted.

That's not YAGNI backfiring. The point of YAGNI is that you shouldn't over-engineer up front until you've proven that you need the added complexity. If you need vector search against 100,000 vectors and you already have PostgreSQL then pgvector is a great YAGNI solution. 10 million vectors that are changing constantly? Do a bit more research into alternative solutions. But don't go integrating a separate vector datab…

I think the tricky thing here is that the specific things I referred to (real time writes and pushing SQL predicates into your similarity search) work fine at small scale in such a way that you might not actually notice that they're going to stop working at scale. When you have 100,000 vectors, you can write these SQL predicates (return the 5 top hits where category = x and feature = y) and they'll work fine up until…

If the consequence of being wrong about the scalability is that you just have to migrate later instead of sooner, that's a win for YAGNI. It's only a loss if hitting this limit later causes service disruption or makes the migration way harder than if you'd done it sooner.

Re: The Case Against PGVector

#72

Post by simonw »

xfalcox wrote: post #18
xfalcox wrote: post #3

> Nobody’s actually run this in production We do at Discourse, in thousands of databases, and it's leveraged in most of the billions of page views we serve. > Pre- vs. Post-Filtering (or: why you need to become a query planner expert) This was fixed in version 0.8.0 via Iterative Scans ( https://github.com/pgvector/pgvector?tab=readme-ov-file#iter... ) > Just use a real vector database If you are running a single ser…

Also worth mentioning that we use quantization extensively: - halfvec (16bit float) for storage - bit (binary vectors) for indexes Which makes the storage cost and on-going performance good enough that we could enable this in all our hosting.

It still amazes me that the binary trick works.

For anyone who hasn't seen it yet: it turns out many embedding vectors of e.g. 1024 floating point numbers can be reduced to a single bit per value that records if it's higher or lower than 0... and in this reduced form much of the embedding math still works!

This means you can e.g. filter to the top 100 using extremely memory efficient and fast bit vectors, then run a more expensive distance calculation against those top 100 with the full floating point vectors to pick the top 10.

Re: The Case Against PGVector

#73

Post by VoVAllen »

nostrebored wrote: post #68
VoVAllen wrote: post #50

Earlier quoted context omitted.

We do have some benchmark number at https://blog.vectorchord.ai/vector-search-over-postgresql-a-... . It varies on different dataset, but most cases it's 2x or more QPS comparing to pgvector's hnsw at same recall.

Your graphs are measuring accuracy [1] (i'm assuming precision?), not recall? My impression is that your approach would miss surfacing potentially relevant candidates, because that is the tradeoff IVF makes for memory optimization. I'd expect that this especially struggles with high dim vectors and large datasets. [1] https://cdn.hashnode.com/res/hashnode/image/upload/v17434120...

It's recall. Thanks for pointing out this, we'll update the diagram.

The core part is a quantization technique called RaBitQ. We can scan over the bit vector to have an estimation about the real distance between query and data. I'm not sure what do you mean by "miss" here. As the approximate nearest neighbor index, all the index including HNSW will miss some potential candidates.

Re: The Case Against PGVector

#74

Post by simonw »

morshu9001 wrote: post #71
Fripplebubby wrote: post #61

Earlier quoted context omitted.

I think the tricky thing here is that the specific things I referred to (real time writes and pushing SQL predicates into your similarity search) work fine at small scale in such a way that you might not actually notice that they're going to stop working at scale. When you have 100,000 vectors, you can write these SQL predicates (return the 5 top hits where category = x and feature = y) and they'll work fine up until…

If the consequence of being wrong about the scalability is that you just have to migrate later instead of sooner, that's a win for YAGNI. It's only a loss if hitting this limit later causes service disruption or makes the migration way harder than if you'd done it sooner.

And honestly, even then YAGNI might still win.

There's a big opportunity cost involved in optimizing prematurely. 9/10 times you're wasting your time, and you may have found product-market fit faster if you had spent that time trying out other feature ideas instead.

If you hit a point where you have to do a painful migration because your product is succeeding that's a point to be celebrated in my opinion. You might never have got there if you'd spent more time on optimistic scaling work and less time iterating towards the right set of features.

Re: The Case Against PGVector

#75

Post by Fripplebubby »

simonw wrote: post #74
morshu9001 wrote: post #71

Earlier quoted context omitted.

If the consequence of being wrong about the scalability is that you just have to migrate later instead of sooner, that's a win for YAGNI. It's only a loss if hitting this limit later causes service disruption or makes the migration way harder than if you'd done it sooner.

And honestly, even then YAGNI might still win. There's a big opportunity cost involved in optimizing prematurely. 9/10 times you're wasting your time, and you may have found product-market fit faster if you had spent that time trying out other feature ideas instead. If you hit a point where you have to do a painful migration because your product is succeeding that's a point to be celebrated in my opinion. You might n…

I think I see this point now. I thought of YAGNI as, "don't ever over-engineer because you get it wrong a lot of the time" but really, "don't over-engineer out of the gate and be thankful if you get a chance to come back and do it right later". That fits my case exactly, and that's what we did (and it wasn't actually that painful to migrate).

Re: The Case Against PGVector

#76

Post by sgarland »

ayende wrote: post #24
sgarland wrote: post #14

> The problem is that index builds are memory-intensive operations, and Postgres doesn’t have a great way to throttle them. maintenance_work_mem begs to differ. > You rebuild the index periodically to fix this, but during the rebuild (which can take hours for large datasets), what do you do with new inserts? Queue them? Write to a separate unindexed table and merge later? You use REINDEX CONCURRENTLY. > But updating…

> maintenance_work_mem That kills the indexing process, you cannot let it run with limited amount of memory. > How do you think a B+tree gets updated? In a B+Tree, you need to touch log H of the pages. In HNSW graph - you need to touch literally thousands of vectors once your graph gets big enough.

> That kills the indexing process, you cannot let it run with limited amount of memory.

Considering the default value is 64 MB, it’s already throttled quite a bit.

Re: The Case Against PGVector

#77

Post by simonw »

Fripplebubby wrote: post #75
simonw wrote: post #74

Earlier quoted context omitted.

And honestly, even then YAGNI might still win. There's a big opportunity cost involved in optimizing prematurely. 9/10 times you're wasting your time, and you may have found product-market fit faster if you had spent that time trying out other feature ideas instead. If you hit a point where you have to do a painful migration because your product is succeeding that's a point to be celebrated in my opinion. You might n…

I think I see this point now. I thought of YAGNI as, "don't ever over-engineer because you get it wrong a lot of the time" but really, "don't over-engineer out of the gate and be thankful if you get a chance to come back and do it right later". That fits my case exactly, and that's what we did (and it wasn't actually that painful to migrate).

Yeah, that's a great way of putting it.

Re: The Case Against PGVector

#78

Post by hobofan »

Fripplebubby wrote: post #61
simonw wrote: post #38

Earlier quoted context omitted.

That's not YAGNI backfiring. The point of YAGNI is that you shouldn't over-engineer up front until you've proven that you need the added complexity. If you need vector search against 100,000 vectors and you already have PostgreSQL then pgvector is a great YAGNI solution. 10 million vectors that are changing constantly? Do a bit more research into alternative solutions. But don't go integrating a separate vector datab…

I think the tricky thing here is that the specific things I referred to (real time writes and pushing SQL predicates into your similarity search) work fine at small scale in such a way that you might not actually notice that they're going to stop working at scale. When you have 100,000 vectors, you can write these SQL predicates (return the 5 top hits where category = x and feature = y) and they'll work fine up until…

> When you have 100,000 vectors [...] and they'll work fine

So 95% of use-cases.

Re: The Case Against PGVector

#79

Post by sgarland »

tacoooooooo wrote: post #23
sgarland wrote: post #14

> The problem is that index builds are memory-intensive operations, and Postgres doesn’t have a great way to throttle them. maintenance_work_mem begs to differ. > You rebuild the index periodically to fix this, but during the rebuild (which can take hours for large datasets), what do you do with new inserts? Queue them? Write to a separate unindexed table and merge later? You use REINDEX CONCURRENTLY. > But updating…

some fair points points on the specifics. > maintenance_work_mem sure, but the knob existing doesn't solve the operational challenge of safely allocating GBs of RAM on prod for hours-long index builds. > REINDEX CONCURRENTLY this is still not free not free—takes longer, needs 2-3x disk space, and still impacts performance. > HNSW vs B+tree it's not that graph updates are uniquely expensive. vector workloads have diff…

> sure, but the knob existing doesn't solve the operational challenge of safely allocating GBs of RAM on prod for hours-long index builds.

How does it not? You should know the amount of freeable memory your DB has, and a rough idea of peak requirements. Give the index build some amount below that.

> this is still not free not free—takes longer, needs 2-3x disk space, and still impacts performance.

Yes, those are the trade-offs for not locking the table during the entire build. They’re generally considered acceptable.

> it's "most teams underestimate the operational complexity.

Agreed, which is why I don’t think dev teams should be running DBs if they lack expertise. Managed solutions (for Postgres; no idea on Pinecone et al.) only remove backup and failover complexity; tuning various parameters and understanding the optimizer’s decisions are still wholly on the human. RDBMS are some of the most complicated pieces of software that exist, and it’s absurd that the hyperscalers pretend that they aren’t.

Post reply on HN