UPSERT Performance Factors:

1. Index on conflict column (CRITICAL):

Without index: Full table scan to check for conflicts (1,000 rows/sec)

With index: Index lookup to check for conflicts (100,000 rows/sec)

100x speedup with proper index.

Example:

CREATE UNIQUE INDEX idx_users_user_id ON users(user_id);

INSERT INTO users (user_id, username) VALUES (1, 'john')

ON CONFLICT (user_id) DO UPDATE SET username = EXCLUDED.username;

Index enables fast conflict detection.

2. Number of columns updated:

Fewer columns = faster update

More columns = slower update (more data to write)

Fast: DO UPDATE SET status = EXCLUDED.status

Slow: DO UPDATE SET col1 = EXCLUDED.col1, col2 = EXCLUDED.col2, ... (20 columns)

3. Triggers:

No triggers: 100,000 rows/sec

With triggers: 10,000 rows/sec (10x slower)

Triggers fire for both INSERT and UPDATE, adding overhead.

4. Table size:

Small table (< 1M rows): 100,000 rows/sec

Large table (> 100M rows): 50,000 rows/sec

Larger tables have more index overhead.

5. Batch size:

Single row UPSERT: 10,000 rows/sec (network overhead)

Bulk UPSERT (1000 rows): 100,000 rows/sec (10x faster)

Use bulk UPSERT for better performance.

6. Conflict rate:

Low conflict (10% updates): 100,000 rows/sec

High conflict (90% updates): 80,000 rows/sec

Updates are slightly slower than inserts.

UPSERT vs Separate INSERT/UPDATE:

UPSERT:

- Single operation

- Atomic (no race conditions)

- 100,000 rows/sec

- Simple code

Separate INSERT/UPDATE:

- Two operations (try INSERT, catch error, UPDATE)

- Race conditions possible