9 ms·
Postgres full-text search is Good Enough (2015)
- okket 10y agoPrevious discussions: https://news.ycombinator.com/item?id=8714477 https://news.ycombinator.com/item?id=8714477 (~2 years ago, 58 comments) https://news.ycombinator.com/item?id=8381748 https://news.ycombinator.com/item?id=8381748 (~2 years ago, 95 comments)
- ngrilly 10y agoAnd this one a few days ago: https://news.ycombinator.com/item?id=12605156 https://news.ycombinator.com/item?id=12605156 And this new index type for PostgreSQL: https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- justin_vanw 10y agoThis comes up over and over. For basic utility stuff or simple backend apps it's pretty useful. For anything serious or consumer facing it's crap.
- nostrademons 10y agoTBH if you're looking to do something serious & consumer facing you may want to look into paying for Google's Custom Search API, or if your data set differs dramatically in shape from webpages, hiring a really good data scientist and rolling your own: https://developers.google.com/custom-search/ https://developers.google.com/custom-search/ I'm an ex-Googler who's now working fairly extensively with ElasticSearch. While I've been impressed at how easy it is to get started, all the published academic ranking algorithms are utter crap compared to what Google has. Okapi BM25 is mentioned as the state-of-the-art up-thread; it was developed in the 80s, long before Google was born, and most of the advances in search since the 90s have happened behind closed corporate doors inside Google, Yahoo, and Bing.
- hacknat 10y agoThis is solid advice. At my last company we had SOLR. I think we would have even qualified for what most people would call a "fair" use case of a search stack. Millions of items in the catalog, and millions of users to go along with it. Lucene is light years faster than a SQL engine with such requirements for search. What is it not better at though? Pretty much everything else. GIS, Cross joins, parent child indexing, etc. Postgres can do it all too with a little digging. The thing neither of them has? Semantic search. If I ever hit that scale again at another company, the jump won't be from SQL to Lucene, but from SQL to Google.
- ousta 10y agowell many search companies do semantic search. elastic does. sinequa does as well. IBM does as well.
- rpedela 10y agoYou can do semantic search with Solr and ES, it just isn't built-in. But it has support for stemming and synonyms which are the building blocks. You have create the synonyms file yourself with word2vec or a similar algorithm.
- patsplat 10y agoThis is a great suggestion. If site requires SEO why bother rolling another search engine. Everyone is just going to complain that it isn't google anyways.
- ousta 10y agoits good but google custom search api is a website search engine. it doesnt search anything else.
- rpedela 10y agoHas Google and/or Microsoft made significant advances in keyword relevance algorithms specifically? Google is a combination of keyword relevance + reputation (PageRank) + semantics (word2vec) + AI + other stuff when combined is the best search engine in world. Is the keyword relevance part really that much better than BM25?
- kowdermeister 10y agoWhere would you draw the line in numbers? Twitter clone with 100k users?
- mrweasel 10y agoI don't think it's the number of users necessarily, it's about the expectations. People seem to expect Google quality search behind every search box on the web. The search needs to find the relevant information, first time, every time. So it's more about being able to find what people are looking for, not the number of users you have. And to be fair, almost all search solution currently available will scale beyond what most of us need in terms for number of users or the size of our datasets.
- chdir 10y ago> For anything serious or consumer facing it's crap I've run consumer facing products with real-time autosuggest using postgresql's search. Calling it crap is hyperbole.
- jgord 10y agoCan you describe a use case where postgres performs badly, or why you prefer XYZ ? In my experience, on commercial projects, using 9.4+ postgres GIN tag/keyword search index is very fast over data on the order of 30 to 100 million records, providing you have a host with SSD disks. I call this size data "MID" data, not "BIG" data, and it seems applicable to a lot of startups before they get to a million customers, and for a lot of real world customer data use cases. Also, a lot of startups need to handle gps geo-coordinates .. having used them, I have to say that the primitives that come with postGIS are pretty superb, and easily integrated. Outside startup-land, there are a lot of companies using horrible data systems, dying to get to their data quickly, who could be much better served with postgres via a web front end. ( Lowering the cost/time to build that is something I'm working on ) So if : * you want fast tag search * your data has locations or nearby-x * you'll have < 1bn rows for some time * your data is worth money then imo, postgres+postGIS+GIN+SSD is pretty compelling. That covers a lot of startup and business surface area.
- mrweasel 10y agoIt's a little sad that people seem to forget that Sphinx Search exists. For quick full-text search implementation Sphinx is much easier than trying to spin up and understand Solr or ElasticSearch. For a large number of sites search could be improved greatly by simple setting up Sphinx with an SQL query for slurping up data from you backend database.
- ngrilly 10y agoYes, but how do you make sure your Sphinx index is always synchronized with your SQL database, within a short window (a few seconds), and without to much pressure on the database?
- mrweasel 10y agoThat's a fair point, but most other search solutions have the same issue. If you can't deal with search being a bit behind your actual content, and you want something better than the built in Postgresql full text search, you can live update a sphinx search index. It's a bit more work, but very easy to do. I was mostly thinking of Sphinx as a solution for smallish site, that just need the search box to be working. Even though is works just as well for site like Craigslist (but I don't think they use the SQL data source)
- irrational 10y agoThis. Our data is such that it must be instantly searchable the instant it hits the database.
- ngrilly 10y ago:-) And you use PostgreSQL built-in FTS for that? If yes, do you order by ts_rank or ts_rank_cs? If yes, are the response times ok?
- irrational 10y agoNo, we are on Oracle right now. But we are looking at moving to Postgres and are considering our options.
- buro9 10y agoPlus it's in your database, which means it is trivial to limit the search results by any JOINable criteria, i.e. permissions, roles, groups, users, organisations or anything that you have in your database.
- jpgvm 10y agoAlso because it's your authoritative datastore you don't need to worry about keeping your secondary indexing system (solr/elasticsearch etc) in-sync. Using Postgres for search just from a simplicity point of view can save many engineering hours especially if you don't have an expert in Solr/Elasticsearch available. This is even more true these days with SolrCloud needing a Zookeeper ensemble as well.
- yummyfajitas 10y agoIt's not that hard to keep your secondary indexes up to date with Postgres. You can set up external triggers with LISTEN/NOTIFY; i.e. every time a row is updated, Postgres will inform the index refreshing service that an update is needed. https://www.chrisstucchio.com/blog/2013/postgres_external_triggers.html https://www.chrisstucchio.com/blog/2013/postgres_external_tr...
- gnud 10y agoAnd how do you get back in sync if the client that did the LISTEN, crashes and has to be restarted?
- openasocket 10y agoUsing NOTIFY puts the data into a message queue, so messages will just hold there until your client listens again. Eventually it the channel queue will be filled up (8GB by default), at which point the NOTIFY will fail. https://www.postgresql.org/docs/9.0/static/sql-notify.html https://www.postgresql.org/docs/9.0/static/sql-notify.html
- 10y ago
- k_kelly 10y agoI've done two large search projects in the last year. One with Postgres search and one with elastic search. The elastic search version was trivial to configure, and was embarrassingly superior to the point where I wish I had never considered Postgres.
- twic 10y agoSuperior in what ways?
- rpedela 10y agoWith Elasticsearch and Solr, you can easily customize analysis and scoring. There are several scoring algorithms built into them [1] such as BM25 (the default now) which is considered the state of the art for keyword relevance. For analysis, you can remove stopwords, stem, apply synonyms, etc [2]. Elasticsearch is specifically designed to scale across multiple machines which is necessary for TB datasets. There are also things like "more like this" queries and context-aware spell checking. Some of that you can do with PG, but not all of it. If PG can do it, it is usually harder to set up. 1. https://www.elastic.co/guide/en/elasticsearch/reference/current/index-modules-similarity.html https://www.elastic.co/guide/en/elasticsearch/reference/curr... 2. https://www.elastic.co/guide/en/elasticsearch/guide/current/analysis-intro.html https://www.elastic.co/guide/en/elasticsearch/guide/current/...
- okket 10y agoThe PostgreSQL team is working on some of the weak spots. Please have a look a the new 'RUM' index, that should improve ranking: https://www.pgcon.org/2016/schedule/attachments/436_pgcon-2016-fts.pdf https://www.pgcon.org/2016/schedule/attachments/436_pgcon-20... https://github.com/postgrespro/rum https://github.com/postgrespro/rum
- eva1984 10y agoRich library of tokenizers and analyzers. A test proof analyzer model and pipeline. For full text search, different score modes are supported that is beyond the trivial case mentitioned tf-idf model, how are going to do field centric ranking in postgres? As far as I am concerned, it is far superior to the goodies that mentioned in this article.
- deleted 10y ago[deleted]
- vemv 10y agoEverything is good enough at low-to-moderate scale.
- elchief 10y agoIt really needs TF-IDF or BM25
- donretag 10y agoI was going to post the same thing. As far as I can tell, there are no term or document frequencies in postgres. Then again, I have seen several Lucene implementations with either or both are disabled. Can you even get an explain back from postgres? The main difference between a database and a search engine (a subtype) is the ability to score and rank. Take away the ability to score effectively diminishes the value. BM25 is simply TFIDF with a couple of tunable parameters. These parameters will help with precisely the issues which force people to disable TF (saturating the value early, ...). From what I can see, Elasticsearch is mainly used as an analytics/logging engine and not search. Many NLP heavy organizations are using Solr (or straight Lucene).
- elchief 10y agothere is the smlar extension (written by one of the guys that wrote fts) but I'd prefer native You'd think it wouldn't be that hard seeing as SQLite has lots of working code, and it's reasonably similar to pg.
- amelius 10y agoGood. We need more competition in search.
- mark_l_watson 10y agoI usually choose in favor of using Postgres for RDMS and search, but I like to write a thin abstraction layer in case I later need to change my mind. The overhead (coding wise) for doing this is fairly small and this approach can save having to change a lot of application code later.
- patsplat 10y agoSaying postgres is "good enough" acknowledges that elastic search is "better" but based on minimum viable product requirements may not be necessary. It's a fair point that is mostly valid when approaching a deadline on a fixed bid contract. Given enough sprints, the search engine will eventually be run by an external service. +1 for the Google Custom Search suggestion. If SEO matters to the problem domain, might as well leverage those carefully indexed documents.