3 ms·

It's not exactly what the post is about, but transactional DDL is a feature I don't actually know how to live without. The idea that schema changes could fail s

It's not exactly what the post is about, but transactional DDL is a feature I don't actually know how to live without. The idea that schema changes could fail somewhere in the middle is terrifying, and I'm quite happy Postgres just solves this.

shayonj 5y ago
Love transactional DDL! Have only used on side projects for now.
mulmen 5y ago
Having worked with Oracle it is indeed terrifying. You can make it safe-er but it’s a lot clunkier and you have to think it through. With Postgres it mostly just works.
CodesInChaos 5y ago
Can you recommend any documentation on doing transactional DDL in production? I would expect major caveats in such a feature, like long lived table locks taking down the whole application for an hour when you don't exactly know what you're doing. For example concurrent index creation is harmless on its own, but combined with another fast but locking schema change, you could get a long lived lock, taking down the application until the index creation finishes (had a similar incident in a different database).
- hamandcheese 5y ago
  I would suggest taking a look at strong migrations[1]. It's a rails project, but the readme does a great job explaining what it checks for and what safe alternative to use instead. I still link to their explanations in PRs for non-rails projects. The examples unfortunately use the Rails migration DSL instead of plain SQL. If anyone knows of a SQL-first reference, I'd love to hear about it. [1]: https://github.com/ankane/strong_migrations#checks https://github.com/ankane/strong_migrations#checks
- dspillett 5y ago
  > Can you recommend any documentation on doing transactional DDL in production MS SQL fellow here, rather than postgres, but the concepts should be the same. No docs I can immediately point you at, but some feeling for how I [don't] do things: I still wouldn't perform most schema updates in live production, I'd wait for a maintenance window. The key benefit from my PoV is that the changes are transactional so either all work or they rollback and you are back where you started instead of some mysterious mid-way point. Adding indexes is fine, as long as you have spare IO capacity for the process to chew if performed on a large table, and many index alterations can be done online too, and adding/modifying procs, views, and similar objects can be similarly undisruptive if properly wrapped in transactions (so active sessions never see a half updated set of parts), but I'd not make actual table changes “properly live” in production. > I would expect major caveats in such a feature, like long lived table locks In DBs with transactional DDL some table operations are practically lock-free or just happen so fast that they might as well be, such as (usually) adding a NULLable column or making an existing one NULLable, and some other operations are, despite being long-winded, sometimes possible to perform online (the new structure is added, then a final sync done for changes made while that happened, then switched over to, and the old parts cleaned up now the new are in use), but I'd never risk it unless I really really had to make the change ASAP and absolutely couldn't arrange a maintenance break in good time. > For example concurrent index creation is harmless on its own, but combined with another fast but locking schema change There should only ever be your process making schema changes, as the application and other users shouldn't be at all, so in theory you don't need to worry about two changes competing for locks or causing lock escalation like that. Several concurrent online index creations/modifications are fine, as long as you can afford the extra IO load that will impose, particularly if the objects being touched are on different storage (or otherwise have separate IO quotas) so the two index changes won't compete with each other for IO, but not table changes and similar: keep them to one at once, while nothing else is happening.
hn_throwaway_99 5y ago
In Node projects, in general I don't recommend using knex.js for data access, but their migrations facility is great. By default it runs all of your DDL within a transaction, but in some cases where you can't run in a transaction (like adding a value to an enum type) it makes it easy to disable it: https://knexjs.org/#Migrations-API-transactions https://knexjs.org/#Migrations-API-transactions
- aw9f70gae 5y ago
  Off topic, but why wouldn't you recommend knex.js / where can I read good criticisms of it?
dspillett 5y ago
> transactional DDL is a feature I don't actually know how to live without Same with MS SQL Server. Make several schema changes, and be assured that if things go base-over-apex part way through you are going to be returned to the last good state.

shayonj 5y ago

Love transactional DDL! Have only used on side projects for now.

mulmen 5y ago

Having worked with Oracle it is indeed terrifying. You can make it safe-er but it’s a lot clunkier and you have to think it through. With Postgres it mostly just works.

CodesInChaos 5y ago

Can you recommend any documentation on doing transactional DDL in production? I would expect major caveats in such a feature, like long lived table locks taking down the whole application for an hour when you don't exactly know what you're doing. For example concurrent index creation is harmless on its own, but combined with another fast but locking schema change, you could get a long lived lock, taking down the application until the index creation finishes (had a similar incident in a different database).

hamandcheese 5y ago
I would suggest taking a look at strong migrations[1]. It's a rails project, but the readme does a great job explaining what it checks for and what safe alternative to use instead. I still link to their explanations in PRs for non-rails projects. The examples unfortunately use the Rails migration DSL instead of plain SQL. If anyone knows of a SQL-first reference, I'd love to hear about it. [1]: https://github.com/ankane/strong_migrations#checks https://github.com/ankane/strong_migrations#checks
dspillett 5y ago
> Can you recommend any documentation on doing transactional DDL in production MS SQL fellow here, rather than postgres, but the concepts should be the same. No docs I can immediately point you at, but some feeling for how I [don't] do things: I still wouldn't perform most schema updates in live production, I'd wait for a maintenance window. The key benefit from my PoV is that the changes are transactional so either all work or they rollback and you are back where you started instead of some mysterious mid-way point. Adding indexes is fine, as long as you have spare IO capacity for the process to chew if performed on a large table, and many index alterations can be done online too, and adding/modifying procs, views, and similar objects can be similarly undisruptive if properly wrapped in transactions (so active sessions never see a half updated set of parts), but I'd not make actual table changes “properly live” in production. > I would expect major caveats in such a feature, like long lived table locks In DBs with transactional DDL some table operations are practically lock-free or just happen so fast that they might as well be, such as (usually) adding a NULLable column or making an existing one NULLable, and some other operations are, despite being long-winded, sometimes possible to perform online (the new structure is added, then a final sync done for changes made while that happened, then switched over to, and the old parts cleaned up now the new are in use), but I'd never risk it unless I really really had to make the change ASAP and absolutely couldn't arrange a maintenance break in good time. > For example concurrent index creation is harmless on its own, but combined with another fast but locking schema change There should only ever be your process making schema changes, as the application and other users shouldn't be at all, so in theory you don't need to worry about two changes competing for locks or causing lock escalation like that. Several concurrent online index creations/modifications are fine, as long as you can afford the extra IO load that will impose, particularly if the objects being touched are on different storage (or otherwise have separate IO quotas) so the two index changes won't compete with each other for IO, but not table changes and similar: keep them to one at once, while nothing else is happening.

hn_throwaway_99 5y ago

In Node projects, in general I don't recommend using knex.js for data access, but their migrations facility is great. By default it runs all of your DDL within a transaction, but in some cases where you can't run in a transaction (like adding a value to an enum type) it makes it easy to disable it: https://knexjs.org/#Migrations-API-transactions https://knexjs.org/#Migrations-API-transactions

aw9f70gae 5y ago
Off topic, but why wouldn't you recommend knex.js / where can I read good criticisms of it?

dspillett 5y ago

> transactional DDL is a feature I don't actually know how to live without Same with MS SQL Server. Make several schema changes, and be assured that if things go base-over-apex part way through you are going to be returned to the last good state.