A story about adding a new column to a table with 600 million records
The Challenge
Picture this. You have a task to implement data loading from a large PostgreSQL database table to Snowflake through a data pipeline. Simple enough, right? Just do it incrementally, using the appropriate column as a bookmark to track how much data has already been processed.
The problem is that the table has 600 million records in production and lacks a column suitable for the bookmark.
This was exactly the situation we faced recently. We needed to:
- Add an updated_at column to track changes.
- Backfill existing records with updated_at = created_at
- Add an index on the updated_at column for efficient querying (essential for bookmark-based incremental loads)
- Do all of this without affecting production traffic.
Why Traditional Approaches Don’t Work
Even though created_at was there, it didn’t fit. The updated_at timestamp was the most suitable choice for the bookmark because a single record can be updated over time, and we need to capture those updates.
The naive approach would be to create a new migration with an ALTER TABLE statement, followed by an UPDATE to populate the column, and then CREATE INDEX:
ALTER TABLE table
ADD COLUMN updated_at TIMESTAMP DEFAULT now();
UPDATE table
SET updated_at = created_at;
CREATE INDEX idx_table_updated_at
ON table(updated_at);
The whole migration would be wrapped inside a transaction by default.
But what happens next?
Adding a column with a default value updates each existing row. This can take hours and locks all rows until it’s done.
An UPDATE without batching creates a large transaction that locks all rows for an extended period.
Even without the transaction, the lock stays until the statement finishes. This can take a while. So, moving the statement to a separate migration won’t help much.
A CREATE INDEX operation gets an exclusive lock on the whole table. This stops all writes and reads for hours.
Any of these operations could bring production to its knees.
The table size is the real issue. With less data, all these operations would finish quickly and not disrupt the system. Locking becomes a problem when the operation takes too long.
The Zero-Downtime Strategy
Here’s how we addressed the issue using PostgreSQL’s features and strategic batching.
Step 1: Add the Column without a default value
We began by adding the column without immediately populating it. In PostgreSQL, adding a nullable column without a default value is almost instantaneous:
ALTER TABLE table ADD COLUMN updated_at TIMESTAMP;
Step 2: Batch Update Existing Records
Rather than updating all 600 million records in a single transaction, we processed them in smaller chunks of 10,000 records.
This method:
- Keeps transactions small and efficient
- Allows other operations to continue between batches
- Prevents lock escalation
DO '
DECLARE
chunk_size INT := 10000;
start_id INT;
max_id INT;
BEGIN
SELECT MIN(id), MAX(id) INTO start_id, max_id FROM table;
WHILE start_id <= max_id LOOP
EXECUTE format(
''UPDATE table SET updated_at = created_at
WHERE id >= %s AND id < %s'',
start_id, start_id + chunk_size
);
start_id := start_id + chunk_size;
END LOOP;
END
';
Step 3: Create Index Concurrently
PostgreSQL’s CREATE INDEX CONCURRENTLY is a valuable option in these scenarios. While index creation is slower than usual, it offers greater safety for production environments.
Unlike regular index creation, it:
- Does not block reads and writes
- Performs multiple table scans to build the index
- Takes longer but ensures full availability
CREATE INDEX CONCURRENTLY idx_table_updated_at ON table (updated_at);
Important note: This command must be executed outside of a transaction block. Running it within a transaction would negate its benefits and cause blocking.
The complete migration script now looks like this:
ALTER TABLE table ADD COLUMN updated_at TIMESTAMP;
DO '
DECLARE
chunk_size INT := 10000;
start_id INT;
max_id INT;
BEGIN
SELECT MIN(id), MAX(id) INTO start_id, max_id FROM table;
WHILE start_id <= max_id LOOP
EXECUTE format(
''UPDATE table SET updated_at = created_at
WHERE id >= %s AND id < %s'',
start_id, start_id + chunk_size
);
start_id := start_id + chunk_size;
END LOOP;
END
';
CREATE INDEX CONCURRENTLY idx_table_updated_at ON table (updated_at);
Implementation with Liquibase
As our backend is built on Spring Boot, we utilize Liquibase for database migrations.
Below is a complete migration file that reflects what we implemented in the production environment:
<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet id="add-table-updated_at" author="amahovac" context="post-release">
<addColumn tableName="table">
<column name="updated_at" type="TIMESTAMP WITH TIME ZONE"/>
</addColumn>
</changeSet>
<changeSet id="update-table-updated_at" author="amahovac"
runInTransaction="false" context="post-release">
<!-- Batch update logic here -->
</changeSet>
<changeSet id="create-concurrent-index-table-updated_at"
runInTransaction="false" author="amahovac" context="post-release">
<!-- Concurrent index creation here -->
</changeSet>
</databaseChangeLog>
Key Details
- Separate migrations
- We organized operations into distinct migrations (changesets) to improve control over each.
- Running migrations outside the transaction
- This is crucial for concurrent operations. If not managed, Liquibase would encompass everything in a transaction, leading to the locks we aim to avoid. Adding runInTransaction=”false” prevents this issue.
- Skipping migrations during deployment
- We used context=”post-release” and adjusted the Spring Boot configuration files to set spring.liquibase.contexts=!post-release before deployment. This way, migrations won’t run during deployment and potentially time out our CodeDeploy process.
- Running it manually
- After deployment, we executed these migrations manually during a low-traffic period to monitor performance impacts and minimize risk.
The Result
The entire operation was completed successfully with zero downtime. The production application continued serving requests throughout the 5+ hour process. Our data pipeline now efficiently syncs the table incrementally to Snowflake.
Lessons Learned
Test with production-sized data: Our initial approach performed well in the development environment with 1 million rows, but required significant adjustments for the production database. Always consider data volume and test with realistic datasets.
Monitor during execution: We kept an eye on CPU, I/O, and lock metrics throughout the process.
Document everything: Future team members need to understand the nuances of these migrations. Our inline comments saved confusion later.
Always include created_at and updated_at columns when creating a table: You never know when they might be needed. If we had included them from the start, we could have avoided all these complications.
Avoid unnecessary locking: Consider concurrent solutions and batch large updates to minimize lock escalation.
Run DDL operations with caution: If possible, execute long-running migrations outside of transactions and separate them from deployment.
Working with large production database tables requires patience, careful planning, and an awareness of the impact of each operation. The extra effort to ensure zero downtime is always worthwhile when users don’t even notice you just modified 600 million records.
