I am trying to add non-null constraints on several columns in a table with about 70,000,000 rows. Each statement is just
ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;, and each statement takes about 4.5 minutes to complete.
During this time, the read IOPS spike to about 8,000, and stay there until the statement completes. However, after executing several of these statements, the IOPS drop to 2,000 and are capped there. At this point each statement takes four times as long. If I wait a while, I can get another 30 minutes or so at 8,000 IOPS.
At first I thought this was the burst credits being used and then regenerated, but
- The burst balance never drops below 98% during this entire time.
- According to the documentation, I should have a balance of about 2,970, not 8,000.
The database details are
- Postgres 11.8 on AWS RDS.
- Instance size is db.t3.large.
- 990 GB of GP storage.
- No provisioned IOPS.
My questions are
- What is going on here? What is the significance of the 8,000 and 2,000 numbers? I can't find documentation about this anywhere.
- Why isn't burst balance being used here?
- Is 4.5 minutes to add the constraint on a 70 million-row table reasonable? And in general, is there a more efficient way I can be doing this? I still have over 60 columns to alter.