ahachete

> schema changes locking

Schema changes need locking... everywhere. Citus is no different. And they need proper design everywhere. If you mean that it requires distributed transactions, well, yes, again: expected and solved. Not even all sharding solutions support this.

> coordinator node

Not sure what the problem is here. If what you mean is that a single coordinator, even with an HA replica, can saturate, that's true, but you can add multiple "query routers" (that's our name in StackGres, see [1]).

> Maybe I am wrong, but I am yet to read stories on operating tens of TB scale workloads on citus.

For example, we have a customer that ingests some 30TB/day, and it's ramping up towards 200TB/day of ingestion. On 24 worker nodes.

[1]: https://stackgres.io/doc/latest/administration/sharded-clust...

If you want an OSS sharded Postgres, just use Citus. Feature-full, proven, mature, boring.

But it's not really accurate:

> This adds some undesirable side effects, however. First, the coordinator becomes the bottleneck.

Apart from being able to use any node as a coordinator (and you can load balance them to avoid having "multiple connection strings), there's a new pattern which effectively allows you to have as many coordinators as you want. They are effectively "data-less" nodes. We have devised and implemented this pattern in StackGres [1].

> Adding a shard with more resources for a noisy tenant, or many small shards for a wide shard space requires substantial manual configuration of not only the database servers themselves, but wiring them up together with Citus.

Adding nodes (infrastructure) is what operators solve. In StackGres, adding new nodes means editing one/two characters from your YAML file: the integer number that represents the number of workers that you have.

Shard rebalancing is fully built-into Citus as a UDF, which you can call (manually or in an automated manner) over Postgres protocol.

> Managing backups is also external to Citus, so operators still need to build the proper infrastructure

Agreed, but it's also solved (see distributed backups in StackGres [2]).

> PgDog is a spiritual successor to PgCat, both of which improve on Citus's architecture substantially.

Unsubstantiated why. I assume it's because of the assumption that a proxy model is superior than Citus. To which I have to say that Citus model is also a proxy model, where the proxy just happens to be Postgres, which unsurprisingly, speaks Postgres protocol. Sure, there are nuances that we could debate in this area and we can say that Citus is not a "pure proxy", but that doesn't lead to concluding that a proxy model is better --it's arguably not.

[1]: https://stackgres.io/doc/latest/administration/sharded-clust...

[2]: https://stackgres.io/doc/latest/administration/sharded-clust...

Yeah, and I don't see how that changes my question, which still holds: did you try to fix the first project, and report the issues, before doing a full rewrite?

I assume you have reported the issues / crashes to pgcopydb. Wasn't it easier to fix them rather than rewriting from scratch? I assume rewrite was heavily AI driven, and if so, probably AI could have fixed pgcopydb rather than rewriting.

Or am I missing something?

Good job, Colin.

I see you are following the Dyna53 [1] steps ;) What a school of thought you have sparked, Corey!

[1]: https://dyna53.io/

Same here. Stopped reading.

I feel ya. I'm always middle clicking twice, story and comments.

This shouldn't be a script (no offense, great job!). It should be built into the browsers!

AGPL and copyleft licenses are not restrictive, they provide forward (open source) guarantees. The only thing they "restrict" is the ability to remove freedoms. Therefore is not a restriction, is a guarantee. A guarantee that the project will endure as open source.

I wouldn't go as far as calling permissive licensing "restrictive" because they actually allow for reducing freedoms; but copyleft is definitely not restrictive at all, it's the opposite.

Having said that, I like and appreciate both kind of licenses and I have and will continue creating open source software using ones or the others.

> note that the local NVMe setup does have backups and WAL-archival to S3, which provides data-durability with RPO of 10s of seconds.

That's good, it provides a reasonable level of RPO, although I prefer just 0. But with this the RTO (or, equivalently, the downtime) is potentially quite large.

> Even with HA setup I expect performance difference to be similar across systems (may be slightly lesser)

I have done some benchmarks and I have observed a 24-27% performance penalty for semi-sync replication on instances using local NVMe. That's why I say this is non zero.

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

That'd be great.

My 2 cents:

> Each run lasts 10 minutes, long enough to move past warmup and capture stable throughput

But short enough not to show the effect of checkpoints (which is quite noticeable). It's not representing a realistic workload. I'd use at the very least 30mins with checkpoint_timeout=5mins so you get at least 6 checkpoint cycles.

> We report average TPS, average latency, P95 latency, and P99 latency

Unless I'm mistaken, only average TPS is reported, but not TPS over time. Again, it varies a lot (especially due to checkpoints!) and you need variation over time to understand patterns and determine SLAs.

> We tested two scale factors: 6849 (~100 GB) and 34247 (~500 GB).

I'd set them an order of magnitude apart (100GB and 1TB). 1TB+ is nowadays not unseen and therefore represents a step into the scale path that I believe should also be compared.

> While all services offer high availability, the underlying architectures differ. Some use standby replication, others use shared or distributed storage layers. Since we are focused on single-node compute and storage performance, we tested without HA enabled to isolate that.

So again, it's not realistic. The impact of HA in production is quite measurable in performance, especially if semi-sync is used.

But more importantly, you are comparing systems that are already redundant and highly available (e.g. Aurora, writing six copies of the data) with a single-instance one writing (if I'm not mistaken) to an ephemeral local NVMe. This is watermelons to grapes comparison (I assume HA CH clusters talk over the network for each confirmed transaction; but here they only go to local NVMe).

> Default Postgres configuration

I believe this is a mistake. Postgres must be tuned (at least to a reasonable degree) and try to homogenize the configuration across the tested systems. Otherwise you are not benchmarking Postgres, but rather the conservationism in the decisions of the default configurations of the managed services.

> We did not compare pricing.

So you leave the fun part out?

> Systems included

I miss a self-managed EC2 cluster (with local NVMe) for comparison.

At StackGres [1] we find Timescale to be one of the most used extensions. Timescale is quite a successful project!

StackGres is actually the first solution recommended by Timescale for self-hosting with Kubernetes operators [2].

So if you are into Kubernetes (or if not, consider it, using something like K3s [3] is quite straightforward and lightweight on resources), this is probably a great option to self-host which would avoid you all the hassle of manually doing all the setup involved for production-ready clusters.

[1] https://stackgres.io

[2] https://www.tigerdata.com/docs/get-started/choose-your-path/...

[3] https://k3s.io/

TBH I don't think it's that straightforward, I see it more of a notable architectural change. At a very high level, this means:

* Adding a sharding function, as you say.

* Developing an external service for metadata (shard placement) or alternatively have that metadata in one place and replicate (consistently!) to every query router.

* Implementing functions/catalogs for the users to understand the placement and configure/alter it.

* Implementing shard migration / rebalancing capabilities, possibly using Postgres logical replication (plus notable automation).

Here's one idea if you follow this path, something that Citus doesn't have: make the sharding function pluggable and pick one by default which is well-known and available in many languages (e.g. xxhash). If you do so, and guarantee stability of those functions, they could be used externally (applications) to route queries / inserts especially to the appropriate shard. While it makes application more complex, it may allow (combined with access to the metadata service) for faster ingestion paths (this is often known as application assisted sharding), and its not exclusive of the query routers.

Edit: formatting

I have mentioned this before, but here it goes again:

I'm really happy that there's more options for Postgres sharding and I applaud Pgdog and the team's efforts and energy.

Having said that, this makes it a no-go for me:

> shard_number = hash(data) % num_shards

https://docs.pgdog.dev/features/sharding/basics/#terminology

Most sharding solutions distribute the hash value over linear ranges, that then split across "virtual shards", that are then placed on the physical shards or worker. This allows for shard replacement when needed. For example, Citus works this way, and even adds convenience functions for shard migration (using logical migration) in an automated way. That's all I'd need.

Operationally, it's worlds apart. With modulo distribution the only way to replace data is to reshard everything --something you don't want to do however fast the operation may be.

> having the ability to scale the coordinator past a single machine

Since Citus v11 (released 4 years ago), any worker node can also work as a "query router" (a node that you can query against [1], and works from this perspective as a pure coordinator:

> for very demanding applications, you now have the option to load balance distributed queries across the workers

You can also setup such query routers as dedicated nodes by setting the `shouldhaveshards` to `false`, becoming an effective coordinator (for querying; not for metadata operations).

So with Citus you can absolutely have as many query routers (coordinators if you wish) as you want.

[1]: https://www.citusdata.com/updates/v11-0/#metadata-sync

Edit: formatting, typo

> You definitely don't want to run a production system at saturation! But it's worthwhile to measure a complex system like Postgres at saturation, see when it gets there and how it behaves there, and then run at a slightly lower throughput.

I disagree. It's worthless a number at saturation. Because "a slightly lower throughput" is at best an unqualified hand-waving. Real numbers can be quite far from that saturation point.

Quote instead real production numbers. You can define them clearly, it's not that hard. E.g.: p95 below 10ms latency. That's it. Measure and report that number.

> I've done some testing (not in the blog post)--doubling instance size/IOPS doesn't improve performance significantly because it doesn't affect the WAL bottleneck. Local NVMe should have a significant impact in theory, but I haven't tested this myself.

But those would be interesting numbers to share! "Doesn't improve performance significantly" --sorry, I'm not big friend of unqualified data points. Is it 10%, 20%, 50%? And definitely, when measured at saturation, surely you don't see improvements. But if measured at an operational regime, you should probably see notable improvements (unless other scaling factors start to dominate, in which case your benchmark becomes much more meaningful because then you are finding Postgres scaling limits and not just the limits of the disk on which it's running). Changes the picture dramatically.

> Those are usage examples (notice the 1000 rps)--actual benchmarks were run at and were stable at much longer duration.

Sorry, but if you use that as an example, gives me little confidence about the real intent. But glad to hear you run at longer duration --add that information to the post! But again, that's not enough. Show the bloat and demonstrate how stable it is, given the tuning required to keep it contained, of course. Also show the tps over time --I'm sure it drops notably in the presence of checkpoints-- and then the "under 10ms latency at p95" will become dominated by write performance during checkpoints.

Because when you determine your SLOs, it's not at the happy path, but the opposite. And saying "Postgres can do 144K writes/sec on this machine" is beyond the happy path, so it's not meaningful for me.

> we find a Postgres server can handle up to 144K of these writes per second. That’s a lot, equivalent to 12 billion writes per day.

Based on the shown graph, this is misleading at best, essentially false. After 120K writes/s p50 spikes from 10ms to 1s (1 second for a write!!!!). That's two orders of magnitude latency spike, and an unacceptable one for an OLTP workload. It clearly shows the server is completely saturated, which is clearly a non operational regime. Quoting 144K is equivalent to quoting the throughput of a highway at the moment traffic comes to a standstill.

Based on this graph the highest number I'd quote is 120K. And probably you want to keep operating the server within a safe margin below peak, but since this is a benchmark, let's call 120K the peak. Because actually p50 is not even the clear-cut. It should be a higher percentile (say p95) at which latency is within reasonable bounds. But for the shake of not over complicating, it could be taken as a reference.

> We found that the bottleneck was in flushing the Postgres write-ahead log (WAL) to disk

Therefore, you are not measuring Postgres peak performance, but rather Postgres performance under the IO constraints of this particular system. Certainly, 120K IOPS is the maximum that this particular instance can have. But it doesn't show if Postgres could do better under a more performant IO disk. Actually, a good test would have been to try the next instance (db.m7i.48xlarge) with 240K IOPS and see if performance doubles (within the same envelop of p50 latency) or not. And afterwards to test on an instance with local NVMe (you won't find this in RDS).

> From [1]: > Postgres insert throughput > uv run python benchmarks/postgres_insert.py --rps 1000 --duration 300

300 seconds test duration?? This is not operational. You are not accounting for checkpoints, background writer, and especially autovacuum. Given that workflow pattern includes UPDATEs, you must validate bloat generation (or, equivalently, bloat removal) by a) observing much longer periods of time (e.g. 1h) and b) making sure the autovacuum configuration (and/or individual table vacuum configuration if required) makes bloat contained in a stable way. Otherwise, shown performance numbers will degrade over time, making them not realistic.

Only after proper autovacuum tuning and under the effects of bgwriter (also tuned!) and checkpoints (all required, especially for write intensive workloads), over much larger periods of time, the benchmark could be considered meaningful.

> We next measure the scalability of Postgres-backed queues.

I'd recommend benchmarking the recently announced PgQue project, that operates bloat-free (one of the largest operational hurdles in queue-like Postgres workloads). See [2] for a previous discussion in HN.

[1]: https://github.com/dbos-inc/dbos-postgres-benchmark [2]: https://news.ycombinator.com/item?id=47817349

Yeah, I know. Welcome to the club :(

https://x.com/ahachete/status/2035783292549755228

> The extension model is great, but it doesn't work with existing postgres providers (RDS, Aurora, etc.). Unless one such extension becomes standard enough that all providers will support it. That would be ideal, IMO.

That's true, but that's a problem of the PGaaS providers for them to fix by providing the best functionality available. I'm planning on following this route in a pure OSS basis.

> Are you talking about connection establishing time or for query delay? I think it should normally be under a millisecond for the later.

Network trip. If the proxy is not co-located with the database but rather a network hop away, that's usually adding at least 1ms there, could be more.

IMO transparent proxies for observability are not the best pattern. And I speak from experience, we developed the Postgres plugin for Envoy [1], [2] and we use it in StackGres [3], among others, for this very same reason, observability.

There's two main problems with said proxies:

* Latency. Yes, yes, yes, they add "microseconds" vs "milliseconds for queries", and that's true, but just part of the story. There's an extra hop. There's two extra sets of TCP layers being traversed. If the hop is local (say a sidecar, as we do in StackGres) it adds complexity in its deployment and management (something we solved by automation, but was an extra problem to solve) and consumes resources. If it's a network hop, then adds milliseconds, and not microseconds.

* Performance. It's not that hard to write a functioning PG wire proxy (it's not trivial either). But it is extremely hard to make it perform well under high load scenarios. Most of the proxies I have seen crack down under moderate to high performance.

What's the solution then? The Postgres extension model to capture the metrics (we also experimented with eBPF, but it causes too many kernel-user space context switches when you can do the same in an extension without them), and a small sidecar to push the metrics out via a standardized protocol like OTEL.

[1]: https://www.envoyproxy.io/docs/envoy/latest/configuration/li...

[2]: https://www.cncf.io/blog/2020/08/13/envoy-1-15-introduces-a-...

[3]: https://stackgres.io

Edit: formatting

Postgres can really scale well vertically (and horizontally for read-only workloads) as the post shows.

However, I'm still surprised about the reasons for not sharding. They have been mentioned before, but I haven't seen a substantial rationale.

Sharding is almost only analyzed from the perspective of write scaling. But sharding may not only be about write scaling, but a path to reducing blast radius. And this is something that triggers much earlier than write scaling needs (especially given how well Postgres scales vertically and reads).

When you shard your database, you end up having N clusters (for HA purposes, each "shard" must be a primary-replica(s) cluster itself), each holding 1/Nth of the data.

There are certain scenarios which, while unlikely, may hit you: data corruption in the WAL replication stream, a problem during a major upgrade, a situation that requires a whole cluster restore from a backup, you name it. For those cases, the whole cluster may experience notable downtime.

If you have a single cluster, 100% of your users experience downtime. If you sharded into N clusters, only 1/Nth of your users experience downtime. For a company servicing 800M users the difference from both scenarios is dramatically different. Even for much much smaller companies.

I'm puzzled why this is not perceived as a risk, and if it is not, how it is mitigated.

While I wouldn't advocate to shard "too early", given that it comes with notable caveats, I believe more and more in sharding your workloads when possible more earlier than later. Way before truly needing it from a write scaling perspective. Because apart from reducing the blast radius, it applies implicitly the principle of "divide-and-conquer", and your problems become much more manageable (your number of connections per cluster decreases at will, backup restore times can be a fraction of the time, logical replication can be considered as a true option for replication/upgrades/etc if you keep shards relatively small and many other operational procedures are greatly simplified if now you have much smaller databases, even if you have many more of them).

Java (modern Java) would be awesome!

I'd also try it, but I'm Linux only ^_^

Not my experience.

It's junior level coding and maybe senior level advising, but even then only when clearly directed with the right questions and guardrails.

As an autonomous thing? Junior at best.

That's why I think it's extremely helpful for seniors: with proper guidance, it really boosts your productivity, writing notable parts of the code.

There's a fundamental disconnect: OP refers to senior engineers being replaced with AI, whereas the evidence and logical reasoning points much more to junior engineers being replaced by AI. And that premise seems like a quite plausible one...

Thank you for acknowledging this and updating the blog post correspondingly.

I'd love to test and compare PeerDB with Debezium (Embedded), and even SynchDB. But as said, the licensing is a blocker for us. And given the focus and bandwidth we currently have, we won't have the chance to deeply look at it unless there's a high chance we could integrate it into StackGres.

Anyway feel free to DM me if you'd like to talk more.

> "As part of the acquisition, we made an important decision: not only to keep PeerDB open source"

> "Here goes the Open Source reference to our validation logic."

> "PeerDB Open Source Repository"

I hate to be that guy, but PeerDB seems to be governed by the Elastic License [1] which makes it NOT open source.

The difference is not small, but significant for many. For one, it won't get integrated into other OSS projects.

In my particular case, we have integrated Debezium Embedded into StackGres, as a high level object (CRD) named SGStream [2]. It allows Postgres logical replication from Postgres and exports to another Postgres and/or CloudEvents. No Kafka required. We'd love to consider other alternatives like PeerDB, but not being OSS is a red line we can't cross (having said that, we're really happy with Debezium in general, but having competition and alternatives it's always great).

[1]: https://github.com/PeerDB-io/peerdb/blob/main/LICENSE.md

[2]: https://stackgres.io/doc/latest/reference/crd/sgstream/#sgst...

[edit: formatting]

"Most components" seems a bit of an understatement when compared with the Framework.

Sure, some components can be replaced. And not at the same cost (opening and manipulating the Framework vs the ThinkPad). But not all, like the motherboard.

I may have built multiple dozens of computers in my life, so it's not that I'm new to this world.

TBH I haven't deeply tested it yet. Laptop is a few days old and I'm still "automating the install" (Ubuntu autoinstall + Ansible tasks for post-installation including BIOS upgrade and install Nix + Nix and home manager to install everything else; post(s) upcoming), so I haven't done "production use" yet.

Anyway, and while I love long battery life, it's not my main concern. Most of the time I have a power socket available and/or a nice portable battery pack that does the job. Laptop feels so much faster than my X1 Carbon that everything else seems to be a distant second feature.

P.S. Hi, Yorick, again, not the first time we cross paths ;)

I have two Framework 13 DIY, top of the line (Ryzen AI 9 HX 370, 96 GB of RAM, 2TB WD BLACK SN850X). Well, TBH just one, the other one was stolen with less than one month, but that's a different story.

Can't be happier with this choice.

First of all, now I feel confident I can easily tinker with my laptop, open it and replace any components. No, I'm not repeating their Marketing, I'm truly confident now. Some months ago my previous Lenovo X1 Carbon stopped charging and I was scared as hell. Sure, I have everything in version control and cloud drives blah blah blah but if it doesn't charge, it's dead. Sure, I can extract the NVMe (is it easy at all?) and rsync the data (for a faster recovery) but is that easy, feasible, can I do it on the go (X1 stopped charging while I was on a trip).

The Framework 13 DIY I built it in less than 10 minutes. I can easily disassemble again in a heartbeat. This is priceless.

Other than this, build quality is higher than expected, and several other people said exactly the same when I showed my unit. Powerful? Most than any desktop out there. Perfectly portable. Works well OOB with Linux. What else do I need? Nothing, it's my laptop and will be the next one.

(Actually the next one may be cheaper, as I may only need to replace the motherboard, we will see)