Hacker News Reader

Comment by 10000truths | Hacker News Reader

The solution to this is the same as with APIs: versioning. Instead of naming your table "my_foo", you name it "my_foo_v1". Then, when you want to make a breaking change to the schema, you:

1. Create a new table "my_foo_v2" with your desired schema

2. Modify write queries for "my_foo_v1" so that they also write to "my_foo_v2"

3. Copy over existing data in "my_foo_v1" to "my_foo_v2" with a migration script

4. Modify read queries for "my_foo_v1" so that they read from "my_foo_v2" instead

5. Remove all write queries to "my_foo_v1"

6. Drop the "my_foo_v1" table

View on HN

Holy hell does that sound awful. In practice, it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't. It becomes indistinguishable whether a row is missing because it was intentionally deleted or because it was written by a client who didn't know any the new table version. There are ways to account for this but it's layers of crap on top of crap.

> it doesn't even solve the core problem as you'd have to deploy new versions of all binaries precisely in sync to avoid data incorrect because some clients know about the v2 table and others don't

its painful but this approach does work as long as every client is migrated between each step.

after 1: v1 is valid, reading v1, writing v1

after 2: v1 is valid, reading v1, writing v1 and v2

after 3: v1 and v2 are valid, reading v1, writing v1 and v2

after 4: v1 and v2 are valid, reading v2, writing v1 and v2

after 5: v2 is valid, reading v2, writing v2

after 6: v2 is valid, reading v2, writing v2

at each point, both the current and the previous version are reading a valid table and writing to a table whose values will make it into v2.

Good luck migrating every single client in the real world (unless you are willing to disable old clients & force an upgrade if they get too old)

These are the same problems you have with any API or package you ship.

Is there really any alternative? Data migrations are always going to be terrible. You need to slowly roll out the incremental changes so that everything is backwards compatible until you the threshold point where you can finally delete the historical route.

With an API, you can often create facades and other patterns to avoid having to migrate the underlying data

You can often also accomplish this with a database view.

In a sensible approach schema migrations are totally decoupled from the API.

With postgres, simple views are updatable, so you can often do this pattern without copying tables or dual writes. It's particularly useful for renaming columns, but you can also do some other things. You also don't have to use version numbers on all your tables since you only need the view temporarily.

    ALTER TABLE my_foo RENAME TO my_foo_tmp;
    ALTER TABLE my_foo_tmp <your desired change here>;
    CREATE VIEW my_foo AS SELECT <how you make your new table seem like your old table>;
    COMMIT;
    <update your apps to use my_foo_tmp>
    DROP VIEW my_foo;
    ALTER TABLE my_foo_tmp RENAME TO my_foo;
    CREATE VIEW my_foo_tmp as select * from my_foo;
    COMMIT;
    <update your apps to use my_foo>
    DROP VIEW my_foo_tmp; COMMIT;

We actually used a similar tactic when transitioning our data to supporting soft deletion. We have a limited number of queries that update data or present data for administrative review and processing - but a lot of wild ones around reporting. We'd rename table `foo` to `foowithdeleted` and create a new `foo` view that excludes any soft-deleted rows. Our reporting queries keep on trucking like normal and read out of the `foo` table (now a view - but they don't care) and we only need to adjust the administrative view to show soft-deleted rows for undeletion operations.

Shell-gaming tables with views can be incredibly useful in postgres.

You know, I've been toying with this very idea in my head for a while, as a way to make safe schema migrations. But I also know very little about databases so had no idea if this was a stupid idea or not. Glad to know I'm not alone.

One issue I've considered is fk relationships - that could get complicated depending on the approach.

Schema migrations are a solved problem. You make backward-incompatible changes in multiple steps.

https://engineering.edx.org/django-migration-donts-f4588fd11...

Imagine you have Android & iOS apps which some of your users won't ever update. You'll never be able to drop any old version's tables, and you'll need to keep all table versions in sync more or less in real-time. Only when the last user has updated from a certain client version will you be able to remove that version's tables.

I'm sorry that is insane. That approach wouldn't pass the first sniff test of any sensible technical design review.

You'd be surprised how many companies do exactly this stuff and not just with DB schema changes. They're called bridging changes.

Reply on news.ycombinator.com