4 ms·

Is anyone actually utilising a recent version of PostgreSQL for full-text searching beyond a hobby project? How do you find the speed and accuracy versus Elasti

by Aeyris 10y ago

Is anyone actually utilising a recent version of PostgreSQL for full-text searching beyond a hobby project? How do you find the speed and accuracy versus Elasticsearch?

flukus 10y ago
This comes from experience with SQL Server, not postgres and lucene instead of elastic search, but no, I don't think full-text search replaces lucene/elastic for any but the simplest searches. Especially if you want to rank results based on a number of factors, not just a text match. The last time I was using them for instance, we wanted geographic location to be weighted strongly and the database approach did not under performed on result speed, result accuracy and developer time (excluding learning lucene, which isn't that hard anyway).
- saurik 10y ago
  PostgreSQL is pretty unique as an RDBMS in its ability to do multi-facet weighted search over disparate types including geographical information as part of a single index query (due to GiST indices): there is a reason why the GIS community has essentially standardized around PostgreSQL (via PostGIS); I do not think it works to use experience from SQL Server to comment on PostgreSQL for this use case.
  - flukus 10y ago
    Interesting, thanks. Is query performance on par with Lucerne? How about indexing cost/latency?
mwpmaybe 10y ago
http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou...
mrmondo 10y ago
Yes we've used full text search for quite some time for one of our products, it's great, we really haven't had any issues performance or otherwise with it. For one of our other products we index data from PostgreSQL / PostGIS into elasticsearch and that's also good, in other ways.
- Aeyris 10y ago
  At a guess stemming from ignorance of Elasticsearch, I'm going to assume you'd join PostgreSQL and Elasticsearch like that in situations where you need to perform more complex analysis over the queries to provide results, is this correct? I've seen this particular architecture before and my biggest question was whether or not it was a piece that could be eliminated. For, say, a tag-based search system (think any forum or booru-style imageboard), is Elasticsearch completely overkill? I realise I could probably just google it™, but it's a lot easier to understand a product's strengths when they're put in a situational context.
  - dguaraglia 10y ago
    Your guess is pretty much it: Postgres full text search is great for simpler scenarios, but elasticsearch provides more powerful search capabilities and simpler implementation of complex features (such as "search all shops in this geographical area with products that match these words the user typed".) That said, I've had good results with both systems, and always found the elasticsearch query syntax pretty arcane and difficult to put together from the docs. The docs essentially assume you are very familiar with Lucene concepts and that you'll be able to figure out what goes where in the JSON object from your past experience. If you are implementing a simple tag system, then I'd use Postgres first and only when performance becomes an issue move to elasticsearch.
renesd 10y ago
Used it for 10,000+ document and 1 million document projects. Was good enough for me (but I guess I wouldn't have written the article if it was bad). I've also used Elasticsearch, and I reckon that's pretty damn amazing. Anyone wanting more in-depth information should read or watch this FTS presentation from last year. It's by some of the people who has done a lot of work on the implementation, and talks about 9.6 improvements, current problems, and things we might expect to see in version 10. https://www.pgcon.org/2016/schedule/events/926.en.html https://www.pgcon.org/2016/schedule/events/926.en.html There's also some previous presentations on the same topic which are interesting. You can see the RUM index (which has faster ranking here): https://github.com/postgrespro/rum https://github.com/postgrespro/rum
combatentropy 10y ago
For our intranet's collection of tens of thousands of pages, results come back in a fraction of a second. Accuracy seems as good as the ten-year-old Google Mini Search Appliance that it replaced, but I had to write a good chunk of SQL. I weigh whether the terms are in the web address, title, or just the body, and also the number of backlinks. This took making a few tables, views, and SQL functions, as well as a daily cron job.
einhverfr 10y ago
I worked on a huge (12TB) bioinformatic project which was using PostgreSQL full text search as a primary component. One would compare with Solr which also was looked at as a possible replacement. Solr had more functionality (largely by being able to throw more nodes at the problem), but for our purposes, PostgreSQL's fts was certainly good enough. There were a few cases where we had to be careful so the full text index would be used (because data distribution did not always match the planner's assumptions) but on the whole it worked very well.
Beltiras 10y ago
Several years ago I was responsible for a CMS for a local newspapers website. A contractor making updates used ElasticSearch to search for articles. It was a rather botched setup with ES crashing once in a while and the search index sliding out of sync with the database. I was using Django and rewrote the search just simply collecting a dictionary of terms that I would then explode into filter on the model. A database of order 100k articles with something like 50 fields, about 20 of them foreign keys, bulk text fields of several to tens of KB responded within milliseconds on a 4 core 4GB machine under load. The "weird thing" about it that I didn't understand at the time was that using a more complex query would often result in speedups. I understand it better now after this article since PG builds some sort of index on text fields. PG is one of the infrastructure pieces that you install and then it just runs performantly without much fuss. There probably are cases where it fails horribly but in almost a decade of use I have yet to see a single operations failure.

by Aeyris 10y ago

Is anyone actually utilising a recent version of PostgreSQL for full-text searching beyond a hobby project? How do you find the speed and accuracy versus Elasticsearch?

flukus 10y ago

This comes from experience with SQL Server, not postgres and lucene instead of elastic search, but no, I don't think full-text search replaces lucene/elastic for any but the simplest searches. Especially if you want to rank results based on a number of factors, not just a text match. The last time I was using them for instance, we wanted geographic location to be weighted strongly and the database approach did not under performed on result speed, result accuracy and developer time (excluding learning lucene, which isn't that hard anyway).

saurik 10y ago
PostgreSQL is pretty unique as an RDBMS in its ability to do multi-facet weighted search over disparate types including geographical information as part of a single index query (due to GiST indices): there is a reason why the GIS community has essentially standardized around PostgreSQL (via PostGIS); I do not think it works to use experience from SQL Server to comment on PostgreSQL for this use case.
- flukus 10y ago
  Interesting, thanks. Is query performance on par with Lucerne? How about indexing cost/latency?

mwpmaybe 10y ago

http://rachbelaid.com/postgres-full-text-search-is-good-enough/ http://rachbelaid.com/postgres-full-text-search-is-good-enou...

mrmondo 10y ago

Yes we've used full text search for quite some time for one of our products, it's great, we really haven't had any issues performance or otherwise with it. For one of our other products we index data from PostgreSQL / PostGIS into elasticsearch and that's also good, in other ways.

Aeyris 10y ago
At a guess stemming from ignorance of Elasticsearch, I'm going to assume you'd join PostgreSQL and Elasticsearch like that in situations where you need to perform more complex analysis over the queries to provide results, is this correct? I've seen this particular architecture before and my biggest question was whether or not it was a piece that could be eliminated. For, say, a tag-based search system (think any forum or booru-style imageboard), is Elasticsearch completely overkill? I realise I could probably just google it™, but it's a lot easier to understand a product's strengths when they're put in a situational context.
- dguaraglia 10y ago
  Your guess is pretty much it: Postgres full text search is great for simpler scenarios, but elasticsearch provides more powerful search capabilities and simpler implementation of complex features (such as "search all shops in this geographical area with products that match these words the user typed".) That said, I've had good results with both systems, and always found the elasticsearch query syntax pretty arcane and difficult to put together from the docs. The docs essentially assume you are very familiar with Lucene concepts and that you'll be able to figure out what goes where in the JSON object from your past experience. If you are implementing a simple tag system, then I'd use Postgres first and only when performance becomes an issue move to elasticsearch.

renesd 10y ago

Used it for 10,000+ document and 1 million document projects. Was good enough for me (but I guess I wouldn't have written the article if it was bad). I've also used Elasticsearch, and I reckon that's pretty damn amazing. Anyone wanting more in-depth information should read or watch this FTS presentation from last year. It's by some of the people who has done a lot of work on the implementation, and talks about 9.6 improvements, current problems, and things we might expect to see in version 10. https://www.pgcon.org/2016/schedule/events/926.en.html https://www.pgcon.org/2016/schedule/events/926.en.html There's also some previous presentations on the same topic which are interesting. You can see the RUM index (which has faster ranking here): https://github.com/postgrespro/rum https://github.com/postgrespro/rum

combatentropy 10y ago

For our intranet's collection of tens of thousands of pages, results come back in a fraction of a second. Accuracy seems as good as the ten-year-old Google Mini Search Appliance that it replaced, but I had to write a good chunk of SQL. I weigh whether the terms are in the web address, title, or just the body, and also the number of backlinks. This took making a few tables, views, and SQL functions, as well as a daily cron job.

einhverfr 10y ago

I worked on a huge (12TB) bioinformatic project which was using PostgreSQL full text search as a primary component. One would compare with Solr which also was looked at as a possible replacement. Solr had more functionality (largely by being able to throw more nodes at the problem), but for our purposes, PostgreSQL's fts was certainly good enough. There were a few cases where we had to be careful so the full text index would be used (because data distribution did not always match the planner's assumptions) but on the whole it worked very well.

Beltiras 10y ago

Several years ago I was responsible for a CMS for a local newspapers website. A contractor making updates used ElasticSearch to search for articles. It was a rather botched setup with ES crashing once in a while and the search index sliding out of sync with the database. I was using Django and rewrote the search just simply collecting a dictionary of terms that I would then explode into filter on the model. A database of order 100k articles with something like 50 fields, about 20 of them foreign keys, bulk text fields of several to tens of KB responded within milliseconds on a 4 core 4GB machine under load. The "weird thing" about it that I didn't understand at the time was that using a more complex query would often result in speedups. I understand it better now after this article since PG builds some sort of index on text fields. PG is one of the infrastructure pieces that you install and then it just runs performantly without much fuss. There probably are cases where it fails horribly but in almost a decade of use I have yet to see a single operations failure.