1

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.

Read IOPS

read IOPS

Write IOPS

write IOPS

Burst balance

burst balance

CPU

CPU

Memory

memory

2

I've not found the IO burst balance to work the way the documents state nor the monitoring of the burst balance to reflect reality, for t* class instances. So while I can't explain your observation, it does not surprise me.

And in general, is there a more efficient way I can be doing this?

A single ALTER TABLE can implement a number of actions, not just a single action. And it should generally scan the table only once, not once for each action.

ALTER TABLE table_name 
   ALTER COLUMN column_name1 SET NOT NULL,
   ALTER COLUMN column_name2 SET NOT NULL,
   ALTER COLUMN column_name3 SET NOT NULL,
   ALTER COLUMN column_name4 SET NOT NULL,
   ALTER COLUMN column_name5 SET NOT NULL...;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.