What happens at low level when I add a column to a table in PostgreSQL?
In Postgres nothing really happens. 

It just adds a row to the system catalogs so that it knows that you added a column. It doesn't even touch the table data. So even on a table with a billion rows this would only take a few milliseconds once it could obtain the exclusive lock on the table. 

Starting with Postgres 11, this even is the case when adding a NOT NULL column with a constant as the default value, e.g. add some_flag integer not null default 42

If the default value is a volatile function (e.g. current_timestamp), Postgres is forced to call this once for each row and put in the result of that. Then this would take a substantial amount of time to add the column with billions of rows.