[coryfoo]

Ostensibly this article is about the wins you can get as far as disk size is concerned by using cstore_fdw, but I can't help but notice they weren't creating any indices on this data.

By creating a simple GIN index on the regular jsonb table, you could dramatically improve those query times. So if your workload doesn't require that you do a table scan for all records then the tradeoff in terms of disk space would more than make up for the perf wins provided by the index.

Does anyone know if cstore supports creating indices of any kind, let alone GIN?

[ddorian43]

columnstore is supposed to not work with indices (although they have different types like zone-maps)

each column is stored separately on disk, so only the requested column-values are read from disk

This makes slow to select/update/delete a single row(oltp) since it needs to fetch multiple pages (for each column). And makes it fast to do queries regarding data in big size (olap) by doing vectorized query execution and sequential-reads on disk