Hacker News Reader

Comment by fizx | Hacker News Reader

One of the many problems with search inside Postgres is that search is very CPU-heavy (and starting to become GPU-heavy). Ideally, you'd like to reserve CPU in your database for transactional updates to your core data model.

I've seen a lot of ES and Solr clusters operating at 100% of 10+ nodes during a re-index, or just 30-50% of 10+ nodes during normal operation. The corresponding database would be say an AWS L/XL instance at 50-100GB of data and 30% CPU utilization. Moving all of the search CPU into your primary DB means now you'd have to shard it.

But I love PG extensions for search, recursive joins, vectors, etc on side projects. It can keep things fun and simple.

View on HN

Could you solve this by searching on a read-only replica?

Convincing your devs to keep two handles two the db, one for reads one for writes from the beginning is a blessing.

Can you elaborate?

Determining whether a query is appropriate to hit the read replica is something I've never seen done automatically and well. But if you design your system from the beginning that when you make queries the developer chooses whether to hit the reader or writer by way of essentially do_query / do_query_reader then it's a million times easier scale reads and it doesn't have to come with any additional operational complexity when you're small you can just have both handles point to the writer but have a read-only user or set TRANSACTION READ ONLY on the session.

DB client libs really should have this built in. Even the heavy-handed things wrapping them don't. I have my own that I copy into every new project with things like xactReadWrite(innerFunc) and xactReadOnly(innerFunc), where the read-only randomly picks from a pool of connections to read replicas.

Some of it, yes.

But in practice, you want to fix a bug in chinese tokenization, or OpenAI releases the next version of its embeddings, or you want to add a few synonyms, or change the aggressiveness of the stemmer.

Then you have to rewrite your whole search index, and if its part of your primary db, you're pretty sad.

Reply on news.ycombinator.com