But great job, I've bookmarked your post to come back to and I'll give it a shot for sure. Definitely very enticing.
The way I'm doing aggregation counts on top of PostgreSQL is very unlikely to scale to millions of documents. Elasticsearch pretty much acts as a real-time, horizontally scalable map/reduce engine so expensive aggregations get executed on multiple shards and the results are gathered back together.
You may find my talk from DjangoCon this year interesting: https://speakerdeck.com/simon/the-denormalized-query-engine-... / https://www.youtube.com/watch?v=NzcvewgqYog
Hence why solutions like elasticsearch and apache solr exist. They scale very well horizontally, allowing you to spread the load between multiple nodes.
Again, this comes down to what your use case is. If you have a tiny app with a relatively small number of users, using postgres for this functionality is probably ok.
I’m hoping to find time soon to modernize it for 2017 era screen size at some point soon. I did at least manage to get some media queries in there so it’s readable on mobile phones.
Calculating the counts for each facet was by far the most expensive part of the query. I found the GROUPING SETS feature very useful to calculate a lot of aggregate counts efficiently. Though this wasn't parallelized by Postgres, I'll have to check if that is possible in Postgres 10 now.
I wondered if there are better ways to calculate facet counts, but I simply can't think of a way that doesn't require at least one full table scan.