1,335 karma · joined September 22, 2016
Physical WAL is key to WalShadow’s architecture, but most managed Postgres services don’t expose it to customers, making it impossible to use WalShadow. ClickHouse Managed Postgres manages both sides of the stack, allowing us to integrate WalShadow directly into the Postgres replication layer and provide a native path from Postgres WAL to ClickHouse.
There’s also some interesting work happening in core with a similar goal of decoupling logical decoding from the Postgres process. We plan to share learnings from WalShadow with the core and hopefully help bring this to Postgres someday :) https://hacking.postgres.tv/topics/logical-decoding/
The biggest problem of Citus was migration effort, transition from single node to multi-node was not trivial. Here I’m not talking about single table use-cases, more classic relational, multi-tenant apps with 100s to 1000s of tables. This is partly expected with most sharding technologies, though.
Sharing some insights based on my multiple years of experience working with Citus!
Here are few customer use-cases I could found:
https://docs.citusdata.com/en/v10.0/get_started/what_is_citu...
https://info.citusdata.com/rs/235-CNE-301/images/Citus_Data_...?
With PgBouncer addressing one of its biggest historical pain points - prepared statement support in transaction mode, in our managed Postgres offering, we’re increasingly seeing customers use the PgBouncer connection string by default for mose use-cases without running into any hiccups. That wouldn’t necessarily have been the case a few years ago.
PgBouncer is also battle-tested, widely validated, and offers a (surprising) level of configurability. You could also run a peered setup and make it multi-threaded, which is something I didn’t expect when I first came to know about it. https://news.ycombinator.com/item?id=48872874
However, if pooling isn’t used, there’s always an overhead (tens of milliseconds or more) when creating a new connection because Postgres needs to fork a process. And yes, applications can be written without pooling, which isn’t ideal, but happens quite a lot.
Application frameworks have also changed. Serverless architectures can generate tens of thousands of connections, which is where Postgres starts to run into issues. I’m personally not a big fan of using more than a few hundred connections, but it is very realistic in this era.
Side note: I’m not a big fan of having 10K+ connections on Postgres, 100s are more than enough to scale Postgres well. But that’s a story for another day. ;)
Separately, I understand taking care of it at storage level, but still don’t get “unifying storage” or “zero copy”.
Anyways, I’ll stop now. Good to see all the innovation happening on converging OLTP/OLAP front. Each with a different approach and perspective. :)
In regards to error prone and speed (lag, latency at real-world scale), I wish the blog went into more detail and gave evidence than talk theory.
You also mention removing CDC pipelines. I’m curious if the materialization (conversion across formats) can catchup to an OLTP workload that is heavy (50K+ tps), which is pretty common these days. Also CDC if done right and with care can be magical for users and stays native to the OLTP/OLAP data-store.
Third, data Lakes and open formats are suitable for Data Warehousing / Data analyst use-cases than real-time customer facing apps. Sure, you might work on changing that, which is what you are upto, but you’ll always run into tradeoffs, which will make it hard to unleash the best performance, much needed for the latter category.
The importance of optimizing (resource) margins and having predictable memory usage increases significantly in the DBaaS/Postgres world, where your process coexists and competes with other critical workloads.
Also, WAL-RUS isn’t rocket science. Postgres already exposes a bunch native constructs for WAL archival, making development fairly straightforward even in Rust.
TL;DR: when to choose Rust or Go really depends on the workload and what you are going after.
Back at Citus/Microsoft, we typically saw around a 30% performance drop with synchronous replication on EBS-backed Postgres. I’d expect something in that ballpark for RDS and Crunchy as well. For NVMe-backed Postgres, we haven’t yet measured the impact of quorum-based replication, and it’s certainly possible the overhead ends up being higher than 30%.
That said, the single-node margins are already quite substantial, over 2× in all cases and up to 5× versus RDS in our benchmarks. Even with a meaningful HA penalty, NVMe-backed setups could still remain very compelling from a performance perspective. We’ve just started running HA benchmarks, so stay tuned.
Side note local NVMe backed Postgres is for perf is not new - many enterprise companies like Datadog and Instacart run their performance critical services on them, though self-managed.
In regard to RTO for single-node setups, it wouldn’t be great (at least minutes) in most systems, since recovery still needs to happen from backups.
Overall, very useful feedback. Thanks again for chiming in!
In regards to HA, partly agreed. We are working on adding HA setup as we speak, should be released soon. However note that the local NVMe setup does have backups and WAL-archival to S3, which provides data-durability with RPO of 10s of seconds. Even with HA setup I expect performance difference to be similar across systems (may be slightly lesser), as round-trip to secondary is common across most other services except Neon, whose reliability/availability story hasn’t been a strong area in general.
In regards to default configs, that was intentional as default tuning is a differentiation across services and that needs to be measured. However we plan to add more configurability on postgres tuning in the future.
Appreciate your feedback, super helpful!
Side note: May be there is way even more native than CDC/logical decoding that you never have to worry about keeping PG and CH in sync. Stay tuned for some updates from us on that end!
- Sai from ClickHouse
Above is exactly why we are embracing the Postgres + ClickHouse stack and are investing heavily to make workflows across both these DBs very easy for developers - PeerDB for native CDC, pg_clickhouse extension for querying CH from PG, pg_stat_ch for query PG observability from ClickHouse and more such are planned for future. And recently we also announced ClickHouse Managed Postgres which pacakages this entire stack as a fully managed service https://clickhouse.com/cloud/postgres
The entire premise here is really about time-series workloads where most operations are based on a timestamp. In those apps partition dropping has been a standard and recommended retention strategy for years. That's precisely why extensions like pg_partman and TimescaleDB exist. Given that context, the title feels more clickbaity, and could easily mislead readers into thinking this applies broadly to OLTP systems when it doesn't;
DELETE with well-tuned autovacuum works pretty well. Have seen it work at TBs scale with no hicuups. If DELETEs are large, we used to recommend customers to follow that with a manual VACUUM for table to reclaim space right away for future rows.
DROP TABLE can be risky, it requires an ACCESS EXCLUSIVE LOCK and if its waiting, it blocks all other statements following it, because of how lock queues work in Postgres. And you cannot keep doing high concurrent DROP TABLEs to run your large scale CRUD app.