> 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.
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.