PostgreSQL Performance & Query Optimization · Lekcja

Upserty na dużą skalę z ON CONFLICT

Implementuj wydajną logikę scalania dla dużych partii, unikając rywalizacji o blokady i fragmentacji.

Lekcja 4 z 413 kroki

Upserty na dużą skalę z ON CONFLICT to bezpłatna lekcja PostgreSQL Performance & Query Optimization na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej PostgreSQL Performance & Query Optimization, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

Części tej lekcji nie zostały jeszcze przetłumaczone i są wyświetlane po angielsku.

Why Upserts Need Care at Scale

An upsert inserts a row, but if it would collide with an existing key, it updates the existing row instead. PostgreSQL spells this INSERT ... ON CONFLICT.

For small workloads it is trivial. For ETL-sized batches (tens of thousands to millions of rows) the naive approach causes three problems:

Lock contention — concurrent writers fighting over the same rows or index pages.
Table bloat — every UPDATE writes a new row version (dead tuple) that VACUUM must later reclaim.
WAL and round-trip overhead — row-by-row upserts multiply network and transaction cost.

This lesson builds a merge that is both correct and throughput-friendly.

The ON CONFLICT Shape

ON CONFLICT requires a conflict target: the column(s) or constraint that define a duplicate. PostgreSQL needs a unique or exclusion constraint on that target to arbitrate.

The two actions are DO NOTHING (skip the colliding row) and DO UPDATE (merge new values in).

Inside DO UPDATE, the incoming row is exposed through the special EXCLUDED pseudo-table.

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

One Statement, Many Rows

The single most important throughput rule: batch your rows into one statement. A multi-row VALUES list (or a feeding SELECT) is parsed, planned, and committed once instead of N times.

This collapses N network round-trips and N transaction commits into one, often a 10-100x speedup over row-by-row upserts.

INSERT INTO products (sku, name, price)
VALUES
  ('A-100', 'Widget', 9.99),
  ('A-101', 'Gadget', 14.50),
  ('A-102', 'Gizmo', 7.25)
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Staging Table + INSERT...SELECT

For real ETL, load raw data into an unlogged staging table first (often via COPY, the fastest bulk path), then merge from staging into the target with one INSERT ... SELECT ... ON CONFLICT.

Benefits:

COPY avoids per-row INSERT overhead.
An UNLOGGED staging table skips WAL for the load phase.
You can dedupe and transform in the SELECT before merging.

CREATE UNLOGGED TABLE products_stage (LIKE products);

-- bulk load: COPY products_stage FROM '/data/products.csv' CSV;

INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM products_stage
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Deduplicate the Batch First

A subtle but fatal error: a single INSERT statement cannot update the same target row twice. If your batch contains two rows with the same conflict key, PostgreSQL raises:

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

Fix it by collapsing duplicates in the source before merging. DISTINCT ON keeps one row per key — typically the newest.

INSERT INTO products (sku, name, price)
SELECT DISTINCT ON (sku) sku, name, price
FROM products_stage
ORDER BY sku, updated_at DESC
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Skip No-Op Updates to Cut Bloat

Every DO UPDATE writes a new row version, even when the new values are identical to the old ones. Those dead tuples bloat the table and create extra work for VACUUM.

Add a WHERE clause to the DO UPDATE so it fires only when something actually changed. Use IS DISTINCT FROM so NULLs compare correctly.

INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM products_stage
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price
WHERE products.name  IS DISTINCT FROM EXCLUDED.name
   OR products.price IS DISTINCT FROM EXCLUDED.price;

Order Batches to Tame Lock Contention

When several ETL workers run concurrently, deadlocks appear if they touch the same keys in different orders. Worker 1 locks key X then Y; worker 2 locks Y then X — both block forever until PostgreSQL kills one.

Defenses:

Sort each batch by the conflict key so all workers acquire locks in the same order.
Partition work by key range so no two workers share keys.
Keep transactions short — long-held row locks magnify contention.

INSERT INTO products (sku, name, price)
SELECT sku, name, price
FROM products_stage
ORDER BY sku            -- consistent lock acquisition order
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Chunk Giant Merges

One enormous transaction merging millions of rows holds locks for a long time, balloons WAL, and blocks autovacuum from cleaning up. Break the work into chunks (for example 10k-50k rows) and commit between them.

Smaller transactions release locks sooner, let autovacuum keep pace, and make retries cheap after a failure. The trade-off is slightly more commit overhead — tune the chunk size against your hardware.

-- Merge one bounded slice; loop over key ranges from the app side.
INSERT INTO products (sku, name, price)
SELECT sku, name, price
FROM products_stage
WHERE sku >= 'A-0000' AND sku < 'A-5000'
ON CONFLICT (sku) DO UPDATE
  SET name  = EXCLUDED.name,
      price = EXCLUDED.price;

Choosing the Right Conflict Target

The conflict target must match an actual unique/primary-key or exclusion constraint. You can target by column list ON CONFLICT (sku) or by constraint name ON CONFLICT ON CONSTRAINT products_sku_key.

For partial unique indexes, repeat the index predicate so PostgreSQL can pick the right one:

-- Unique only among active rows
CREATE UNIQUE INDEX products_active_sku
  ON products (sku) WHERE is_active;

INSERT INTO products (sku, name, is_active)
VALUES ('A-100', 'Widget', true)
ON CONFLICT (sku) WHERE is_active DO UPDATE
  SET name = EXCLUDED.name;

ON CONFLICT vs MERGE

PostgreSQL 15+ adds the SQL-standard MERGE, which can INSERT, UPDATE, and DELETE in one pass. For high-throughput upserts, INSERT ... ON CONFLICT is usually still preferred:

ON CONFLICT is atomic against concurrent inserts — it handles a race where another transaction inserts the same key, retrying internally.
Classic MERGE can raise a unique-violation under heavy concurrency because it does not have that built-in conflict arbitration.

Use MERGE when you need DELETE branches or complex conditional logic; use ON CONFLICT for plain, concurrency-safe upserts.

Maintenance: VACUUM and Indexes

Even an optimized merge produces dead tuples on the updated rows. Keep performance steady with maintenance discipline:

Ensure autovacuum keeps up; for hot ETL tables lower autovacuum_vacuum_scale_factor so it triggers more often.
After a massive one-off backfill, run VACUUM (ANALYZE) to reclaim space and refresh planner statistics.
Every extra index on the target slows the merge (each insert/update maintains all of them) — keep only the indexes you truly need.

VACUUM (ANALYZE) products;

Quick Check

Test your understanding of safe, high-throughput merges.

Odpowiedz w interaktywnej lekcji

Recap

Efficient upserts at scale come from a few combined habits:

Batch rows into one statement; for ETL, stage with COPY then INSERT ... SELECT ... ON CONFLICT.
Deduplicate the batch (DISTINCT ON) so no key appears twice.
Skip no-op updates with a WHERE ... IS DISTINCT FROM clause to curb dead tuples and bloat.
Order by the conflict key and partition work to avoid deadlocks; chunk huge merges into short transactions.
Prefer ON CONFLICT for concurrency-safe upserts; keep autovacuum healthy and indexes lean.

Together these turn a fragile row-by-row merge into a fast, low-contention ETL load.

Bezpłatny start

Ucz się SQL dzięki korepetycjom AI — za darmo

Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.

Kursy: 22
Lekcje: 88

Zacznij naukę bezpłatnie Wyświetl pełny kurs

Często zadawane pytania

Czy lekcja „Upserty na dużą skalę z ON CONFLICT” jest bezpłatna?

Tak — pełny tekst „Upserty na dużą skalę z ON CONFLICT” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu PostgreSQL Performance & Query Optimization, przejdź na CoddyKit PRO. Kurs PostgreSQL Performance & Query Optimization zawiera 4 lekcji w sumie.

Co nauczysz się w „Upserty na dużą skalę z ON CONFLICT”?

Implementuj wydajną logikę scalania dla dużych partii, unikając rywalizacji o blokady i fragmentacji. Ćwiczysz PostgreSQL Performance & Query Optimization z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć PostgreSQL Performance & Query Optimization?

Nie wymagamy żadnego doświadczenia. PostgreSQL Performance & Query Optimization w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.

Ile czasu zajmuje lekcja „Upserty na dużą skalę z ON CONFLICT”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji PostgreSQL Performance & Query Optimization?

Tak. Każda lekcja PostgreSQL Performance & Query Optimization zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

Wydajność COPY a wielowierszowego INSERT
Odroczone indeksy i ograniczenia podczas ładowania
Dostrajanie WAL i punktów kontrolnych podczas importu
Upserty na dużą skalę z ON CONFLICT

← Powrót do PostgreSQL Performance & Query Optimization