Broadly for Pg, minor version upgrades are straightforward as the data on disk is guaranteed to be compatible. All it takes is a restart or switchover to upgrade.
Major versions are more challenging for "vanilla" Postgres because that's not the case. Storage/catalog formats may change. There needs to be an explicit upgrade process for the data (eg, a database created with v18 won't work out of the box with v19).
The cool thing is, software like Neki (and Vitess for MySQL, which we maintain) have architectures that lend themselves to making this much more feasible. Because the actual database nodes sit behind a router + parser layer with Pg/MySQL compatibility, the upgrades can be done transparently to the user. We plan to write more about how this works in the future.
You can do live parallel routing and blue-green migrations transparently. In your docs you already describe how to do it but the user has to do it manually at the application level when you can do it just fine at the DB load balancer/router level.
Exactly. When you have a router + query parser in front of the db nodes, the burden of managing multiple versions simultaneously can be taken out of the app and into the database layer.
I believe most major versions of postgres only change metadata, so the downtime only scales with the size of the schema (usually small) and not the size of the data.
Assuming you're on a filesystem that supports reflinks (Btrfs, XFS), `--clone` is an interesting option as well. Though assuming you create a volume backup before starting migration, hardlinks don't really have downsides and will probably have better performance after the upgrade since the avoid copy-on-write.
What I emphasized in this article (author here) is scaling postgres backups. This works because there's already rock-solid systems built into postgres + surrounding tooling to build from.
The broader takeaway is the principle of, "how do I take something that doesn't scale on its own and make it so?" This applies to backups, compute, storage layers, proxies. It's why Neki and Vitess are so powerful for everything from small 1GB databases to petabytes.
Hello! Very nice article. I have a couple of questions if you don't mind.
When doing the last streaming of the wal from the primary the article mentions that the nodes will catch up to replication time `T`.
How do the nodes coordinate this time `T`? Is it simply just choosing a time in the future (after the backup has started) and waiting till they all catch up or is there more realtime coordination happening?
Also, in another part it's mentioned that "Time T is saved to ensure we know the precise time, down to the second, included in this backup." My question is that if 1 second is granular enough? I'm assuming that this is a simplification for the sake of explanation and Time T is a timestamp with at least millisecond granularity. I regularly play with otel data that can have nano-second granularity so I'm assuming is millisecond or more
(Also PlanetScale employee here)
Each shard finishes a backup at it's own time T, so two different shards could finish minutes apart (or even more, depending on the difference in size).
for pretty much any use of a backup, you'll effectively be doing a PITR, not a raw restore from the cluster. The PITR timestamp is what unifies all the clusters together, regardless on when each backup finishes. think of backups as jumpstarts for actual restores (like for cluster resizes), where WAL replay and replication get the node to real time (or some specific point)
with that said you can restore without PITR if you don't care about synchronization, but generally you'll just use PITR
How do you cope with distributed transactions? (I'm not sure if you support them?)
I know that Citus has a `citus_create_restore_point()` (or so) function that, when called, guarantees that no 2PC commits are in flight and creates a WAL restore point in every shard. Therefore, restoring shards to that point will leave the DB in a consistent state. Do you do something similar?
Neki will support cross-shard ACID transactions with a combination of an external transaction coordinator and some changes to PostgreSQL itself to support this (either by engine modifications or extensions).
I expect as part of that, we'll allow users to leverage something similar to make sure that we don't get transaction tearing in backups.
Technically, they are using js + gsap + svg embedded i the article with iframes.
Process-wise, I drafted most of them as static images in excalidraw, passed the images along to cursor for a first draft, applied styling rules, and then did a bunch of fine-tuning.
Thank you so much.!!!!
It can't be easy to put this much care into helping others understand, with that hand drawn feel and everything. I really learn a lot from it.
The animations were great and made everything really easy to understand. Have a nice day.!
A lot of what you're referring to is dictated by the sharding strategy. Vitess and Neki both let you configure this via the VSchema / data topology (That's what I'm getting at here)
This is something you face any time you shard data, no matter the system. Single-shard queries are preferred to cross-shard ones. We want 99% of what we do to be single shard, but occasionally cross-shard work is unavoidable.
Exactly. sharding is for scaling usecase where queries can be handled within a shard(co-partitioning). If sharding strategy can assign keys from two tables where join is needed you can get achieve same performance.
In another usecase where joins are not needed realtime, you can build another system where results are precomputed and cached.
I consider sharding as methodology for scaling instead of onefit all solution. Overall it depends on usecase and sound sharding strategy for performance.
> So an extreme example is OpenAI needing 50 replicas, but we're doing five blades ... err, we're doing 768 servers because the need arose "pretty quickly"?
If you read the OpenAI article, you'll see that they actually used sharding to offload a bunch of work from their "1 primary 50 replicas setup"
>>> "To mitigate these limitations and reduce write pressure, we’ve migrated, and continue to migrate, shardable (i.e. workloads that can be horizontally partitioned), write-heavy workloads to sharded systems such as Azure Cosmos DB..."
The 768 servers and 1PB example is just one of many configurations. A business with 10TB may choose to go from a monolithic database to a 8-shard setup to improve backup times, eliminate single-point-of-failure, have more breathing room for scaling.
Process wise (for most of them) I sketched them out in advance in excalidraw for figure out layout, then passed these along to cursor to have it build out an initial draft from the image, then used some styling rules to get all the styles inline, then did a bunch of fine-tuning.
Spreading requests out across hundreds, thousands, and in some cases even more is precisely what is done in the industry for big databases! Good examples:
The cashapp post is actually working towards GPs point. It explains that networked storage MySQL was unreliable and expensive. They migrated to $fancytool which offers a db instance with attached local nvme.
While cashapp may actually need sharding, there are so many companies who are overpaying for shitty performing network storage databases.
Not to mention you are subject to cloud provider networking and compute allocation code. They change it. Big slow network storage DB suddenly gets even worse and you don’t have leverage to have it fixed on their end.
Caching at all levels is key to good db performance (cpu cache <-> ram <-> disk). CPU cache optimization is not my area of expertise, but I did write another fun article on io devices and how it related to databsae perf:
Network saturation, and just resource saturation broadly, is a huge reason to shard. If/When the network, cpu, or disk for a subset of shards becomes a bottleneck, add more shards.
Major versions are more challenging for "vanilla" Postgres because that's not the case. Storage/catalog formats may change. There needs to be an explicit upgrade process for the data (eg, a database created with v18 won't work out of the box with v19).
The cool thing is, software like Neki (and Vitess for MySQL, which we maintain) have architectures that lend themselves to making this much more feasible. Because the actual database nodes sit behind a router + parser layer with Pg/MySQL compatibility, the upgrades can be done transparently to the user. We plan to write more about how this works in the future.