Hacker News new | ask | show | jobs
by moonikakiss 404 days 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.
2 comments
I_am_tiberius 404 days 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.
link
moonikakiss 404 days 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!

link
I_am_tiberius 404 days 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.
link
moonikakiss 404 days ago
yep exactly. we can wait for replay LSN. So you're only reading once all writes to OLTP are complete.
link
I_am_tiberius 404 days ago
A follow up question: You can't join columnar tables with OLTP tables, right?
link
moonikakiss 404 days ago
yes you can. Even if the columnar tables are in the read replica. you'll be able to do joins with your OLTP tables
link
I_am_tiberius 404 days ago
That's great, thanks.
link
compton93 404 days 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).
link