5 ms·

Hey, author for the blog post is here. If you have any questions or comments, please let me know! It's also worth calling out the first diagram shows dependenc

by pwmtr 2y ago

Hey, author for the blog post is here. If you have any questions or comments, please let me know! It's also worth calling out the first diagram shows dependencies between features for Ubicloud's managed Postgres. AWS, Azure, and GCP's managed Postgres service would have a different diagram. That's because we at Ubicloud treat write-ahead logs (WAL) as a first class citizen.

aae42 2y ago
surprised the article doesn't mention use of pgbouncer, do you not use a proxy in front of your pg instances?
- pwmtr 2y ago
  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.
candiddevmike 2y ago
Your blog doesn't really mention any of the turn key PostgreSQL deployment options out there. These days, especially on Kubernetes, it has never been easier to run a SaaS-equivalent PostgreSQL stack. I think you may benefit from researching the ecosystem some more.
- metadat 2y ago
  Being critical without posting actual better solutions isn't so helpful. If you have concrete knowledge, please share it and don't be cryptic!
- sakjur 2y ago
  The author seems to have spent the better part of a decade working professionally with Postgres. I think editorial choice, rather than ignorance, might be why they’re not mentioning more prior art.
  - pwmtr 2y ago
    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.
    - _cenw 2y ago
      I'm not sure if 20k lines of code and and writing a controller on k8s are really that incompatible. kubebuilder has made it pretty approachable.
rembicilious 2y ago
From the article: “If the user isn’t actively working on the database, PostgreSQL won’t generate a WAL file each minute. WAL files are by default 16 MB and PostgreSQL waits for the 16 MB to fill up. So, your restore granularity could be much longer, and you may not be able to restore to a particular minute in time. You can overcome this problem by setting the archive_timeout and forcing Postgres to generate a new WAL file every minute. With this configuration, Postgres would create a new WAL file when it hits the 1-minute or 16 MB threshold, whichever comes first. The second issue with backup/restore is “no activity”. In this case, PostgreSQL wouldn’t create a new file even if the archive_timeout is set. As a solution, you can generate artificial write activity by calling pg_current_xact_id().” Can you explain why to create a WAL file even though there is no activity?
- infogulch 2y ago
  The point is to create a WAL file if there is a little activity, but not enough to fill 16MB.
- solatic 2y ago
  "Point in time restore" is a bad way to call the feature if you don't let your customers pick a moment in time to restore to, so those tricks ensure that there's enough WAL entries to allow people to pick with per-second granularity.
  - pintxo 2y ago
    One can let users pick a time and then just replay the closest backup? Why create empty backups?
  - Doxin 2y ago
    But if there has been no activity you surely can just pick the most recent log that's older than the time the user picked?
    - Tostino 2y ago
      How do you know the WAL was not lost during transmission, or the server crashed before transferring the WAL even though there were writes after the last WAL you have in your backup? That's why.
wg0 2y ago
Great write up. Do you plan to have similar for MySQL as well?
- pwmtr 2y ago
  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.
  - wg0 2y ago
    I meant the second one. Thank you for your answer. Second unrelated question that anyone might want to add to, why is MySQL falling out of favour in recent years in comparison to Postgres?
    - mxuribe 2y ago
      > ...why is MySQL falling out of favour in recent years in comparison to Postgres I'm curious about this myself! Anyone know, or care to share?
      - fcatalan 2y ago
        Until I switched to Postgres 8 years ago I knew a lot about MySQL. I barely know anything about Postgres
      - pixl97 2y ago
        Anecdotal evidence? I used to work with/host lots of small php apps that tied in with MySQL. PHP has dropped in popularity over the years. Add to this hosting for Postgres has become common so you're not tied to cheap hosting for MySQL only. At least I think that the Oracle name being tied to MySQL made it icky for a lot of people that think the big O is a devil straight from hell. This is something that got me looking at Postgres way back when. There's probably a myriad of 'smaller' reasons just like this that enforce the trend.
matharmin 2y ago
1. Do you use logical or physical replication for high availability? It seems like logical replication requires a lot of manual intervention, so I'd assume physical replication? 2. If that's the case, does that mean customers can't use logical replication for other use cases? I'm asking because logical replication seems to become more and more common as a solution to automatically replicate data to external systems (another Postgres instance, Kafka, data warehousing, or offline sync systems in my case), but many cloud providers appear to not support it. Many others do (including AWS, GCP), so I'd also be interested in how they handle high availability.
- pwmtr 2y ago
  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-failover.html https://www.postgresql.org/docs/17/logical-replication-failo...), but it is still too early.
  - matharmin 2y ago
    Yeah, I've dealt with some of those edge cases on AWS and GCP. Some examples: 1. I've seen a delay of hours without any messages being sent on the replication protocol, likely due to a large transaction in the WAL not committed when running out of disk space. 2. `PgError.58P02: could not create file \"pg_replslot/<slot_name>/state.tmp\": File exists` 3. `replication slot "..." is active for PID ...`, with some system process holding on to the replication slot. 4. `can no longer get changes from replication slot "<slot_name>". ... This slot has never previously reserved WAL, or it has been invalidated`. All of these require manual intervention on the server to resolve. And that's not even taking into account HA/failover, these are just issues with logical replication on a single node. It's still a great feature though, and often worth having to deal with these issues now and then.
markus_zhang 2y ago
Thanks for the article. Reading it makes me realize being a good SRE/DevOps is crazily difficult. Did you start as a DB admin?
- pwmtr 2y ago
  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.
  - markus_zhang 2y ago
    Thanks for sharing. Nuff said, I need to find a new gig.
simmschi 2y ago
I enjoyed reading the article very much. Thanks for the write up!
iamcreasy 2y ago
Thanks for the great write up. What is MMW on the flowchart?
- pwmtr 2y ago
  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.
crabbone 2y ago
There are "strange" properties your users have... who are those people? * You describe them as wanting to deploy the database in container: why would anyone do that (unless for throwaway testing or such)? * The certificate issue seems also very specific to the case when something needs to go over public Internet to some anonymous users... Most database deployments I've seen in my life fall into one of these categories: database server for a Web server, which talk in a private local network, or database backing some kind of management application, where, again the communication between the management application and the database happen without looping in the public Internet. * Unwilling to wait 10 minutes to deploy a database. Just how often do they need new databases? Btw. I'm not sure any of the public clouds have any ETAs on VM creation, but from my practice, Azure can easily take more than 10 minutes to bring up a VM. With some flavors it can take a very long time. The only profile I can think of is the kind of user who doesn't care about what they store in the database, in the sense that they are going to do some quick work and throw the data away (eg. testing, or maybe BI on a slice of data). But then why would these people need HA or backups, let alone version updates?
- pwmtr 2y ago
  * 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?

by pwmtr 2y ago

aae42 2y ago

surprised the article doesn't mention use of pgbouncer, do you not use a proxy in front of your pg instances?

pwmtr 2y ago
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.

candiddevmike 2y ago

Your blog doesn't really mention any of the turn key PostgreSQL deployment options out there. These days, especially on Kubernetes, it has never been easier to run a SaaS-equivalent PostgreSQL stack. I think you may benefit from researching the ecosystem some more.

metadat 2y ago
Being critical without posting actual better solutions isn't so helpful. If you have concrete knowledge, please share it and don't be cryptic!
sakjur 2y ago
The author seems to have spent the better part of a decade working professionally with Postgres. I think editorial choice, rather than ignorance, might be why they’re not mentioning more prior art.
- pwmtr 2y ago
  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.
  - _cenw 2y ago
    I'm not sure if 20k lines of code and and writing a controller on k8s are really that incompatible. kubebuilder has made it pretty approachable.

rembicilious 2y ago

From the article: “If the user isn’t actively working on the database, PostgreSQL won’t generate a WAL file each minute. WAL files are by default 16 MB and PostgreSQL waits for the 16 MB to fill up. So, your restore granularity could be much longer, and you may not be able to restore to a particular minute in time. You can overcome this problem by setting the archive_timeout and forcing Postgres to generate a new WAL file every minute. With this configuration, Postgres would create a new WAL file when it hits the 1-minute or 16 MB threshold, whichever comes first. The second issue with backup/restore is “no activity”. In this case, PostgreSQL wouldn’t create a new file even if the archive_timeout is set. As a solution, you can generate artificial write activity by calling pg_current_xact_id().” Can you explain why to create a WAL file even though there is no activity?

infogulch 2y ago
The point is to create a WAL file if there is a little activity, but not enough to fill 16MB.
solatic 2y ago
"Point in time restore" is a bad way to call the feature if you don't let your customers pick a moment in time to restore to, so those tricks ensure that there's enough WAL entries to allow people to pick with per-second granularity.
- pintxo 2y ago
  One can let users pick a time and then just replay the closest backup? Why create empty backups?
- Doxin 2y ago
  But if there has been no activity you surely can just pick the most recent log that's older than the time the user picked?
  - Tostino 2y ago
    How do you know the WAL was not lost during transmission, or the server crashed before transferring the WAL even though there were writes after the last WAL you have in your backup? That's why.

wg0 2y ago

Great write up. Do you plan to have similar for MySQL as well?

pwmtr 2y ago
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.
- wg0 2y ago
  I meant the second one. Thank you for your answer. Second unrelated question that anyone might want to add to, why is MySQL falling out of favour in recent years in comparison to Postgres?
  - mxuribe 2y ago
    > ...why is MySQL falling out of favour in recent years in comparison to Postgres I'm curious about this myself! Anyone know, or care to share?
    - fcatalan 2y ago
      Until I switched to Postgres 8 years ago I knew a lot about MySQL. I barely know anything about Postgres
    - pixl97 2y ago
      Anecdotal evidence? I used to work with/host lots of small php apps that tied in with MySQL. PHP has dropped in popularity over the years. Add to this hosting for Postgres has become common so you're not tied to cheap hosting for MySQL only. At least I think that the Oracle name being tied to MySQL made it icky for a lot of people that think the big O is a devil straight from hell. This is something that got me looking at Postgres way back when. There's probably a myriad of 'smaller' reasons just like this that enforce the trend.

matharmin 2y ago

1. Do you use logical or physical replication for high availability? It seems like logical replication requires a lot of manual intervention, so I'd assume physical replication? 2. If that's the case, does that mean customers can't use logical replication for other use cases? I'm asking because logical replication seems to become more and more common as a solution to automatically replicate data to external systems (another Postgres instance, Kafka, data warehousing, or offline sync systems in my case), but many cloud providers appear to not support it. Many others do (including AWS, GCP), so I'd also be interested in how they handle high availability.

pwmtr 2y ago
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-failover.html https://www.postgresql.org/docs/17/logical-replication-failo...), but it is still too early.
- matharmin 2y ago
  Yeah, I've dealt with some of those edge cases on AWS and GCP. Some examples: 1. I've seen a delay of hours without any messages being sent on the replication protocol, likely due to a large transaction in the WAL not committed when running out of disk space. 2. `PgError.58P02: could not create file \"pg_replslot/<slot_name>/state.tmp\": File exists` 3. `replication slot "..." is active for PID ...`, with some system process holding on to the replication slot. 4. `can no longer get changes from replication slot "<slot_name>". ... This slot has never previously reserved WAL, or it has been invalidated`. All of these require manual intervention on the server to resolve. And that's not even taking into account HA/failover, these are just issues with logical replication on a single node. It's still a great feature though, and often worth having to deal with these issues now and then.

markus_zhang 2y ago

Thanks for the article. Reading it makes me realize being a good SRE/DevOps is crazily difficult. Did you start as a DB admin?

pwmtr 2y ago
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.
- markus_zhang 2y ago
  Thanks for sharing. Nuff said, I need to find a new gig.

simmschi 2y ago

I enjoyed reading the article very much. Thanks for the write up!

iamcreasy 2y ago

Thanks for the great write up. What is MMW on the flowchart?

pwmtr 2y ago
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.

crabbone 2y ago

There are "strange" properties your users have... who are those people? * You describe them as wanting to deploy the database in container: why would anyone do that (unless for throwaway testing or such)? * The certificate issue seems also very specific to the case when something needs to go over public Internet to some anonymous users... Most database deployments I've seen in my life fall into one of these categories: database server for a Web server, which talk in a private local network, or database backing some kind of management application, where, again the communication between the management application and the database happen without looping in the public Internet. * Unwilling to wait 10 minutes to deploy a database. Just how often do they need new databases? Btw. I'm not sure any of the public clouds have any ETAs on VM creation, but from my practice, Azure can easily take more than 10 minutes to bring up a VM. With some flavors it can take a very long time. The only profile I can think of is the kind of user who doesn't care about what they store in the database, in the sense that they are going to do some quick work and throw the data away (eg. testing, or maybe BI on a slice of data). But then why would these people need HA or backups, let alone version updates?

pwmtr 2y ago
* 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?