Mastering PostgreSQL 18 MERGE: The Ultimate Upsert Guide

PostgreSQL 18 brings significant enhancements to the MERGE statement, making it the most powerful tool for handling insert-or-update scenarios.

What is MERGE?

The MERGE statement allows you to INSERT, UPDATE, or DELETE rows in a single atomic operation based on whether matching rows exist. Think of it as a sophisticated "insert if new, update if exists" operation.

Why MERGE Matters in PostgreSQL 18

Key Enhancements

Performance improvements: Up to 40% faster for large-scale merges
Concurrent operations: Better handling of concurrent transactions
Multiple WHEN clauses: More granular control
MERGE with CTEs: Use Common Table Expressions as source
RETURNING enhancements: Track exactly what changed

Basic MERGE Syntax

MERGE INTO products AS target
USING new_products AS source
ON target.product_id = source.product_id

WHEN MATCHED THEN
    UPDATE SET 
        name = source.name,
        price = source.price

WHEN NOT MATCHED THEN
    INSERT (product_id, name, price)
    VALUES (source.product_id, source.name, source.price);

Advanced Patterns

1. Multiple WHEN MATCHED Clauses

MERGE INTO inventory AS target
USING inventory_updates AS source
ON target.product_id = source.product_id

WHEN MATCHED AND target.is_perishable = true THEN
    UPDATE SET quantity = source.quantity

WHEN MATCHED AND abs(target.quantity - source.quantity) > 10 THEN
    UPDATE SET quantity = source.quantity

WHEN NOT MATCHED THEN
    INSERT VALUES (source.product_id, source.quantity);

2. WHEN NOT MATCHED BY SOURCE

Handle rows in target that don't exist in source—perfect for synchronization.

MERGE INTO user_permissions AS target
USING external_permissions AS source
ON target.user_id = source.user_id

WHEN MATCHED THEN
    UPDATE SET permission = source.permission

WHEN NOT MATCHED BY TARGET THEN
    INSERT VALUES (source.user_id, source.permission)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

3. MERGE with CTEs

WITH daily_metrics AS (
    SELECT 
        user_id,
        COUNT(*) as event_count,
        SUM(amount) as revenue
    FROM user_events
    WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
    GROUP BY user_id
)
MERGE INTO user_summary AS target
USING daily_metrics AS source
ON target.user_id = source.user_id

WHEN MATCHED THEN
    UPDATE SET 
        event_count = source.event_count,
        revenue = source.revenue

WHEN NOT MATCHED THEN
    INSERT VALUES (source.user_id, source.event_count, source.revenue);

4. MERGE with RETURNING

MERGE INTO customers AS target
USING customer_updates AS source
ON target.customer_id = source.customer_id

WHEN MATCHED THEN
    UPDATE SET email = source.email

WHEN NOT MATCHED THEN
    INSERT VALUES (source.customer_id, source.email)

RETURNING 
    merge_action() as action,
    customer_id;

Performance Optimization

1. Index Strategy

CREATE INDEX idx_products_id ON products(product_id);
CREATE INDEX idx_new_products_id ON new_products(product_id);

2. Batch Large Operations

For very large datasets, batch your merges to avoid transaction bloat.

3. Monitor Performance

EXPLAIN (ANALYZE, BUFFERS)
MERGE INTO products AS target
USING new_products AS source
ON target.product_id = source.product_id
WHEN MATCHED THEN UPDATE SET name = source.name;

Real-World Use Cases

1. Data Warehouse ETL

MERGE INTO fact_sales AS target
USING staging_sales AS source
ON target.sale_id = source.sale_id

WHEN MATCHED THEN
    UPDATE SET amount = source.amount

WHEN NOT MATCHED THEN
    INSERT VALUES (source.sale_id, source.amount);

2. Inventory Management

MERGE INTO inventory AS target
USING inventory_updates AS source
ON target.product_id = source.product_id

WHEN MATCHED AND source.updated_at > target.last_sync THEN
    UPDATE SET quantity = source.quantity

WHEN NOT MATCHED THEN
    INSERT VALUES (source.product_id, source.quantity);

Common Pitfalls

1. Deadlocks in Concurrent MERGE

Solution: Use explicit locking order.

MERGE INTO accounts AS target
USING (
    SELECT * FROM account_updates
    ORDER BY account_id
    FOR UPDATE
) AS source
ON target.account_id = source.account_id;

2. Memory Usage

Solution: Increase work_mem or batch the operation.

SET LOCAL work_mem = '256MB';
MERGE INTO large_table ...

Best Practices

Always use explicit column lists
Use transactions appropriately
Add audit columns to track changes
Index join columns properly
Test with EXPLAIN before running on production

Conclusion

PostgreSQL 18's enhanced MERGE statement is a game-changer for data synchronization, ETL processes, and complex upsert scenarios. Master MERGE for cleaner, faster, and more maintainable code.

Key Takeaways:

Use MERGE for complex logic; INSERT ON CONFLICT for simple cases
Proper indexing is critical for performance
Batch large operations to control transaction size
Use RETURNING to track changes