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

31–40 of 144 posts

Re: The Case Against PGVector

#31

Post by tacoooooooo »

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…

for sure people are running pgvector in prd! i was more pointing at every tutorial

iterative scans are more of a bandaid for filtering than a solution. you will still run into issues with highly restrictive filters. you still need to understand ef_search and max_search_tuples. strict vs relaxed ordering, etc. it's an improvement for sure, but the planner still doesn't deeply understand the cost model of filtered vector search

there isn't a general solution to the pre- vs post-filter problem—it comes down to having a smart planner that understands your data distribution. question is whether you have the resources to build and tune that yourself or want to offload it to a service that's able to focus on it directly

Re: The Case Against PGVector

#33

Post by dpflan »

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…

What are you using it for? Is it part of a hybrid search system (keyword + vector)?

Re: The Case Against PGVector

#34

Post by tacoooooooo »

indigo945 wrote: post #22

> None of the blogs mention that building an HNSW index on a few million vectors > can consume 10+ GB of RAM or more (depending on your vector dimensions and > dataset size). On your production database. While it’s running. For potentially > hours. 10 GB? Oh jolly gosh! That will almost show up as a pixel or two on my metrics dashboard. Who are these people that run production Postgres clusters on tiny hardware and t…

guess it depends on your scale? for some, 10+ GB of RAM being consumed on an index build is > 25% of the DB's RAM. apply that same proportion to your setup and maybe it'll make more sense

Re: The Case Against PGVector

#35

Post by simonw »

"HNSW index on a few million vectors can consume 10+ GB of RAM or more (depending on your vector dimensions and dataset size). On your production database. While it’s running. For potentially hours."

How hard is it to move that process to another machine? Could you grab a dump of the relevant data, spin up a cloud instance with 16GB of RAM to build the index and then cheaply copy the results back to production when it finishes?

Re: The Case Against PGVector

#37

Post by tacoooooooo »

simonw wrote: post #35

"HNSW index on a few million vectors can consume 10+ GB of RAM or more (depending on your vector dimensions and dataset size). On your production database. While it’s running. For potentially hours." How hard is it to move that process to another machine? Could you grab a dump of the relevant data, spin up a cloud instance with 16GB of RAM to build the index and then cheaply copy the results back to production when i…

i discuss that specifically!

> The problem is that index builds are memory-intensive operations, and Postgres doesn’t have a great way to throttle them. You’re essentially asking your production database to allocate multiple (possibly dozens) gigabytes of RAM for an operation that might take hours, while continuing to serve queries.

> You end up with strategies like:

    Write to a staging table, build the index offline, then swap it in (but now you have a window where searches miss new data)
    Maintain two indexes and write to both (double the memory, double the update cost)
    Build indexes on replicas and promote them
    Accept eventual consistency (users upload documents that aren’t searchable for N minutes)
    Provision significantly more RAM than your “working set” would suggest
> None of these are “wrong” exactly. But they’re all workarounds for the fact that pgvector wasn’t really designed for high-velocity real-time ingestion.

short answer--maybe not that _hard_, but it adds a lot of complexity to manage when you're trying to offer real-time search. most vector DB solutions offer this ootb. This post is meant to just point out the tradeoffs with pgvector (that most posts seem to skip over)

Re: The Case Against PGVector

#38

Post by simonw »

Fripplebubby wrote: post #13
clickety_clack wrote: post #7

My default is basically YAGNI. You should use as few services as possible, and only add something new when there’s issues. If everything is possible in Postgres, great! If not, at least I’ll know exactly what I need from the New Thing.

The post is a clear example of when YAGNI backfires, because you think YAGNI but then, you actually do need it. I had this experience, the author had this experience, you might as well - the things you think you AGN are actually pretty basic expectations and not luxuries: being able to write vectors real-time without having to run other processes out of band to keep the recall from degrading over time, being able to…

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 database for 100,000 vectors on the assumption that you'll need it later.

Re: The Case Against PGVector

#39

Post by the_mitsuhiko »

tacoooooooo wrote: post #37
simonw wrote: post #35

"HNSW index on a few million vectors can consume 10+ GB of RAM or more (depending on your vector dimensions and dataset size). On your production database. While it’s running. For potentially hours." How hard is it to move that process to another machine? Could you grab a dump of the relevant data, spin up a cloud instance with 16GB of RAM to build the index and then cheaply copy the results back to production when i…

i discuss that specifically! > The problem is that index builds are memory-intensive operations, and Postgres doesn’t have a great way to throttle them. You’re essentially asking your production database to allocate multiple (possibly dozens) gigabytes of RAM for an operation that might take hours, while continuing to serve queries. > You end up with strategies like: Write to a staging table, build the index offline,…

> short answer--maybe not that _hard_, but it adds a lot of complexity to manage when you're trying to offer real-time search. most vector DB solutions offer this ootb. This post is meant to just point out the tradeoffs with pgvector (that most posts seem to skip over)

Question is if that tradeoff is more or less complexity than maintaining a whole separate vector store.

Re: The Case Against PGVector

#40

Post by summarity »

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.

That's where it's at. I'm using the 1600D vectors from OpenAI models for findsight.ai, stored SuperBit-quantized. Even without fancy indexing, a full scan (1 search vector -> 5M stored vectors), takes less than 40ms. And with basic binning, it's nearly instant.
Post reply on HN