PostgreSQL 10.6: Alter parent table will block ALL children?

Question

I want to add a column to near 74.000 child tables. These tables continually have insertions and reads from all the services in the company, in almost realtime. If I add a column to the parent table, it will block the 74.000 tables, or will be one-to-one?

Answer 1

Adding a column to the inheritance parent will only be completed once all children are modified. So yes, you will have to wait until all 74000 tables have been modified, and there will be an ACCESS EXCLUSIVE lock on all tables at the same time.

I hope you have set max_locks_per_transaction high enough that the locking table can hold all these locks. Having 74000 tables in one database is a problem, and having them all in an inheritance hierarchy is an even bigger problem. But that problem is with the database design.