It’s a pretty basic topic, because the scheme changes over time anyway.

I usually use Flyway, because it supports migrations in SQL file format (so as not to study individual syntaxes, and then not think about how to implement some special SQL syntax for migrations in them) and does not support rolling back migrations (they only go forward: if something is wrong, then we do a new migration).

The main fundamental problem of updates without stopping work is that for some time the old code must work with the new database schema.

The second problem is the duration of the operation when the database is not working. In some databases, in some cases, updating the schema leads to downtime.

I found good material on this topic for Postgres with examples of SQL constructs for various cases: