tommyzli

Just last week I started hacking on a k8s operator for managing postgres roles, grants and databases. I really like the continuous reconciliation for this scenario, especially having previously worked at places that managed this with shell scripts or terraform.

I'm curious to hear more about your troubles with alloydb. It's something I just started to test out at work

the last time I gave the Postgres operator space a serious look was about a year ago, and at the time the Zalando operator was far and away the most feature complete and mature.

We had a couple unusual requirements that the operator wasn't really suited for, so we ultimately ended up writing our own helm chart and forgoing the operator route altogether

Wow, I wasn't expecting such an in-depth response!

It sounds like making user profiles a reference table would solve the cross-shard join problem, but what would the performance implications look like?

The docs just say that it does a 2PC, which I'm assuming won't perform very well in a high-write workload

1. You'll have to define "slow" - I have a 3TB table where an index only scan takes under 1ms

2. hot_standby_feedback is absolutely safe. I've got 5 hot standbys in prod with that flag enabled

3. Again, it depends on how "heavy" your update throughput is. It is definitely tough to find the right balance to configure autovacuum between "so slow that it can't keep up" and "so fast that it eats up all your I/O"

Believe it or not these numbers are actually from _after_ me and some others spent a few weeks cleaning up our heavier queries

TIL, this is really good to know! Do you know offhand if this is a new feature, or have I just always been wrong

It's not in the post, but I answered this in a separate thread. RDS doesn't let us provision as many IOPS as we need.

Apparently Aurora behaves differently, but I wasn't aware of that when we specced out the project.

Nope.

We are living life on the edge to an extent, but we have 5 hot standbys across AZs and regular backups + WAL archives to S3.

May not be as durable as EBS, but it's enough for me to sleep soundly at night. And with a highly concurrent WAL-G download, it takes like an hour to catch up a new replica from scratch.

Apparently I'm living in the twilight zone because I have a vivid memory of reading the Aurora docs and seeing the same limit. Oh well, it's something to consider for the next upgrade.

basically what paulryanrogers said.

We thought about migrating to Citus, but I don't have a good idea of how to shard our dataset efficiently.

If we were to shard by user id, then creating a match between two people would require cross-shard transactions and joins. Sharding by geography is also tough because people move around pretty frequently.

ants_a is correct. Also, our NVMe storage is ephemeral so you aren't recovering from a power loss anyways :)

How would that have worked with multiple replicas cascading from the new primary? Streaming replication doesn't work across versions, so would we have had to build out a tree of new instances, then pg_upgrade them all at the same time?

Thanks! We stuck with plain EC2. RDS has a limit of 80,000 provisioned IOPS and our read replicas on Postgres 9.6 would regularly hit near double that during peak