The good and the bad of using Postgresql for full text search in the real world
Team Pleroma is back from FOSDEM and I finally got rid of most of my Tenshi stickers! The meetup was a lot of fun and we did learn a thing or two. One talk I went to see was this one on the state of full text search in current PostgreSQL. Sadly, it didn’t offer any new information to me, but it’s a good primer on the subject.
Overall, it paints a very rosy picture of full text search in PostgreSQL. With the right indexes, you can do efficient and fast full text searches without Elastic or any other additional component. While this is in general true, the devil is in the details. We have been using PostgreSQL for FTS for a while now in Pleroma, so this article is about all the gotchas, pitfalls and caveats that will ruin your search performance and make your users hate you.
(This image is just to grab your attention, what a wall of text, wow.)