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.
We started with the most battle-tested and native option to Postgres, which is PgBouncer and tried tuning it the right way. Also now that long due kinks like support for prepared statements are solved, it’s been working really well. There are many customers scaling well with 10K+ Postgres connections. We will consider other options like odyssey, pgdog in the future!
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. ;)
Ack, makes sense. I’m very curious on how this affects throughput due to a potential extra network hop from pgbouncer to Postgres. Expecting it to have a minor difference, but still curious.
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.
It could be the other way round too. ;) We just limited the number of Postgres providers in the first batch. May add you in the next and might ping you before sharing it in public, just to make you sure you are aligned and like them.
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!
It wasn’t left out on purpose, it was just prioritization, we chose 5 that most commonly came up. Please feel free to submit a PR, it should be pretty straightforward.
Ack on most points i.e. duration, scale factor (data size) and pricing. We will try incorporating these in the next iterations.
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.
You should check this message that talks about everything we are doing to make adoption of ClickHouse with Postgres easy. It captures all the tools we’ve released and are actively working on.
We at PeerDB/CickPipes tried to make that mirroring as frictionless as possible. It is validated at scale across 1000s of customer moving over half a PB of a data per month from Postgres to ClickHouse. You should give it a shot and you might be surprised.
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 here. Totally with you here, ClickHouse isn't a replacement for Postgres. Most use-cases are co-existence - Postgres for OLTP and ClickHouse for OLAP, basically right tool for the right job situation. Both are purpose-built technologies with a similar OSS ethos/story. Btw on an interesting co-incidence, Postgres turned 30 this year and ClickHouse turned 10.
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
I know I know. Some people have loved it as it captures what it does (peering dbs) and some haven't because of the exact reason you called out. So we get it! :)
(Most) CRUD/OLTP applications don't delete data by timestamp; they delete by primary key. For those workloads, DROP TABLE (or dropping a partition) isn't a palatable option.
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;
Partially true but too much of a blanket statement and clickbaity.
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.
It depends on the use case. For real-time, customer-facing analytics, ClickHouse’s MergeTree engine is a natural fit, so a Postgres → ClickHouse CDC setup with low latencies (single-digit seconds) is better.
Replication to Iceberg/S3 is better suited for offline analytics and data warehousing use cases. You can use the same ClickHouse engine to query layer Iceberg data in S3.
Great progress, guys! It’s impressive to see all the enhancements - more types, more aggregate functions, cross-node DML, resharding, and reliability-focused connection pooling and more. Very cool! These were really hard problems and took multiple years to build at Citus. Kudos to the shipping velocity.