1) Using jsonb to store user-generated forms and submissions to those forms. As an example, you can create a form with text inputs, checkboxes, etc., and others can submit responses with that form. I find that these forms and their submissions are best stored as jsonb because their contents are largely opaque (I don't care about the contents except where they are rendered on the client), their structure is highly dynamic, and their schema changes frequently.
2) As a specialized case on #2, applying filters to user-generated form submissions. jsonb supports subset operators (@> and <@, if i remember correctly), which makes easy work of dynamically filtering form submissions on custom form fields even for complex filter conditions.
3) Storing/munging/slicing relatively low-volume log data is fantastic with jsonb. This is always for admin/diagnostic reasons, so it's not as performance-critical, and the ability to group on and do subset operations on jsonb fields makes slicing your data really easy.
Using ->> (or ->) in a WHERE statement is generally a bad idea, and certainly a terrible idea without an explicit index. Use @> instead.
In the specific example given it might, but you will still wind up with a handful of queries that are planned wrong and are orders of magnitude slower.
I read a presentation titled "Powering Heap" by Dan Robinson, Lead Engineer at Heap, which contains interesting info about how you use PostgreSQL. [1]
At Heap, do you try to keep rows belonging to the same customer_id contiguous on disk, in order to minimize disk seeks?
If yes, how do you it? Do you use something like pg_repack?
If no, don't you suffer from reading heap pages that contain only one or a few rows belonging to the requested customer_id?
[1] http://info.citusdata.com/rs/235-CNE-301/images/Powering_Hea...
Currently, maintaining the clustering has only been best-effort. We sort our data whenever we copy it from one location to another and the data comes in sorted by time, so it's fairly easy to maintain a high row correlation with time.
May I ask how many logical shards do you have per physical shard/machine? And what is the average size of a logical shard on disk?
You wrote "the data is sharded by customer and then sub-sharded by end user within the customer", but malisper wrote above that "clustering by time winds up being a much bigger win". Isn't it contradictory?
One last question: I guess most queries target a time range. Do you use BRIN indexes to avoid scanning the whole 800 MB of data in each shard, and just read the necessary pages?
We're in the process of switching to a more balanced schema (mentioned in this post) and the results have been pretty good so far.
Another win has been that the better stats make it possible to reliably get bitmap joins from the planner. Our configuration uses ~12 RAIDed ebs drives, so the i/o concurrency is really high and prefetching for a bitmap scan works particularly well.