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

PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

blog.vectorchord.ai

Post reply on HN
Print view View original

61–70 of 90 posts

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#61

Post by CodesInChaos »

sgarland wrote: post #15
aobdev wrote: post #3

Earlier quoted context omitted.

This is not my area of expertise so take this with a grain of salt, but my initial instinct was to question why you would need to store the tsvector both in the table and in the index (because the tsvector values will in fact be stored losslessly in a GIN index). The PG docs make it clear that this only affects row rechecks, so this would only affect performance on matching rows when you need to verify information no…

If only Postgres had Virtual Generated Columns. Not being snarky; MySQL has had them for ages, and they are a perfect fit for this: takes up essentially zero disk space, but you can index it (which is of course stored). It is, in my mind, the single biggest remaining advantage MySQL has. I used to say that MySQL’s (really, InnoDB) clustering index was its superpower when yielded correctly, but I’ve done some recent b…

I hope OrioleDB will succeed in replacing Postgres' high maintenance storage engine with something that just works.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#62

Post by wodenokoto »

cryptonector wrote: post #41
retakeming wrote: post #37

I'm one of the pg_search maintainers. Hello! A few thoughts. First, both strategies - the one outlined by the Neon/ParadeDB article, and the one used here -- are presented as viable alternatives by the Postgres docs: https://www.postgresql.org/docs/current/textsearch-tables.ht... . Second - as the article correctly demonstrates, the problem with Postgres FTS isn't "how can I pick and optimize a single pre-defined que…

From the blog about pg_search linked by TFA: This is what we did: DB with pg_search: We created a single BM25 index DB without pg_search: We created all these indexes GIN index on message (for full-text search) GIN index on country (for text-based filtering) B-tree indexes on severity, timestamp, and metadata->>'value' (to speed up filtering, ordering, and aggregations) See the problem? You didn't create an index on…

Are you sure parent is the author of that blog post?

Maybe I’m reading the whole thread wrong, but it looks like you are screaming at a maintainer of pg_search that someone else did a poor benchmark

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#63

Post by ltbarcly3 »

I first used pg full text in around 2008. I've also used SOLR and ElasticSearch to power search and recommendation in substantial products.

The issue I have had with postgres full text search isn't that it's too slow, it's that it's too inflexible. It's a nice way to add simple search to fields but poor if you want to tune the search at all. Even allowing for general substrings is too much to ask, even allowing for custom tokenization is too much to ask. There's no tokenization pipeline to speak of unless you want to write c extensions (which of course you can't do for hosted databases anyway). Solr and Elasticsearch let you set up very complex indexes and search processing via configuration. There's absolutely nothing that would prevent postgres from adopting a lot of this capability, but instead postgres offers literally NOTHING. I get the impression that most of the developers for postgres full text haven't spent much time with other solutions as from previous discussions they don't really understand what I mean when I talk about tokenization and filter setup, and they don't really understand why this is a deal-breaker even for very simple applications. Postgres just splits on whitespace (and lets you basically manually use stopwords and stemming, which is crap). There is really no way to concatenate fields in a clean way into a single index, which again makes it extremely annoying to work with. There's no way to score searches based on field weighting or really any other kind of weighting beyond BM. Compared to the alternatives it's a toy system.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#64

Post by eqvinox »

nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

It's because postgres is in fact good at a lot of vaguely database-looking things. Even if it weren't the best for anything, if it does 80% of things at 80% best possible — it is reasonable to have postgres as "first thing to reach for" by default.

That said, it's easy to forget to check if you're in either of those 20% (or both.) There's probably a whole bunch of postgres usage where really something else should be used, and people just never checked.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#67

Post by xmcqdpt2 »

bmelton wrote: post #22
nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

If you can avoid adding an extra service without paying too much penalty, it means not having to acquire an extra skill or hire another devops person or keep yet another service in sync / maintained / etc. The cost of adding services to an app is so much higher than people give it credit for at organizations of every size, it's shocking to me that more care isn't done to avoid it. I certainly understand at the enterp…

Where I work did an inventory a few years back of their systems and found that we had about the same number of databases (not tables!) as employed engineers, counting all deployed (QA and prod) instances.

The team on that inventory project obviously created a new database to put their data in, plus QA and test replicas. They (probably) have since moved to another DB system but left the old ones running for legacy applications!

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#68

Post by xmcqdpt2 »

bob1029 wrote: post #34
nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

You can abstract this to any RDBMS, and the justification is that it makes everything a lot faster & easier. I just got off a call with a client where their developers were using ORM-style abstractions to manipulate data for downstream processing in code, turning what should have been a few seconds of one custom SQL command into several hours of passing objects around multiple computer systems. If we can put the FTS…

> There is ZERO honor in offloading what could have been a SQL view into a shitty pile of nested iterators somewhere. I don't understand where any of this energy comes from. The less code the better. It's pure downside.

I wholeheartedly agree with you. As to why we use ORMs, the impression I get from the engineers I work with is that many of them a) don’t know SQL and b) feel like it’s “data analyst” stuff and so beneath them to learn it. Real engineering requires objects and inheritance or structs, pointers and arrays (depending on the engineer).

I think it’s the declarative nature of SQL that turns them off.

Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

#70

Post by _joel »

bvirb wrote: post #13
nostrebored wrote: post #6

I'm legitimately curious -- why do people want to put EVERYTHING into postgres? I don't understand this trend (vector search, full text search, workload orchestration, queues, etc.)

We've been using Elasticsearch + PG and it's pretty nice and fast, but it adds a whole layer of extra stuff to deal with when your data is in PG but then also needs to be indexed into an external server outside of those PG transactions. In our case I'm pretty convinced it hasn't been worth the effort. I think we could've optimized PG to be as fast as we needed with a lot less overhead than dealing with an external se…

.. along with naming things and off by one errors.
Post reply on HN