You can precompute to_tsvector() in parallel ahead of time if you're storing it in a dedicated column. CREATE INDEX runs on a single thread, including the part where it evaluates to_tsvector() for each row. If you ever need to recreate the index, it'll be faster if the tsvector is in a dedicated column.

I have a table with 30 million documents using pgsql's full text index. Creating the index takes ages, and search performance is generally very poor. The difference between creating the index with the precomputed column versus creating the index with the expression in the index itself (which is how I originally did it) was substantial.

No, there's no real reason why you would not use a functional index. The reason so many tutorials use a dedicated column is simply lack of up-to-date information, I think.

That is the way I do it also. I have used Lucene and Solr a lot in the past, but I now find Postgres text search to be more than adequate for my needs and it does make software development and deployment simpler.

You're not including the title from the JSON column or setting weights in your alternate version. I'm not sure if that can be included in the index or not, but that might be the reasoning.

If you want to preprocess your document, or aggregate different parts of it with different labels and weights, it can be helpful to store it in a separate column.

Of course you can always just index your preprocessing/aggregating function and call it every time you want to search, but depending on how expensive that is, it might be in your interest to do it upfront and make your searches a bit quicker.

It has the same effect as the UPDATE/INSERT trigger they use as far as I'm aware, without the additional storage.

With triggers you can fetch and index data from relations.

There's no question that Solr and ElasticSearch have more capability than PostgreSQL alone, but you'd be surprised at what Postgres can do!

It's certainly more than just full text search. Check out this article [1] that talks about Postgres' support for stemming, ranking / boost, multiple languages, fuzzy search, and accent support.

[1] http://blog.lostpropertyhq.com/postgres-full-text-search-is-...

Sometimes, Accurate Enough is all you need. It's definitely a concern, but there are other issues to balance in there -- weight of infrastructure from adding new components, etc.

Text-search ranking is customisable[0] and results vary wildly based on the ranking behaviours selected and the weights assigned to different labels. It takes a bit of fine tuning and with the wrong parameters for your data set, you can definitely get results that seem unintuitive.

This should be a concern to the author, but there's no reason to think the search ranking is not working as documented.

[0] http://www.postgresql.org/docs/9.4/static/textsearch-control...

>Isn't this a concern as the main objective of search is to provide accurate results?

If it provides 99% of the results and misses some documents because of some weird bug or encoding issue, then it could very well be good enough for their purposes. Heck, even 90% could be good depending on what they do (e.g. serve articles in an online site).

For other uses, like police or medical records obviously they'd need 100% results.

PG text search is entirely accurate. The author just screwed something up. Probably forgot to include the regconfig (english vs simple for example)

Curious - Why not Sphinx or solr?

Cool, thxs for the link !

It's a form of 'document storage' that allows a application to store data without the need to account for everything in the database schema.

It depends on what those columns are. For this case multiple columns are probably better, but if you're trying to search over multiple document types a JSON column could be more flexible.

If I'm not mistaken: Altering a table can be costly and may cause a performance issues, specially if you have millions of records. Rows will be most likely locked and get updated and during this process other threads should wait until related row become "free".

The table that we run this on has 220K rows with a size on disk of 1.8 GB. I mentioned in the post that we'd tried out this approach with 23 million rows which was slow! From our (limited) testing the speed holds up until around 1 to 2 million rows, at which point I'd suggest a dedicated solution.

Looking at elasticsearch, the first thing I see is a pom.xml. Seems sketchy. ;)

Seriously though, none of my servers have a JVM installed. Most of them have nginx and ruby on them. And all of them talk to a handful of postgres servers. If a client wants to add some kind of full-text search to an app, this is a great approach to doing it in a cost-effective way. I don't particularly want to have to add more infrastructure just to be able to search through data. That's an additional monthly cost, along with a new environment to monitor for critical security updates etc.

If someone needs high performance search, sure, I'd cost out elasticsearch or solr. If they just need something basic, postgres full-text looks like the perfect low-effort solution.

I hate writing SQL blind, but more like this... note that you fetch only the identifiers with the inner query, and use them in the outer query to retrieve the documents.

    SELECT d.id
          ,d.meta->>'title' as title
          ,d.meta
      FROM (
  	       -- Only select the identifier column
               SELECT id
                 FROM data_rows
                     ,plainto_tsquery('search query') AS q
                WHERE tsv @@ q
                ORDER BY ts_rank_cd(tsv, q) DESC
                LIMIT 5
           ) AS ids
     -- Join back onto the data_rows, this fetches the documents
     INNER JOIN data_rows d ON d.id = ids.id;

You'll want to go through the explain plan, but treat identifier retrieval as a step by itself. Things like ts_headline (not shown), or meta->>'title' (shown) should only be performed for the few rows you actually are retrieving and you should make the data you pass around be as small as possible at all points.

Note that you don't need to call plainto_tsquery twice, you can use the one you've already declared.

And I notice you are returning the JSON document whole, have you checked whether it's faster to extract the title here or later in whatever your programming language is?

The giveaway clue in your post btw is this: "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.".

As soon as you notice a linear slowdown, when you're only returning 5 rows whatever happens... then internally you must be doing something in the query that relates to all rows.

PostgreSQL fulltext scales nicely, I use it on a platform driving over 300 web sites with 10s of millions of comments. I originally used it thinking it will buy me time before I have to add a dedicated search engine, and it helps now as I store permissions knowledge in the database too... but truth is, it's just not been an issue yet. I'm not needing to consider anything else as it's performing great. The slowest part of my query isn't fulltext search, it's my permissions stuff (because I've gone down the rabbit hole of users, groups, roles, etc and this is calculated for all content), but still the 95th percentile for searches is typically under 10ms.