任何理由不使用PostgreSQL在Heroku上的内置全文搜索？ [英] Any reason not use PostgreSQL's built-in full text search on Heroku?

问题描述

我准备在Heroku上部署Rails应用程序，需要全文搜索。到目前为止，我一直使用带有Sphinx的MySQL在VPS上运行它。

然而，如果我想在Heroku上使用Sphinx或Solr，我需要支付附加费用。

我注意到PostgreSQL（在Heroku上使用的数据库）具有内置的全文搜索功能。

是否有原因导致我无法使用Postgres的全文搜索？是否比狮身人面像慢或有其他一些主要限制？

解决方案

编辑，2016 - 为什么不是两个？

如果你对Postgres和Lucene感兴趣，为什么不呢？查看Postgres的 ZomboDB 扩展，它将Elasticsearch集成为一流的索引类型。仍然是一个相当早期的项目，但它看起来对我很有前途。

• https://github.com/zombodb/zombodb

（技术上不可用在Heroku上，但仍值得期待。）

---

披露：我是 Websolr 和盆景 Heroku附加组件，所以我的观点有点偏向于Lucene。

我对Postgres全文搜索的看法是，它对于直观的用例非常稳固，但有一些为什么Lucene（以及因此Solr和ElasticSearch）在性能和功能方面都优越。

对于初学者来说， jpountz 提供了一个真正优秀的技术答案，为什么Solr比Postgres快得多？这是值得一读的真正消化。

我也评论过最近的RailsCast插曲比较了Postgres全文搜索与Solr的相对优点和缺点。让我回顾一下：

Postgres的实用优势

• 重新使用现有的服务，而不是设置和维护（或支付）其他服务。


• 远远优于非常慢的SQL LIKE
• 

• 减少数据保持同步的麻烦，因为它们都在同一个数据库中 - 没有与某些外部数据服务API进行应用程序级集成。



Solr（或ElasticSearch）的优点

关闭我的头顶， / b>

• 将您的索引和搜索负载与常规数据库负载分开放大。


• 更灵活的术语分析例如口音正常化，语言词干，N-gram，标记删除...其他很酷的功能，如拼写检查，丰富的内容（例如PDF和Word）提取... ... $ / $ b $ solo / Lucene可以做一切都在 Postgres全文搜索TODO列表就好了。


• 可以在搜索时进行高效定制。


• 可能对常见术语或复杂查询的搜索性能更快。


• 可能效率更高索引性能优于Postgres。

通过将索引与主数据存储解耦，更好地容忍数据模型中的更改容忍

显然，我认为基于Lucene的专用搜索引擎是更好的选择。基本上，您可以将Lucene视为事实上的开源搜索专家库。

但是，如果您唯一的其他选项是 LIKE 运算符，那么Postgres全文搜索就是一个明确的胜利。

I'm preparing to deploy a Rails app on Heroku that requires full text search. Up to now I've been running it on a VPS using MySQL with Sphinx.

However, if I want to use Sphinx or Solr on Heroku, I'd need to pay for an add-on.

I notice that PostgreSQL (the DB used on Heroku) has built-in full text search capability.

Is there a reason I couldn't use Postgres's full-text search? Is it slower than Sphinx or is there some other major limitation?

解决方案

Edit, 2016 — Why not both?

If you're interested in Postgres vs. Lucene, why not both? Check out the ZomboDB extension for Postgres, which integrates Elasticsearch as a first-class index type. Still a fairly early project but it looks really promising to me.

• https://github.com/zombodb/zombodb

(Technically not available on Heroku, but still worth looking at.)

---

Disclosure: I'm a cofounder of the Websolr and Bonsai Heroku add-ons, so my perspective is a bit biased toward Lucene.

My read on Postgres full-text search is that it is pretty solid for straightforward use cases, but there are a number of reasons why Lucene (and thus Solr and ElasticSearch) is superior both in terms of performance and functionality.

For starters, jpountz provides a truly excellent technical answer to the question, Why is Solr so much faster than Postgres? It's worth a couple of reads through to really digest.

I also commented on a recent RailsCast episode comparing relative advantages and disadvantages of Postgres full-text search versus Solr. Let me recap that here:

Pragmatic advantages to Postgres

• Reuse an existing service that you're already running instead of setting up and maintaining (or paying for) something else.
• Far superior to the fantastically slow SQL LIKE operator.
• Less hassle keeping data in sync since it's all in the same database — no application-level integration with some external data service API.

Advantages to Solr (or ElasticSearch)

Off the top of my head, in no particular order…

• Scale your indexing and search load separately from your regular database load.
• More flexible term analysis for things like accent normalizing, linguistic stemming, N-grams, markup removal… Other cool features like spellcheck, "rich content" (e.g., PDF and Word) extraction…
• Solr/Lucene can do everything on the Postgres full-text search TODO list just fine.
• Much better and faster term relevancy ranking, efficiently customizable at search time.
• Probably faster search performance for common terms or complicated queries.
• Probably more efficient indexing performance than Postgres.
• Better tolerance for change in your data model by decoupling indexing from your primary data store

Clearly I think a dedicated search engine based on Lucene is the better option here. Basically, you can think of Lucene as the de facto open source repository of search expertise.

But if your only other option is the LIKE operator, then Postgres full-text search is a definite win.

这篇关于任何理由不使用PostgreSQL在Heroku上的内置全文搜索？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！