brentjanderson

I think Ducklake[1] is a terrific example of this. They said "look, let's build a lake house over S3, but for the bit that needs strong consistency (the manifest of which S3 blobs are in play), let's use Postgres". Postgres as a metadata catalog or control plane is brilliant for this, since you get strong consistency and the scaling story around a metadata catalog is far different than the volume of data you need to store. Use S3 for volume, Postgres for consistent metadata.

A similar pattern has spilled out of projects like Warpstream[2], which I suspect is using Postgres behind the scenes of their control plane.

[1]: https://ducklake.select

[2]: https://www.warpstream.com/

> So many of us have been taught that it is the fate of the small to be destroyed by the big, since they are more ruthless and more powerful. But the evidence doesn't really support this just-so story.

This is the comment I came here to read. Thank you, I already have a copy of the book and intend to dig in more on these themes specifically.

There's actually a term for this, Context Collapse [1] that explores how social media forces everyone to have a single online persona instead of presenting in the way that makes sense for a given social context (e.g. the "you" at work vs. the "you" at school vs. the "you" with family).

[1]: https://en.wikipedia.org/wiki/Context_collapse

Sadly, most of them are lost to time. There's one that I'm aware of at https://archive.org/details/hypercard_voyager-engineer-new is just one station of about 15 from one of the ships.

https://thoriumsim.com is a modern incarnation of the same software.

Bill's contribution with HyperCard is of course legendary. Apart from the experience of classrooms and computer labs in elementary schools, it was also the primary software powering a fusion of bridge-simulator-meets-live-action-drama field trips (among many other things) for over 20 years at the Space Center in central Utah.[0] I was one of many beneficiaries of this program as a participant, volunteer, and staff member. It was among the best things I've ever done.

That seed crystal of software shaped hundreds of thousands of students that to this day continue to rave about this program (although the last bits of HyperCard retired permanently about 12 years ago, nowadays it's primarily web based tech).

HyperCard's impact on teaching students to program starship simulators, and then telling compelling, interactive, immersive, multi-player dramatic stories in those ships is something enabled by Atkinson's dream in 1985.

May your consciousness journey between infinite pools of light, Bill.

Also, if you've read this far, go donate to Pancreatic Cancer research.[1]

[0]: https://spacecenter.alpineschools.org [1]: https://pancan.org

IANAL either but if I were you, I’d start here: https://www.vanta.com/products/hipaa or look for competitors.

And perhaps look at Stripe Atlas for getting my corporate ducks in a row to start with. https://stripe.com/atlas

Wading into that to get oriented, you would then be better equipped to have at least a baseline. A corporate attorney would be the next step to verify what you’re doing.

Minnestar.org hosts networking events that may be useful for finding people in the intersection of tech, healthcare, and law. Attend and get some face time to find people who may want to help. Lots of corporate centers in Minneapolis (assuming you’re in or near the twin cities), including healthcare. Depending on financial considerations, you may be able to find on ramps to grants, investors, or donors to fund compliance. Not sure on that though, but it’s possible.

Good luck!

One of the linked pieces in the Neon blog post is from Knock, where we pulled off a practically zero downtime migration: https://knock.app/blog/zero-downtime-postgres-upgrades

In that post we walk through all the steps we took to go from Postgres 11.9 to 15.3.

https://rpginabox.com/ is fantastic. Includes a lot of great assets, constrained enough to ensure you can actually build something, open enough that you can build a lot of different things with it.

OP Here -

1. There was zero downtime - no dropped requests, no 5xx errors. There _was_ a latency spike that was carefully tuned to be within timeout limits for our customers, but we dropped zero requests from the cut over.

2. Yes, it's very tedious, and in its own way painful. We also did a MongoDB upgrade recently and, while we still took the time to verify our workloads on the more recent versions, because Mongo is an AP system, it's trivial to failover to the new version and move on.

That said, the application-level logic changes were not particularly complicated. The script to orchestrate the cutover was application-specific, and I think for migrations like this you have to do the work to get it done right.

I'd also add that the tedium of doing it right, while ideally avoidable, is precisely why customers pay us to do handle this complexity on their behalf. Sometimes you've just got to do the work. They want a service that's up all the time. While no one can guarantee that, we strive for it within reason, and even then going to "unreasonable" lengths to have a better customer experience is exactly what makes many products unreasonably good.

Stretching the work out and taking each step carefully did avoid critical mistakes. We had a few missteps along the way, and we were able to rollback without critically affecting the service. Doing an in-place upgrade, trying to minimize the time spent on this problem, would have been far more risky than spreading that risk out over the whole process we took. Of course, each team needs to figure out what's going to work for their situation & constraints.

3. We do use Aurora, but our instance was old enough to not be supported for zero-downtime patch upgrades (ZDP) which does not handle major version upgrades. They also recently released blue/green deployments for Aurora Postgres clusters, which may be a way to do what we did without having to resort to as many changes.

OP here:

> Can't initialize a replica from a backup

You could, but you're not going to get any of the constant writes happening during the backup. You will have missing writes on the restored system without some kind of replication involved unless you move up to the application layer.

For example, you could update your app to apply dual writes. I'm aware of teams that have replatformed entire applications on to completely different DBs that way (e.g. going from an RDBMS to something completely different like Apache Cassandra).

For our situation, dual-writes seemed more risky than just doing the dirty work of setting up streaming replication using out of the box Postgres features. But, for some teams it could be a better move.

> This isn't "zero downtime"

and

> The article omits details on how consistency was preserved

In the post we go into detail about how we preserved consistency & avoided API downtime, but the gist is that the app was connected to both databases, but not using the new one by default. We then sent a signal to all instances of our app to cut over using Launch Darkly, which maintains a low-latency connection to all instances of our app.

For the first second after that signal, the servers queued up database requests to allow for replication to catch up. This caused a brief spike in latency that was within intentionally calculated tolerances. After that pause, requests flowed as usual but against the new database and the cut over was complete.

We included a force-disconnect against any pending traffic against the old database as well, with a 500 ms timeout. This timeout was much higher than our p99 query times, so no running queries were force terminated. This ensured that the old database's traffic had ceased, and gave replication plenty of time to catch up.

> No mention of a rollback option

Although it didn't make the cut for the blog post, we considered setting up a fallback database on PG 11.9 and replicating the 15.3 database into that third database. If we needed to abort, we could roll forward to this database on the same version.

We opted to not do this after practicing our upgrade procedure multiple times in staging to ensure we could do this successfully. Having practiced the procedure multiple times gave us confidence when it came to performing the cut over. We also used canary deployments in production to verify certain read-only workloads against the database, treating the 15.3 instance as a read replica.

To your point about it being late at night, we intentionally did this in the early evening on a weekend to avoid "fat finger" type mistakes. The cut over was carefully scripted and rehearsed to reduce the risk of human error as well.

In the event that we needed to rollback, the system was also prepared to flip back to the old database in the event of a catastrophic failure. This would have lead to some data loss against the new database, and we were prepared to reconcile any key pieces of the system in that scenario. To minimize the risk of data loss, we paused certain background tasks in the system briefly during the cutover to reduce the number of writes applied against the system. These details didn't make the blog post as we were going for more of the specifics to Postgres and less to Knock-specific considerations. Teams trying to apply this playbook will always need to build their own inventory of risks and seek to mitigate them in a context-dependent way.

Edit: More detail about rollback procedure

OP here - we avoid sequences in all but one part of our application due to a dependency. We use [KSUIDs][1] and UUID v4 in various places. This one "gotcha" applies to any sequence, so it's worth calling out as general advice when running a migration like this.

[1]: https://segment.com/blog/a-brief-history-of-the-uuid/

OP here: we looked at this and were not confident in manually advancing the LSN as proposed, and detecting any inconsistency if we missed any replication as a result. Table by table seemed more reliable, despite being more painstaking.

OP here. We don’t specify, but it’s big enough that it’s not reasonable to do a dump and restore style upgrade.

The strategies in the post should work for any size database. The limit becomes more a matter of individual table sizes, since we propose using an incremental approach to synchronizing one table at a time.

Neat tool! Some of our findings for large tables could be interesting for a tool like this, making it easier to apply the right strategy to the right tables. Having something like this with those strategies could be indispensable to teams running a migration like this in the future.

OP here - we have more coming about the role that the BEAM VM played in this migration too.

(The BEAM is the virtual machine for the Erlang ecosystem, analagous to the JVM for Java. Knock runs on Elixir, which is built on Erlang & the BEAM).

That might be possible. We were within days of performing our upgrade when the blue/green feature became available for Postgres, so we didn't consider it for our work.

You may be able to boot up an 11.21 replica in an existing Aurora cluster as a read replica, and then failover to that replica as your primary, which would be a minimally disruptive process if your application is designed to tolerate replica failover.

From there, you could upgrade the rest of your cluster to 11.21, and then use the blue/green upgrade process for AWS. If you do, I'd love to hear about how it goes as we will definitely consider the blue/green feature next time.

I looked at getting LSN numbers after an upgrade/cluster replacement, and IIRC restoring from a snapshot emits LSN information into the logs, but it's a bit of of a mixed bag as to whether or not you get the __right__ LSN out the other side. Because the LSN is more a measure of how many bytes have been written within a cluster, it's not something that meaningfully translates to other clusters, unfortunately.

OP Here - that's great feedback! Our hope is to build confidence in both the reliability of our product _and_ the consistency of the workloads. Of course, presenting the illusion of consistency while being flaky is far worse than managing customer expectations and taking intentional downtime to, in the long run, have better uptime.

Indeed, having periodic maintenance windows expected up-front probably leads to more robust architectures overall: customers building in the failsafes they need to tolerate downtime leads to more resilience. Teams that can trust their customers in that way can, in turn, take the time they need to make the investments they need to build a better product.

Perhaps this will be the blog post we write after our next major version upgrade: expectation setting around downtime _is_ the way to very high uptime.

That's a very interesting approach, I'm not sure if the sequences would remain consistent under that model or not. AWS RDS Aurora also requires you to drop replication slots when performing version upgrades, so we would unfortunately have lost the LSNs for replication slots that we use to synchronize with other services (e.g. data warehouse).

I'd look into it more next time if it weren't for the fact that AWS now supports Blue/Green upgrades on Aurora for our version of Postgres. But, it's an interesting approach for sure.

We actually did those steps as part of our overall assessment, and you're right that we could have taken an outage window for that long and called it a day. We decided the tradeoff wasn't worth it for our situation, but taking the outage window is definitely a viable option.

I'm sympathetic to your comment that 15 minutes of planned downtime is fine for approximately 100% of SaaS companies. That's probably true here too, and maybe the work of doing this kind of upgrade was a waste in that regard. But, in considering the kind of product experience we would want for ourselves, zero downtime seems better than no downtime. The opportunity cost of feature work over the same window is real, but so is the reputation we hope to build as a platform that "just works" even if it seems crazy the lengths we might go to so that our customers don't have to think about it.

OP here: It’s true that all services have downtime for one reason or another. We discussed taking an outage window, but one thing that we kept coming back to was how we might trial run the upgrade with production data. Having a replica on PG 15 that was up to date with production data was invaluable for verifying our workloads worked as expected. Using a live replica makes it possible to trial run in production with minimal impact.

A key learning for me from this migration was how nice it can be to track and mitigate all of the risks you can think of for a project like this. The risk of an in-place upgrade in the end seemed higher than the risks associated with the route we chose, outage windows notwithstanding.

As a bonus, if we need this approach in the future, this blog post should give us a head start, saving us many weeks of work. We hope it helps other teams in similar situations do the same.

I hear you on that, and can say that Postgres is incredibly capable at going beyond typical relational database workloads. One example are durable queues that are transactionally consistent with the rest of the database play a unique role in our architecture that would otherwise require more ceremony. More details here: https://getoban.pro

We are also working on shifting some workloads off of Postgres on to more appropriate systems as we scale, like logging. But we intentionally chose to minimize dependencies by pushing Postgres further to move faster, with migration plans ready as we continue to reach new levels of scale (e.g. using a dedicated log storage solution like elastic search or clickhouse).

OP here - we would have used the same approach for the minor upgrades. This isn’t a case of “we procrastinated ourselves into a corner” and more a matter of “if it isn’t broke, don’t fix it” recognizing we would need to make the jump eventually.

You’re right! OP here. We were on 11.9 which is not supported by Blue/green deployments for Aurora. Maybe next time.

That sort of problem is beyond the scope of the runtime in any case, isn't it? In either of the examples you offered (holding a lock, coordinating with other processes), there must be timeouts enforced by the lock or the other processes so that, if something goes wrong, the system isn't waiting for a crashed process to continue the work.

Erlang/Elixir do make this pretty easy to manage, including the scenario where the process does recover by reverting back to a known good state. It won't do it for you automatically, but it exposes enough surface area to make problems like that solvable without reaching for a lot of extra tools - it's built into the runtime.

Erlang/Elixir have a great story here: “let it crash”. Because each slice of activity in an application is wrapped in its own process (think single threaded loop but you can run a million at a time, almost free to create and destroy), if it crashes it only takes down that web request/process. Recovery mechanisms are built in to get back to a know good state.

You may be able to incorporate the [Membrane Framework](https://membrane.stream/) to do that. Built in Elixir, deals in those types of multimedia problems.

I'm not an expert here, but I'd expect that capturing a sample using Membrane and piping it into Whisper should be doable.

We needed a way to selectively order ASC or DESC in a Postgres window function. It turns out you can with CASE statements!

https://www.mealime.com/ is fantastic. I've been using it for years. It has saved us money and eat better, with plenty of variety in what we choose to eat each week. 10/10

Depends on the database - I know that CockroachDB supports rolling upgrades with zero downtime, as it is built with a multi-primary architecture.

For PostgresQL or MySQL/MariaDB, your options are more limited. Here are two that come to mind, there may be more:

# The "Dual Writer" approach

1. Spin up a new database cluster on the new version. 2. Get all your data into it (including dual writes to both the old and new version). 3. Once you're confident that the new version is 100% up to date, switch to using it as your primary database. 4. Shut down the old cluster.

# The eventually consistent approach

1. Put a queue in front of each service for writes, where each service of your system has its own database. 2. When you need to upgrade the database, stop consuming from the queue, upgrade in place (bringing the DB down temporarily) and resume consumption once things are back online. 3. No service can directly read from another service's database. Eventually consistent caches/projections service reads during normal service operation and during the upgrade.

A system like this is more flexible, but suffers from stale reads or temporary service degradation.