How do most "popular" (MySQL, Postgres...) database system handle altering tables on live production databases (like adding, deleting or changing the type of colums)?
I know the correct way is to backup everything schedule downtime and do then do the changes.
But... does any current database system support doing these things "on-line" without stopping anything? (maybe just delaying the queries that reference a column that is just being changed/deleted)
And what does it happen when I just do an
ALTER TABLE... on a live running database?
Does everything stop when this happens?
Can data get corrupted?
Again, I'm mostly referring to Postgres or MySQL as these are what I encounter.
(And, yes, anytime I had to do this before I did it "the right way", backing things up, scheduling downtine etc. ...but I just want to know if it's possible to do this sort and things "quick and dirty" or if there is any DB system that actually has support for "quick, live and dirty" schema changes)
Someone just suggested Online Schema Change for MySQL from Facebook script (with a tutorial here and source here)... seems like a nice way to automate a the set of "hacky" ways to do it... has anyone ever used it in something resemblig production?
pg_reorgcan help with the more difficult scenarios.