7 ms·

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

427 points by VoVAllen 1y ago

danpalmer 1y ago
> 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.
- aobdev 1y ago
  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.
  - sgarland 1y ago
    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 benchmarks, and even when designing schema to exploit a clustered index, Postgres was able to keep up in performance. EDIT: the other thing MySQL does much better than Postgres is “just working” for people who are neither familiar with nor wish to learn RDBMS care and feeding. Contrary to what the hyperscalers will tell you, DBs are special snowflakes, they have a million knobs to turn, and they require you to know what you’re doing to some extent. Postgres especially has the problem of table bloat and txid buildup from its MVCC implementation, combined with inadequate autovacuum. I feel like the docs should scream at you to tune your autovacuum settings on a per-table basis once you get to a certain scale (not even that big; a few hundred GB on a write-heavy table will do). MySQL does not have this problem, and will happily go years on stock settings without really needing much from you. It won’t run optimally, but it’ll run. I wouldn’t say the same about Postgres.
    - senorrib 1y ago
      That’s just syntax sugar for a trigger. Not really a big advantage.
    - VoVAllen 1y ago
      It's coming in the Postgres 18. https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-virtual-generated-columns/ https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-...
      - sgarland 1y ago
        Yes (very exciting!), but you won’t be able to index them, and that’s really where they shine, IMO. Still, I’m sure they’ll get there. Maybe they’ll also eventually get invisible columns, though tbf that’s less of a problem for Postgres as it is for MySQL, given the latter’s limited data types.
        danielheath 1y ago
        You can index arbitrary expressions, though, including indexing the same expression used to define the invisible column, right?
    - brightball 1y ago
      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-columns.html 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 indexes based on the output of functions forever though, which does the job here too.
      - sgarland 1y ago
        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.
  - codesnik 1y ago
    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.
    - aobdev 1y ago
      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.
      - cryptonector 1y ago
        There is still the overhead of updating that extra space.
  - cryptonector 1y ago
    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.
- brightball 1y ago
  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.
  - therealdrag0 1y ago
    What’s the biggest scale you’ve used Postgres search for?
    - brightball 1y ago
      A site with about 300,000 users where we were still scaling it vertically.
- DoctorOW 1y ago
  > 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. Small writing note, I probably would've swapped the order of those. Hanlon's Razor and all. :)
- timClicks 1y ago
  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.
- fnord123 1y ago
  > 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. vibe sysadminning, bro
Sytten 1y ago
Off 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.
Thaxll 1y ago
When it's custom code that runs on PG I don't call it native.
- eqvinox 1y ago
  Only the ranking function at the end of TFA is custom. Everything until there is standard postgres.
nostrebored 1y ago
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.)
- edoceo 1y ago
  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)
- NavinF 1y ago
  Maintaining a new service sucks. Not being able to do atomic commits to both postgres and the other db sucks.
- whalesalad 1y ago
  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.
- ketzo 1y ago
  There are good reasons mentioned already, but additionally, there’s a real strong cargo cult developing around Postgres these days.
  - sgarland 1y ago
    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 that baffles me. You’ve selected a DB with native support for esoteric but useful data types like INET (stop storing IP addresses as strings in dotted quad!), and a whole host of index types beyond B+tree, but they’re never using them. Read your RDBMS docs, people. They’re full of interesting tidbits.
zhousun 1y ago
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.
- retakeming 1y ago
  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 https://www.paradedb.com/blog/block_storage_part_one
  - sunzhousz 1y ago
    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.
unit149 1y ago
[dead]
nattaylor 1y ago
I wish there were some explain plans in either post, since I don't get what's going on. If the query uses the index, then the on the fly tsvector rechecks are only on the matches and the benchmark queries have LIMIT 10, so few rechecks right? Edit: yes but the query predicates have conditions on 2 gin indexes, so I guess the planner chooses to recheck all the matches for one index first even though it could avoid lots of work by rechecking row-wise
lamp_book 1y ago
> 10M log entries I don’t think the question is speed, it’s scale. Use it until it breaks, though.
retakeming 1y ago
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.html#TEXTSEARCH-TABLES-INDEX 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 query" it's "how do I bring Postgres to Elastic-level performance across a wide range of real-world boolean, fuzzy, faceted, relevance-ranked, etc. queries?" `pg_search` is designed to solve the latter problem, and the benchmarks were made to reflect that. 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: - Created composite b-tree indexes for each of the queries with boolean predicates - Extracted the all the text fields from JSONBs, stored and indexed them as a separate columns for queries against JSONB But that's not realistic for many real-world use cases. `pg_search` doesn't require that - it's a simple index definition that works for a variety of "Elastic style" queries and Postgres types and doesn't ask the user to duplicate every text column.
- cryptonector 1y ago
  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?
chenhoey1211 1y ago
I’ve seen a lot of teams jump straight to Elasticsearch or Meilisearch without realizing how much performance you can get out of native PG FTS when used properly. could we get similar performance in the browser using something like SQLite + FTS5 + Wasm? Seems like an interesting direction for offline-first apps...
ltbarcly3 1y ago
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.
atemerev 1y ago
10 million records is a toy dataset. Usually, you can fit it in memory on a laptop. There are open large(-ish) text datasets like full Wikipedia or pre-2022 Reddit comments, that would work much better for benchmarking.
Vonng 1y ago
BTW for anyone who interested, I've packed pg_search and vchord_bm25 extension RPM/DEBs https://pigsty.io/ext/fts/vchord_bm25 https://pigsty.io/ext/fts/vchord_bm25 https://pigsty.io/ext/fts/pg_search https://pigsty.io/ext/fts/pg_search In case anyone want to benchmark by themselves. ;)
some_developer 1y ago
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 https://news.ycombinator.com/item?id=27977526 . Never got a chance to try it nowadays (newer versions of everything, never hardware, etc.)
- thr0w 1y ago
  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.
apps4datr 1y ago
[dead]
johnthescott 1y ago
for text search we use the "rum" extension from postgrespro. search terabytes of pdfs < 1sec. a talk here https://github.com/jmscott/talk/blob/master/pgday-austin-20161112.pdf

427 points by VoVAllen 1y ago

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

aobdev 1y ago
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.
- sgarland 1y ago
  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 benchmarks, and even when designing schema to exploit a clustered index, Postgres was able to keep up in performance. EDIT: the other thing MySQL does much better than Postgres is “just working” for people who are neither familiar with nor wish to learn RDBMS care and feeding. Contrary to what the hyperscalers will tell you, DBs are special snowflakes, they have a million knobs to turn, and they require you to know what you’re doing to some extent. Postgres especially has the problem of table bloat and txid buildup from its MVCC implementation, combined with inadequate autovacuum. I feel like the docs should scream at you to tune your autovacuum settings on a per-table basis once you get to a certain scale (not even that big; a few hundred GB on a write-heavy table will do). MySQL does not have this problem, and will happily go years on stock settings without really needing much from you. It won’t run optimally, but it’ll run. I wouldn’t say the same about Postgres.
  - senorrib 1y ago
    That’s just syntax sugar for a trigger. Not really a big advantage.
  - VoVAllen 1y ago
    It's coming in the Postgres 18. https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-virtual-generated-columns/ https://www.depesz.com/2025/02/28/waiting-for-postgresql-18-...
    - sgarland 1y ago
      Yes (very exciting!), but you won’t be able to index them, and that’s really where they shine, IMO. Still, I’m sure they’ll get there. Maybe they’ll also eventually get invisible columns, though tbf that’s less of a problem for Postgres as it is for MySQL, given the latter’s limited data types.
      - danielheath 1y ago
        You can index arbitrary expressions, though, including indexing the same expression used to define the invisible column, right?
  - brightball 1y ago
    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-columns.html 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 indexes based on the output of functions forever though, which does the job here too.
    - sgarland 1y ago
      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.
- codesnik 1y ago
  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.
  - aobdev 1y ago
    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.
    - cryptonector 1y ago
      There is still the overhead of updating that extra space.
- cryptonector 1y ago
  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.
brightball 1y ago
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.
- therealdrag0 1y ago
  What’s the biggest scale you’ve used Postgres search for?
  - brightball 1y ago
    A site with about 300,000 users where we were still scaling it vertically.
DoctorOW 1y ago
> 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. Small writing note, I probably would've swapped the order of those. Hanlon's Razor and all. :)
timClicks 1y ago
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.
fnord123 1y ago
> 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. vibe sysadminning, bro

Sytten 1y ago

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

Thaxll 1y ago

When it's custom code that runs on PG I don't call it native.

eqvinox 1y ago
Only the ranking function at the end of TFA is custom. Everything until there is standard postgres.

nostrebored 1y ago

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

edoceo 1y ago
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)
NavinF 1y ago
Maintaining a new service sucks. Not being able to do atomic commits to both postgres and the other db sucks.
whalesalad 1y ago
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.
ketzo 1y ago
There are good reasons mentioned already, but additionally, there’s a real strong cargo cult developing around Postgres these days.
- sgarland 1y ago
  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 that baffles me. You’ve selected a DB with native support for esoteric but useful data types like INET (stop storing IP addresses as strings in dotted quad!), and a whole host of index types beyond B+tree, but they’re never using them. Read your RDBMS docs, people. They’re full of interesting tidbits.

zhousun 1y ago

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.

retakeming 1y ago
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 https://www.paradedb.com/blog/block_storage_part_one
- sunzhousz 1y ago
  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.

unit149 1y ago

[dead]

nattaylor 1y ago

I wish there were some explain plans in either post, since I don't get what's going on. If the query uses the index, then the on the fly tsvector rechecks are only on the matches and the benchmark queries have LIMIT 10, so few rechecks right? Edit: yes but the query predicates have conditions on 2 gin indexes, so I guess the planner chooses to recheck all the matches for one index first even though it could avoid lots of work by rechecking row-wise

lamp_book 1y ago

> 10M log entries I don’t think the question is speed, it’s scale. Use it until it breaks, though.

retakeming 1y ago

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.html#TEXTSEARCH-TABLES-INDEX 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 query" it's "how do I bring Postgres to Elastic-level performance across a wide range of real-world boolean, fuzzy, faceted, relevance-ranked, etc. queries?" `pg_search` is designed to solve the latter problem, and the benchmarks were made to reflect that. 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: - Created composite b-tree indexes for each of the queries with boolean predicates - Extracted the all the text fields from JSONBs, stored and indexed them as a separate columns for queries against JSONB But that's not realistic for many real-world use cases. `pg_search` doesn't require that - it's a simple index definition that works for a variety of "Elastic style" queries and Postgres types and doesn't ask the user to duplicate every text column.

cryptonector 1y ago
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?

chenhoey1211 1y ago

I’ve seen a lot of teams jump straight to Elasticsearch or Meilisearch without realizing how much performance you can get out of native PG FTS when used properly. could we get similar performance in the browser using something like SQLite + FTS5 + Wasm? Seems like an interesting direction for offline-first apps...

ltbarcly3 1y ago

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.

atemerev 1y ago

10 million records is a toy dataset. Usually, you can fit it in memory on a laptop. There are open large(-ish) text datasets like full Wikipedia or pre-2022 Reddit comments, that would work much better for benchmarking.

Vonng 1y ago

BTW for anyone who interested, I've packed pg_search and vchord_bm25 extension RPM/DEBs https://pigsty.io/ext/fts/vchord_bm25 https://pigsty.io/ext/fts/vchord_bm25 https://pigsty.io/ext/fts/pg_search https://pigsty.io/ext/fts/pg_search In case anyone want to benchmark by themselves. ;)

some_developer 1y ago

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 https://news.ycombinator.com/item?id=27977526 . Never got a chance to try it nowadays (newer versions of everything, never hardware, etc.)

thr0w 1y ago
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.

apps4datr 1y ago

[dead]

johnthescott 1y ago

for text search we use the "rum" extension from postgrespro. search terabytes of pdfs < 1sec. a talk here https://github.com/jmscott/talk/blob/master/pgday-austin-20161112.pdf