4 ms·
Using PostgreSQL for full text search is a bad idea. There is no native support for exact phrase searching "like this;" there are some hacky workarounds but you
by mapgrep 15y ago
Using PostgreSQL for full text search is a bad idea. There is no native support for exact phrase searching "like this;" there are some hacky workarounds but you lose stemming and have to do a scan (http://stackoverflow.com/questions/1489617/how-do-you-do-phrase-based-full-text-search-in-postgres-that-takes-advantage-of http://stackoverflow.com/questions/1489617/how-do-you-do-phr...).
High quality, world class text search is a basic prerequisite for a production web app these days (if your app needs search at all). The idea of keeping your database and search engine data in one silo is really beautiful, conceptually, but at the moment it is better for your users if you swallow the complexity of maintaining a parallel, dedicated full text search index (e.g. like Lucene) alongside your regular db. Relying on postgres for full text search is a three quarters solution, and if you believed in three quarter solutions you would not be using postgres in the first place.
Just my .02.
- michaelbuckbee 15y agoI'm not sure it is a bad idea so much as not the one size fits all solution. It's fast, easy administratively to setup, works on Heroku with no addons, and greatly reduced the gap between data entering our system and data being indexed for search results (an important consideration for our problem). It's not perfect, but it is a definite improvement over our previous separate db + ft engine solution.
- techscruggs 15y ago"High quality, world class text search is a basic prerequisite for a production web app these days" That is a pretty bold statement, that I can't agree with. The right tool for the job can vary dependent on need. Perhaps you are building your MVP or you have a small ops team or search is an admin function or etc ... If search is one of the main components of my site, then no, I'd probably not use Postgres for that. On the other hand, out right dismissing it sounds like a recipe for shaving yaks.
- rbranson 15y agoEh, it's good for basic stuff and getting to MVP, but I tend to agree that if search is a core feature, ElasticSearch or Solr are the way to go.
- einhverfr 15y agoWondering about using the pg_trgm option for this sort of search. Might not be perfect but it might help a lot and as a side effect might suggest where words are misspelled too.
- einhverfr 15y agoguess not.
- einhverfr 15y agoWondering if you can write sprocs that use Lucene in PL/J?