15 ms·

Postgres Full-Text Search: A search engine in a database

601 points by twakefield 5y ago

rattray 5y ago
TBH I hadn't known you could do weighted ranking with Postgres search before. Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases. [0] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb
- craigkerstiens 5y ago
  Zombo is definitely super interesting and we should probably add a bit in the post about it. Part of the goal here was that you can do a LOT with Postgres, without adding one more system to maintain. Zombo is great if you have Elastic around, but want Postgres as the primary interface, but what if you don't want to maintain Elastic. My ideal is always though to start with Postgres, and then see if it can solve my problem. I would never Postgres is the best at everything it can do, but for most things it is good enough without having another system to maintain and wear a pager for.
  - rattray 5y ago
    Zombo does at least promise to handle "complex reindexing processes" for you (which IME can be very painful) but yeah, I assume you'd still have to deal with shard rebalancing, hardware issues, network failures or latency between postgres and elastic, etc etc. The performance and cost implications of Zombo are more salient tradeoffs in my mind – if you want to index one of the main tables in your app, you'll have to wait for a network roundtrip and a multi-node write consensus on every update (~150ms or more[0]), you can't `CREATE INDEX CONCURRENTLY`, etc. All that said, IMO the fact that Zombo exists makes it easier to pitch "hey lets just build search with postgres for now and if we ever need ES's features, we can easily port it to Zombo without rearchitecting our product". [0] https://github.com/zombodb/zombodb/issues/640 https://github.com/zombodb/zombodb/issues/640
    - zombodb 5y ago
      I wonder what the ZomboDB developers are up to now? What great text-search-in-postgres things could they be secretly working on?
rattray 5y ago
Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being able to say, "keep in mind we'll need to budget a 3-mo transition to ES once we need X, Y, or Z".
- kayodelycaon 5y ago
  If I recall correctly, Postgres search doesn't scale well. Not sure where it falls apart but it isn't optimized in the same way something like Solr is.
  - ezekg 5y ago
    I have a table with over a billion rows and most full-text searches still respond in around a few milliseconds. I think this will depend on a lot of factors, such as proper indexing, and filtering down the dataset as much as possible before performing the full-text ops. I've spent a considerable amount of time on optimizing these queries, thanks to tools like PgMustard [0]. Granted, I do still have a couple slow queries (1-10s query time), but that's likely due to very infrequent access i.e. cold cache. I will say, if you use open source libraries like pg_search, you are unlikely to ever have performant full-text search. Most full-text queries need to be written by hand to actually utilize indexes, instead of the query-soup that these types of libraries output. (No offense to the maintainers -- it's just how it be when you create a "general" solution.) [0]: https://pgmustard.com https://pgmustard.com
    - kayodelycaon 5y ago
      Oh cool. I was right and wrong. Thanks!
    - snack-boye 5y ago
      Silly question, I'm using pg right now and most of my queries are something like this (in english) Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days. Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data? Thanks
lettergram 5y ago
I actually built a search engine back in 2018 using postgresql https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... Worked quite well and still use it daily. Basically doing weighted searches on vectors is slower than my approach, but definitely good enough. Currently, I can search around 50m HN & Reddit comments in 200ms on the postgresql running on my machine.
- rattray 5y ago
  Nice – looks like the ~same approach recommended here of adding a generated `tsvector` column with a GIN index and querying it with `col @@ @@ to_tsquery('english', query)`.
  - lettergram 5y ago
    Yeah my internal approach was creating custom vectors which are quicker to search.
- vincnetas 5y ago
  Offtopic, but currious what are your use cases when searching all HN and reddit comments? Im at the beggining of this path, just crawled HN, but what to do with this, still a bit cloudy.
  - lettergram 5y ago
    I built a search engine that quantified the expertise of authors of comments. Then I created what I called “expert rank” that allowed me to build a really good search engine. Super good if you’re at a company or something https://twitter.com/austingwalters/status/1041894765439201281?s=21 https://twitter.com/austingwalters/status/104189476543920128...
    - turbocon 5y ago
      Wow, that lead me down quite a rabbit hole, impressive work.
Grimm1 5y ago
Postgres Full-Text search is a great way to get search running for a lot of standard web applications. I recently used just this in Elixir to set up a simple search by keyword. My only complaint was Ecto (Elixir's query builder library) doesn't have first class support for it and neither does Postgrex the lower level connector they use. Still, using fragments with sanitized SQL wasn't too messy at all.
MushyRoom 5y ago
I was hyped when I found out about it a while ago. Then I wasn't anymore. When you have 12 locales (kr/ru/cn/jp/..) it's not that fun anymore. Especially on a one man project :)
- oauea 5y ago
  Why support so many locales in a one man project?
  - habibur 5y ago
    To attract more visitors/customers I guess. I plan to extend to 10 languages too. 1 man project.
  - MushyRoom 5y ago
    It's just a project to learn all the things that are web. It's mainly a database for a game now with most of the information sourced from the game (including its localization files). I'm slowly transitioning from MariaDB to Postgres - again as a learning experience. There is cool stuff and there is annoying stuff to reproduce things like case-insensitive + ignore accents (utf8_general_ci) in Postgres. I've looked into FTS and searching for missing dictionaries to support all the locales but Chinese is one of the harder ones.
  - blacktriangle 5y ago
    One (dev) project here, we're up to 5 locales at a surprisingly small number of customers. Problem is when your customers are global, all of a sudden a single customer can bring along multiple locales. I very much regret not taking localization far more seriously early in development but we were blindsided by the interest outside the Angleosphere.
- freewizard 5y ago
  For small project and simple full text search requirement, try this generic parser: https://github.com/freewizard/pg_cjk_parser https://github.com/freewizard/pg_cjk_parser
bityard 5y ago
I know Postgres and SQLite have mostly different purposes but FWIW, SQLite also has a surprisingly capable full-text search extension built right in: https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
- jjice 5y ago
  It's very impressive, especially considering the SQLite version you're already using probably has it enabled already. I use it for a small site I run and it works fantastic. Little finicky with deletes and updates due to virtual tables in SQLite, but definitely impressive and has its uses.
SigmundA 5y ago
Keep wondering if RUM Indexes [1] will ever get merged for faster and better ranking (TF/IDF). Really would make PG a much more complete text search engine. https://github.com/postgrespro/rum https://github.com/postgrespro/rum
theandrewbailey 5y ago
> You could also look into enabling extensions such as unaccent (remove diacritic signs from lexemes) or pg_trgm (for fuzzy search). Trigrams (pg_trgm) are practically needed for usable search when it comes to misspellings and compound words (e.g. a search for "down loads" won't return "downloads"). I also recommend using websearch_to_tsquery instead of using the cryptic syntax of to_tsquery.
- kyrra 5y ago
  Trigrams are amazing. I was doing a sideproject where I wanted to allow for substring searching, and trigrams seemed to be the only way to do it (easily/well) in postgres. Gitlab did a great writeup on this a few years ago that really helped me understand it: https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/ https://about.gitlab.com/blog/2016/03/18/fast-search-using-p... You can also always read the official docs: https://www.postgresql.org/docs/current/pgtrgm.html https://www.postgresql.org/docs/current/pgtrgm.html
syoc 5y ago
My worst search experiences always come from the features applauded here. Word stemming and removing stop words is a big hurdle when you know what you are looking for but get flooded by noise because some part of the search string was ignored. Another issue is having to type out a full word before you get a hit in dynamic search boxes (looking at you Confluence).
- Someone1234 5y ago
  I'd argue that isn't a problem with the feature, but a thoughtless implementation. A good implementation will weigh verbatim results highest before considering the stop-word stripped or stemmed version. Configuring to_tsvector() to not strip stop words or using a stemming dictionary is, in my opinion, a little clunky in Postgres: You'll want to make a new [language] dictionary and then call to_tsvector() using your new dictionary as the first parameter. After you've set up the dictionary globally, this would look something like: setweight(to_tsvector('english_no_stem_stop', col), 'A') || setweight(to_tsvector('english', col), 'B')) I think blaming Postgres for adding stemming/stop-word support because it can be [ab]used for a poor search user experience is like blaming a hammer for a poorly built home. It is just a tool, it can be used for good or evil. PS - You can do a verbatim search without using to_tsvector(), but that cannot be easily passed into setweight() and you cannot use features like ts_rank().
pvsukale3 5y ago
If you are using Rails with Postgres you can use pg_search gem to build the named scopes to take advantage of full text search. https://github.com/Casecommons/pg_search https://github.com/Casecommons/pg_search
deleted 5y ago
[deleted]
jcuenod 5y ago
Huh, just yesterday I blogged[0] about using FTS in SQLite[1] to search my PDF database. SQLite's full-text search is really excellent. The thing that tripped me up for a while was `GROUP BY` with the `snippet`/`highlight` function but that's the point of the blog post. [0] https://jcuenod.github.io/bibletech/2021/07/26/full-text-search-for-pdfs/ https://jcuenod.github.io/bibletech/2021/07/26/full-text-sea... [1] https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html
- edwinyzh 5y ago
  A very well written article about SQLite FTS5! One question - it seems that your search result displays the matching paging number, how did you do that? because as far as I know, unlike FTS4, FTS5 has no `offsets` function.
  - jcuenod 5y ago
    I index each page individually. It's not in the article (because I also don't explain separating out the metadata) but I did weigh the options. I considered indexing page pairs, for example (and having every page indexed twice). But I figured that if search terms are broken across pages, they're likely to also be separated by text in headers & footers, page numbers, and footnotes. So in the end I decided to just index individual pages. The FTS table now has `id`, `page_number`, and `content` columns (where `id` is the foreign key to a table that stores the metadata).
- bob1029 5y ago
  We've been using SQLite's FTS capabilities to index customer log files since 2017 or so. It's been a wonderful approach for us. Even if we move to our own in-house data store (event sourced log), we would still continue using SQLite for tracing because it brings so many of these sorts of benefits.
tabbott 5y ago
Zulip's search is powered by this built-in Postgres full-text search feature, and it's been a fantastic experience. There's a few things I love about it: * One can cheaply compose full-text search with other search operators by just doing normal joins on database indexes, which means we can cheaply and performantly support tons of useful operators (https://zulip.com/help/search-for-messages https://zulip.com/help/search-for-messages). * We don't have to build a pipeline to synchronize data between the real database and the search database. Being a chat product, a lot of the things users search for are things that changed recently; so lag, races, and inconsistencies are important to avoid. With the Postgres full-text search, all one needs to do is commit database transactions as usual, and we know that all future searches will return correct results. * We don't have to operate, manage, and scale a separate service just to support search. And neither do the thousands of self-hosted Zulip installations. Responding to the "Scaling bottleneck" concerns in comments below, one can send search traffic (which is fundamentally read-only) to a replica, with much less complexity than a dedicated search service. Doing fancy scoring pipelines is a good reason to use a specialized search service over the Postgres feature. I should also mention that a weakness of Postgres full-text search is that it only supports doing stemming for one language. The excellent PGroonga extension (https://pgroonga.github.io/ https://pgroonga.github.io/) supports search in all languages; it's a huge improvement especially for character-based languages like Japanese. We're planning to migrate Zulip to using it by default; right now it's available as an option. More details are available here: https://zulip.readthedocs.io/en/latest/subsystems/full-text-search.html https://zulip.readthedocs.io/en/latest/subsystems/full-text-...
- brightball 5y ago
  All of this. It’s such a good operational experience that I will actively fight against the introduction of a dedicated search tool unless it’s absolutely necessary.
- stavros 5y ago
  I cannot tell you how much I love Zulip, but I can tell you that I have no friends any more because everyone is tired of me evangelizing it.
thom 5y ago
We get really nice results with gist indexes (gist_trgm_ops) searching across multiple entity types to do top X queries. It’s very useful to be able to make a stab at a difficult-to-spell foreign football player’s name, possibly with lots of diacritics, and get quick results back. I’m always surprised when I find a search engine on any site that is so unkind as to make you spell things exactly.
simonw 5y ago
The Django ORM includes support for PostgreSQL search and I've found it a really productive way to add search to a project: https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/search/ https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/s...
mrinterweb 5y ago
I've seen Elasticsearch set up for applications that would have equal benefit from just using the postgresql db's full-text search they already have access to. The additional complexity is usually incurred when the data in postgresql changes, and those changes need to be mirrored up to Elasticsearch. Elasticsearch obviously has its uses, but for some cases, postgresql's built in full-text search can make more sense.
shakascchen 5y ago
No fun doing it for Chinese, especially for traditional Chinese. I had to install software but on Cloud SQL you can't. You have to do it on your instances.
- justusw 5y ago
  Same story for performing searches in Japanese.
eric4smith 5y ago
Postgres FTS is normally quite good. But it does not know how to deal with languages like Chinese, Japanese and Thai. For that you have to use something like PGroonga extension. The rest of PostgreSQL mostly handles things ok, unless you try to sort on one of these languages and the same things happen again. There are all ways around these problems. But it’s not as easy as turning on Unicode and just expect everything to work! Yes I’m native English speaker who started to develop in Asia and discovered all of this recently.
nuker 5y ago
Is there alternative to ES that scales nicely? I'm running ELK stack for logging using AWS Elasticsearch. Logs have unpredictable traffic volume and even overprovisioned ES cluster gets clogged sometimes. I wonder is there something more scalable than ES, and have nice GUI like Kibana?
- shard972 5y ago
  loki
- jillesvangurp 5y ago
  It's more a matter of configuring it right. I'd recommend trying out Elastic Cloud. It's a bit easier to deal with than Amazon's offering and much better supported. AWS has always been a bit hands-off on that front. Their opensearch project does not seem to break that pattern so far. Also, with Elastic Cloud you get some access to useful features for logging (like life cycle management and data streams) that will help you scale the setup. Kibana in recent iterations has actually improved quite a bit. The version you are getting from Amazon is probably a bit bare bones in comparison. One nice thing with Elastic is that going with the defaults gets you some useful dashboards out of the box if you use e.g. file or docker beats for collecting logs.
  - nuker 5y ago
    Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :) > you use e.g. file or docker beats for collecting logs. My setup is custom app reading from CW Logs. But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?
    - jillesvangurp 5y ago
      Autoscaling is of course something they can do: https://www.elastic.co/guide/en/cloud/current/ec-autoscaling.html https://www.elastic.co/guide/en/cloud/current/ec-autoscaling... If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to). Basically keeping your hot indices small helps keeping things fast. If you have issues with app writes spiking, I'd use some queuing solution in between. Plenty of solutions for that. The rest is just a matter of configuring things right in terms of number of shards and setting up properly. Basically, you get what you pay for in the end.
kureikain 5y ago
I used Postgres full-text search for mail log feature on my email forward app https://hanami.run https://hanami.run Essentially allow arbitraty query in from/to/subject/body. One thing that make full-text serch work great for me is that I don't need to sort or rank the relevant of query. I just show a list of email that match the query order by their id. I also don't do pagination and counting, instead users has to load more paged and the ID of the email is pass to the query as a point to compare( where id < requests.get.before). And with those strategy, full text search works great for us since we don't really want to bring in ElasticSearch because only about 20% of users use this features.

601 points by twakefield 5y ago

TBH I hadn't known you could do weighted ranking with Postgres search before. Curious there's no mention of zombodb[0] though, which gives you the full power of elasticsearch from within postgres (with consistency no, less!). You have to be willing to tolerate slow writes, of course, so using postgres' built-in search functionality still makes sense for a lot of cases. [0] https://github.com/zombodb/zombodb https://github.com/zombodb/zombodb

craigkerstiens 5y ago
Zombo is definitely super interesting and we should probably add a bit in the post about it. Part of the goal here was that you can do a LOT with Postgres, without adding one more system to maintain. Zombo is great if you have Elastic around, but want Postgres as the primary interface, but what if you don't want to maintain Elastic. My ideal is always though to start with Postgres, and then see if it can solve my problem. I would never Postgres is the best at everything it can do, but for most things it is good enough without having another system to maintain and wear a pager for.
- rattray 5y ago
  Zombo does at least promise to handle "complex reindexing processes" for you (which IME can be very painful) but yeah, I assume you'd still have to deal with shard rebalancing, hardware issues, network failures or latency between postgres and elastic, etc etc. The performance and cost implications of Zombo are more salient tradeoffs in my mind – if you want to index one of the main tables in your app, you'll have to wait for a network roundtrip and a multi-node write consensus on every update (~150ms or more[0]), you can't `CREATE INDEX CONCURRENTLY`, etc. All that said, IMO the fact that Zombo exists makes it easier to pitch "hey lets just build search with postgres for now and if we ever need ES's features, we can easily port it to Zombo without rearchitecting our product". [0] https://github.com/zombodb/zombodb/issues/640 https://github.com/zombodb/zombodb/issues/640
  - zombodb 5y ago
    I wonder what the ZomboDB developers are up to now? What great text-search-in-postgres things could they be secretly working on?

rattray 5y ago

Something that's missing from this which I'm curious about is how far can't postgres search take you? That is, what tends to be the "killer feature" that makes teams groan and set up Elasticsearch because you just can't do it in Postgres and your business needs it? Having dealt with ES, I'd really like to avoid the operational burden if possible, but I wouldn't want to choose an intermediary solution without being able to say, "keep in mind we'll need to budget a 3-mo transition to ES once we need X, Y, or Z".

kayodelycaon 5y ago
If I recall correctly, Postgres search doesn't scale well. Not sure where it falls apart but it isn't optimized in the same way something like Solr is.
- ezekg 5y ago
  I have a table with over a billion rows and most full-text searches still respond in around a few milliseconds. I think this will depend on a lot of factors, such as proper indexing, and filtering down the dataset as much as possible before performing the full-text ops. I've spent a considerable amount of time on optimizing these queries, thanks to tools like PgMustard [0]. Granted, I do still have a couple slow queries (1-10s query time), but that's likely due to very infrequent access i.e. cold cache. I will say, if you use open source libraries like pg_search, you are unlikely to ever have performant full-text search. Most full-text queries need to be written by hand to actually utilize indexes, instead of the query-soup that these types of libraries output. (No offense to the maintainers -- it's just how it be when you create a "general" solution.) [0]: https://pgmustard.com https://pgmustard.com
  - kayodelycaon 5y ago
    Oh cool. I was right and wrong. Thanks!
  - snack-boye 5y ago
    Silly question, I'm using pg right now and most of my queries are something like this (in english) Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days. Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data? Thanks

lettergram 5y ago

I actually built a search engine back in 2018 using postgresql https://austingwalters.com/fast-full-text-search-in-postgresql/ https://austingwalters.com/fast-full-text-search-in-postgres... Worked quite well and still use it daily. Basically doing weighted searches on vectors is slower than my approach, but definitely good enough. Currently, I can search around 50m HN & Reddit comments in 200ms on the postgresql running on my machine.

rattray 5y ago
Nice – looks like the ~same approach recommended here of adding a generated `tsvector` column with a GIN index and querying it with `col @@ @@ to_tsquery('english', query)`.
- lettergram 5y ago
  Yeah my internal approach was creating custom vectors which are quicker to search.
vincnetas 5y ago
Offtopic, but currious what are your use cases when searching all HN and reddit comments? Im at the beggining of this path, just crawled HN, but what to do with this, still a bit cloudy.
- lettergram 5y ago
  I built a search engine that quantified the expertise of authors of comments. Then I created what I called “expert rank” that allowed me to build a really good search engine. Super good if you’re at a company or something https://twitter.com/austingwalters/status/1041894765439201281?s=21 https://twitter.com/austingwalters/status/104189476543920128...
  - turbocon 5y ago
    Wow, that lead me down quite a rabbit hole, impressive work.

Grimm1 5y ago

Postgres Full-Text search is a great way to get search running for a lot of standard web applications. I recently used just this in Elixir to set up a simple search by keyword. My only complaint was Ecto (Elixir's query builder library) doesn't have first class support for it and neither does Postgrex the lower level connector they use. Still, using fragments with sanitized SQL wasn't too messy at all.

MushyRoom 5y ago

I was hyped when I found out about it a while ago. Then I wasn't anymore. When you have 12 locales (kr/ru/cn/jp/..) it's not that fun anymore. Especially on a one man project :)

oauea 5y ago
Why support so many locales in a one man project?
- habibur 5y ago
  To attract more visitors/customers I guess. I plan to extend to 10 languages too. 1 man project.
- MushyRoom 5y ago
  It's just a project to learn all the things that are web. It's mainly a database for a game now with most of the information sourced from the game (including its localization files). I'm slowly transitioning from MariaDB to Postgres - again as a learning experience. There is cool stuff and there is annoying stuff to reproduce things like case-insensitive + ignore accents (utf8_general_ci) in Postgres. I've looked into FTS and searching for missing dictionaries to support all the locales but Chinese is one of the harder ones.
- blacktriangle 5y ago
  One (dev) project here, we're up to 5 locales at a surprisingly small number of customers. Problem is when your customers are global, all of a sudden a single customer can bring along multiple locales. I very much regret not taking localization far more seriously early in development but we were blindsided by the interest outside the Angleosphere.
freewizard 5y ago
For small project and simple full text search requirement, try this generic parser: https://github.com/freewizard/pg_cjk_parser https://github.com/freewizard/pg_cjk_parser

bityard 5y ago

I know Postgres and SQLite have mostly different purposes but FWIW, SQLite also has a surprisingly capable full-text search extension built right in: https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html

jjice 5y ago
It's very impressive, especially considering the SQLite version you're already using probably has it enabled already. I use it for a small site I run and it works fantastic. Little finicky with deletes and updates due to virtual tables in SQLite, but definitely impressive and has its uses.

SigmundA 5y ago

Keep wondering if RUM Indexes [1] will ever get merged for faster and better ranking (TF/IDF). Really would make PG a much more complete text search engine. https://github.com/postgrespro/rum https://github.com/postgrespro/rum

theandrewbailey 5y ago

> You could also look into enabling extensions such as unaccent (remove diacritic signs from lexemes) or pg_trgm (for fuzzy search). Trigrams (pg_trgm) are practically needed for usable search when it comes to misspellings and compound words (e.g. a search for "down loads" won't return "downloads"). I also recommend using websearch_to_tsquery instead of using the cryptic syntax of to_tsquery.

kyrra 5y ago
Trigrams are amazing. I was doing a sideproject where I wanted to allow for substring searching, and trigrams seemed to be the only way to do it (easily/well) in postgres. Gitlab did a great writeup on this a few years ago that really helped me understand it: https://about.gitlab.com/blog/2016/03/18/fast-search-using-postgresql-trigram-indexes/ https://about.gitlab.com/blog/2016/03/18/fast-search-using-p... You can also always read the official docs: https://www.postgresql.org/docs/current/pgtrgm.html https://www.postgresql.org/docs/current/pgtrgm.html

syoc 5y ago

My worst search experiences always come from the features applauded here. Word stemming and removing stop words is a big hurdle when you know what you are looking for but get flooded by noise because some part of the search string was ignored. Another issue is having to type out a full word before you get a hit in dynamic search boxes (looking at you Confluence).

Someone1234 5y ago
I'd argue that isn't a problem with the feature, but a thoughtless implementation. A good implementation will weigh verbatim results highest before considering the stop-word stripped or stemmed version. Configuring to_tsvector() to not strip stop words or using a stemming dictionary is, in my opinion, a little clunky in Postgres: You'll want to make a new [language] dictionary and then call to_tsvector() using your new dictionary as the first parameter. After you've set up the dictionary globally, this would look something like: setweight(to_tsvector('english_no_stem_stop', col), 'A') || setweight(to_tsvector('english', col), 'B')) I think blaming Postgres for adding stemming/stop-word support because it can be [ab]used for a poor search user experience is like blaming a hammer for a poorly built home. It is just a tool, it can be used for good or evil. PS - You can do a verbatim search without using to_tsvector(), but that cannot be easily passed into setweight() and you cannot use features like ts_rank().

pvsukale3 5y ago

If you are using Rails with Postgres you can use pg_search gem to build the named scopes to take advantage of full text search. https://github.com/Casecommons/pg_search https://github.com/Casecommons/pg_search

deleted 5y ago

[deleted]

jcuenod 5y ago

Huh, just yesterday I blogged[0] about using FTS in SQLite[1] to search my PDF database. SQLite's full-text search is really excellent. The thing that tripped me up for a while was `GROUP BY` with the `snippet`/`highlight` function but that's the point of the blog post. [0] https://jcuenod.github.io/bibletech/2021/07/26/full-text-search-for-pdfs/ https://jcuenod.github.io/bibletech/2021/07/26/full-text-sea... [1] https://www.sqlite.org/fts5.html https://www.sqlite.org/fts5.html

edwinyzh 5y ago
A very well written article about SQLite FTS5! One question - it seems that your search result displays the matching paging number, how did you do that? because as far as I know, unlike FTS4, FTS5 has no `offsets` function.
- jcuenod 5y ago
  I index each page individually. It's not in the article (because I also don't explain separating out the metadata) but I did weigh the options. I considered indexing page pairs, for example (and having every page indexed twice). But I figured that if search terms are broken across pages, they're likely to also be separated by text in headers & footers, page numbers, and footnotes. So in the end I decided to just index individual pages. The FTS table now has `id`, `page_number`, and `content` columns (where `id` is the foreign key to a table that stores the metadata).
bob1029 5y ago
We've been using SQLite's FTS capabilities to index customer log files since 2017 or so. It's been a wonderful approach for us. Even if we move to our own in-house data store (event sourced log), we would still continue using SQLite for tracing because it brings so many of these sorts of benefits.

tabbott 5y ago

Zulip's search is powered by this built-in Postgres full-text search feature, and it's been a fantastic experience. There's a few things I love about it: * One can cheaply compose full-text search with other search operators by just doing normal joins on database indexes, which means we can cheaply and performantly support tons of useful operators (https://zulip.com/help/search-for-messages https://zulip.com/help/search-for-messages). * We don't have to build a pipeline to synchronize data between the real database and the search database. Being a chat product, a lot of the things users search for are things that changed recently; so lag, races, and inconsistencies are important to avoid. With the Postgres full-text search, all one needs to do is commit database transactions as usual, and we know that all future searches will return correct results. * We don't have to operate, manage, and scale a separate service just to support search. And neither do the thousands of self-hosted Zulip installations. Responding to the "Scaling bottleneck" concerns in comments below, one can send search traffic (which is fundamentally read-only) to a replica, with much less complexity than a dedicated search service. Doing fancy scoring pipelines is a good reason to use a specialized search service over the Postgres feature. I should also mention that a weakness of Postgres full-text search is that it only supports doing stemming for one language. The excellent PGroonga extension (https://pgroonga.github.io/ https://pgroonga.github.io/) supports search in all languages; it's a huge improvement especially for character-based languages like Japanese. We're planning to migrate Zulip to using it by default; right now it's available as an option. More details are available here: https://zulip.readthedocs.io/en/latest/subsystems/full-text-search.html https://zulip.readthedocs.io/en/latest/subsystems/full-text-...

brightball 5y ago
All of this. It’s such a good operational experience that I will actively fight against the introduction of a dedicated search tool unless it’s absolutely necessary.
stavros 5y ago
I cannot tell you how much I love Zulip, but I can tell you that I have no friends any more because everyone is tired of me evangelizing it.

thom 5y ago

We get really nice results with gist indexes (gist_trgm_ops) searching across multiple entity types to do top X queries. It’s very useful to be able to make a stab at a difficult-to-spell foreign football player’s name, possibly with lots of diacritics, and get quick results back. I’m always surprised when I find a search engine on any site that is so unkind as to make you spell things exactly.

simonw 5y ago

The Django ORM includes support for PostgreSQL search and I've found it a really productive way to add search to a project: https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/search/ https://docs.djangoproject.com/en/3.2/ref/contrib/postgres/s...

mrinterweb 5y ago

I've seen Elasticsearch set up for applications that would have equal benefit from just using the postgresql db's full-text search they already have access to. The additional complexity is usually incurred when the data in postgresql changes, and those changes need to be mirrored up to Elasticsearch. Elasticsearch obviously has its uses, but for some cases, postgresql's built in full-text search can make more sense.

shakascchen 5y ago

No fun doing it for Chinese, especially for traditional Chinese. I had to install software but on Cloud SQL you can't. You have to do it on your instances.

justusw 5y ago
Same story for performing searches in Japanese.

eric4smith 5y ago

Postgres FTS is normally quite good. But it does not know how to deal with languages like Chinese, Japanese and Thai. For that you have to use something like PGroonga extension. The rest of PostgreSQL mostly handles things ok, unless you try to sort on one of these languages and the same things happen again. There are all ways around these problems. But it’s not as easy as turning on Unicode and just expect everything to work! Yes I’m native English speaker who started to develop in Asia and discovered all of this recently.

nuker 5y ago

Is there alternative to ES that scales nicely? I'm running ELK stack for logging using AWS Elasticsearch. Logs have unpredictable traffic volume and even overprovisioned ES cluster gets clogged sometimes. I wonder is there something more scalable than ES, and have nice GUI like Kibana?

shard972 5y ago
loki
jillesvangurp 5y ago
It's more a matter of configuring it right. I'd recommend trying out Elastic Cloud. It's a bit easier to deal with than Amazon's offering and much better supported. AWS has always been a bit hands-off on that front. Their opensearch project does not seem to break that pattern so far. Also, with Elastic Cloud you get some access to useful features for logging (like life cycle management and data streams) that will help you scale the setup. Kibana in recent iterations has actually improved quite a bit. The version you are getting from Amazon is probably a bit bare bones in comparison. One nice thing with Elastic is that going with the defaults gets you some useful dashboards out of the box if you use e.g. file or docker beats for collecting logs.
- nuker 5y ago
  Thanks mate. I did tried Elastic before settling on AWS ES, it was slower somehow. As for Kibana features, im happy with barebones :) > you use e.g. file or docker beats for collecting logs. My setup is custom app reading from CW Logs. But can Elastic cluster scale automatically on indexing latency spikes, so my apps writes do not time out? If yes then how, please?
  - jillesvangurp 5y ago
    Autoscaling is of course something they can do: https://www.elastic.co/guide/en/cloud/current/ec-autoscaling.html https://www.elastic.co/guide/en/cloud/current/ec-autoscaling... If you use life cycle management and data streams (which AWS doesn't have), you'd be able to control the sizes of your hot indices (i.e. the ones you write to). Basically keeping your hot indices small helps keeping things fast. If you have issues with app writes spiking, I'd use some queuing solution in between. Plenty of solutions for that. The rest is just a matter of configuring things right in terms of number of shards and setting up properly. Basically, you get what you pay for in the end.

kureikain 5y ago

I used Postgres full-text search for mail log feature on my email forward app https://hanami.run https://hanami.run Essentially allow arbitraty query in from/to/subject/body. One thing that make full-text serch work great for me is that I don't need to sort or rank the relevant of query. I just show a list of email that match the query order by their id. I also don't do pagination and counting, instead users has to load more paged and the ID of the email is pass to the query as a point to compare( where id < requests.get.before). And with those strategy, full text search works great for us since we don't really want to bring in ElasticSearch because only about 20% of users use this features.