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

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

#41

Post by cryptonector » Wed, Apr 09, 2025, 5:12 AM UTC

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 the vector in the without-pg_search case. You didn't compare apples to apples. TFA is all about that.

Perhaps you can argue that creating a fastupdates=on index would have been the right comparison, but you didn't do that in that blog.

> You can always cherry-pick a query and optimize it at the expense of data duplication and complexity. The Neon/ParadeDB benchmarks contained 12 queries in total, and the benchmarks could have:

TFA isn't cherry-picking to show you that one query could have gone faster. TFA is showing that you didn't compare apples to apples. Looking at those 12 queries nothing screams at me that TFA's approach of storing the computed tsvector wouldn't work for those too.

Perhaps pg_search scales better and doesn't require trading off update for search performance, and that would be a great selling point, but why not just make that point?

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

#42

Post by cryptonector » Wed, Apr 09, 2025, 5:16 AM UTC

aobdev wrote: post #3

danpalmer wrote: post #2

> Mistake #1: Calculating tsvector On-the-Fly (Major issue) I'm shocked that the original post being referred to made this mistake. I recently implemented Postgres FTS in a personal project, and did so by just reading the Postgres documentation on FTS following the instructions. The docs lead you through the process of creating the base unoptimized case, and then optimising it, explaining the purpose of each step and…

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…

That is definitely an issue. And that seems like a win for pg_search. And as siblings note PG 18 will have virtual generated, indexable columns, so that advantage for pg_search will go away.

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

#43

Post by cryptonector » Wed, Apr 09, 2025, 5:17 AM UTC

aobdev wrote: post #29

codesnik wrote: post #23

Earlier quoted context omitted.

It's not only an additional disk space, it also a need to sync it with main column, using triggers or whatever, and have much bigger backups. Why "initial instinct was to question", though? I don't see downsides still, unless yeah, weighted queries or need to search in joined tables etc.

It's a generated column, so there's no overhead to keep it up to date, but all generated columns in PG are stored. The corpus for text search will be stored 1x in the source column, 1x as a tsvector in the index, and an additional 1x in the generated column if you do so. That's a 50% increase in disk space.

There is still the overhead of updating that extra space.

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

#44

Post by charettes » Wed, Apr 09, 2025, 5:19 AM UTC

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…

Virtual generated columns are not required to allow an index to be used in this case without incurring the cost of materializing `to_tsvector('english', message)`. Postgres supports indexing expressions and the query planner is smart enough to identify candidate on exact matches.

I'm not sure why the author doesn't use them but it's clearly pointed out in the documentation (https://www.postgresql.org/docs/current/textsearch-tables.ht...).

In other words, I believe they didn't need a `message_tsvector` column and creating an index of the form

  CREATE INDEX idx_gin_logs_message_tsvector
  ON benchmark_logs USING GIN (to_tsvector('english', message))
  WITH (fastupdate = off);

would have allowed queries of the form

  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')

to use the `idx_gin_logs_message_tsvector` index without materializing `to_tsvector('english', message)` on disk outside of the index.

Here's a fiddle supporting it https://dbfiddle.uk/aSFjXJWz

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

#45

Post by cryptonector » Wed, Apr 09, 2025, 5:20 AM UTC

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.)

Because PG is a fantastic platform for doing everything you need in SQL and it performs real well. Add PostgREST and you've got a REST API almost for free (you have to design a schema of what to expose, so not entirely free, but still, pretty close). Also, PG moves _fast_ and has a very active developer and user community, which means you'll be getting more awesome functionality in the coming future. (E.g., I've been following the AIO thread and the performance improvements coming from that patch set will be hefty.)

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

#46

Post by timClicks » Wed, Apr 09, 2025, 5:20 AM UTC

danpalmer wrote: post #2

> Mistake #1: Calculating tsvector On-the-Fly (Major issue) I'm shocked that the original post being referred to made this mistake. I recently implemented Postgres FTS in a personal project, and did so by just reading the Postgres documentation on FTS following the instructions. The docs lead you through the process of creating the base unoptimized case, and then optimising it, explaining the purpose of each step and…

Perhaps the most generous interpretation is that the authors were writing an article for people who do the naïve thing without reading the docs. There are quite a few people in that category.

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

#47

Post by cryptonector » Wed, Apr 09, 2025, 5:26 AM UTC

klysm wrote: post #38

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.)

Avoiding distributed systems problems. Distributed systems are so incredibly hard to get right that I will vertically scale postgres until I hit an insurmountable wall before giving in.

You can also build distributed DBs with PG. For example for a DB with multiple write nodes all you need to do is implement an event shipping model with logical replication where your servers publish their events and subscribe to others, and you need to implement conflict resolution rules, naturally. I think PG's type system can probably be leveraged to make a CRDT system on top (and I bet someone's already done it).

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

#48

Post by cryptonector » Wed, Apr 09, 2025, 5:27 AM UTC

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…

This. ORMs by and large suck.

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

#49

Post by throwaway7783 » Wed, Apr 09, 2025, 5:29 AM UTC

sroussey wrote: post #40

simonw wrote: post #14

Earlier quoted context omitted.

I've built a number of systems that run a database and a separate search index (Elasticsearch, Solr, Xapian). The hardest part by far is keeping the search index in sync with the database. I gave a talk about this a while ago: https://simonwillison.net/2017/Aug/16/denormalized-query-eng... Using the search engine built into PostgreSQL, MySQL or SQLite makes this problem SO MUCH less difficult.

Yes. Scale matters here. If it’s just me trying to get shit done, then one system (database, monorepo, whatever) is 100% the best. I’ve done the multiple system thing at scale, and it’s fine if I have the liquidity of engineers to do it and the cost is warranted. Bundling everything into one system will eventually fall apart. But it’s sooo good while you can do it. And I am decades past the point where I introduce ne…

Doing everything in postgresql need not necessarily mean doing it in one database server. We could have different servers meant for search, queues, analytics (columnar) etc, all replicated (pg native replication) from a vanilla transactional postgresql server.

Now applications need only one technology, not necessarily one server.

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

#50

Post by mastax » Wed, Apr 09, 2025, 6:03 AM UTC

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…

MySQL logical replication isn’t quite foolproof but it’s vastly easier than anything PostgreSQL offers out of the box. (I hope I’m wrong!)