In PostgreSQL, powerful Full Text Search is available out of the box
1–10 of 20 posts
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#2I often see complicated search solutions implemented when PostgreSQL is just sitting there with its incredible FTS powers being ignored. To help some of the teams I'm working with get an idea of the power available to them I wrote up this two part article with a github repo for spinning up a db to follow along.
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#3I use it; it is indeed awesome, although the “powerful” version of the query DSL can be intimidating (I implemented some complex-ish regex transformations to utilize it).
It’s fantastic having 1 less dependency though!
One caveat- if you migrate any field in the indexed table, you will likely have to drop and recreate all your triggers and stored procs again
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#4PostgreSQL FTS is mostly great - I wrote a tutorial on using it to build faceted search with Django a few years ago: https://simonwillison.net/2017/Oct/5/django-postgresql-facet...
It does have one surprising limitation: it calculates relevance based on just the current row, rather than being able to take statistics across the whole corpus into account.
Most search engines use TF/IDF or BM25 for relevance calculations, which consider the relative common-ness of terms in comparison to the rest of the corpus. PostgreSQL FTS can't do that as far as I know.
SQLite's built-in FTS CAN do relevance calculations like this! Surprising to see a feature as significant as that show up in SQLite but not in PostgreSQL.
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#5PostgreSQL FTS is mostly great - I wrote a tutorial on using it to build faceted search with Django a few years ago: https://simonwillison.net/2017/Oct/5/django-postgresql-facet... It does have one surprising limitation: it calculates relevance based on just the current row, rather than being able to take statistics across the whole corpus into account. Most search engines use TF/IDF or BM25 for relevance calculation…
I hope someone implements BM25 and combines it with Pgvector to bring hybrid search to Postgres. I feel like that is the jsonb of the next couple of years.
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#6Great article and underlying point. Reminds me of https://www.amazingcto.com/postgres-for-everything/ which also highlights FTS by linking to this article: https://supabase.com/blog/postgres-full-text-search-vs-the-r...
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#7The only problem I face is partial querying. For instance, if the user queries rus (and it does not match any lexemme) nothing returns. Any workarounds?
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#8The example here only covers short text fields like titles — does anyone know if it performant for full body search of long documents?
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#9I read these articles and am always tempted, but I've found they often don't do well with Chinese, Japanese, Korean or other non-latin languages. I understand that isn't the focus for most people but it's very hard to find good data/information on how to best support these languages.
Re: In PostgreSQL, powerful Full Text Search is available out of the box
#10The only problem I face is partial querying. For instance, if the user queries rus (and it does not match any lexemme) nothing returns. Any workarounds?
I've had good results for partial match searching using PostgreSQL trigram indexes: https://about.gitlab.com/blog/2016/03/18/fast-search-using-p...