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

I'm not well versed on modern pg for this use, but when I managed a Solr instance ~5 years ago, it was the ranking of the results that was the killer feature. Finding results fast most systems can do. Knowing which results to present is harder.

Our case was a domain specific knowledge base, with certain terms occurring often in many articles. Searching for a term could bring up thousands of results, but few of them were actually relevant to show in context of the search, they just happened to use the term.

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

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

> sophisticated linguistic and feature scoring pipelines to optimize your search quality

You CAN score results using setweight, although it's likely not as sophisticated as Elasticsearch's

https://www.postgresql.org/docs/9.1/textsearch-controls.html

Disclaimer: I use Postgres fulltext search in production, very happy with it although maintaining the various triggers and stored procs it requires to work becomes cumbersome whenever you have to write a migration that alters any of them (or that in fact touches any related field, as you may be required to drop and recreate all of the parts in order not to violate referential integrity)

It is certainly nice having not to worry about 1 additional dependency when deploying, though

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