Transforming Postgres into a Fast OLAP Database

This is pretty cool. It seems like there are a lot of hard database problems that still need to be solved, but finding the right database system (or extension) seems about as difficult as finding a research article and implementing the solution yourself lately. This seems like a step in the right direction by integrating with Postgres, which is widely used and supported.

Case in point regarding OLAP in particular, I am currently trying to solve a problem where I have a high number of categorical dimensions, and I want to perform a “count distinct” over any combination of dimensions, grouped by any other combination of dimensions, filtered by specific values in each dimension. E.g., count(distinct a.col1, b.col2), count(distinct a.col1), count(distinct b.col3) from table a join table b using (id) group by a.col4, b.col7.

Sounds obscure when I word it that way, but this is actually a pretty “generic” problem that appears whenever you want to filter and count the number of distinct property combinations that occur within a fact dataset of transactions or events that has been joined with other dimensional datasets. A naive implementation is exorbitantly expensive (and impractical) if you have to join many large tables before grouping and performing count distinct.

However, this specific problem manifests in various equivalent forms mathematically: model counting of boolean expressions, low rank factorization of sparse high dimensional boolean tensors (each row in your transaction dataset corresponds to a value of “true” in a sparse tensor with dimensions indexed by the values of your columns), minimal hypergraph covering set, etc.

Is there a database already out there that’s optimized for this fairly common business problem? Maybe...? I searched for a while but couldn’t easily separate the startup database hype from the actual capabilities of a particular offering. Plus, even if the ideal “hypergraph counting database” exists, it’s not like my company is just going to replace its standard cloud SQL platform that serves as the backbone of our entire product with a niche and fragile experimental database with questionable long-term support. It’s much easier to just translate one of the latest tensor factoring research papers into a Python script, plop that into the data processing pipeline, and output the simple factored form of the transactions dataset into a new table that can be easily queried in the standard way.

How do you find working with Arrow / Parquet?

Personally I found it very hard to reason about and thought that Clickhouse's strategy for managing columnar data much more reasonable.

Do you have an example where CH was more reasonable? I don't have enough experience here, so would greatly appreciate hearing what you'e seen. Thank you!

As long as you own it, you will love it. Trust me.

Thank you for your great job!