But assume you have a json field with various, lets say, product attributes which might vary greatly between various products, let's say, based on the category they are in.
Hard drives for example might have a "capacity" field, whereas monitors might have a "screen size" field. Also assume that you have 10000s of products, 100s with completely different attributes, 1000s of distinct attributes across all products.
Now you want to add a search feature that allows to search for products based on these attributes (like "give me all products where screen_size=37 inches")
If you don't have any traditional columns with indexes you can use to limit the amount of rows, that query will either cause a sequence scan over all products, or you will have to create a functional index over the screen_size field in your JSON document which then only covers screen_size, but not, say, the disk_size.
This will mean that you will have to create 1000s of indexes (one for every custom attribute) which also means that you lose a lot of flexibility: Adding a new custom attribute now means that you have to add another index (or you'll suddenly have to deal with unexpected sequence scans).
Using jsonb and a gin index, you need exactly one index to find arbitrary key/value combinations in your json document, giving you the flexibility of trivially adding new attributes but just storing that in the json document.
I think I ran your exact application (large retailer catalog). We just used solr as a sort of external index. Worked like a charm, though obviously introduced additional complexity.
Yeah. I'm also pondering the use of elastic search in my case.
But the additional complexity is scaring me a bit, especially as the product in question's main strength is not showing most of the products it knows about, so none of the elastic search results could be displayed directly, but would have to go through additional processing.
Being able to do all of this directly in Postgres will be much nicer, so I'm really looking forward to 9.4
For any kind of product search I'd go for an external index (I'm strongly biased towards ES, but SOLR is certainly on par feature-wise). Postgres is my go-to relational database and I do love it's json/hstore features, but it does not offer features such as stemming, aggregation, proper weighting or any other analyzer based features.
Adding ES as a secondary datastore just for search is relatively easy in terms of technical complexity, especially if you already have json data. The more complex part is deciding on a relevancy model, but you'd have to do that for every technology you choose.
