| Biggest difference for me is DDLs are transactional in Postgres, but not on Oracle. That means migration scripts for software on Postgress can just have all DDLs (alter, create, drop, grant etc.) and DMLs (inserty, update, etc.) mixed in whatever order they need to be, and if any particular line of the migration script fails - the whole thing is rolled back as if nothing happened. And then you fix the problem and run migration again. Easy. In comparison writing migration scripts on Oracle is a nightmare - DDLs aren't transactional (THEY COMMIT ON EACH LINE...), so you have to separate them from DMLs and ensure that only the scripts that haven't passed yet are re-run later. I've worked in 3 different companies that used oracle, and there were 3 different approaches to that problem, and all 3 of them sucked :) In one company we had several big customers each with 1 production db, and software was written on separate branches for each customer, and helpdesk staff was dealing with migrations - programmers just asked helpdesk to add a column and worked on the test db for that customer. It was a lot of unnecessary work to port changes and bugfixes between branches, but at least we knew exactly what is on each db and could fix problems by ourselves. There was no migration to speak of, just manual changes on dbs and documenting them in svn (it was before git was popular). In another company there was one development branch and several customers, and there were migration scripts written by all developers when they made changes, which were merged into development branch for db by 1 guy whose whole job was to merge these scripts and check if migration works. It slowed down development (because when you finished your task on local db you had to make a migration script(s) and send them to be verified. And even "that guy" sometimes made mistakes and then if you fetched db scripts in the morning you couldn't work until stuff was fixed (or you had to recreate oracle db from scratch which took several hours). That was before docker BTW, now they probably use docker so that can be less of a problem. In the third company we had one customer but with hundreds of installations, and we had one development branch with frequent releases. Developers maintained migration scripts between release, major and minor versions. There was no "that guy" - we had smoke tests instead, and it sometimes took more time to write that migration script(s) than to change the code. So you want to add 3 columns to 3 tables and fill them? And it has to be done in order because of dependencies? Write no less than 6 migration scripts (alter table 1, update table 1, alter table 2, ...). Add them with proper names and some boilerplate to the migration scripts for minor versions (3.4.5 -> 3.4.6). But that's not all! We also have migration scripts for major versions (3.4.0 -> 3.5.0), so you also need to add them there. You have to check the migration separately because these scripts often use shortcuts to run faster. So your scripts might break despite working for minor version migration. Then there's the scripts for release version migration (3.0.0->4.0.0). Add your scripts there as well, and test once again. Oh, and testing these scripts on test data doesn't mean they will work - each installation of db changes slightly over time - people add stuff from ui. There are rules what they can change and what they cannot, but if you don't think about it you might break something with your migration scripts on production despite it working on test data. When that happens you have to write migration fixes which need to detect that problem and fix it on data you don't have direct access to :) It was a nightmare. Meanwhile Postgress is just doing the right thing, write 1 migration script with everything in it, if it works it works, if not - it rollbacks. Nobody thinks twice about it. |
The answer I received more than once from Oracle evangelists regarding transactional DDL: it's useless and if you need it, you are not testing your scripts properly