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

81–90 of 90 posts

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

#81

Post by sunzhousz »

retakeming wrote: post #39
zhousun wrote: post #10

Glab to see more 'postgres-native' full-text search implementation. Alternative solutions (lucene/ tantivy) are both designed for 'immutable segments' (indexing immutable files), so marrying them with postgres heap table would results in a worse solution.

The segments themselves being immutable doesn't mean that Tantivy is incompatible with Postgres - it just means that Tantivy needs to be made compatible with Postgres' concurrency control mechanisms (MVCC) and storage format (block storage). This blog post explains the latter: https://www.paradedb.com/blog/block_storage_part_one

the fundamental mismatch i saw is "creating a new segment for each individual dml", it is possible to alleviate but i don't think there's a good general solution.

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

#82

Post by therealdrag0 »

brightball wrote: post #17
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…

I’ve been a Postgres FTS advocate for over a decade since replacing a Solr search with it and getting easier maintenance, more flexibility with queries and virtually no difference in speed. It’s pretty great. Elastic is on a different level for a lot of use cases, but pg is more than enough for the vast majority of workloads.

What’s the biggest scale you’ve used Postgres search for?

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

#83

Post by whilenot-dev »

Spivak wrote: post #80
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.

Well you could, with some distributed locking mechanism, but doing that right has its challenges.

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

#84

Post by whilenot-dev »

sgarland wrote: post #18
ketzo wrote: post #11

Earlier quoted context omitted.

There are good reasons mentioned already, but additionally, there’s a real strong cargo cult developing around Postgres these days.

Sorry you’re being downvoted; you are correct. I love Postgres, but devs absolutely flock to it because influencers said to. At a job a while ago, my team put out a poll asking for devs opinions and reasons for their preferred RDBMS. Every single one said Postgres, but no one could elaborate as to why. One said “it’s more flexible,” which is true, but no one there was using ANY of its flexibility. That’s the part tha…

I think you're looking at it from a weird angle...

"Every single one" in your team agreeing on a single specific technological choice is one of the rarest things I can image! Developers argue about libraries, frameworks, programming languages, services, etc., and I think it speaks for itself if Postgres is the thing that comes closest in bridging the gap at least on one layer in the tech stack. Postgres is a "conservative" choice with a very active community and extensible ecosystem.

Also, nobody is ever making use of their technological choice to its full extent, you'd rarely know what you'll need beforehand, and it's just nice not having to add other storage engines when that one feature request steps into your life.

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

#85

Post by charettes »

sgarland wrote: post #76
charettes wrote: post #44

Earlier quoted context omitted.

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.

TIL I wasn't aware MySQL functional indices were implemented using virtual columns [0]

[0] https://dev.mysql.com/doc/refman/8.4/en/create-index.html#cr...

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

#86

Post by stuhood »

lostb1t wrote: post #60
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…

just an fyi: The blog link in your readme does not work.

Thanks for reporting this! I'm having trouble finding the link you are referring to though. Would you mind sharing a link to the file/page containing the dead link?

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

#87

Post by brightball »

sgarland wrote: post #27
brightball wrote: post #21

Earlier quoted context omitted.

I mean, technically any database with triggers can have generated columns, but PostgreSQL has had generated columns since version 13. Current version is 17. https://www.postgresql.org/docs/current/ddl-generated-column... I can’t think of any advantage of a virtual generated column over a generated column for something like a search index where calculating on read would be very slow. Postgres has been able to create i…

The advantage is when you want to store something for ease of use, but don’t want the disk (and memory, since pages read are loaded into the buffer pool) hit. So here, you could precompute the vector and index it, while not taking the double hit on size.

That’s the same benefit in Postgres as creating an index with the result of function.

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

#89

Post by brightball »

therealdrag0 wrote: post #82
brightball wrote: post #17

Earlier quoted context omitted.

I’ve been a Postgres FTS advocate for over a decade since replacing a Solr search with it and getting easier maintenance, more flexibility with queries and virtually no difference in speed. It’s pretty great. Elastic is on a different level for a lot of use cases, but pg is more than enough for the vast majority of workloads.

What’s the biggest scale you’ve used Postgres search for?

A site with about 300,000 users where we were still scaling it vertically.
Post reply on HN