UPSERT and MERGE

1. Introduction

UPSERT (UPDATE or INSERT) is a pattern that inserts a new row if it doesn't exist, or updates the existing row if a conflict occurs on a unique constraint. PostgreSQL implements this with INSERT ... ON CONFLICT. The SQL standard defines MERGE for more complex conditional insert/update/delete logic, which PostgreSQL 15+ supports natively.

2. Why It Matters

• Idempotent writes: UPSERT makes operations safe to retry — critical for message queues, webhook handlers, and distributed systems where duplicate delivery is common.
• Counter aggregation: Increment counters (page views, vote counts) atomically without race conditions using ON CONFLICT DO UPDATE SET count = count + 1.
• ETL pipelines: Data warehouse loads use MERGE to handle both new records and corrections to existing records in a single pass.
• Configuration management: Store application settings that should be created on first use and updated on subsequent uses.

3. Real-World Analogy

4. How It Works

PostgreSQL ON CONFLICT (UPSERT):

-- Insert or update on unique constraint conflict
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON CONFLICT (user_id)
DO UPDATE SET
    login_count = user_stats.login_count + 1,
    last_login = EXCLUDED.last_login;
-- Insert or do nothing (skip duplicates)
INSERT INTO emails (address, user_id)
VALUES ('alice@example.com', 42)
ON CONFLICT (address) DO NOTHING;
-- Using a partial unique index as conflict target
INSERT INTO featured_products (product_id, category, is_featured)
VALUES (101, 'Electronics', true)
ON CONFLICT (product_id) WHERE category = 'Electronics'
DO UPDATE SET is_featured = EXCLUDED.is_featured;

PostgreSQL 15+ MERGE statement:

-- MERGE: Conditional INSERT, UPDATE, and DELETE in one statement
MERGE INTO inventory AS target
USING new_shipment AS source
ON target.product_id = source.product_id
WHEN MATCHED AND target.quantity + source.quantity > 1000 THEN
    UPDATE SET quantity = 1000, overflow_note = 'Capped at max'
WHEN MATCHED THEN
    UPDATE SET quantity = target.quantity + source.quantity,
               last_restocked = NOW()
WHEN NOT MATCHED THEN
    INSERT (product_id, quantity, last_restocked)
    VALUES (source.product_id, source.quantity, NOW());

5. Internal Architecture

UPSERT Execution Flow:
┌─────────────────────────────────────────────────────────┐
│ 1. Attempt INSERT into table                             │
│ 2. Check unique constraint / unique index                │
│    ├─ No conflict → INSERT succeeds                      │
│    └─ Conflict detected →                                │
│       3. Lock the conflicting row                        │
│       4. Evaluate DO UPDATE SET expressions              │
│          - EXCLUDED.* references the proposed new row    │
│          - table.* references the existing row           │
│       5. Apply UPDATE to existing row                    │
│       6. Re-check constraints on updated row             │
│ 7. Write WAL record → Return                             │
└─────────────────────────────────────────────────────────┘
Key: The entire UPSERT is atomic — no race condition
between the check and the write.

6. Visual Explanation

7. Practical Example

Atomic counter with UPSERT:

-- Page view counter (handles concurrent requests safely)
INSERT INTO page_views (page_url, view_date, view_count)
VALUES ('/products/123', CURRENT_DATE, 1)
ON CONFLICT (page_url, view_date)
DO UPDATE SET view_count = page_views.view_count + 1;
-- Shopping cart: add item or increase quantity
INSERT INTO cart_items (user_id, product_id, quantity, added_at)
VALUES (42, 101, 2, NOW())
ON CONFLICT (user_id, product_id)
DO UPDATE SET
    quantity = cart_items.quantity + EXCLUDED.quantity,
    added_at = NOW();

Data synchronization with MERGE:

-- Sync staging data to production, handling inserts, updates,
-- and soft-deletes for removed records
MERGE INTO customers AS prod
USING staging_customers AS stg
ON prod.email = stg.email
WHEN MATCHED AND stg.is_active = false THEN
    UPDATE SET deleted_at = NOW()
WHEN MATCHED THEN
    UPDATE SET
        name = stg.name,
        phone = stg.phone,
        updated_at = NOW()
WHEN NOT MATCHED THEN
    INSERT (email, name, phone, created_at)
    VALUES (stg.email, stg.name, stg.phone, NOW());

8. Common Mistakes

9. Quick Quiz

10. Scenario-Based Challenge

11. Debugging Exercise

INSERT INTO settings (user_id, key, value)
VALUES (42, 'theme', 'dark')
ON CONFLICT (user_id)
DO UPDATE SET value = 'dark';

12. Interview Questions

13. Production Considerations

• Index overhead: Every unique constraint requires a unique B-tree index. High-write tables with many UPSERTs should minimize redundant unique indexes.
• Lock contention: UPSERT acquires a row-level lock on the conflicting row. High-concurrency UPSERTs on the same key create a serialization bottleneck — consider application-level deduplication first.
• WAL generation: Each UPSERT that results in an UPDATE generates WAL records just like a regular UPDATE. Heavy UPSERT workloads should tune WAL settings accordingly.
• Partial unique indexes: Use CREATE UNIQUE INDEX ... WHERE condition for conflict targets that only apply to a subset of rows (e.g., only active records).
• Monitoring: Track UPSERT hit rates (insert vs update ratio) to understand data flow patterns. A 90% update rate may indicate an opportunity to batch or cache at the application layer.