Upsert Overhead and Gotchas

Is there overhead when doing large batches of upserts? Are there performance gotchas to care about? How to do it safely?

Yes. Large batches of INSERT ... ON CONFLICT DO UPDATE can be significantly more expensive than plain inserts, especially when many rows already exist.

Where the overhead comes from

For each candidate row, PostgreSQL generally must:

1. Insert or attempt an insert.
2. Check the unique index / conflict target.
3. If a conflict exists, lock the conflicting row.
4. Run the update path if DO UPDATE applies.
5. Maintain affected indexes.
6. Write WAL for durability and replication.
7. Potentially create dead tuples, which later need vacuuming.

An upsert that becomes an update is not “free,” even if the values are unchanged.

INSERT INTO assets (external_id, title, updated_at)
VALUES (...)
ON CONFLICT (external_id) DO UPDATE
SET title = EXCLUDED.title,
    updated_at = EXCLUDED.updated_at;

If title and updated_at already have those exact values, this can still produce an update, WAL, index work, triggers, and table bloat.

Major performance gotchas

1. Updating unchanged rows

This is one of the biggest migration pitfalls. Avoid updates unless data actually changed.

INSERT INTO assets (external_id, title, description)
VALUES (...)
ON CONFLICT (external_id) DO UPDATE
SET
    title = EXCLUDED.title,
    description = EXCLUDED.description
WHERE (assets.title, assets.description)
      IS DISTINCT FROM
      (EXCLUDED.title, EXCLUDED.description);

IS DISTINCT FROM is null-safe, unlike !=.

This prevents unnecessary updates, reduces WAL volume and bloat, and avoids firing update-related side effects for identical records.

2. Missing or incorrect conflict indexes

The conflict target must correspond to a unique constraint or unique index:

CREATE UNIQUE INDEX CONCURRENTLY assets_external_id_uniq
ON assets (external_id);

Then:

ON CONFLICT (external_id) ...

Without a proper unique index, PostgreSQL cannot efficiently identify conflicts—and ON CONFLICT will not work at all without an appropriate uniqueness constraint/index.

For multi-tenant systems, the natural key may be composite:

CREATE UNIQUE INDEX CONCURRENTLY assets_org_external_id_uniq
ON assets (organization_id, external_id);

ON CONFLICT (organization_id, external_id) ...

Be precise about the real identity key. A bad key can silently merge unrelated customer records, which is a correctness failure rather than merely a performance problem.

3. Oversized transactions

One huge transaction can cause:

• long lock duration
• large WAL generation
• replication lag
• rollback pain if it fails near the end
• transaction ID / vacuum pressure
• poor operational recoverability

Instead, process bounded chunks—often somewhere around hundreds to low-thousands of rows per transaction, then tune from measurements.

There is no universal ideal batch size. It depends on row width, indexes, network latency, triggers, database capacity, and conflict rate. Start conservatively, observe, and tune.

A migration job should checkpoint after each committed batch:

batch 1: source IDs 1–1,000 committed
batch 2: source IDs 1,001–2,000 committed
...

That makes retries safe and avoids having to restart a multi-hour import.

4. Duplicate keys within the same input batch

Postgres will reject an upsert batch if the same conflict key appears more than once and would be updated twice:

ON CONFLICT DO UPDATE command cannot affect row a second time

For example, two source rows with the same (organization_id, external_id) in a single INSERT can fail the whole statement.

Deduplicate before loading, with a documented rule such as “latest source timestamp wins.” For staging-table workflows:

SELECT DISTINCT ON (organization_id, external_id)
    organization_id,
    external_id,
    title,
    source_updated_at
FROM staging_assets
ORDER BY organization_id, external_id, source_updated_at DESC;

Do not leave this behavior implicit; it is a data-governance decision.

5. Deadlocks from inconsistent ordering

Concurrent workers upserting overlapping key sets can deadlock. For example:

• Worker A locks key 1, then wants key 2
• Worker B locks key 2, then wants key 1

Mitigate this by:

• partitioning work so workers do not overlap, such as by tenant or ID range
• processing records in a stable order by conflict key
• keeping transactions short
• retrying deadlock / serialization failures with bounded exponential backoff

For a migration, tenant-level partitioning is often especially practical: one organization’s records are handled by one worker at a time.

6. Index and trigger costs

Every insert or update may update several indexes and invoke triggers. Review the destination table before bulk work:

• How many indexes exist?
• Are all necessary during initial load?
• Are there expensive triggers, audit rows, search indexing, notifications, or denormalization tasks?
• Are foreign keys causing lookup pressure?

Do not casually disable constraints or triggers in a customer migration. If doing so is justified, use a controlled workflow:

1. Load into staging.
2. Validate referential integrity and data quality.
3. Merge into production tables.
4. Re-enable / validate constraints.
5. Reconcile counts and checksums.

For very large initial loads, loading into a staging table and merging is often safer and faster than directly upserting raw source data.

7. JSONB updates can be costly

Replacing a large JSONB document on every conflict creates a new row version and can generate substantial TOAST/WAL activity.

Avoid blindly doing:

SET metadata = EXCLUDED.metadata

if it has not changed. Also, avoid frequent piecemeal updates to large documents during import if you can construct the final document once.

Where possible, keep frequently queried or identity-bearing fields in normal typed columns rather than repeatedly querying or updating deep JSONB paths.

Safer migration architecture

A robust pattern is:

source file/API
  → parse and normalize
  → validate
  → staging table
  → deduplicate and classify
  → merge/upsert target tables in checkpointed batches
  → reconcile and report

Why staging helps

A staging table lets you:

• preserve raw source values for investigation
• validate types, required fields, vocabularies, and references before modifying production records
• identify duplicates and malformed rows
• generate a clear error report for customers or migration operators
• compare source and target counts
• make the final merge set-based rather than issuing many ORM calls

Example staging table:

CREATE UNLOGGED TABLE migration_asset_stage (
    migration_run_id uuid NOT NULL,
    organization_id bigint NOT NULL,
    external_id text NOT NULL,
    title text,
    description text,
    source_updated_at timestamptz,
    raw_payload jsonb,
    row_number integer NOT NULL
);

UNLOGGED can improve load performance, but it is not crash-safe and is not replicated. That can be acceptable for reconstructable staging data, not for the authoritative target tables.

Then merge:

INSERT INTO assets (
    organization_id,
    external_id,
    title,
    description,
    source_updated_at
)
SELECT
    s.organization_id,
    s.external_id,
    s.title,
    s.description,
    s.source_updated_at
FROM (
    SELECT DISTINCT ON (organization_id, external_id) *
    FROM migration_asset_stage
    WHERE migration_run_id = :run_id
    ORDER BY organization_id, external_id, source_updated_at DESC, row_number DESC
) AS s
ON CONFLICT (organization_id, external_id) DO UPDATE
SET
    title = EXCLUDED.title,
    description = EXCLUDED.description,
    source_updated_at = EXCLUDED.source_updated_at
WHERE
    assets.source_updated_at IS NULL
    OR EXCLUDED.source_updated_at >= assets.source_updated_at
AND (
    assets.title,
    assets.description,
    assets.source_updated_at
) IS DISTINCT FROM (
    EXCLUDED.title,
    EXCLUDED.description,
    EXCLUDED.source_updated_at
);

The timestamp condition is important if source messages can arrive out of order: an older import should not overwrite newer data.

Django-specific considerations

Django’s bulk_create(..., update_conflicts=True, ...) can be useful:

Asset.objects.bulk_create(
    assets,
    batch_size=1000,
    update_conflicts=True,
    unique_fields=["organization_id", "external_id"],
    update_fields=["title", "description", "source_updated_at"],
)

But be aware:

• It bypasses Model.save().
• It does not run model validation (full_clean()).
• It does not invoke normal pre_save / post_save signal behavior in the same way as per-object saves.
• It may update unchanged rows depending on generated SQL and version/usage.
• It is less flexible for conditional update logic, deduplication, staging workflows, and detailed reporting.

For complex migrations, a Django management command with a service layer plus parameterized SQL for the set-based merge is often a better fit than trying to express every migration rule through the ORM.

Use transaction.atomic() around each checkpointed batch, not necessarily the entire migration:

for batch in batches:
    with transaction.atomic():
        validate_batch(batch)
        load_stage(batch)
        merge_stage_into_target(batch.run_id)
        record_checkpoint(batch)

Store migration state durably, e.g.:

migration_run
- id
- customer / organization
- source version
- status
- last_completed_checkpoint
- started_at / finished_at
- records_read / inserted / updated / skipped / rejected
- error summary

This turns “rerun the migration” into an idempotent, observable operation rather than a risky manual action.

Operational safety checklist

Before running at customer scale:

• Verify the natural key and unique index match the intended record identity.
• Validate input before writes: required fields, data types, controlled vocabularies, foreign-key mappings.
• Deduplicate source records deterministically.
• Make writes idempotent.
• Use conditional updates to avoid unchanged-row churn.
• Use bounded, committed batches and durable checkpoints.
• Ensure retries are safe for deadlocks, connection failures, and transient errors.
• Measure conflict rate, insert/update/no-op counts, batch duration, WAL volume, and replication lag.
• Test against production-like data volumes and distributions—not just a small happy-path sample.
• Run reconciliation after migration: counts, missing IDs, duplicates, invalid relationships, and representative record-level comparisons.
• Keep a clear rollback or corrective-migration plan.

The key senior-engineering answer is: bulk upserts are useful, but treat them as a controlled data pipeline. The hard part is usually not writing ON CONFLICT; it is defining identity, preventing stale or duplicate source data from overwriting correct records, making retries safe, and keeping the process observable and recoverable.