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)

#71

Post by sgarland » Wed, Apr 09, 2025, 12:02 PM UTC

mastax wrote: post #50

sgarland wrote: post #15

Earlier quoted context omitted.

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

I think they’re about the same in complexity, other than that Postgres offers more options. MySQL did have logical replication long before Postgres, so I’ll give it that.

Postgres has one option for replication that is a godsend, though: copy_data. This lets you stand up a new replica without having to first do a dump / restore (assuming your tables are small enough / your disk is large enough, since the primary will be holding WAL during the initial sync). Tbf, MySQL doesn’t need that as much, because it offers parallel dump and restore, even on a single table.

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

#72

Post by ahoka » Wed, Apr 09, 2025, 12:42 PM UTC

charettes wrote: post #44

sgarland wrote: post #15

Earlier quoted context omitted.

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/te…

I had the same question when reading the article, why not just index the expression?

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

#73

Post by moogleii » Wed, Apr 09, 2025, 12:54 PM UTC

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…

There is an art to communicating that I think people learn around their college years...

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

#74

Post by hobs » Wed, Apr 09, 2025, 1:05 PM UTC

xmcqdpt2 wrote: post #67

bmelton wrote: post #22

Earlier quoted context omitted.

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 app…

Hah, that's table stakes - I have definitely worked at companies with 100 or 1000x the database to engineer ratio.

Depending on your database system, it may even have a 1:1 equivalency with Schemas (MySQL.)

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

#75

Post by hobs » Wed, Apr 09, 2025, 1:06 PM 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.

People ask me - "but can we just distribute it because everything in one basket makes me uneasy"

Yeah distributing state among 10 nodes, totally easy, fine, good.

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

#76

Post by sgarland » Wed, Apr 09, 2025, 1:30 PM UTC

charettes wrote: post #44

sgarland wrote: post #15

Earlier quoted context omitted.

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/te…

You are correct, I missed that. In MySQL, functional indices are implemented as invisible generated virtual columns (and there is no vector index type supported yet that I'm aware of), but Postgres has a more capable approach.

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

#77

Post by hylaride » Wed, Apr 09, 2025, 1:39 PM 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.)

When you're starting something new and the amounts of data is still small, it's often better early on to focus on the product than optimizing for theoretical performance optimization that may never pan out (either because the project will fail or that the bottlenecks may ultimately not be what you thought or expected).

At my current gig, we used to shove everything (including binary data) into postgres because it was easy and all our code plugged into it anyways. When it started to become uneconomical (mostly due to RDS storage costs), we then started shunting data to S3, DynamoDB, etc.

Also, not everybody can be on a cloud with easy access to all the fancy products for queuing, caching, etc. Sometimes it's better overall to have to deal with one complex beast (that you'd have to maintain anyways) than spending time deploying Kafka, MongoDB, etc (even though it can sometimes be easier than ever with pre-built manifests for K8s) as well as securing and keeping them all up to date.

I do strongly encourage people to treat code that deals with these things with as much abstraction as possible to make migrations easier later on, though.

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

#78

Post by some_developer » Wed, Apr 09, 2025, 1:52 PM UTC

Years ago I wanted to use native FTS (because of tall the things mentioned, having to sync to external simply adds complexity) and it failed at another point.

Not completely surprising, but on a table with _potentially_ couple of thousand of inserts / seconds, it slowed down the overall updates to the point that transactions timed out.

We already added an index for one of the columns we wanted to index and were running the statement for the second one. The moment this the second index finished, we started to see timeouts from our system when writing to that table, transaction failing etc.

We had to drop the indices again. So, sadly, we did never get to the point to test the actual FTS performance :/ I would have like to test this, because didn't necessarily had to search hundreds of millions of documents, due to customer tenants this would always be constrained to a few million _at most_.

ps: I already wrote about this -> https://news.ycombinator.com/item?id=27977526 . Never got a chance to try it nowadays (newer versions of everything, never hardware, etc.)

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

#79

Post by thr0w » Wed, Apr 09, 2025, 2:30 PM UTC

some_developer wrote: post #78

Years ago I wanted to use native FTS (because of tall the things mentioned, having to sync to external simply adds complexity) and it failed at another point. Not completely surprising, but on a table with _potentially_ couple of thousand of inserts / seconds, it slowed down the overall updates to the point that transactions timed out. We already added an index for one of the columns we wanted to index and were runni…

Sounds like the issue was just co-location of search index + other transactional data in the same table. If you had a table acting as your search index only then would insert lag on that table matter? I could maybe see connections piling up, but with proper batching I bet it'd be fine.

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

#80

Post by Spivak » Wed, Apr 09, 2025, 3:01 PM 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.)

All these replies have me so confused, the reason to shove everything into your database when you can is because you can transact across them. That's thing you can't get once you have a second system.