The UPDATE That Never Finishes and What to Run Instead

This article recounts a common but frustrating experience of running long-running database updates, specifically updating a massive number of rows in a single UPDATE statement. The author describes the pitfalls and inefficiencies they encountered while trying to execute such a migration.

The key points are:

1. Adding a column, backfilling it, and committing the UPDATE seems trivial on a local laptop or test database. However, deploying this change to production can be a nightmare.

2. Running a single giant UPDATE statement holds a lock on the entire table, making it impossible to scale the operation. Even after the UPDATE completes or is rolled back, there's no way to know how long the operation took or how many rows were affected, as Postgres doesn't provide a percentage or row count.

3. The article presents three common approaches to splitting the table for parallel updates:

a. Using a window function like NTILE to compute chunk boundaries - this requires a full scan of the table to order and bucket the rows, which is inefficient for large tables.

b. Using OFFSET/LIMIT to partition the rows - OFFSET doesn't skip rows, so it's slow, and row deletions or insertions during the scan can cause double-processing or missing rows.

c. Using keyset pagination with a monotonically increasing ID column - while this approach is fast, stable, and accurate, it still requires a lookup of the smallest and largest ID values from the database index, essentially performing the same full scan work.

4. The author emphasizes that a simple solution exists - using the existing ID column, which is a dense, monotonically increasing integer and likely already present in the table as the primary key. By querying the MIN and MAX values of this ID column, you can quickly determine the range of row numbers to update in parallel, without any additional database queries or scans.

The author concludes that many engineers overlook the power of the ID column and instead try to reinvent the wheel with complex window functions or pagination techniques. By leveraging this simple column, you can split the table into manageable chunks for parallel updates, avoiding the pitfalls of the approaches mentioned above. This "dumb" trick eliminates the need for expensive full scans and provides a more efficient, scalable solution for large-scale database migrations.