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

We used es or solr for those cases. For English FTS with 100k documents doing it in PG is super easy and one less dependency

Edit: if any of the Crunchy Data people are reading this: support for RUM indexes would be super cool to have in your managed service.

My company relied on PG as its search engine and everything went well from POC to production. After a few years of production and new clients requiring volumes of data an order of magnitude above our comfort zone, things went south pretty fast.

Not many months later but many sweaty weeks of engineering after, we switched to ES and we're not looking back.

tl;dr; even with great DB engineers (which we had), I'd suggest that scale is a strong limiting factor on this feature.

There are in my mind two reasons to not use PG's search.

1. Elasticsearch allows you to build sophisticated linguistic and feature scoring pipelines to optimize your search quality. This is not a typical use case in PG.

2. Your primary database is usually your scaling bottleneck even without adding a relatively expensive search workload into the mix. A full-text search tends to be around as expensive as a 5% table scan of the related table. Most DBAs don't like large scan workloads.

If migrating to ES makes you groan you can use a managed service like Pinecone[3] (disclaimer: I work there) just for storing and searching through text embeddings in-memory through an API while keeping the rest of your data in PG.

[1] Nearest-neighbor searches in Open Distro: https://opendistro.github.io/for-elasticsearch-docs/docs/knn...

[2] More on how semantic similarity is measured: https://www.pinecone.io/learn/semantic-search/

[3] https://www.pinecone.io

that's where it cant be used for anything serious.

https://github.com/zombodb/zombodb

It basically gives you a new kind of index (create index .. using zombodb(..) ..)

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.

1. PostgreSQL has a cap on column length, and the search index has to be stored in a column. The length of the column is indeterminate - it is storing every word in the document and where it's located, so a short document with very many unique words (numbers are treated as words too) can easily burst the cap. This means you have to truncate each document before indexing it, and pray that your cap is set low enough. You can use multiple columns but that slows down search and makes ranking a lot more complicated. I truncate documents at 4MB.

2. PostgreSQL supports custom dictionaries for specific languages, stemmers, and other nice tricks, but none of those are supported by AWS because the dictionary gets stored as a file on the filesystem (it's not a config setting). You can still have custom rules like whether or not numbers count as words.

That said, simonw has been on the case[2] demonstrating an implementation of that using Django and PostgreSQL.

[1] - https://en.wikipedia.org/wiki/Faceted_search

[2] - https://simonwillison.net/2017/Oct/5/django-postgresql-facet...

A few years ago we added yet-another part to our product and, whilst ES worked "okay", we got a bit weary of ES due to "some issues" (some bug in the architecture keeping things not perfect in sync, certain queries with "joins" of types taking long, demand on HW due to the size of database, no proper multi-node setup due to $$$ and time constraint, etc.; small things piling up over time).

Bright idea: let's see how far Postgres, which is our primary datastore, can take us!

Unfortunately, the feature never made it fully into production.

We thought that on paper, the basic requirements were ideal:

- although the table has multiple hundreds of millions of entries, natural segmentation by customer IDs made possible individual results much smaller

- no weighted search result needed: datetime based is perfect enough for this use-case, we thought it would be easy to come up with the "perfect index [tm]"

Alas, we didn't even get that far:

- we identified ("only") 2 columns necessary for the search => "yay, easy"

- one of those columns was multi-language; though we didn't have specific requirements and did not have to deal with language specific behaviour in ES, we had to decide on one for the TS vectorization (details elude me why "simple" wasn't appropriate for this one column; it was certainly for the other one)

- unsure which one, or both, we would need, for one of the columns we created both indices (difference being the "language")

- we started out with a GIN index (see https://www.postgresql.org/docs/9.6/textsearch-indexes.html )

- creating a single index took > 15 hours

But once the second index was done, and had not even rolled out the feature in the app itself (which at this point was still an ever changing MVP), unrelated we suddenly got hit by lot of customer complains that totally different operations on this table (INSERTs and UPDATEs) started to be getting slow (like 5-15 seconds slow, something which usually takes tiny ms).

Backend developer eyes were wide open O_O

But since we knew that second index just finished, after checking the Posgres logs we decided to drop the FTS indices and, lo' and behold, "performance problem solved".

Communication lines were very short back then (still are today, actually) and it was promptly decided we just cut the search functionality from this new part of the product and be done with it. This also solved the problem, basically (guess there's some "business lesson" to be learned here too, not just technical ones).

Since no one within the company counter argued this decision, we did not spend more time analyzing the details of the performance issue though I would have loved to dig into this and get an expert on board to dissect this.

--

A year later or so I had a bit free time and analyzed one annoying recurring slow UPDATE query problem on a completely different table, but also involving FTS on a single column there also using a GIN index. That's when I stumble over https://www.postgresql.org/docs/9.6/gin-implementation.html

> Updating a GIN index tends to be slow because of the intrinsic nature of inverted indexes: inserting or updating one heap row can cause many inserts into the index (one for each key extracted from the indexed item). As of PostgreSQL 8.4, GIN is capable of postponing much of this work by inserting new tuples into a temporary, unsorted list of pending entries. When the table is vacuumed or autoanalyzed, or when gin_clean_pending_list function is called, or if the pending list becomes larger than gin_pending_list_limit, the entries are moved to the main GIN data structure using the same bulk insert techniques used during initial index creation. This greatly improves GIN index update speed, even counting the additional vacuum overhead. Moreover the overhead work can be done by a background process instead of in foreground query processing.

In this particular case I was able to solve the occasional slow UPDATE queries with "FASTUPDATE=OFF" on that table and, thinking back about the other issue, it might have solved or minimized the impact.

Back to the original story: yep, this one table can have "peaks" of inserts but it's far from "facebook scale" or whatever, basically 1.5k inserts / second were the absolute rare peak I measured and usually it's in the <500 area. But I guess it was enough for this scenario to add latency within the database.

--

Turning back my memory further, I was always "pro" trying to minimize / get rid of ES after learning about http://rachbelaid.com/postgres-full-text-search-is-good-enou... even before we used any FTS feature. At also mentions the GIN/GiST issue but alas, in our case: ElasticSearch is good enough and, besides the thwarts we've with it, actually easier to reason about (so far).

The big problem with all the off-the-shelf search solutions (RDBMS full-text search, ES, Algolia) is that search ranking is a complicated and subtle problem, and frequently depends on signals that are not in the document itself. Google's big insight is that how other people talk about a website is more important than how the website talks about itself, and its ranking algorithm weights accordingly.

ES has the basic building blocks to construct such a ranking algorithm. In terms of fundamental infrastructure I found ES to be just as good as Google, and better in some ways. But its out-of-the-box ranking function sucks. Expect to put a domain expert just on search ranking and evaluation to get decent results, and they're going to have to delve pretty deeply into advanced features of ES to get there.

AFAICT Postgres search only lets you tweak the ranking algorithm by assigning different weights to fields, assuming that the final document score is a linear combination of individual fields. This is usually not what you want - it's pretty common to have non-linear terms from different signals.

Actually, it is possible, but doing a search on a particular segment of rows is a very slow operation - say text search for all employees with name matching 'x', in organization id 'y'.

It is not able to utilise the index on organization id in this case, and it results in a full scan.

However search tech is pretty mature with Lucene at the core and there are many better options [2] from in-process libraries to simple standalone servers to full distributed systems like Elastic. There are also other databases (relational like MemSQL, or documentstores like MongoDB/RavenDB) that are adding search as native querying functions with most of the abilities of ES. If search is a core or complex part of your application (like patterns in raw image data or similarities in audio waveforms) then that's where ES will excel.

1. https://stackoverflow.com/questions/46122175/fulltext-search...

2. https://gist.github.com/manigandham/58320ddb24fed654b57b4ba2...

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

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.

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

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.

(No full text search though, you have to define indexed labels from which to start your search when ingesting)