4 ms·

What I emphasized in this article (author here) is scaling postgres backups. This works because there's already rock-solid systems built into postgres + surroun

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. Hanging around to answer questions, too :)

gandreani 2mo ago
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
- imjosh-dev 2mo ago
  (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
  - adastral 2mo ago
    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?
    - bddicken 2mo ago
      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.
  - gandreani 2mo ago
    Ah that makes a lot of sense! Thanks for taking the time to implement. I like the design of the system.
gregsadetsky 2mo ago
hey, just curious - would you consider using or recommending https://pgbackrest.org/ https://pgbackrest.org/ (in non massive postgres backup scenarios) And do you think PlanetScale would consider supporting that project? (it was shut down but resurrected thanks to support from sponsors)
- bddicken 2mo ago
  pgBaseBackup seems very cool. It's great that they got support to continue building. We just don't use it presently.

gandreani 2mo ago

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

imjosh-dev 2mo ago
(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
- adastral 2mo ago
  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?
  - bddicken 2mo ago
    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.
- gandreani 2mo ago
  Ah that makes a lot of sense! Thanks for taking the time to implement. I like the design of the system.

gregsadetsky 2mo ago

hey, just curious - would you consider using or recommending https://pgbackrest.org/ https://pgbackrest.org/ (in non massive postgres backup scenarios) And do you think PlanetScale would consider supporting that project? (it was shut down but resurrected thanks to support from sponsors)

bddicken 2mo ago
pgBaseBackup seems very cool. It's great that they got support to continue building. We just don't use it presently.