caffeinated_me

There was no intent for a human to read the message. By your logic, if she wrote a threat in a diary and a burglar broke in and read it, it would be a crime on her part.

Yeah, I'm probably misremembering some details there. Thanks

Do you have any benchmarks with a mix of long open transactions and short ones? I've struggled a lot with WAL-E in the past there, and am curious if that changes here.

Looks like that still has downtime for a Postgres migration- you're suggesting going into maintenance mode and just doing a dump/restore. I've seen that take hours once you hit the terabyte scale, depending on hardware.

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.

Thanks! Out of curiosity, does the NVME have a big effect on replication throughput? I've been wondering how much trouble I've had with other solutions is due to parsing WAL and how much is just slow cloud disk

It sounds like you're doing something similar to how Databricks works now that they've acquired neon, or Snowflake now that they got Crunchy. I'm guessing the local SSD is a big advantage, but what else is different with your approach?

I've linked this elsewhere in thread, but here's testing results from a US Navy pilot project for carbon fiber unmanned subs. It looks like this found it pretty viable.

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

This isn't the first carbon fiber submarine, although it is the first manned one. The US Navy tried out an unmanned model in the 80s, and got much better results- they were expecting at least 1000 successful dives before stress fatigue was an issue.

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.

Good find! I've seen similar behavior before and was wondering why it wasn't easy to stop.

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

I'd argue that horizontally sharded databases can work well, but they do tend to have significant non obvious tradeoffs that can be pretty painful.

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

It can be great, depending on your schema and planned growth. Questions I'd be asking in your shoes:

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

2. Are you sure you're going to grow to the scale where you need Citus? Depending on workload, it's not too hard to have a 20TB single-server PG database, and that's more than enough for a lot of companies these days.

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 a multi-tenant use case, yeah, pretty close to thinking about partitioning.

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.

Depends on your schema, really. The hard part is choosing a distribution key to use for sharding- if you've got something like tenant ID that's in most of your queries and big tables, it's pretty easy, but can be a pain otherwise.

Seems like this is a similar philosophy, but is missing a bunch of things the Citus coordinator provides. From the article, I'm guessing Citus is better at cross-shard queries, SQL support, central management of workers, keeping schemas in sync, and keeping small join tables in sync across the fleet, and provides a single point of ingestion.

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.

Any recommendations on telehealth suppliers to contact for that compounded formulation? They're easy to find, but I'm not sure who is trustworthy on this topic.

I've generally found something similar- lots of gotchas, but also some very useful products.

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.

I believe they're referring to the short story of The Road Not Taken, by Turtledove.

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

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

The article mentions a need for primary keys for data sync. Does anyone know if compound primary keys are currently supported? That's been a huge pain for me with the existing stack, so it would be nice to have an alternative.

Was that break on Market Street, out of curiosity? After a related incident, I discovered that I was now the 5th co-worker a friend had that had broken a wrist or leg there.

We did some trials and ended up on Crunchy Data for the Postgres part of the equation. Wish we had done so earlier, logical replication was a big win.

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

I've seen ZFS being used with Postgres in a few different environments. Seems to work fine for the most part- surprisingly good compression (~8X in one case, usually lower), with the major downside being increased CPU usage when taking advantage of said compression.

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.

My company was seeing GCP Airflow environments not responding, but they seem to have recovered in the past few minutes.

This seems to have some fairly scary security implications if used maliciously, but I can't think of a good way to protect against this.

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