10 ms·

A major Postgres upgrade with zero downtime

249 points by stopachka 2y ago

deleted 2y ago
[deleted]
xyzzy_plugh 2y ago
The title is pretty misleading. They're not even running Postgres, but AWS Aurora, which is Postgres compatible, but is not Postgres. Also, pausing queries does count as downtime. The system was unavailable for that period of time.
- stopachka 2y ago
  > The title is pretty misleading. They're not even running Postgres, but AWS Aurora, which is Postgres compatible, but is not Postgres. For what it's worth, every command ran works on normal Postgres. Hence we didn't think it mattered to mention Aurora specifically in the title. > Also, pausing queries does count as downtime. If a query takes a bit longer to respond, I don't think that counts as downtime. From the perspective of the user, they couldn't distinguish this migration event from some blip of slightly slower queries.
  - lionkor 2y ago
    It depends if it feels like an outage
  - awesome_dude 2y ago
    > If a query takes a bit longer to respond, I don't think that counts as downtime "We're sorry that your query took 7 hours to be responded to, but it wasn't an outage - honest"
    - stopachka 2y ago
      We would count 7 hours as downtime too. Our pause was less than 5 seconds.
      - ElijahLynn 2y ago
        Less than 5 seconds seems pretty reasonable to me to call it zero down time.
      - tossandthrow 2y ago
        5 seconds pause on queries would make our app server drop connections and throw errors under cyclical high load - which would result in a incident.
      - libraryofbabel 2y ago
        Nice job, then! Technical downtime that’s virtually undetectable to users is a big win. In fact, “less than 5 seconds of downtime” in the title would actually make me want to read the article more as I tend to be suspicious of “zero downtime” claims for database upgrades, whereas <5s is clearly almost as good as zero and actually quantified :)
        awesome_dude 2y ago
        Yeah - a quantifiable amount in the headline would change the likelihood of the article being taken seriously - it goes from "No downtime? I call BS" to "Less than 5 seconds, that seems reasonable, and worth investigating"
        _flux 2y ago
        On the other than "less than 5 seconds of downtime" might give the impression that new queries sent within that time period would be rejected, while zero implies this doesn't happen, i.e. that it's undistinguishable from normal operation for the client. And being even more precise in the title would just make it less titley :).
    - paulddraper 2y ago
      Strong energy of "someone brushed up against me and that's assault" going on here
      - awesome_dude 2y ago
        [flagged]
  - RadiozRadioz 2y ago
    Completely depends on what the "user" is. Are they a human, or a machine that explicitly requires timings within a particular threshold?
  - scottlamb 2y ago
    > If a query takes a bit longer to respond, I don't think that counts as downtime. From the perspective of the user, they couldn't distinguish this migration event from some blip of slightly slower queries. It comes down to defining Service Level Objectives (SLOs) that are meaningful to your users. For one system I worked on, latency was important, and so one SLO was "99.999% of <a certain class of> requests with a deadline >=1s should succeed with latency <1s", so if this affected more than 0.0001% of requests in <time interval defined in our SLO>, we'd have called it an outage. But I've also worked on systems with looser SLOs where this would have been fine.
    - nijave 2y ago
      Not only that but I think you also need to take upstream systems into account. With a reasonably robust frontend that handles transient issues and retries reasonably, I think it's ok to say "no downtime"
- paulddraper 2y ago
  > They're not even running Postgres, but AWS Aurora But everything described is also PostgreSQL compatible. > downtime Context switching pauses execution too FYI.
- unethical_ban 2y ago
  They reduced their potential downtime from 60s to what I assume is only a few seconds (they don't state in the article). If there is not noticeable user impact or unavailability of services (this is unique to each service in existence) then there is no downtime.
  - stopachka 2y ago
    > they don't state in the article Thank you for pointing this out. I updated the essay to mention how long the pause took explicitly: After about a 3.5 second pause [^13], the failover function completed smoothly! We had a new Postgres instance serving requests, and best of all, nobody noticed. [^13]: About 2.5 seconds to let active queries complete, and about 1 second for the replica to catch up
    - metadat 2y ago
      What is the [^13] notation? Is it different than a *?
      - Izkata 2y ago
        They copy/pasted from the article, that's how they're formatting footnote links. Article has 15 footnotes and that's number 13.
- SahAssar 2y ago
  AWS Aurora Postgres is a forked Postgres with a different storage engine. Sure you are technically correct, but there are many things called "Postgres compatible" that are very much less Postgres that AWS Aurora Postgres (like for example CockroachDB).
  - nijave 2y ago
    Iirc AWS explicitly calls out they still use upstream Postgres query engine and some other parts. It very much _is_ Postgres but not 100% pure upstream Postgres.
    - SahAssar 2y ago
      Yep, for example that is how they advertise protocol, feature and language compatibility.
darth_avocado 2y ago
Pause all writes > let 16 to catch up > resume writes on 16 Isn’t that….. downtime? Unless you mean downtime to be only when reads are also not available.
- stopachka 2y ago
  We count downtime if a service is unavailable and drops requests. In this case, since the pause took about 3.5 seconds, we were able to service all requests.
  - dboreham 2y ago
    By that definition you can have any length pause and still claim zero downtime.
    - nijave 2y ago
      Only if nothing has timeouts. Likely components somewhere in the stack have timeouts so you're bound by that
  - darth_avocado 2y ago
    Interesting. If that was the case, I’m curious why you had to write custom code. Would RDS proxy not work? The way we usually upgrade is create a new upgraded cluster, replicate all data to new cluster, pause writes and redirect RDS proxy to the new cluster. And that usually takes a few seconds.
- dspillett 2y ago
  Not if the process doesn't take long, so the most the user (or consuming service) sees is a slower response than usual. That I would class as a temporary performance degradation. If the degradation is significant it might still not be acceptable, of course, but I'd not call it downtime.
upghost 2y ago
I can't believe they took the time to make such an amazing write-up. With formatting and everything. Normally I just grab whatever broken crayon is closest and scribble on the back of an overdue bill "don't fsck up next time"
honestSysAdmin 2y ago
Zero-downtime Postgres upgrades have been kind of normalized, at least in the environments I have been exposed to, with pgcat https://github.com/postgresml/pgcat
- stopachka 2y ago
  Is there some resource that explains how to do a major version upgrade with pgcat? Would love to take a look
  - honestSysAdmin 2y ago
    As far as I know, there is not. I could probably write something up.
  - Nelkins 2y ago
    Probably something like the steps listed in this blog post: https://www.instacart.com/company/how-its-made/zero-downtime-postgresql-cutovers/ https://www.instacart.com/company/how-its-made/zero-downtime...
- wswope 2y ago
  This is really cool/useful to know about - thanks for dropping the link!
- Izkata 2y ago
  Indenting with 2 spaces is for code formatting, which is why the URL isn't a link. Don't indent it if you want a link: https://github.com/postgresml/pgcat https://github.com/postgresml/pgcat
jedberg 2y ago
This is impressive! I know others are questioning the "no downtime" bit, but that is why service level objectives exist -- because it really depends on the customer experience. If you managed to have a cutover with no noticeable dip in business metrics (aka the users didn't notice) then I'd call that a no-downtime upgrade! Very clever on the improvement over Lyft's methods. Thanks for the writeup. Now maybe someone can get it down from 3 seconds of pausing. :)
- honestSysAdmin 2y ago
  > then I'd call that a no-downtime upgrade! It'd be really convenient for me, well not me but others, if we could tell our customers this. However, those of us running DBaaS do have to offer an actual no-downtime upgrade.
sgarland 2y ago
I have to wonder – are they using a connection pooler? I'm leaning towards no, since what they did in code can be natively done with PgBouncer, PgCat, et al. That would also explain the last footnote: > The big bottleneck is all the active connections For anyone who is unaware, Postgres (and Aurora-compatible Postgres, which sucks but has a great marketing team) uses a process per connection, unlike MySQL (and others, I think) which use a thread per connection. This is inevitably the bottleneck at scale, long before anything else. I did feel for them here: > We couldn’t create a blue-green deployment when the master DB had active replication slots. The AWS docs did not mention this. [emphasis mine] The docs also used to explicitly say that you could run limited DDL, like creating or dropping indices, on the Green DB. I found this to be untrue in practice, notified them, and I see they've since updated their docs. A painful problem to discover though, especially when it's a huge DB that took a long time to create the B/G in the first place.
- stopachka 2y ago
  > are they using a connection pooler We use Hikari [1] an in-process connection pooler. We didn't opt for pgbouncer at al, because we didn't want to add the extra infra yet. > since what they did in code can be natively done with PgBouncer, PgCat, et al. Can you point me to a reference I could look at, about doing a major version upgrade with PgBouncer et al? My understanding is that we would still need to write a script to switch masters, similar to what we wrote. > The big bottleneck is all the active connections The active connections we were referring too were websocket connections; we haven't had problems with PG connections. Right now the algorithm we use to find affected queries and notify websockets starts to falter when the number of active websocket connections on one machine get too high. We're working on improving it in the coming weeks. I updated the footnote to clarify that it was about websocket connections. > I did feel for them here: Thank you! That part was definitely the most frustrating. [1] https://github.com/brettwooldridge/HikariCP https://github.com/brettwooldridge/HikariCP
  - sgarland 2y ago
    I’m not sure about a reference, other than their docs [0]. Basically, you’d modify the config to point to the new servers, issue PAUSE to PgBouncer to gracefully drain connections, then RELOAD to pick up the new config, then RESUME to accept new traffic. This would result in client errors while paused, though, so perhaps not quite the same. To me, a few seconds of downtime is fine, but everyone has their own opinions. EDIT: you could of course also modify your client code (if it doesn’t already) to gracefully retry connections, which would effectively make this zero downtime. ProxySQL (which I think now supports Postgres) has a global delay option where you can effectively make clients think that the query is just taking a long time; meanwhile, you can do the same sequence as outlined. If you had HA Bouncers (which hopefully you would), you could cheat a little as you eluded to in the post, and have one still allow read queries to hit the old DB while cutting over writes on the other one, so the impact wouldn’t be as large. [0]: https://www.pgbouncer.org/usage.html https://www.pgbouncer.org/usage.html
wenbin 2y ago
Awesome! The best we’ve achieved is 0 downtime for read operations and less than 1 minute downtime for write ops [1] Achieving 0 downtime for write ops is super hard! [1] https://www.listennotes.com/blog/a-practical-way-to-upgrade-postgres-major-49/ https://www.listennotes.com/blog/a-practical-way-to-upgrade-...
paulgb 2y ago
When I see instantdb in the domain on here I always know it's going to be a good read :) Nicely done!
nijave 2y ago
I haven't it tried it, but in another post recently someone mentioned enabling synchronous replication after the clone is up to date https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#SY...
n_u 2y ago
They say the "stop the world" approach that causes more downtime is Turn off all writes. Wait for 16 to catch up Enable writes again — this time they all go to 16 and instead they used a better algorithm: Pause all writes. Wait for 16 to catch up. Resume writes on 16. These seem pretty similar. 1. What is the difference in the algorithm? Is it just that in the "stop the world" approach the client sees their txns fail until "wait for 16 to catch up" is done? Whereas in the latter approach the client never sees their txns fail, they just have a bit more latency? 2. Why does the second approach result in less downtime?
- stopachka 2y ago
  > in the "stop the world" approach the client sees their txns fail until "wait for 16 to catch up" is done? Whereas in the latter approach the client never sees their txns fail, they just have a bit more latency? Yes, this is the main difference. For "stop the world", we imagined a simpler algorithm: instead of a script, we could manually toggle a switch for example. However, by writing the script, the user only experiences a bit more latency, rather than failed transactions.
  - n_u 2y ago
    > If we went with the ‘stop the world approach’, we’d have about the same kind of downtime as blue-green deployments: a minute or so. > After about a 3.5 second pause [13], the failover function completed smoothly! We had a new Postgres instance serving requests > [13] About 2.5 seconds to let active queries complete, and about 1 second for the replica to catch up Why is the latter approach faster though? It seems in the "stop the world" approach wouldn't it still take only 1 second for the replica to catch up? Where do the other ~59 seconds of write downtime come from?
    - stopachka 2y ago
      In the "stop the world approach", I imagined our algorithm to be a bit more manual: for example, we would turn the switch on manually, wait, and then turn it back on. You make a good point though, that with enough effort it could also be a few seconds. I updated the essay to reflect this: https://github.com/instantdb/instant/pull/774/files https://github.com/instantdb/instant/pull/774/files
mkleczek 2y ago
I wonder why they didn't use synchronous_commit option. That would eliminate replication lag and allow for real zero-downtime.
- stopachka 2y ago
  The script we wrote though already has a method to detect exactly when the replica has caught up [^1]. Had we enabled synchronous commit, we'd had the same kind of performance as now. A few changes we could do to improve this even further: Right now the algorithm waits a) 2.5 seconds for transactions to complete, and b) cancels the rest. We could make a) the amount of time we wait more exact, by actually subscribing to active transactions and waiting for them to complete. This way in most cases we'd only have to wait up to 500ms. We couldn't do that when we wrote this essay, because we didn't differentiate read connections from write connections. We do now [^2] We could improve b) how many transactions we have to cancel, by having the function make "attempts" throughout the day. For example, the function could try to wait 500ms for transactions to complete, but if it's a particularily busy period and we have too many straggling transactions in process, the function could abort the migration and try again another time. [^1]: https://github.com/instantdb/instant/blob/main/server/src/instant/jdbc/failover.clj#L44-L70 https://github.com/instantdb/instant/blob/main/server/src/in... [^2]: https://github.com/instantdb/instant/pull/743 https://github.com/instantdb/instant/pull/743
jillyboel 2y ago
> The next few hours was frustrating: we would change a setting, start again, wait 30 minutes, and invariably end up with the same error. Sounds about right for cloud services.
pilif 2y ago
> But we had less than a terabyte of data I really wonder how an in-place `pg_upgrade` of such small amounts of data would take 30+ minutes. My experience from a less mission-critical situation where 5 minutes of maintenance are absolutely acceptable is that an in-place `pg_upgrade` with `--link` of a 8 TB database takes less than a minute and will not accidentally lose data or fail to properly configure schema search paths or whatever other mess the article was talking about. I understand that 30 minutes of downtime are not acceptable. But if it's 5 minutes or less, I would seriously consider an offline upgrade using `pg_upgrade` And if it takes 30 minutes to hard-link less than 1 TB of data files, you should seriously consider changing hosts because that's absolutely unacceptable performance.
- nijave 2y ago
  I don't think pg_upgrade takes the whole time. Some of it is overhead of AWS managed database service where it's creating a snapshot before and after, applying new config, spinning for no apparent reason
- stopachka 2y ago
  > 30 minutes The Lyft team reported 30 minutes for their 30TB database. Our db took about 15 minutes. In the essay we wrote: > So we cloned our production database and tested an in-place upgrade. Even with our smaller size, it took about 15 minutes for the clone to come back online.
  - deleted 2y ago
    [deleted]
- timacles 2y ago
  Yeah we just did it with the --link option on a 6TB database and it took like 30 seconds. Something has to be off with their OS settings or disk speeds. The main challenge with that is running an ANALYZE on all the tables though, that took like 30 minutes during which time the DB was unusable
  - zonkd1234 2y ago
briffle 2y ago
Logical replication is a great tool, and we are using it for our next DB upgrade coming up in the next few months. It just has a few limitations I wish they would address, especially since logical replication is quickly becoming one of the more popular ways to upgrade databases with minimal downtime. I understand that logical replication handles partial replication, and the data warehouse use case, and I understand WHY many of the choices they made for it are there. I just wish that there was a flag you could set in a subscription that would enable it to be a complete 1:1 copy of the database, including DDL, sequences, etc, without having to do all the workarounds that are necessary now. Currently if a dev adds a column to a table on the publisher, and forgets to do it on the subscriber, there is no error, no indication, until it actually tries to replicate data for that table. Then you have to be monitoring for that error, and go figure out what other new tables, columns, etc, might have been added to the publisher that are missed on the subscriber. Its a huge opportunity for problems.
- svilen_dobrev 2y ago
  dump schema on both, and compare before copying? or that may have false positives?
jatins 2y ago
> all Instant databases are hosted under one Aurora Postgres instance Doesn't the increase the chances of one bad tenant taking the database down for all other tenants?
- stopachka 2y ago
  > Doesn't the increase the chances of one bad tenant taking the database down for all other tenants? We have safe-guards in place to prevent and isolate bad actors. It's more tricky then if resources weren't shared, but that is the trade-off for multi-tenancy.

249 points by stopachka 2y ago

deleted 2y ago

[deleted]

The title is pretty misleading. They're not even running Postgres, but AWS Aurora, which is Postgres compatible, but is not Postgres. Also, pausing queries does count as downtime. The system was unavailable for that period of time.

stopachka 2y ago
> The title is pretty misleading. They're not even running Postgres, but AWS Aurora, which is Postgres compatible, but is not Postgres. For what it's worth, every command ran works on normal Postgres. Hence we didn't think it mattered to mention Aurora specifically in the title. > Also, pausing queries does count as downtime. If a query takes a bit longer to respond, I don't think that counts as downtime. From the perspective of the user, they couldn't distinguish this migration event from some blip of slightly slower queries.
- lionkor 2y ago
  It depends if it feels like an outage
- awesome_dude 2y ago
  > If a query takes a bit longer to respond, I don't think that counts as downtime "We're sorry that your query took 7 hours to be responded to, but it wasn't an outage - honest"
  - stopachka 2y ago
    We would count 7 hours as downtime too. Our pause was less than 5 seconds.
    - ElijahLynn 2y ago
      Less than 5 seconds seems pretty reasonable to me to call it zero down time.
    - tossandthrow 2y ago
      5 seconds pause on queries would make our app server drop connections and throw errors under cyclical high load - which would result in a incident.
    - libraryofbabel 2y ago
      Nice job, then! Technical downtime that’s virtually undetectable to users is a big win. In fact, “less than 5 seconds of downtime” in the title would actually make me want to read the article more as I tend to be suspicious of “zero downtime” claims for database upgrades, whereas <5s is clearly almost as good as zero and actually quantified :)
      - awesome_dude 2y ago
        Yeah - a quantifiable amount in the headline would change the likelihood of the article being taken seriously - it goes from "No downtime? I call BS" to "Less than 5 seconds, that seems reasonable, and worth investigating"
      - _flux 2y ago
        On the other than "less than 5 seconds of downtime" might give the impression that new queries sent within that time period would be rejected, while zero implies this doesn't happen, i.e. that it's undistinguishable from normal operation for the client. And being even more precise in the title would just make it less titley :).
  - paulddraper 2y ago
    Strong energy of "someone brushed up against me and that's assault" going on here
    - awesome_dude 2y ago
      [flagged]
- RadiozRadioz 2y ago
  Completely depends on what the "user" is. Are they a human, or a machine that explicitly requires timings within a particular threshold?
- scottlamb 2y ago
  > If a query takes a bit longer to respond, I don't think that counts as downtime. From the perspective of the user, they couldn't distinguish this migration event from some blip of slightly slower queries. It comes down to defining Service Level Objectives (SLOs) that are meaningful to your users. For one system I worked on, latency was important, and so one SLO was "99.999% of <a certain class of> requests with a deadline >=1s should succeed with latency <1s", so if this affected more than 0.0001% of requests in <time interval defined in our SLO>, we'd have called it an outage. But I've also worked on systems with looser SLOs where this would have been fine.
  - nijave 2y ago
    Not only that but I think you also need to take upstream systems into account. With a reasonably robust frontend that handles transient issues and retries reasonably, I think it's ok to say "no downtime"
paulddraper 2y ago
> They're not even running Postgres, but AWS Aurora But everything described is also PostgreSQL compatible. > downtime Context switching pauses execution too FYI.
unethical_ban 2y ago
They reduced their potential downtime from 60s to what I assume is only a few seconds (they don't state in the article). If there is not noticeable user impact or unavailability of services (this is unique to each service in existence) then there is no downtime.
- stopachka 2y ago
  > they don't state in the article Thank you for pointing this out. I updated the essay to mention how long the pause took explicitly: After about a 3.5 second pause [^13], the failover function completed smoothly! We had a new Postgres instance serving requests, and best of all, nobody noticed. [^13]: About 2.5 seconds to let active queries complete, and about 1 second for the replica to catch up
  - metadat 2y ago
    What is the [^13] notation? Is it different than a *?
    - Izkata 2y ago
      They copy/pasted from the article, that's how they're formatting footnote links. Article has 15 footnotes and that's number 13.
SahAssar 2y ago
AWS Aurora Postgres is a forked Postgres with a different storage engine. Sure you are technically correct, but there are many things called "Postgres compatible" that are very much less Postgres that AWS Aurora Postgres (like for example CockroachDB).
- nijave 2y ago
  Iirc AWS explicitly calls out they still use upstream Postgres query engine and some other parts. It very much _is_ Postgres but not 100% pure upstream Postgres.
  - SahAssar 2y ago
    Yep, for example that is how they advertise protocol, feature and language compatibility.

darth_avocado 2y ago

Pause all writes > let 16 to catch up > resume writes on 16 Isn’t that….. downtime? Unless you mean downtime to be only when reads are also not available.

stopachka 2y ago
We count downtime if a service is unavailable and drops requests. In this case, since the pause took about 3.5 seconds, we were able to service all requests.
- dboreham 2y ago
  By that definition you can have any length pause and still claim zero downtime.
  - nijave 2y ago
    Only if nothing has timeouts. Likely components somewhere in the stack have timeouts so you're bound by that
- darth_avocado 2y ago
  Interesting. If that was the case, I’m curious why you had to write custom code. Would RDS proxy not work? The way we usually upgrade is create a new upgraded cluster, replicate all data to new cluster, pause writes and redirect RDS proxy to the new cluster. And that usually takes a few seconds.
dspillett 2y ago
Not if the process doesn't take long, so the most the user (or consuming service) sees is a slower response than usual. That I would class as a temporary performance degradation. If the degradation is significant it might still not be acceptable, of course, but I'd not call it downtime.

upghost 2y ago

I can't believe they took the time to make such an amazing write-up. With formatting and everything. Normally I just grab whatever broken crayon is closest and scribble on the back of an overdue bill "don't fsck up next time"

honestSysAdmin 2y ago

Zero-downtime Postgres upgrades have been kind of normalized, at least in the environments I have been exposed to, with pgcat https://github.com/postgresml/pgcat

stopachka 2y ago
Is there some resource that explains how to do a major version upgrade with pgcat? Would love to take a look
- honestSysAdmin 2y ago
  As far as I know, there is not. I could probably write something up.
- Nelkins 2y ago
  Probably something like the steps listed in this blog post: https://www.instacart.com/company/how-its-made/zero-downtime-postgresql-cutovers/ https://www.instacart.com/company/how-its-made/zero-downtime...
wswope 2y ago
This is really cool/useful to know about - thanks for dropping the link!
Izkata 2y ago
Indenting with 2 spaces is for code formatting, which is why the URL isn't a link. Don't indent it if you want a link: https://github.com/postgresml/pgcat https://github.com/postgresml/pgcat

jedberg 2y ago

This is impressive! I know others are questioning the "no downtime" bit, but that is why service level objectives exist -- because it really depends on the customer experience. If you managed to have a cutover with no noticeable dip in business metrics (aka the users didn't notice) then I'd call that a no-downtime upgrade! Very clever on the improvement over Lyft's methods. Thanks for the writeup. Now maybe someone can get it down from 3 seconds of pausing. :)

honestSysAdmin 2y ago
> then I'd call that a no-downtime upgrade! It'd be really convenient for me, well not me but others, if we could tell our customers this. However, those of us running DBaaS do have to offer an actual no-downtime upgrade.

sgarland 2y ago

I have to wonder – are they using a connection pooler? I'm leaning towards no, since what they did in code can be natively done with PgBouncer, PgCat, et al. That would also explain the last footnote: > The big bottleneck is all the active connections For anyone who is unaware, Postgres (and Aurora-compatible Postgres, which sucks but has a great marketing team) uses a process per connection, unlike MySQL (and others, I think) which use a thread per connection. This is inevitably the bottleneck at scale, long before anything else. I did feel for them here: > We couldn’t create a blue-green deployment when the master DB had active replication slots. The AWS docs did not mention this. [emphasis mine] The docs also used to explicitly say that you could run limited DDL, like creating or dropping indices, on the Green DB. I found this to be untrue in practice, notified them, and I see they've since updated their docs. A painful problem to discover though, especially when it's a huge DB that took a long time to create the B/G in the first place.

stopachka 2y ago
> are they using a connection pooler We use Hikari [1] an in-process connection pooler. We didn't opt for pgbouncer at al, because we didn't want to add the extra infra yet. > since what they did in code can be natively done with PgBouncer, PgCat, et al. Can you point me to a reference I could look at, about doing a major version upgrade with PgBouncer et al? My understanding is that we would still need to write a script to switch masters, similar to what we wrote. > The big bottleneck is all the active connections The active connections we were referring too were websocket connections; we haven't had problems with PG connections. Right now the algorithm we use to find affected queries and notify websockets starts to falter when the number of active websocket connections on one machine get too high. We're working on improving it in the coming weeks. I updated the footnote to clarify that it was about websocket connections. > I did feel for them here: Thank you! That part was definitely the most frustrating. [1] https://github.com/brettwooldridge/HikariCP https://github.com/brettwooldridge/HikariCP
- sgarland 2y ago
  I’m not sure about a reference, other than their docs [0]. Basically, you’d modify the config to point to the new servers, issue PAUSE to PgBouncer to gracefully drain connections, then RELOAD to pick up the new config, then RESUME to accept new traffic. This would result in client errors while paused, though, so perhaps not quite the same. To me, a few seconds of downtime is fine, but everyone has their own opinions. EDIT: you could of course also modify your client code (if it doesn’t already) to gracefully retry connections, which would effectively make this zero downtime. ProxySQL (which I think now supports Postgres) has a global delay option where you can effectively make clients think that the query is just taking a long time; meanwhile, you can do the same sequence as outlined. If you had HA Bouncers (which hopefully you would), you could cheat a little as you eluded to in the post, and have one still allow read queries to hit the old DB while cutting over writes on the other one, so the impact wouldn’t be as large. [0]: https://www.pgbouncer.org/usage.html https://www.pgbouncer.org/usage.html

wenbin 2y ago

Awesome! The best we’ve achieved is 0 downtime for read operations and less than 1 minute downtime for write ops [1] Achieving 0 downtime for write ops is super hard! [1] https://www.listennotes.com/blog/a-practical-way-to-upgrade-postgres-major-49/ https://www.listennotes.com/blog/a-practical-way-to-upgrade-...

paulgb 2y ago

When I see instantdb in the domain on here I always know it's going to be a good read :) Nicely done!

nijave 2y ago

I haven't it tried it, but in another post recently someone mentioned enabling synchronous replication after the clone is up to date https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#SY...

n_u 2y ago

They say the "stop the world" approach that causes more downtime is Turn off all writes. Wait for 16 to catch up Enable writes again — this time they all go to 16 and instead they used a better algorithm: Pause all writes. Wait for 16 to catch up. Resume writes on 16. These seem pretty similar. 1. What is the difference in the algorithm? Is it just that in the "stop the world" approach the client sees their txns fail until "wait for 16 to catch up" is done? Whereas in the latter approach the client never sees their txns fail, they just have a bit more latency? 2. Why does the second approach result in less downtime?

stopachka 2y ago
> in the "stop the world" approach the client sees their txns fail until "wait for 16 to catch up" is done? Whereas in the latter approach the client never sees their txns fail, they just have a bit more latency? Yes, this is the main difference. For "stop the world", we imagined a simpler algorithm: instead of a script, we could manually toggle a switch for example. However, by writing the script, the user only experiences a bit more latency, rather than failed transactions.
- n_u 2y ago
  > If we went with the ‘stop the world approach’, we’d have about the same kind of downtime as blue-green deployments: a minute or so. > After about a 3.5 second pause [13], the failover function completed smoothly! We had a new Postgres instance serving requests > [13] About 2.5 seconds to let active queries complete, and about 1 second for the replica to catch up Why is the latter approach faster though? It seems in the "stop the world" approach wouldn't it still take only 1 second for the replica to catch up? Where do the other ~59 seconds of write downtime come from?
  - stopachka 2y ago
    In the "stop the world approach", I imagined our algorithm to be a bit more manual: for example, we would turn the switch on manually, wait, and then turn it back on. You make a good point though, that with enough effort it could also be a few seconds. I updated the essay to reflect this: https://github.com/instantdb/instant/pull/774/files https://github.com/instantdb/instant/pull/774/files

mkleczek 2y ago

I wonder why they didn't use synchronous_commit option. That would eliminate replication lag and allow for real zero-downtime.

stopachka 2y ago
The script we wrote though already has a method to detect exactly when the replica has caught up [^1]. Had we enabled synchronous commit, we'd had the same kind of performance as now. A few changes we could do to improve this even further: Right now the algorithm waits a) 2.5 seconds for transactions to complete, and b) cancels the rest. We could make a) the amount of time we wait more exact, by actually subscribing to active transactions and waiting for them to complete. This way in most cases we'd only have to wait up to 500ms. We couldn't do that when we wrote this essay, because we didn't differentiate read connections from write connections. We do now [^2] We could improve b) how many transactions we have to cancel, by having the function make "attempts" throughout the day. For example, the function could try to wait 500ms for transactions to complete, but if it's a particularily busy period and we have too many straggling transactions in process, the function could abort the migration and try again another time. [^1]: https://github.com/instantdb/instant/blob/main/server/src/instant/jdbc/failover.clj#L44-L70 https://github.com/instantdb/instant/blob/main/server/src/in... [^2]: https://github.com/instantdb/instant/pull/743 https://github.com/instantdb/instant/pull/743

jillyboel 2y ago

> The next few hours was frustrating: we would change a setting, start again, wait 30 minutes, and invariably end up with the same error. Sounds about right for cloud services.

pilif 2y ago

> But we had less than a terabyte of data I really wonder how an in-place `pg_upgrade` of such small amounts of data would take 30+ minutes. My experience from a less mission-critical situation where 5 minutes of maintenance are absolutely acceptable is that an in-place `pg_upgrade` with `--link` of a 8 TB database takes less than a minute and will not accidentally lose data or fail to properly configure schema search paths or whatever other mess the article was talking about. I understand that 30 minutes of downtime are not acceptable. But if it's 5 minutes or less, I would seriously consider an offline upgrade using `pg_upgrade` And if it takes 30 minutes to hard-link less than 1 TB of data files, you should seriously consider changing hosts because that's absolutely unacceptable performance.

nijave 2y ago
I don't think pg_upgrade takes the whole time. Some of it is overhead of AWS managed database service where it's creating a snapshot before and after, applying new config, spinning for no apparent reason
stopachka 2y ago
> 30 minutes The Lyft team reported 30 minutes for their 30TB database. Our db took about 15 minutes. In the essay we wrote: > So we cloned our production database and tested an in-place upgrade. Even with our smaller size, it took about 15 minutes for the clone to come back online.
- deleted 2y ago
  [deleted]
timacles 2y ago
Yeah we just did it with the --link option on a 6TB database and it took like 30 seconds. Something has to be off with their OS settings or disk speeds. The main challenge with that is running an ANALYZE on all the tables though, that took like 30 minutes during which time the DB was unusable
- zonkd1234 2y ago

briffle 2y ago

Logical replication is a great tool, and we are using it for our next DB upgrade coming up in the next few months. It just has a few limitations I wish they would address, especially since logical replication is quickly becoming one of the more popular ways to upgrade databases with minimal downtime. I understand that logical replication handles partial replication, and the data warehouse use case, and I understand WHY many of the choices they made for it are there. I just wish that there was a flag you could set in a subscription that would enable it to be a complete 1:1 copy of the database, including DDL, sequences, etc, without having to do all the workarounds that are necessary now. Currently if a dev adds a column to a table on the publisher, and forgets to do it on the subscriber, there is no error, no indication, until it actually tries to replicate data for that table. Then you have to be monitoring for that error, and go figure out what other new tables, columns, etc, might have been added to the publisher that are missed on the subscriber. Its a huge opportunity for problems.

svilen_dobrev 2y ago
dump schema on both, and compare before copying? or that may have false positives?

jatins 2y ago

> all Instant databases are hosted under one Aurora Postgres instance Doesn't the increase the chances of one bad tenant taking the database down for all other tenants?

stopachka 2y ago
> Doesn't the increase the chances of one bad tenant taking the database down for all other tenants? We have safe-guards in place to prevent and isolate bad actors. It's more tricky then if resources weren't shared, but that is the trade-off for multi-tenancy.