[HN Gopher] The operational relational schema paradigm
       ___________________________________________________________________
        
       The operational relational schema paradigm
        
       Author : kiyanwang
       Score  : 21 points
       Date   : 2022-05-15 10:03 UTC (1 days ago)
        
 (HTM) web link (planetscale.com)
 (TXT) w3m dump (planetscale.com)
        
       | awinter-py wrote:
       | programming in time is hard
       | 
       | (especially with migrations, because migrations _take_ time)
       | 
       | this feels too high-level -- different migration operations can
       | afford different guarantees (esp in the multi-node case). In
       | particular this doesn't talk about data migrations, where you
       | might need to lock 2 tables + join them to generate a third. not
       | all migrations are pure-sql either, they may include app code
       | 
       | my favorite article about this is benchling's
       | https://benchling.engineering/move-fast-and-migrate-things-h...
       | because it talks about pre + post deploy migrations
        
       | newaccount2021 wrote:
        
       | daenz wrote:
       | I wish relational databases would offer One Simple Trick that I
       | think would improve migrations immensely:
       | 
       | Every table should--by default--be a view of the actual
       | underlying table. In other words, a table is a shim for the real
       | table. This way, when it's time to perform migrations, the shim
       | to the underlying table could change independently of the actual
       | migration, since the interface to the table would be preserved
       | for all applications.
       | 
       | I know there are overhead concerns for this shim-view idea, but
       | I'm sure many of them can be cleverly elided under many
       | conditions.
        
         | bob1029 wrote:
         | This is a conversation our team had for a while. We are still
         | on the fence about it because the physical schema is very
         | stable at this point. There haven't been any major issues with
         | migrations in years.
         | 
         | But, this is one of those things like planting a tree. If the
         | entire business depends directly on the physical tables,
         | getting a major change in could instantly become a nightmare.
        
       | mr_gibbins wrote:
       | Too much here to deconstruct in a comment, but some choice
       | responses:
       | 
       | * How does the author suggest non-blocking DDL is actioned? You'd
       | have to snapshot your entire table using e.g. a similar method to
       | snapshot isolation at the row level to allow concurrent access.
       | Accessing the table, while in transition, will lead to
       | inconsistent output data sets.
       | 
       | * If your migration is 'days-long', then you are doing something
       | wrong. Try creating a new table (instant) then ETL'ing your data
       | over from the original, dropping and renaming. It will be much
       | faster AND it addresses accessibility on the table (no table
       | lock).
       | 
       | * You can schedule migrations if using a tool like Flyway by
       | doing this in code. If you insist on using a database product,
       | you could use (for MSSQL) SQL Agent, or cron for anything on
       | Linux (run a SP), etc.
       | 
       | * It is already possible to revert a migration if you are using
       | Oracle, using Oracle Flashback. For other DBs, you can still
       | revert a migration if you are using your deployment tool properly
       | i.e. redeploy an earlier version of your schema and drop all
       | other objects. You'll have to deal with your data, of course.
       | 
       | This would have been a more interesting post if a possible
       | solution to all the points was posited.
        
       ___________________________________________________________________
       (page generated 2022-05-16 23:02 UTC)