Adding new table columns with default values faster in PostgreSQL 11
Before PostgreSQL 11 adding anew table column with a non-null default value results in a rewrite of the entire table, This works fine for a smaller data sets but the whole thing goes super complicated and expensive with high volume databases because of ACCESS EXCLUSIVE LOCK ( default lock mode for LOCK TABLE statements that do not specify a mode explicitly ) on the table which conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE) and guarantees the holder of the lock is the only transaction accessing the table in any way, and it’ll block every other operation until it’s released; even simple SELECT statements have to wait, This is unacceptable for a continuously 24*7 accessed table and is a serious performance bottleneck. PostgreSQL 11 has addressed this problem gracefully by storing default value in the catalog and ushered whenever needed in rows exiting at the time the change was made and for new rows / new versions of existing rows are written with default value in place. The rows which existed before this change was made with NULL values uses the value stored in the catalog when the row is fetched. This makes adding new table columns with default values faster and even smarter. To conclude, The default value doesn’t have to be a static expression, It can be even non-volatile expressions like CURRENT_TIMESTAMP but volatile expressions such as random(), currval(), timeofday() will still result in table rewrites.
If you want MinervaDB PostgreSQL consultants to help you in PostgreSQL Performance Optimization and Tuning, Please book for an no obligation PostgreSQL consulting below:
