Migrating 500 Million Shopify Webhook Records Without Dropping a Single Insert
Standard ALTER TABLE statements lock PostgreSQL tables and cause massive webhook ingestion failures during high-traffic merchant events. Using the expand-and-contract pattern ensures schema changes never interrupt live multi-tenant Shopify apps.
Running a simple ALTER TABLE in a local development environment takes milliseconds. Running that exact same command on a production PostgreSQL database during a merchant's Black Friday flash sale can trigger a catastrophic cascading failure. If you maintain a Shopify app processing thousands of webhooks per minute, standard database migration strategies are a liability.
Let's look at a seemingly innocent pull request. Your team needs to track exactly when an orders/create webhook was fully processed by your background workers, rather than just when it was ingested. A developer adds this migration:
ALTER TABLE shopify_webhooks
ADD COLUMN processed_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
In PostgreSQL, adding a column with a constant default value (like DEFAULT '2026-01-01') is fast in modern versions. However, if that default involves a volatile function like NOW(), or if you are changing a column type, PostgreSQL must rewrite the entire table. To do this, it acquires an AccessExclusiveLock.
Shopify requires your app to respond to webhooks with a 200 OK within 5 seconds. If PostgreSQL holds an AccessExclusiveLock for 45 seconds while rewriting a 50GB table, every incoming INSERT from your webhook HTTP handlers queues up. Your Node.js or Rust connection pool exhausts instantly. HTTP requests from Shopify time out. Shopify's delivery system marks them as failed and schedules retries, creating a massive thundering herd that slams your infrastructure the moment the lock releases.
To survive continuous, high-volume webhook ingestion, you have to abandon synchronous schema changes.
The Expand and Contract Pattern
Zero-downtime database migrations rely on breaking down a single blocking operation into multiple non-blocking phases. This is known as the Expand and Contract pattern. You expand the schema to support both the old and new application states, migrate the data incrementally, and then contract the schema by removing the old state.
Phase 1: Safe Expansion
First, we add the new column but make it completely passive. We do not apply defaults, and we do not apply NOT NULL constraints.
-- Safe: Acquires AccessExclusiveLock for less than a millisecond
ALTER TABLE shopify_webhooks
ADD COLUMN processed_at TIMESTAMP WITH TIME ZONE;
Because there is no default value to populate and no constraints to check, PostgreSQL simply updates the system catalogs. The lock is held for a fraction of a second, and webhook inserts continue uninterrupted.
Next, you update your application code to start writing to this new column for all new incoming webhooks and deploy the application. Now, your table has a mix of populated rows (new webhooks) and NULL rows (historical webhooks).
Phase 2: Concurrent Backfilling
We need to backfill the historical rows. Running a single UPDATE shopify_webhooks SET processed_at = created_at WHERE processed_at IS NULL; is infrastructure suicide. It creates massive dead tuples (bloating the table) and takes out row locks that might conflict with active background workers trying to update those same records.
Instead, we backfill in small, batched transactions using a background worker.
// Background worker executing batched updates
async function backfillWebhooks(batchSize = 1000) {
let rowsAffected = batchSize;
let lastId = 0;
while (rowsAffected === batchSize) {
const result = await db.query(`
UPDATE shopify_webhooks
SET processed_at = created_at
WHERE id IN (
SELECT id FROM shopify_webhooks
WHERE id > $1 AND processed_at IS NULL
ORDER BY id ASC
LIMIT $2
FOR UPDATE SKIP LOCKED
)
RETURNING id;
`, [lastId, batchSize]);
rowsAffected = result.rowCount;
if (rowsAffected > 0) {
lastId = result.rows[result.rows.length - 1].id;
}
// Yield to let autovacuum catch up and prevent replication lag
await new Promise(resolve => setTimeout(resolve, 100));
}
}
Notice the FOR UPDATE SKIP LOCKED clause. This tells PostgreSQL to lock the rows it intends to update, but if any of those rows are currently locked by another transaction (like an active webhook processor), it should simply skip them rather than waiting. This prevents our backfill worker from deadlocking against live application traffic.
Phase 3: Enforcing Constraints Safely
Once the backfill script finishes, we want to enforce data integrity by ensuring processed_at is always present. A standard ALTER TABLE ... ALTER COLUMN processed_at SET NOT NULL requires an AccessExclusiveLock and scans the whole table to verify no NULLs exist, blocking writes again.
Instead, we enforce this using a CHECK constraint applied in two distinct steps:
-- Step 1: Add the constraint but don't validate existing rows.
-- This requires a short lock and finishes instantly.
ALTER TABLE shopify_webhooks
ADD CONSTRAINT processed_at_not_null
CHECK (processed_at IS NOT NULL) NOT VALID;
At this point, any new inserts or updates will be checked against the constraint, but PostgreSQL hasn't verified the historical data.
-- Step 2: Validate the constraint concurrently.
-- This scans the table without blocking reads or writes.
ALTER TABLE shopify_webhooks
VALIDATE CONSTRAINT processed_at_not_null;
The VALIDATE CONSTRAINT command acquires a ShareUpdateExclusiveLock. This lock allows concurrent SELECT, INSERT, UPDATE, and DELETE operations to continue processing Shopify webhooks normally while PostgreSQL verifies the historical rows in the background.
Index Creation Under Load
Another frequent requirement is adding an index to power a new merchant dashboard—for instance, querying webhooks by shop domain and processing status. Standard CREATE INDEX locks out concurrent writes. If you run it on a large table, your app is down until the index builds.
Always use concurrent index creation on live tables:
CREATE INDEX CONCURRENTLY idx_webhooks_shop_processed_at
ON shopify_webhooks (shop_domain, processed_at DESC);
There is a catch: CREATE INDEX CONCURRENTLY cannot run inside a transaction block. If your migration tool (like Prisma or TypeORM) wraps all migrations in a BEGIN ... COMMIT block by default, the migration will fail. You must configure your migration runner to execute concurrent index builds outside of a transaction.
Migration Strategy Trade-offs
Here is how the standard approach compares to the zero-downtime expand-and-contract method when dealing with multi-tenant SaaS workloads:
| Metric | Standard ALTER TABLE |
Expand-and-Contract |
|---|---|---|
| Lock Type | AccessExclusiveLock (Blocks everything) |
ShareUpdateExclusiveLock (Allows writes) |
| App Impact | Connection pool exhaustion, 5xx errors | Zero impact on ingestion |
| Shopify Impact | Webhook timeouts, thundering herd retries | Normal 200 OK responses |
| Rollback | Difficult if failed mid-transaction | Trivial (just drop the passive column/index) |
| Implementation | 1 line of SQL, 1 deployment | Multi-phase SQL, 2+ deployments |
Handling Schema Caching in ORMs
If your stack uses an ORM that caches schema state or relies on prepared statements, you have to be incredibly careful during the contract phase (e.g., dropping a deprecated column).
If you run ALTER TABLE shopify_webhooks DROP COLUMN old_status; while old Kubernetes pods or Vercel functions are still executing, their prepared statements will suddenly crash with a column does not exist error.
To safely drop a column:
- Deploy application code that completely stops referencing the column in any
SELECT,INSERT, orUPDATEstatements. - Wait for all old instances of the application to drain and terminate.
- Run the database migration to actually drop the column.
Designing for Continuous Ingestion
When you build Shopify apps that scale to tens of thousands of merchants, the database ceases to be a static repository you can freeze for maintenance. It becomes a live organism that must be operated on while it is sprinting.
By completely eliminating AccessExclusiveLock operations from your deployment pipelines, you protect your infrastructure from self-inflicted outages. Shopify will inevitably send you massive spikes of webhooks during flash sales or bulk inventory updates. Making sure your database migrations aren't the bottleneck that drops them is a foundational requirement for high-availability e-commerce engineering.
CheckoutShield: Protect checkout margins, eliminate cart friction, and boost AOV with native Shopify checkout protection.
Engineered for high-volume Shopify merchants to safeguard checkouts, build instant buyer confidence, and capture incremental high-margin revenue without slowing down storefront page loads.
Related Engineering Insights
View All (10)Beating Shopify Function CPU Limits with Optimized Rust WebAssembly
Shopify restricts custom checkout extensions to strict execution time and memory limits. Replacing JavaScript runtimes with natively compiled Rust WebAssembly prevents timeouts and enables complex B2B pricing algorithms.
Off-Main-Thread Execution for Heavy E-Commerce Analytics Scripts
Marketing pixels and analytics trackers routinely monopolize the browser main thread and ruin Interaction to Next Paint metrics. Pushing these scripts to Web Workers using proxy architectures keeps the UI responsive during checkout.