Hacktoberfest 2026: the issues maintainers tagged for October, open and beginner-friendly. Browse Hacktoberfest issues

INSERT ... ON CONFLICT into an attached table copies every row's key on each statement: O(target) per upsert

Open
  • Is something wrong?
#575 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
cpp, postgresql, python
Domain
databases, performance

Research direction

Start with the Python repro.py entry point and the INSERT ... ON CONFLICT path for an attached PostgreSQL table; enable pg_debug_show_queries to observe the emitted COPY and UPDATE statements. Trace where the target scan is planned, then verify that the upsert no longer reads every target row and retains correct conflict behavior.

Written by the indexing model from the issue text.

Description

What happens

One single-row INSERT ... ON CONFLICT into an attached Postgres table takes time proportional to the rows in the target, not the rows in the statement. DuckDB 1.5.2 and 1.5.5, Postgres 16.15, a table with a primary key, median of 5, beside a plain single-row INSERT into the same table:

Target rows ON CONFLICT upsert Plain insert
1,000 0.023 s 0.004 s
100,000 0.301 s 0.007 s
1,000,000 2.339 s 0.005 s

Since #359 the statement is planned as MERGE INTO: the source is joined against a scan of the target, matched rows are updated by ctid, and the rest are inserted. The target scan carries no filter on the source's keys, so every statement copies the key columns and ctid of the whole table into DuckDB. With SET pg_debug_show_queries = true, one upsert of one row into a 1,000-row table sends, after the catalog queries:

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
COPY (SELECT "k", ctid FROM "public"."t" ) TO STDOUT (FORMAT "binary");
CREATE LOCAL TEMPORARY TABLE "update_data_<uuid>"("v" INTEGER, __page_id_string VARCHAR) ON COMMIT DROP;
COPY "update_data_<uuid>" FROM STDIN (FORMAT TEXT, NULL '')
UPDATE "public"."t" SET "v" = "update_data_<uuid>"."v" FROM "update_data_<uuid>" WHERE "t".ctid=__page_id_string::TID
COMMIT

The first COPY reads the whole of t. On a larger table the scan is split into ctid BETWEEN ranges, which read the same rows.

A streaming job that upserts a few rows a minute into a table that only grows reads more on every statement.

What would help

Either of these:

  • Push the source's key values into the target scan as a filter, WHERE (k) IN (...) or a semi-join, so the read is O(statement).
  • Send INSERT ... ON CONFLICT to Postgres as written when the target is a Postgres table. That also lets Postgres apply column defaults for columns the statement omits (#487) and raise cardinality_violation when a statement carries one key twice, where today one of the two rows is kept.

Versions

  • DuckDB 1.5.2 with postgres extension c89234f, and DuckDB 1.5.5 with 41223e5. Both send the statements above and both show the table above, within noise.
  • PostgreSQL 16.15.

Reproduce

The log alone, from any client, against any table with a primary key:

ATTACH 'postgresql://...' AS pg (TYPE POSTGRES);
CREATE TABLE pg.t (k BIGINT PRIMARY KEY, v INTEGER);
INSERT INTO pg.t SELECT range, 0 FROM range(1000);
SET pg_debug_show_queries = true;
INSERT INTO pg.t (k, v) VALUES (1, 1) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;

The log and the table together, with the Python client:

# PG=postgresql://... python repro.py
import os, time, duckdb

c = duckdb.connect()
c.execute("INSTALL postgres; LOAD postgres;")
c.execute(f"ATTACH '{os.environ['PG']}' AS pg (TYPE POSTGRES)")

def fill(n):
    c.execute("CALL postgres_execute('pg', 'DROP TABLE IF EXISTS t; CREATE TABLE t (k bigint PRIMARY KEY, v int)')")
    c.execute(f"CALL postgres_execute('pg', 'INSERT INTO t SELECT g, 0 FROM generate_series(1, {n}) g')")
    c.execute("CALL pg_clear_cache()")

fill(1000)
c.execute("SET pg_debug_show_queries = true")
c.execute("INSERT INTO pg.t (k, v) VALUES (1, 1) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v")
c.execute("SET pg_debug_show_queries = false")

for n in (1_000, 100_000, 1_000_000):
    fill(n)
    ts = []
    for i in range(5):
        t0 = time.perf_counter()
        c.execute(f"INSERT INTO pg.t (k, v) VALUES ({i + 1}, {i}) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v")
        ts.append(time.perf_counter() - t0)
    t0 = time.perf_counter()
    c.execute(f"INSERT INTO pg.t (k, v) VALUES ({n + 7}, 0)")
    plain = time.perf_counter() - t0
    print(f"rows={n:>9,}  upsert={sorted(ts)[2]:.3f}s  plain insert={plain:.3f}s")
Dominant language
C++
Stars
372
Forks
107
Avg merge
13h 55m
Merged PRs (30d)
13

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from duckdb/duckdb-postgres

All issues in duckdb/duckdb-postgres

Similar issues

More C++ issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.