I've had pretty good luck setting up logical replication from Heroku to the new provider and having a 10-15 minute maintenance window to catch up once it's in sync. Might be worth considering.

You might also want to add a warning about Postgres versions. There's some old bugs around primary key hash functions that can cause corruption on a migration. I've seen it twice when migrating from Heroku to other vendors.

https://apps.dtic.mil/sti/pdfs/ADA270438.pdf

Here's a detailed report on it. Pages 32-33 has their take on material analysis, probably the most relevant to this failure

https://apps.dtic.mil/sti/pdfs/ADA270438.pdf

I'm personally more suspicious of oceangates manufacturing process than the material, but I'm far from an expert here.

This isn't the only place Postgres can act like this, though. I've seen similar behavior when a foreign data wrapper times out or loses connection, and had to resort to either using kill -9 or attaching to the process using a debugger and closing the socket, which oddly enough also worked.

Might be worth generalizing this approach to also handle that kind of failure

There's a handful of companies that have scaled Citus past 1PB for production usage, but the examples I'm aware of all had more engineering to avoid capability or architecture limitations than one might like. I'd love to see someone come back with a fresh approach that covered more use cases effectively.

Disclaimer: former Citus employee

1. Does the schema have an obvious column to use for distribution? You'll probably want to fit one of the 2 following cases, but these aren't exclusive:

    1a. A use case where most traffic is scoped to a subset of data. (e.g. a multitenant system). This is the easiest use case- just make sure most of your queries contain the column (most likely tenant ID or equivalent), and partially denormalize to have it in tables where it's implicit to make your life easier. Do not use a timestamp. 

    1b. A rollup/analytics based use case that needs heavy parallelism (e.g. a large IoT system where you want to do analytics across a fleet). For this, you're looking for a column that has high cardinality witout too many major hot spots- in the IoT use case mentioned, this would probably be a device ID or similar

3. When do you want to migrate? Logical replication in should work these days (haven't tested myself), but the higher the update rate and larger the database, the more painful this gets. There's not a lot of tools that are very useful for the more difficult scenarios here, but the landscape has changed since I've last had to do this

4. Do you want to run this yourself? Azure does offer a managed service, and Crunchy offers Citus on any cloud, so you have options.

5. If you're running this yourself, how are you managing HA? pg_auto_failover has some Citus support, but can be a bit tricky to get started with.

I did get my Citus cluster over 1 PB at my previous job, and that's not the biggest out out there, so there's definitely room to scale, but the migration can be tricky.

Disclaimer: former Citus employee

For other use cases, there can be big gains from cross-shard queries that you can't really match with partitioning, but that's super use case dependent and not a guaranteed result.

That being said, this does seem to handle replicas better than Citus ever really did, and most of the features it's lacking aren't relevant for the sort of multitenant use case this blog is describing, so it's not a bad tradeoff. This also avoids the coordinator as a central point of failure for both outages and connection count limitations, but we never really saw those be a problem often in practice.

The best way I've found to approach it is to treat GCP as something that has to be evaluated at an individual service level. It's great if you're on one of their expected workflows/golden paths, and you can get lucky with a good fit if you aren't, but they seem to have a lot of unspoken assumptions and limits baked in that might or might not align with your use case.

Disclaimer: My use cases are pretty unusual from talking to our account rep, so this might be over-fitting to weird data.

https://en.wikipedia.org/wiki/The_Road_Not_Taken_(short_stor...

PDF of story: https://www.eyeofmidas.com/scifi/Turtledove_RoadNotTaken.pdf

Sadly, we're still using Heroku DNS, but this should accelerate finding an alternative.

I think that only one or two of those environments were heavily used production instances, so if there is a serious gotcha here it might not have been apparent to me.

Does anyone know of a browser extension to limit access to the history API?