4 ms·

Sai from PeerDB/ClickHouse here. Nice to see the progress on this project! I wanted to leave a few notes: For analytics on transactional data, it looks like yo

Sai from PeerDB/ClickHouse here. Nice to see the progress on this project! I wanted to leave a few notes: For analytics on transactional data, it looks like you'd still need to use logical replication (https://github.com/Mooncake-Labs/pg_mooncake/issues/90 https://github.com/Mooncake-Labs/pg_mooncake/issues/90). Logical replication is somewhat similar to an ETL/CDC experience, though it's more Postgres-native. Managing logical replication at a production grade isn't trivial — it's quite common for customers to use PeerDB for homogeneous replication due to performance, manageability and observability issues with logical replication. One potential value of extensions is Postgres compatibility for your queries. However, I'm curious about the SQL coverage on columnar tables — for example, advanced constructs like partitioned tables, prepared statements, correlated subqueries, RCTEs, triggers, and more. While it seems there’s ongoing work to improve compatibility, achieving full Postgres compatibility is challenging. In some cases, you might need to fall back to Postgres (instead of DuckDB), sacrificing performance for certain queries. The biggest challenge we faced at Citus was the constant trade-off between Postgres compatibility and performance — both are complex and constantly moving targets. This was the key reason why Citus couldn't compete with purpose-built databases like Snowflake, ClickHouse, SingleStore. While DuckDB didn’t exist when we built Citus, it's still fundamentally different from Postgres. Even though the DuckDB dialect is somewhat similar to Postgres (as is Snowflake’s), retrofitting two distinct databases — each evolving independently for world's best performance — isn't trivial. In short, relying on ETL (logical replication) without providing full Postgres compatibility raises the question: is it better to lean on a purpose-built database with a laser focus on performance, or adopt a more generalized approach? Anyway, I come from a different school of thought — using the right tool for the right job. That said, I love seeing all the progress and evolution in the Postgres community — Postgres will always be my first love!

theLiminator 2y ago
Do you believe that things like cedardb might one day remove the distinction between OLAP and OLTP dbs?
- zhousun 2y ago
  Interesting enough, the mooncake team was building SingleStore before, and so far it is the best production-ready HTAP system. One lesson I really learned is, people don't want to switch their system-of-record OLTP system. Spoiler-alert: Mooncake will be supporting HTAP use-cases soon, and it comes with better trade-offs: keep your OLTP postgres tables as is, and mooncake adds analytics capacity to those table on up-to-date data.
zhousun 2y ago
Hi, Zhou From Mooncake labs here. Love your work on PeerDB and it's inspiring the evolvement of pg_mooncake (logical replication will be the killing feature for V2) The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres: 1. For small devs, we allow the whole stack to be embedded as a Postgres extension for ease of use 2. For enterprise, our stack is also purpose-built stack similar to PeerDB + ClickHouse, not a more generalized approach We allow a gradual transition from 1 to 2.
- saisrirampur 2y ago
  Thank you for the kind words! :) 1, makes sense. On 2, I understand your thinking around purpose-built — but you're retrofitting an analytical database into a transactional database without fully supporting all the features (both in terms of functionality and performance) of either. It's really hard to be truly purpose-built this way. As a result, users might not get the best of both worlds. PeerDB is different. We keep Postgres and ClickHouse separate and just move data reliably between them. Users get to query Postgres and ClickHouse in isolation and make the best of each of them. Anyway, keep up the good work! Just wanted to share some challenges we've seen before when building an analytics extension (Citus), particularly around chasing both Postgres compatibility and performance.
  - zhousun 2y ago
    Yep what I want say is the line between the two designs is indeed very blur. Logical replication with mooncake will try to create a columnar version of a postgres heap table, that can be readable within postgres (using pg_mooncake); or outside postgres (similar to peerdb + clickhouse) with other engines like duckdb, StarRocks,Trino and possibly ClickHouse. But since we can purposely build the columnstore storage engine to have postgres CDC in mind, we can replicate real-time updates/deletes(especially in cases traditional OLAP system won't keep up).
    - saisrirampur 2y ago
      I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.

theLiminator 2y ago

Do you believe that things like cedardb might one day remove the distinction between OLAP and OLTP dbs?

zhousun 2y ago
Interesting enough, the mooncake team was building SingleStore before, and so far it is the best production-ready HTAP system. One lesson I really learned is, people don't want to switch their system-of-record OLTP system. Spoiler-alert: Mooncake will be supporting HTAP use-cases soon, and it comes with better trade-offs: keep your OLTP postgres tables as is, and mooncake adds analytics capacity to those table on up-to-date data.

zhousun 2y ago

Hi, Zhou From Mooncake labs here. Love your work on PeerDB and it's inspiring the evolvement of pg_mooncake (logical replication will be the killing feature for V2) The core idea of mooncake is to built upon open columnar format + substitutable vectorized engine, while natively integrate with Postgres: 1. For small devs, we allow the whole stack to be embedded as a Postgres extension for ease of use 2. For enterprise, our stack is also purpose-built stack similar to PeerDB + ClickHouse, not a more generalized approach We allow a gradual transition from 1 to 2.

saisrirampur 2y ago
Thank you for the kind words! :) 1, makes sense. On 2, I understand your thinking around purpose-built — but you're retrofitting an analytical database into a transactional database without fully supporting all the features (both in terms of functionality and performance) of either. It's really hard to be truly purpose-built this way. As a result, users might not get the best of both worlds. PeerDB is different. We keep Postgres and ClickHouse separate and just move data reliably between them. Users get to query Postgres and ClickHouse in isolation and make the best of each of them. Anyway, keep up the good work! Just wanted to share some challenges we've seen before when building an analytics extension (Citus), particularly around chasing both Postgres compatibility and performance.
- zhousun 2y ago
  Yep what I want say is the line between the two designs is indeed very blur. Logical replication with mooncake will try to create a columnar version of a postgres heap table, that can be readable within postgres (using pg_mooncake); or outside postgres (similar to peerdb + clickhouse) with other engines like duckdb, StarRocks,Trino and possibly ClickHouse. But since we can purposely build the columnstore storage engine to have postgres CDC in mind, we can replicate real-time updates/deletes(especially in cases traditional OLAP system won't keep up).
  - saisrirampur 2y ago
    I understand. In that scenario, why can't users just use these other query engines directly instead of the extension. You're heavily relying on DuckDB within your extension but may not be able to unleash its full power since you're embedding it within Postgres and operating within the constraints of the Postgres extension framework and interface.