Faceting large result sets in PostgreSQL

Faceting is really useful, but quite expensive if implemented in a naive way in Postgres. This looks very interesting, but the really annoying thing about requiring a Postgres extension is that you can't use it with most managed Postgres services.

What is the best way to calculate facets in Postgres when you can't rely on the ability to install an extension? The naive way would be a bunch of "count(*) group by" queries, a slightly less naive way would be to do the same using grouping sets if possible. But are there any other tricks one could use to calculate facets for large, but not impossibly large numbers of rows?

Surprised this doesn't mention Materialized Views. Those can be used to create the buckets/facets from the larger table then offer faster lookup of the facet values.

Creating the MView would still be from a sequential scan but then you'll have small/fast views to queue queries for the huge table.

I'm implementing some faceting in a project I'm working on. I have some nested `jsonb` columns which include categorical variables (to quote this article).

The table is only 5 million rows and isn't likely to grow significantly, but it is not very wide.

The performance is okay and I was about to experiment with flattening out the table (putting all categorical vars into their own columns) and indexing that way vs. indexing JSONb paths.

I'm not super familiar with the postgres internals to know if this is better or worse so wondering if anyone could share any similar experience?!

This looks brilliant! Amazing work