pwmtr

The author of the blog post here.

In hindsight, the kernel bug seems obvious, but the process of tracking it down was quite interesting. I especially enjoyed the statistical analysis part, which isn't something you normally get to do while debugging :)

Anyway, I'd be happy to answer any questions you might have.

We’ve been seeing the same trend. Lots of teams moving to Hetzner for the price/performance, but then realizing they have to rebuild all the Postgres ops pieces (backups, failover, monitoring, etc.).

We ended up building a managed Postgres that runs directly on Hetzner. Same setup, but with HA, backups, and PITR handled for you. It’s open-source, runs close to the metal, and avoids the egress/I/O gotchas you get on AWS.

If anyone’s curious, I added here are some notes about our take [1], [2]. Always happy to talk about it if you have any questions.

[1] https://www.ubicloud.com/blog/difference-between-running-pos... [2] https://www.ubicloud.com/use-cases/postgresql

If you are looking for Postgres on Hetzner, you may want to check out Ubicloud.

We host in various bare metal providers, including Hetzner. (I am the lead engineer building Ubicloud PostgreSQL, so if you have questions I can answer them)

Yes, that is correct. As you noted, Aurora performs quite well on TPC-C due to its quorum-based write acknowledgment and on TPC-H when data modifications are minimal. However, CH workloads introduce contention, write amplification, and read/write reconciliation under analytical scan pressure, which is a difficult use-case for Aurora to handle.

That said, in our tests we only saw 2x improvements in CH benchmarks. However, we found out that it was due to an architectural issue in our VM I/O path and how we virtualize the storage. Based on our estimations we should see ~5x difference but for that we need to revamp our storage virtualization first.

We have plans for publishing a CH benchmark results on a follow up blog post. However, we didn't want to do that for now to not put misleading results.

Really appreciated the authors persistence on keeping to use PostgreSQL. There are many specialized solutions out there, but at the end they usually lack PostgreSQL's verstatility and battle testedness.

I read something similar on Yuval Harari's Homo Sapiens, where he suggests wheat domesticated humans not the other way around. An excerpt can be found here [1]. Whole essay is great but I especially liked this part:

> The word “domesticate” comes from the Latin domus, which means “house.” Who’s the one living in a house? Not the wheat. It’s the Sapiens.

[1] https://www.ynharari.com/topic/ecology/

At the time of our investigation, we found few articles supporting that power caps could potentially cause hardware degradation, though I don't have the exact sources at hand. I see the child comment shared one example, and after some searching, I found a few more sources [1], [2].

That said, I'm not an electronics engineer, so my understanding might not be entirely accurate. It’s possible that the degradation was caused by power fluctuations rather than the power cap itself, or perhaps another factor was at play.

[1] https://electronics.stackexchange.com/questions/65837/can-el... [2] https://superuser.com/questions/1202062/what-happens-when-ha...

The root cause was a problem with the motherboard, though the exact issue remains unknown to us. I suspect that a component on the motherboard may have been vulnerable to power limitations or fluctuations and that the newer-generation motherboards included additional protection against this. However, this is purely my speculation.

I don't believe they simply lifted a power cap (if there was one in the first place). I genuinely think the fix came after the motherboard replacements. We had 2 batches of motherboard replacements and after that, the issue disappeared.

If someone from Hetzner is here, maybe they can give extra information.

Author of the blog post here.

Yeah, this is generally a good practice. The silver lining is that our suffering helped uncover the underlying issue faster. :)

This isn’t part of the blog post, but we also considered getting the servers and keeping them idle, without actual customer workload, for about a month in the future. This would be more expensive, but it could help identify potential issues without impacting our users. In our case, the crashes started three weeks after we deployed our first AX162 server, so we need at least a month (or maybe even longer) as a buffer period.

Definitely interesting material. I realized, especially in last few years, there is an increased interest on moving away from propriety clouds/PaaS to K8s or even to bare metal, primarily driven by high prices and also interest of having more control.

At Ubicloud, we are attacking the same problem, though from a different angle. We are building an open-source alternative to AWS. You can host it yourself or use our managed services (which are 3x-10x more affordable than comparable services). We already built some primitives such as VMs, PostgreSQL, private networking, load balancers and also working on K8s.

I have a question to HN crowd; which primitives are required to run your workloads? It seems the OP's list consists of Postgres, Redis, Elasticsearch, Secret Manager, Logging/Monitoring, Ingress and Service Mesh. I wonder if this is representative of typical requirements to run HN crowd's workloads.

Our runners are running on our own infrastructure. We get bare metal instances from providers like Hetzner or Leaseweb and virtualize them. Your runners run on the ephemeral VMs on those bare metal instances.

Is being on AWS a requirement for you? Is it because of caching? We also have our own runner cache. You can find more information from here https://www.ubicloud.com/docs/github-actions-integration/ubi...

If you have more questions, please let me know or alternatively you can send us a mail from [email protected]

They have a Ruby control plane; https://www.crunchydata.com/blog/crunchy-bridges-ruby-backen...

I cannot comment on Let's Encrypt's reliability. Maybe I had just too many bad experiences from DigiCert outages and I'm bit pessimistic. However, their status page does not give much confidence https://letsencrypt.status.io/pages/history/55957a99e800baa4...

I think if you need to generate a certificate once in a while, using Let's Encrypt or DigiCert is OK. Even if they are down, you can wait for few hours. If you need to generate a certificate every few minutes, few hours of downtime means hundreds of failed provisionings. Hence, we opted for self-signing.

In terms of reliability, it is great, because we control everything. It is also quite fast; it takes few seconds to generate and sign a certificate. The biggest drawback is that you need to distribute the certificate for CA as well. Historically, this was fine, because you need to pass CA cert to PostgreSQL as a parameter anyway, so the additional friction for users that we introduced due to CA cert distribution was low. However with PG16, now there is an option sslrootcert=system, which automatically uses OS trusted CA roots certs. Now the alternative is much seamless and requires almost no action from user, which tilted the balance in favor of globally trusted CAs, but still it doesn't give me enough reason for the switch.

I have few ideas around simultaneously self signing a cert and also requesting certificate from Let's Encrypt. The database can start with the self signed certificate at the beginning and we can switch to Let's Encrypt certificate when it is ready. Maybe I'd implement something like that in the future.

There is a "Connection Pooling" box at the first diagram. Though, the article does not talk about all boxes. It would simply be very long wall of text if we had mention all components. Instead we picked some of the more interesting parts and focused on those.

* Sorry for the confusion, our customers do not necessarily want to deploy database in a container. It is just we encountered lots of folks who wants to do that and asked us about how to handle HA and backups. Though, I don't think it is rare situation to be in. Even in this thread, there are multiple people asking about running Postgres on K8s.

* We saw many deployments where communication between web server and database were going through public internet. It doesn't need to be for anonymous users. It is also even somewhat common where web server and database are managed by different SaaS providers, so they have to (in most cases) communicate through public network.

* We (and all cloud providers) are trying to reduce overall provisioning time, mostly to reduce the friction for first time users. There is no SLA but for common instance types, it would be unusual to wait for more than 1 minute at AWS, Azure or Ubicloud for VM provisionings.

Maybe you and I just experience different parts of the big computing ecosystem, hence what is "usual" for each of us is different. Out of curiosity, are you coming from enterprise background?

At k8s, isolation is at the container level, thus properly isolating (for security purposes) system calls is quite difficult. This wouldn't be a concern if you are running Postgres for yourself.

Also for us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.

Sure you can, but Let's Encrypt, just like DigiCert, is a 3rd party provider and they don't guarantee that you would get a signed certificate in few minutes. If they have an outage, it could take hours to get a certificate and you wouldn't be able to provision any database servers during that time. In our previous gig at Microsoft, we had multiple DigiCert outages which blocked the provisionings.

It is managed maintenance window, which basically means letting user to pick a time window such as Saturday 8PM-9PM and as a service provider you ensure that non critical maintenances happen at that time window.

At another thread in this page, I wrote more about this, but in summary; we also like k8s-based managed Postgres solutions. They are quite useful if you are running Postgres for yourself. In managed Postgres services offered by hyperscalers or companies like Crunchy though, it is not used very commonly.

You are not wrong. There are benefits for sharing certificate authority per customer. We actually considered using one authority per organization. However in our case, there is no organization entity in our data model. There is projects, which is similar but not exactly same. So we were not entirely sure where should we put the boundary and decided to go with more isolated approach.

It is likely that we would add a organization-like entity in the future to our data model and at that time sharing certificate authority would make more sense.

Definitely agreed. It is great feature and a building block for many complex features such as multiple primaries, zero down time migrations etc. I'm also quite happy to see with each PG version, it becomes more stable/easy to use.

Do you mean writing something similar for MySQL or building a MySQL service at Ubicloud?

For the first one, the answer would be no, because I don't have any expertise on MySQL. I only used it years ago on my hobby projects. I'm not the best person to write something like this for MySQL.

For the second one, the answer would be maybe, but not any time soon. We have list of services that we want to build at Ubicloud and currently services like K8s have more priority.

I started as software developer and I'm still a software developer. Though I always worked on either core database development or building managed database services, by coincidence at the beginning and by choice later on. I was also fortunate to have the opportunity to work alongside some of the leading experts in this domain and learn from them.

You can still restore to a given minute even without one minute WAL boundaries (most of the time). Consider the case where you have a very low write activity and you would be able to fill up one WAL file (16MB) in 1 hour. That WAL file won't be archived until it is full and if you lose your database for some reason, you won't have last 1 hour's data in your backups. That means you cannot to restore any minute in that one hour window. Shorter WAL boundaries reduces your exposure. If you set archive_timeout to 1 minute, then you can restore any minute in the past with the exception of the last minute (in practice, it is possible to lose last few minutes because their WAL file might not be archived yet, but still the exposure would be much less)

DigitalOcean uses 5 minutes as archive_timeout, which is also a reasonable value. In our experience, we saw that most of our customers prefer less exposure and we settled on 1 minute as archive_timeout value.

Roughly archive_timeout defines your RPO(recovery point objective).

Yes, we use physical replication for HA.

There are many reasons that cloud providers don't want to support logical replication;

- It requires giving superuser access to user. Many cloud providers don't want to give that level of privilege. Some cloud providers fork PostgreSQL or write custom extensions to allow managing replication slots without requiring superuser access. However, doing it securely is very difficult. You suddenly open up a new attack vector for various privilege escalation vulnerabilities.

- If user creates a replication slot, but does not consume the changes, it can quickly fill up the disk. I dealt many different failure modes of PostgreSQL, and I can confidently say that disk full cases one of the most problematic/annoying ones to recover from.

- It requires careful management of replication slots in case of fail over. There are extensions or 3rd party tools helping with this though.

So, some cloud providers don't support logical replication and some support it weakly (i.e. don't cover all edge cases).

Thankfully there are some improvements are being done in PostgreSQL core that simplifies failover of logical replication slot (check out this for more information https://www.postgresql.org/docs/17/logical-replication-failo...), but it is still too early.

Yes. :) We quite like k8s-based managed Postgres solutions. In fact, we convinced Patroni's author to come work with us in a previous gig at Microsoft. We find that a good number of companies successfully use k8s to manage Postgres for themselves.

In this blog post, we wanted to focus on running Postgres for others. AWS, Azure, and GCP's managed Postgres services, and those offered by startups like Crunchy, don't use k8s-based solutions. For us, one reason was operational simplicity. You can write a control plane for managed Postgres in 20K lines of code, including unit tests. This way, if anything breaks at scale, you can quickly figure out the issue without having to dive into dependencies.

We also understand different tradeoffs apply when you're running Postgres for yourself or for others. In this blog post, we wanted to focus on the latter scenario.

We want to ensure that the users are able to restore to any minute in the past.

Then, as @pintxo, @bjornsing, and @Doxin asked below; Why not let users pick a time and then replay the closest backup? Why create empty backups? This is a valid question.

The answer is that without an empty backup, it's not possible to confirm there was no write activity. Maybe some write activity occurred but you fail to archive the WAL file. You need at least one WAL file (empty or not) with a timestamp equal to or more recent than the target restore time to ensure there were no write activity.

I didn't explain this in detail in the blog post for the sake of brevity, but you don't need to create an empty WAL file for each minute with no write activity. For instance, if there was no write activity for 10 minutes and then a WAL file is generated, PostgreSQL can deduce there was no write activity during those 10 minutes and is able restore to any minute within that period. However, if the user wants to restore to 5 minutes ago and there has been no write activity for the past 10 minutes, PostgreSQL doesn't have a WAL file to confirm lack of write activity. In this case, you need to call pg_current_xact_id() to generate a WAL file. So the trick is, when you get a restore request, call pg_current_xact_id() only once, just in case if there were no write activity.

This is a good example of difference between running PostgreSQL for yourself vs. others. When running PostgreSQL for yourself, such edge cases is not a concern. However, when managing it for others, an edge case like this could prevent a restore and create an operational burden. Automating this process is required especially if you are managing lots of PostgreSQL databases.

At Ubicloud, we have ARM64 runners. They are compatible with Github Runners. It is just one line change in your workflow file. It is also 6x more cost effective than Github Runners (btw. our x64 runners are 10x more cost effective)

More informative can be found here https://www.ubicloud.com/use-cases/github-actions

and here https://www.ubicloud.com/docs/github-actions-integration/qui...