Full text search in milliseconds with PostgreSQL

<i>To get optimum performance with PostgreSQL full text search you need to create a column to store the tsvector values with an index on it. Then you need to fill this column with the tsv values and create a trigger to update the field on INSERT and UPDATE.</i><p>I&#x27;ve been playing around with full text search in Postgres, and I took this sort of approach when starting out, but then realized you I could just have the index be an expression.<p>So instead of (per the example)<p><pre><code> CREATE INDEX tsv_idx ON documents USING gin(tsv); </code></pre> doing something like<p><pre><code> CREATE INDEX tsv_idx ON documents USING gin(to_tsvector(&#x27;english&#x27;, text)); </code></pre> Is there any reason you <i>wouldn&#x27;t</i> do this? For multi-languange you you&#x27;d have to detect the language of the text, but there&#x27;s no reason you couldn&#x27;t parameterize that too.

&gt; This query takes about 50ms! We experimented with returning the document text with results and found that if we returned the full text of documents, it added about 350ms to the query time which seems to be network overhead rather than a slower query. If we only returned 200 characters from the texts then it only added around 100ms.<p>The way you have written the query, it has to extract the title from all of the JSON documents before applying the LIMIT.<p>Perhaps do the work to get the identifiers, order by rank, and apply the limit... all in a subquery. So that only the outer query actually touches the JSON document and extracts the titles.<p>Try it and report back on the speed improvement.

Sure, if you only need full text search there&#x27;s no need to use Solr or ElasticSearch.<p>I&#x27;ve never tried with PostgreSql, but I noticed the same drop in performance in MySql at the 1-2 million documents count, although full text search queries in MySql are slower (from what I remember, I haven&#x27;t tested in a couple of years).<p>The power of Solr and other tools out there is all the other stuff you can do, like synonyms processing, faceting, range queries, etc, etc... as well as give you more flexibility on what data you load and how you process it. But if you don&#x27;t need it, there&#x27;s no reason to use a different tool.

&quot;The accuracy of PostgreSQL searches is not the best. In the examples that follow I’ve explained how to weight titles over text, but we found a few cases where typing the exact title of a document wouldn’t return that document as the first result.&quot;<p>Isn&#x27;t this a concern as the main objective of search is to provide accurate results?

Nice writeup, I wrote a similar blog post a few years ago: <a href="http:&#x2F;&#x2F;tech.pro&#x2F;tutorial&#x2F;1142&#x2F;building-faceted-search-with-postgresql" rel="nofollow">http:&#x2F;&#x2F;tech.pro&#x2F;tutorial&#x2F;1142&#x2F;building-faceted-search-with-p...</a><p>Mine covers tags and other aspects of faceted search as well.<p>We were able to build a really simple search facility for our marketplace using Postgres in a very short period of time and for cheap. It was incredibly helpful for a small startup short on people and resources.

Naive question from a person with little DB experience: Why store the metadata in JSON inside the database instead of in multiple columns in the same table? Is this not the point of using a database?

&gt; The main arguments against using PostgreSQL search are accuracy and speed at scale.<p>Also, lack of faceted search, phrase search, partial word search (although you could use pg_trgm)<p>Previous 2 related discussions: - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=8381748" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=8381748</a> - <a href="https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=8714477" rel="nofollow">https:&#x2F;&#x2F;news.ycombinator.com&#x2F;item?id=8714477</a>

I tried using Postgres 9.4 full-text search for my forum, but ran into some &quot;column wider than full-text index&quot; error (don&#x27;t remember the exact issue). Wasn&#x27;t running into any of these: <a href="http:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;8.3&#x2F;static&#x2F;textsearch-limitations.html" rel="nofollow">http:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;8.3&#x2F;static&#x2F;textsearch-limitat...</a><p>Googling the error lead me to a mailing list where someone was &quot;working on it&quot; or &quot;going to revert the problem soon&quot;.<p>Ended up using AWS CloudSearch which costs a minimum of $50&#x2F;mo where I was already paying for Postgres.

&gt; This query takes about 50ms!<p>Would be useful to know how many rows you had in the table (?)

50ms! Wow... take it from me, you should have used Solr.

PostgreSQL is coming up a lot and making waves, never thought much about it until recently it turns out to be more reliable than mongodb. Interesting phenomena.

Seems sketchy.<p>While Postgres <i>can</i> do full text search and it <i>may</i> help you in your case, you can set up Elasticsearch for this sort of quantity of data in like an afternoon, if that.<p>Also, these &#x27;advanced features&#x27; people are throwing around are really trivial; they&#x27;re the sort of things that would crop up in lecture 2 of an information retrieval course. Good search is really reliant on proper weighting etc (not like &#x27;we weight the title more highly than the body&#x27; but more &#x27;foogle is a far more important word than bar, if we have both in a query, we care about foogle more&#x27;). This generally requires a tonne of experimentation; information retrieval is not easy and it&#x27;s very subtle.<p>Postgres seems to try and make everyone design their own ranking function, which is a recipe for disaster. I haven&#x27;t used Elasticsearch for a while, but I&#x27;d be surprised if they didn&#x27;t have a bunch of relatively good presets.<p>Postgres is good for loads of things, but Elasticsearch or Solr are state-of-the-art and Postgres isn&#x27;t (so far as I know); if you aren&#x27;t just hacking around then <i>please</i> consider the extra time to set up Elasticsearch and it&#x27;ll make your life easier.