Stripe processes 1B+ payment events daily using UPSERT to sync payment status (insert new, update existing). Shopify syncs 10M+ products from suppliers using INSERT...ON CONFLICT (handles new products and price updates). Netflix uses MERGE to update user viewing history (insert new views, update watch progress). Uber upserts 100M+ ride records hourly (new rides inserted, status updates for existing). Airbnb syncs 5M+ listings using UPSERT (new listings inserted, availability/price updated). MERGE/UPSERT is essential for ETL pipelines, API synchronization, cache refresh, and idempotent operations where you need insert-or-update logic in single atomic operation.

Stripe: Payment Event Processing (1B Events Daily)

Stripe processes 1 billion payment events daily from webhooks. Events can arrive multiple times due to retries (network failures, timeouts). Challenge: Prevent duplicate payment records, handle status updates for existing payments. Traditional approach: Check if payment exists, INSERT or UPDATE. Problem: Race conditions (two webhooks for same payment arrive simultaneously), 10x slower. Solution: Use PostgreSQL INSERT...ON CONFLICT for idempotent event processing.

Stripe uses UPSERT for idempotent payment processing: (1) Webhook delivers payment event with payment_id, (2) UPSERT on payment_id (insert new payment or update status), (3) If event delivered twice, second UPSERT updates to same status (idempotent), (4) No duplicate payments, no lost updates. Architecture: Webhook → Queue → Worker → UPSERT → Database. Performance: 120,000 events/sec. Benefits: Idempotent (safe to retry), atomic (no race conditions), 10x faster than check-then-insert.

-- Stripe payment event processing (simplified)

-- Payments table with unique constraint on payment_id

CREATE TABLE payments (

payment_id VARCHAR(50) PRIMARY KEY,

customer_id VARCHAR(50),

amount DECIMAL(10,2),

currency VARCHAR(3),

status VARCHAR(20),

created_at TIMESTAMP,

updated_at TIMESTAMP DEFAULT NOW()

);

-- Create index for fast conflict detection

CREATE UNIQUE INDEX idx_payments_payment_id ON payments(payment_id);

-- Process payment event (idempotent)

INSERT INTO payments (payment_id, customer_id, amount, currency, status, created_at, updated_at)

VALUES (

'pay_1234567890',

'cus_ABC123',

100.00,

'usd',

'succeeded',

'2024-01-15 10:30:00',

NOW()

)

ON CONFLICT (payment_id)

DO UPDATE SET

status = EXCLUDED.status,

updated_at = NOW()

WHERE payments.status != EXCLUDED.status;

-- Only updates if status changed (avoids unnecessary writes)

-- If event delivered twice:

-- First delivery: Inserts payment with status='succeeded'

-- Second delivery: Updates status to 'succeeded' (no change, idempotent)

-- Result: Single payment record, correct status

-- Bulk event processing from queue (10K events)

INSERT INTO payments (payment_id, customer_id, amount, currency, status, created_at, updated_at)