3 ms·

Thanks. Is there a list somewhere of Postgres-based columnar datastores (and which are MPP/clustered vs single/SMP-node)? * Citus (cstore_fwd) extension * Azu

• hankchinaski 3y ago
  For sure there are GitHub lists somewhere. I recently had to research for olap Postgres compatible workloads and I know major ones: redshift, clickhouse, hydra
• wmf 3y ago
  TimescaleDB?
  • claytonjy 3y ago
    I consider Timescale to be more of a hybrid than a "pure" columnar DB. IIRC the latest chunks are row-wise, but it goes columnar for older chunks to compress them. You can do a lot of OLAPish stuff, but it's arguably OLTP-first.
• hazaskull 3y ago
  Not Postgres-based (but wire- and mostly syntax-compatible): cockroachDB using column families is much like a columnar MPP. Yugabyte is PG-based and MPP but not columnar.
  • riku_iki 3y ago
    > cockroachDB using column families is much like a columnar MPP. I am wondering why they are saying it is not for OLAP workload..
    • hazaskull 3y ago
      They don't optimize for it and I suppose the data distribution is primarily aimed at parallel OLTP rather than OLAP. Just wanted to mention that design-wise it is similar but that's indeed not all there is to it. I'd be hesitant to store large volumes of data on a single PG instance; don't see how a single-writer, filesystem-based database is suitable at all for data that is large enough to warrant columnar storage
      • riku_iki 3y ago
        so, what would be your db choice for OLAP?
        • hazaskull 3y ago
          No expert but I'd say you'd rather be looking at bigquery, Redshift, Clickhouse, Snowflake, etc.
        • esafak 3y ago
          You can also go HTAP with TiDB which has TiKV for OLTP and TiFlash (Raft-based columnar replicas) for OLAP.
          • riku_iki 3y ago
            I am more interested in actual OLAP than HTAP, and don't see strong OSS OLAP offering on the market right now, my rants in previous discussion: https://news.ycombinator.com/item?id=36992039 https://news.ycombinator.com/item?id=36992039 But I should look at TiDB, they looks like interesting and relatively mature project.
            • esafak 3y ago
              https://www.starrocks.io/ https://www.starrocks.io/ is on my shortlist for OLAP
              • riku_iki 3y ago
  • refset 3y ago
    The presence and use of column families is only half of the puzzle - it doesn't strictly imply that the execution engine is capable of working in a vectorized columnar style (which is necessary for competitive OLAP).
    • hazaskull 3y ago
      Indeed. As I commented alsewhere this is just about the general design. It is not targeting OLAP in this case (even though I do believe cockroach employs vectorization for reads)
    • hazaskull 3y ago
      Was unable to edit my previous. It does use vectorization: https://www.cockroachlabs.com/docs/stable/vectorized-execution https://www.cockroachlabs.com/docs/stable/vectorized-executi...
      • refset 3y ago
        Thanks for sharing that - TIL! These blog posts elaborate with more detail: https://www.cockroachlabs.com/blog/vectorized-hash-joiner/ https://www.cockroachlabs.com/blog/vectorized-hash-joiner/ https://www.cockroachlabs.com/blog/vectorizing-the-merge-joiner-in-cockroachdb/ https://www.cockroachlabs.com/blog/vectorizing-the-merge-joi... ...it seems the distinction here is that the vectorization is only present in the execution layer and not the storage layer also. I would guess that from a storage perspective, even with column families in play, everything is being streamed out of sorted a LSM engine regardless. So there isn't additionally some highly-tuned buffer pool serving up batches of compressed column files etc.
  • ddorian43 3y ago
    Note column families has nothing to do with columnar. Another example is cassandra is not column oriented.
    • hazaskull 3y ago
      Thank you for the correction. Indeed it is not entirely the same thing. Though I'd expect that at least the benefit of not having to read columns that aren't in the family would still help (haven't tried in earnest). I suppose compression is not an option though.
• mslot 3y ago
  Note that cstore_fdw ≈ Citus columnar (modernized using table access method) ≈ Azure Cosmos DB for PostgreSQL (managed Citus service) ≈ Hydra (a Citus columnar fork) Fwiw, the main use case we had in mind when developing the Citus columnar table access method was compression of old data in a time-partitioned table. Citus is commonly used for real-time analytics on time series data. That involves more materialization than typical OLAP reporting use cases. However, you might still want to keep the source data in the database for ad-hoc queries or future materialization and it's preferable to keep the source data in compressed form. Redshift is specifically architected for ad-hoc OLAP queries. AlloyDB I'm not sure.