PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
blog.vectorchord.ai
PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
1–10 of 90 posts
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#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 why it's faster. It's really clear that is what it's doing, and I could only assume that someone making this mistake is either doing so to intentionally misrepresent Postgres FTS, or because they haven't read the basic documentation.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#3> 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 not stored in the index, e.g. queries with weighted text or queries against a lossy GiST index. It's going to be use-case dependent but I would check if your queries need this before using up the additional disk space.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#4Off topic but this kind of content marketing is excellent for any startup that tries to get its name out compared with a well-known competitor, like it is the case here with ParadeDB.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#5When it's custom code that runs on PG I don't call it native.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#6I'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.)
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#7I'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.)
PostgreSQL hasn't let me down in 20+ years. It's not perfect but it's really damn good for all your data cases (may require tuning)
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#8I'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.)
Maintaining a new service sucks. Not being able to do atomic commits to both postgres and the other db sucks.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#9I'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.)
Why not? Pareto principle. It’ll get you 80% of the way there for most things… then when you need a highly optimized solution you pivot to that.
Re: PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)
#10Glab 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.