4 ms·

Interesting approach, with a pretty dark side-effect: the tech debt silently creeping in and saying forever. Apart from that, you can use any abstraction over t

Interesting approach, with a pretty dark side-effect: the tech debt silently creeping in and saying forever. Apart from that, you can use any abstraction over the base schema, depending on the use-case. Renaming a column can be as simple as renaming a field in the DTO structure. The interesting part is when you actually get to pay the debt back. Yes, this is not free lunch. You move fast, but you have to eventually pay the cost of the underlying storage containing all the historical schemas combined at once. This is the difference between carefully planned schema migrations and abstraction-based migrations. The former takes more time planning and executing, but the end state is the reference schema. The latter keeps on extending the underlying schema and building projections over it. Over time, the system accumulates tech debt in the form of ever-expanding base schema that contains all the historical columns.

fabianlindfors 5y ago
This is definitely a valid concern. My plan for building migrations on top of this is to have older migrations automatically be deleted as they become unused. Preferably you wouldn't actually keep using the old schemas but rather keep them as you roll out changes to your application servers. Migrations could then be deleted (in reverse order) based on some condition, for example time since the schema was created or time since last query.
- kroolik 5y ago
  But in order to support the "add column" migration you have to add it to the base schema, right? It has to be materialized somewhere. Now, when you remove a column, you just create a new view without it being available. But the column in the base schema must stay because: you support the old schema, and because you want to avoid the operational complexity of physically removing it, right? Now, you can obviously split a table into a list of tables each storing the pk and some column value. But you have just invented columnar storage in a row-based rdbms :D A different approach is to have scheduled maintenance window, for example quarterly or every 6 month when you materialize all the changes at once. The amortized operational cost is way lower :)
  - robertlagrant 5y ago
    I think you've misunderstood - the issue is how to keep two views in place - one supporting current clients, until the last one disconnects, and another supporting new clients. Until you have no current clients left, you can't delete the old view or remove underlying columns that that old view requires. Once they have, you can execute that phase.
  - deleted 5y ago
    [deleted]
johbjo 5y ago
The point here is to do migrations live while clients randomly connect and stay connected for random time. When all old clients have disconnected, the old schema can be removed. The point is not to let old clients work indefinitely. In other versions of this idea, all views/procedures are prefixed with some revision-number but that looks messy.
- kroolik 5y ago
  But what happens when schema Xn adds a column N, and schema Xn+1 removes it? The base schema, the source for all the views will contain all the columns Xn... from all the migrations until purged. That purging is what has a lot of operational complexity. And renaming a column. All the rest is zero-downtime in PG already.
  - johbjo 5y ago
    It would have to be in steps: Add Xn+1: create new views without column. When no Xn clients remain: drop column from tables and drop schema Xn. The point as I see it is to not break live client connections which expect the column to exist.
    - kroolik 5y ago
      Yes, this is a very important insight! I think the author proposes this approach as replacement for regular schema migrations, whilst both approaches complement one another. You can use views to make migrations that were previously tricky zero-downtime. If that's not the case, then I mist've read the article wrong! Edit: although when I think of it - if you want to eventually materialize old migration schemas into the base schema, you need to do the rename, too. Which is not zero-downtime because of new migration schemas that do the renaming automatically. Meaning changing views' definition, meaning lots of locking. So, you still need maintenance windows to merge all the changes. Just not on every change. Otherwise the base schema will then eventually be completely out of sync and contain tons of old, unused columns.
      - 5y ago

fabianlindfors 5y ago

This is definitely a valid concern. My plan for building migrations on top of this is to have older migrations automatically be deleted as they become unused. Preferably you wouldn't actually keep using the old schemas but rather keep them as you roll out changes to your application servers. Migrations could then be deleted (in reverse order) based on some condition, for example time since the schema was created or time since last query.

kroolik 5y ago
But in order to support the "add column" migration you have to add it to the base schema, right? It has to be materialized somewhere. Now, when you remove a column, you just create a new view without it being available. But the column in the base schema must stay because: you support the old schema, and because you want to avoid the operational complexity of physically removing it, right? Now, you can obviously split a table into a list of tables each storing the pk and some column value. But you have just invented columnar storage in a row-based rdbms :D A different approach is to have scheduled maintenance window, for example quarterly or every 6 month when you materialize all the changes at once. The amortized operational cost is way lower :)
- robertlagrant 5y ago
  I think you've misunderstood - the issue is how to keep two views in place - one supporting current clients, until the last one disconnects, and another supporting new clients. Until you have no current clients left, you can't delete the old view or remove underlying columns that that old view requires. Once they have, you can execute that phase.
- deleted 5y ago
  [deleted]

johbjo 5y ago

The point here is to do migrations live while clients randomly connect and stay connected for random time. When all old clients have disconnected, the old schema can be removed. The point is not to let old clients work indefinitely. In other versions of this idea, all views/procedures are prefixed with some revision-number but that looks messy.

kroolik 5y ago
But what happens when schema Xn adds a column N, and schema Xn+1 removes it? The base schema, the source for all the views will contain all the columns Xn... from all the migrations until purged. That purging is what has a lot of operational complexity. And renaming a column. All the rest is zero-downtime in PG already.
- johbjo 5y ago
  It would have to be in steps: Add Xn+1: create new views without column. When no Xn clients remain: drop column from tables and drop schema Xn. The point as I see it is to not break live client connections which expect the column to exist.
  - kroolik 5y ago
    Yes, this is a very important insight! I think the author proposes this approach as replacement for regular schema migrations, whilst both approaches complement one another. You can use views to make migrations that were previously tricky zero-downtime. If that's not the case, then I mist've read the article wrong! Edit: although when I think of it - if you want to eventually materialize old migration schemas into the base schema, you need to do the rename, too. Which is not zero-downtime because of new migration schemas that do the renaming automatically. Meaning changing views' definition, meaning lots of locking. So, you still need maintenance windows to merge all the changes. Just not on every change. Otherwise the base schema will then eventually be completely out of sync and contain tons of old, unused columns.
    - 5y ago