Comment by moonikakiss

9 months ago

great blog. It seems like you might benefit from columnar storage in Postgres for that slow query that took ~20seconds.

It's interesting that people typically think of columnstores for strict BI / analytics. But there are so many App / user-facing workloads that actually need it.

ps: we're working on pg_mooncake v0.2. create a columnstore in Postgres that's always consistent with your OLTP tables.

It might help for this workload.

8 comments

moonikakiss

Reply
I_am_tiberius  9 months ago

That sounds awesome. Are you saying you still use your normal OLTP table for writing data and the columnstore table is always in sync with that OLTP table (that's fantastic)? I ready it works with duckdb - how does it work? I guess there's no chance this is going to be available on Azure Flexible Server anytime soon.

  • moonikakiss  9 months ago

    exactly. we take the CDC output / logical decoding from your OLTP tables and write into a columnar format with <s freshness.

    We had to design this columnstore to be 'operational' so it can keep up with changing oltp tables (updates/deletes).

    You'll be able to deploy Mooncake as a read-replica regardless of where your Postgres is. Keep the write path unchanged, and query columnar tables from us.

    --- v0.2 will be released in preview in ~a couple weeks. stay tuned!

    • I_am_tiberius  9 months ago

      Ah, I see. So there's a replication process similar to ClickHouse's MaterializedPostgres. Ideally, there would be functionality allowing a columnstore query to wait until all writes to the OLTP tables — up to the query's execution time — are available. This would make the system truly Postgres-native and address issues that no other system currently solves.

      1 reply →

compton93  9 months ago

What are your thoughts on Fujitsu's VCI? I typically work for ERP's but im always advocating to offload the right queries to columnar DB's (not for DB performance but for end user experience).