If you do it right, it's zero downtime (i.e. no DB locks held for long enough to cause timeouts to clients).

In most cases. However, some changes, such as adding the column and needing to manipulate data (populate nulls, transforms, etc.) could still cause locking.

This really comes down to knowing the boundaries of what changes could cause timeouts and working around them as best you can. However, some edge cases could cause downtime.