Real‑World Scenario: Keeping a Product Catalog in Sync

Imagine we receive a nightly feed of product updates from an external supplier. Each record contains a SKU, name, price, and stock level. Our goal is to insert new products and update existing ones without creating duplicates or running separate SELECT‑then‑INSERT/UPDATE logic.

Why a Simple SELECT‑Then‑INSERT/UPDATE Falls Short

The naive approach looks like this:


BEGIN;
SELECT 1 FROM products WHERE sku = $1;
IF FOUND THEN
    UPDATE products SET name = $2, price = $3, stock = $4 WHERE sku = $1;
ELSE
    INSERT INTO products (sku, name, price, stock) VALUES ($1, $2, $3, $4);
END IF;
COMMIT;

At first glance this works, but it suffers from two practical problems:

  1. Race conditions – Two processes could both see that the SKU does not exist and each try to INSERT, causing a unique‑key violation.
  2. Extra round‑trips – Every feed line forces a SELECT, doubling the workload on the database.

In a high‑volume ingestion pipeline these issues translate into failed jobs and unnecessary load.

The Upsert Pattern with INSERT … ON CONFLICT

PostgreSQL (since 9.5) provides a declarative way to handle this situation: INSERT … ON CONFLICT DO UPDATE. The database attempts the insert; if a unique constraint violation occurs, it runs an UPDATE instead—all in a single statement and without the race window.

Production‑Ready Example

Assume the products table has a unique index on sku:


CREATE UNIQUE INDEX ux_products_sku ON products (sku);

Now the upsert becomes a single, tidy statement:


INSERT INTO products (sku, name, price, stock)
VALUES ($1, $2, $3, $4)
ON CONFLICT (sku) DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = EXCLUDED.stock,
    updated_at = now();

Let’s break down what happens:

  • EXCLUDED refers to the row that would have been inserted.
  • If the sku already exists, the UPDATE clause runs, setting the columns to the values from the incoming feed.
  • The updated_at column (if you have one) is refreshed to show when the record was last touched.
  • All of this occurs inside a single transaction, eliminating the race condition.

Handling Multiple Unique Constraints

Sometimes a table has more than one business key. Suppose we also want to enforce uniqueness on a combination of brand and model. You can target a specific constraint or index:


INSERT INTO products (sku, name, price, stock, brand, model)
VALUES ($1, $2, $3, $4, $5, $6)
ON CONFLICT ON CONSTRAINT ux_products_brand_model DO UPDATE SET
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    stock = EXCLUDED.stock;

If you omit the constraint name, PostgreSQL will use the first applicable unique index, which can lead to surprising behavior when multiple indexes exist.

Performance Considerations

The upsert avoids the extra SELECT, reducing CPU and I/O. Benchmarks on a modest‑size table (≈1 million rows) show a 30‑40 % drop in average latency per row when switching from the SELECT‑then‑INSERT/UPDATE pattern to ON CONFLICT. The trade‑off is a slightly more complex execution plan, but the planner handles it efficiently.

One caveat: if your workload is heavily skewed toward updates (rare inserts), the planner may choose a different path. In such cases you can still benefit from the atomicity, but you might want to test both approaches on a representative data set.

When Not to Use It

The ON CONFLICT clause is specific to PostgreSQL. If you need to support other RDBMS (e.g., MySQL, SQL Server) you would look for their equivalents: MySQL’s INSERT … ON DUPLICATE KEY UPDATE or SQL Server’s MERGE. For truly cross‑platform code you might abstract the logic into a stored procedure or application‑level upsert.

Wrap‑Up

Adopting INSERT … ON CONFLICT DO UPDATE turned our nightly product feed from a fragile, two‑step process into a reliable, single‑statement operation. It eliminated duplicate key errors, cut latency, and simplified the application code. Whenever you face an "insert if missing, otherwise update" scenario, reach for this pattern first—it’s a small change that yields big gains in correctness and performance.