saisrirampur

Great blog! Thanks for writing this one up. Such a useful one for anyone who is build with Postgres. Succinctly reminds of all the battle scars working with many customers over the past decade. ;)

It improved quite a lot! It scales pretty well to thousands of connections: https://techcommunity.microsoft.com/blog/adforpostgresql/imp....

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.

Ack, we will consider offering pgdog in the future! More context on why we chose pgbouncer https://news.ycombinator.com/item?id=48873867

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.

Oh no, I did read the blog. Not throwing shade at anyone here—the blog is great. It just doesn’t provide real-world evidence, and it opens up a bunch of technical questions that I’m trying to understand. that’s exactly what HN is for. :)

;) sounding good on paper vs how it works in practice (supporting demanding real-time OLTP/OLAP workloads) are completely different ball games.

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. :)

Exactly! Why unifying storage, which opens up a can of trade-offs.

Taxing Postgres is one thing, which can be overcome with ways like using standbys. There could other more native ways (than unifying storage), which you’ll hear about in a few weeks. Also I don’t fully agree on logical replication taxing Postgres, if the client is built with care and precision.

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.

But why? I’m skeptical of the idea of unifying storage just because it sounds “elegant” or “cool”. It’s not obvious to me how a single storage engine can compete with purpose-built OLTP and OLAP systems like Postgres and ClickHouse, without significant tradeoffs.

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.

++ We’re big Go fans, most of PeerDB is written in Go: https://github.com/PeerDB-io/peerdb

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.

Ack, your insight is very interesting.

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.

Good feedback, we will aim to include them in the future.

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.

Appreciate your feedback, super helpful!

Yes, you absolutely can. It takes 15 mins to run the benchmark amd get results.

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.

https://news.ycombinator.com/item?id=48599644

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

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! :)

Separately, this is one of the Postgres autovacuum tuning blog that I've ever read. Have seen it work across many customers and it is also simple to decipher and implement. https://www.citusdata.com/blog/2022/07/28/debugging-postgres...

(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.

Ack, makes sense. When making public posts that reach a large audience, it’s better to be clear with the messaging rather than risk it being blurry for even a subset of the audience, especially if that could lead to misguidance and affect their day-to-day work.

Ack, agreed. But there’s a better way to communicate than making blanket statements like “just use Postgres.” For example, you could say “Postgres is the default database,” etc.

Don’t get me wrong—I’m a huge huge fan of Postgres. I’ve worked at Postgres companies for a decade, started a Postgres company, and now lead a Postgres service within a company! But I prefer being real rather than doing marketing hype and blanket love.

That wasn’t my intention though to mention my workplace multiple times for the sake of PR. Try to avoid being that, atleast on hn. :)

Agree to disagree here. I see a world where developers need to think about (reasonable) scale from day one, or at least very early. We’ve been seeing this play out at ClickHouse - the need for purpose-built OLAP is reducing from years to months. Also integration with ClickHouse is few weeks of effort for potentially significantly faster performance for analytics.