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

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

Zulip&#x27;s search is powered by this built-in Postgres full-text search feature, and it&#x27;s been a fantastic experience. There&#x27;s a few things I love about it:<p>* 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 (<a href="https:&#x2F;&#x2F;zulip.com&#x2F;help&#x2F;search-for-messages" rel="nofollow">https:&#x2F;&#x2F;zulip.com&#x2F;help&#x2F;search-for-messages</a>).<p>* We don&#x27;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.<p>* We don&#x27;t have to operate, manage, and scale a separate service just to support search. And neither do the thousands of self-hosted Zulip installations.<p>Responding to the &quot;Scaling bottleneck&quot; 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.<p>Doing fancy scoring pipelines is a good reason to use a specialized search service over the Postgres feature.<p>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 (<a href="https:&#x2F;&#x2F;pgroonga.github.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;pgroonga.github.io&#x2F;</a>) supports search in all languages; it&#x27;s a huge improvement especially for character-based languages like Japanese. We&#x27;re planning to migrate Zulip to using it by default; right now it&#x27;s available as an option.<p>More details are available here: <a href="https:&#x2F;&#x2F;zulip.readthedocs.io&#x2F;en&#x2F;latest&#x2F;subsystems&#x2F;full-text-search.html" rel="nofollow">https:&#x2F;&#x2F;zulip.readthedocs.io&#x2F;en&#x2F;latest&#x2F;subsystems&#x2F;full-text-...</a>

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

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

I know Postgres and SQLite have mostly different purposes but FWIW, SQLite also has a surprisingly capable full-text search extension built right in: <a href="https:&#x2F;&#x2F;www.sqlite.org&#x2F;fts5.html" rel="nofollow">https:&#x2F;&#x2F;www.sqlite.org&#x2F;fts5.html</a>

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

The Django ORM includes support for PostgreSQL search and I&#x27;ve found it a really productive way to add search to a project: <a href="https:&#x2F;&#x2F;docs.djangoproject.com&#x2F;en&#x2F;3.2&#x2F;ref&#x2F;contrib&#x2F;postgres&#x2F;search&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.djangoproject.com&#x2F;en&#x2F;3.2&#x2F;ref&#x2F;contrib&#x2F;postgres&#x2F;s...</a>

Keep wondering if RUM Indexes [1] will ever get merged for faster and better ranking (TF&#x2F;IDF). Really would make PG a much more complete text search engine.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;postgrespro&#x2F;rum" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;postgrespro&#x2F;rum</a>

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&#x27;s query builder library) doesn&#x27;t have first class support for it and neither does Postgrex the lower level connector they use. Still, using fragments with sanitized SQL wasn&#x27;t too messy at all.

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.

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

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

I was hyped when I found out about it a while ago. Then I wasn&#x27;t anymore.<p>When you have 12 locales (kr&#x2F;ru&#x2F;cn&#x2F;jp&#x2F;..) it&#x27;s not that fun anymore. Especially on a one man project :)

I used Postgres full-text search for mail log feature on my email forward app <a href="https:&#x2F;&#x2F;hanami.run" rel="nofollow">https:&#x2F;&#x2F;hanami.run</a><p>Essentially allow arbitraty query in from&#x2F;to&#x2F;subject&#x2F;body. One thing that make full-text serch work great for me is that I don&#x27;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.<p>I also don&#x27;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 &lt; requests.get.before).<p>And with those strategy, full text search works great for us since we don&#x27;t really want to bring in ElasticSearch because only about 20% of users use this features.

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.<p><a href="https:&#x2F;&#x2F;github.com&#x2F;Casecommons&#x2F;pg_search" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;Casecommons&#x2F;pg_search</a>

No fun doing it for Chinese, especially for traditional Chinese.<p>I had to install software but on Cloud SQL you can&#x27;t. You have to do it on your instances.

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

I&#x27;ve seen Elasticsearch set up for applications that would have equal benefit from just using the postgresql db&#x27;s full-text search they already have access to.<p>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&#x27;s built in full-text search can make more sense.

Is there alternative to ES that scales nicely? I&#x27;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?