PostgreSQL UPSERT: Product Catalog Sync

advanced

E-commerce platform syncs 1M products from suppliers every hour. Products can be new (insert) or existing (update price/stock). Traditional approach: Try INSERT, catch error, UPDATE. Problem: Race conditions, 10x slower, complex error handling. Solution: Use INSERT...ON CONFLICT for atomic UPSERT.

-- Scenario: Sync products from supplier feed

-- Products table with unique constraint on product_id

CREATE TABLE products (

product_id VARCHAR(50) PRIMARY KEY,

name VARCHAR(200),

price DECIMAL(10,2),

stock INT,

supplier_id INT,

updated_at TIMESTAMP DEFAULT NOW()

);

-- Create index for fast conflict detection

CREATE UNIQUE INDEX idx_products_product_id ON products(product_id);

-- Single product UPSERT

INSERT INTO products (product_id, name, price, stock, supplier_id, updated_at)

VALUES ('PROD-12345', 'Wireless Mouse', 29.99, 100, 42, NOW())

ON CONFLICT (product_id)

DO UPDATE SET

name = EXCLUDED.name,

price = EXCLUDED.price,

stock = EXCLUDED.stock,

supplier_id = EXCLUDED.supplier_id,

updated_at = NOW();

-- If product exists: updates price/stock

-- If product new: inserts row

-- Atomic operation, no race conditions

-- Bulk UPSERT from staging table (1M products)

INSERT INTO products (product_id, name, price, stock, supplier_id, updated_at)

SELECT product_id, name, price, stock, supplier_id, NOW()

FROM staging_products

ON CONFLICT (product_id)

DO UPDATE SET

name = EXCLUDED.name,

price = EXCLUDED.price,

stock = EXCLUDED.stock,

supplier_id = EXCLUDED.supplier_id,

updated_at = NOW();

-- Processes 1M products in 10 seconds (100,000 rows/sec)