Luckily, just as not all NoSQL databases are the same, nor are all SQL databases. If NoSQL is an option, standards are obviously less relevant, so you can rely on the behaviour of one SQL database engine of your choice. Postgres is one of a few SQL database engines which implement transactional schema changes.

Companies such as Percona also provide open-source tools which allow for online, non-locking schema changes for Postgres, and I assume commercial database engines have options as well.