3 ms·
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 breakin
by 10000truths 3y ago
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
- deleted 3y ago[deleted]
- singron 3y agoWith 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;
- munk-a 3y agoWe 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.
- JustLurking2022 3y agoHoly 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.
- enragedcacti 3y ago> 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.
- fbdab103 3y agoIs 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.
- danielvaughn 3y agoYou 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.
- clintonb 3y agoSchema migrations are a solved problem. You make backward-incompatible changes in multiple steps. https://engineering.edx.org/django-migration-donts-f4588fd11b64 https://engineering.edx.org/django-migration-donts-f4588fd11...
- mvdtnz 3y agoI'm sorry that is insane. That approach wouldn't pass the first sniff test of any sensible technical design review.
- camgunz 3y agoYou'd be surprised how many companies do exactly this stuff and not just with DB schema changes. They're called bridging changes.
- manmal 3y agoImagine 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.
- deleted 3y ago[deleted]