Databases · Senior · Question 34 of 75

How do you safely backfill a new column on a table with 500 million rows?

1Copy the table to a CSV and back
2Run one UPDATE for all rows in a single transaction
✓Correct answer: Update in small batches by primary key ranges, committing each batch and pausing to limit locks, WAL and replication lag

Explanation

A single giant update holds locks for long, bloats the table and can stall replicas. Batches are resumable if something fails.

Further reading

Test yourself

Take the Databases · Senior quiz: questions and options are shuffled, and you get a review of your mistakes at the end.