Compliant Web Scraping

Upserting scraped records into Postgres #

Scrapers re-visit the same pages, and every re-crawl produces rows you have seen before with fields that may have changed. A plain INSERT either duplicates them or fails on a unique constraint; a naive “delete then insert” opens a window where the data is missing and burns write amplification. The correct primitive is an idempotent upsert — INSERT ... ON CONFLICT DO UPDATE — so that re-running the same batch converges to one authoritative row per entity. This page covers key design, batched execution with psycopg, and deduplication at write time. It is a detail page under Structured Data Sinks and Warehousing, part of the Pipeline Storage, Deduplication & Monitoring section.

Problem Framing #

Idempotency is the property that matters here: running the same ingestion twice must leave the database in the same state as running it once. Without it, a retried Airflow task or a re-processed message-queue batch silently doubles your row count, and every downstream COUNT and SUM is wrong.

The design hinges on one choice: what makes two scraped records “the same entity”? A natural key is a column (or combination) that comes from the source data and uniquely identifies the entity — a product SKU, an ISBN, a canonical URL. A surrogate key is a database-generated BIGINT/UUID with no business meaning. You need both: the surrogate as the primary key for foreign-key stability, and a UNIQUE constraint on the natural key so ON CONFLICT has a target to detect duplicates against. The natural key is what makes the upsert idempotent; the surrogate is what keeps joins cheap.

Step-by-Step Implementation #

  1. Model the table with both key types. Surrogate id as primary key, a UNIQUE constraint on the natural key, and a content_updated_at to track real changes.
  2. Dedup the batch in memory first. A single INSERT statement cannot touch the same conflict target twice in one command — Postgres raises ON CONFLICT DO UPDATE command cannot affect row a second time. Collapse in-batch duplicates before sending.
  3. Send the batch with execute_values (psycopg2) or executemany with pipeline mode (psycopg 3) so hundreds of rows travel in one round trip.
  4. Update only when content actually changed, using a WHERE clause on the DO UPDATE so unchanged rows do not bump updated_at or generate dead tuples.
CREATE TABLE product (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- surrogate
    source_domain   TEXT NOT NULL,
    sku             TEXT NOT NULL,
    title           TEXT NOT NULL,
    price           NUMERIC(12, 2),
    content_hash    TEXT NOT NULL,        -- stable hash of normalised fields
    first_seen_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    content_updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (source_domain, sku)           -- the natural key / conflict target
);

-- Idempotent upsert. Re-running the same batch is a no-op after the first run
-- because the WHERE clause suppresses updates when nothing changed.
INSERT INTO product (source_domain, sku, title, price, content_hash)
VALUES (%s, %s, %s, %s, %s)
ON CONFLICT (source_domain, sku) DO UPDATE
    SET title              = EXCLUDED.title,
        price              = EXCLUDED.price,
        content_hash       = EXCLUDED.content_hash,
        content_updated_at = now()
    WHERE product.content_hash IS DISTINCT FROM EXCLUDED.content_hash;

The WHERE product.content_hash IS DISTINCT FROM EXCLUDED.content_hash clause is what makes re-crawls cheap: if the row is byte-for-byte identical, Postgres skips the update entirely, avoiding a dead tuple and leaving content_updated_at untouched so it reflects genuine change history. That content_hash should be computed exactly as described in deduplicating records with content hashing.

Now batch it from Python:

import psycopg2
from psycopg2.extras import execute_values

UPSERT_SQL = """
INSERT INTO product (source_domain, sku, title, price, content_hash)
VALUES %s
ON CONFLICT (source_domain, sku) DO UPDATE
    SET title = EXCLUDED.title,
        price = EXCLUDED.price,
        content_hash = EXCLUDED.content_hash,
        content_updated_at = now()
    WHERE product.content_hash IS DISTINCT FROM EXCLUDED.content_hash;
"""

def upsert_products(conn, records: list[dict]) -> None:
    # Dedup on the natural key: last write wins within the batch, so a single
    # INSERT never hits the same conflict target twice.
    deduped = {(r["source_domain"], r["sku"]): r for r in records}
    rows = [
        (r["source_domain"], r["sku"], r["title"], r["price"], r["content_hash"])
        for r in deduped.values()
    ]
    with conn, conn.cursor() as cur:
        execute_values(cur, UPSERT_SQL, rows, page_size=500)

execute_values rewrites the single VALUES %s placeholder into one multi-row INSERT, so a 500-row page_size means one statement and one network round trip per 500 records instead of 500 separate calls. The surrounding with conn block commits on success and rolls back on any exception, keeping the batch atomic.

Verification & Testing #

Prove idempotency directly: run the same batch twice and assert the row count and update timestamps are stable.

def test_upsert_is_idempotent(conn):
    batch = [{"source_domain": "shop.example", "sku": "A1",
              "title": "Widget", "price": "9.99", "content_hash": "abc123"}]
    upsert_products(conn, batch)
    upsert_products(conn, batch)   # second run must be a no-op

    with conn.cursor() as cur:
        cur.execute("SELECT count(*), max(content_updated_at) FROM product "
                    "WHERE sku = 'A1'")
        count, first_ts = cur.fetchone()
    assert count == 1              # not duplicated

    upsert_products(conn, batch)   # identical again → timestamp unchanged
    with conn.cursor() as cur:
        cur.execute("SELECT max(content_updated_at) FROM product WHERE sku = 'A1'")
        assert cur.fetchone()[0] == first_ts

To confirm the WHERE guard is doing its job, run EXPLAIN (ANALYZE) on the upsert with an unchanged row and check that zero rows were updated, or query pg_stat_user_tables and watch that n_dead_tup does not climb across repeated identical runs.

Compliance & Operational Guardrails #

  • The natural key must not itself be personal data you cannot lawfully store. Hash or pseudonymise identifiers such as email addresses before using them as a conflict target; a raw email in a UNIQUE index is still processing under GDPR.
  • Keep an update trail, not just current state. first_seen_at and content_updated_at give you the lineage a data-protection audit expects; pair them with documented GDPR compliance for scraped personal data so you can answer when and why a record was last touched.
  • Make erasure targetable. Because the upsert converges to one row per entity, a right-to-erasure request maps to a single DELETE on the natural key rather than a hunt through duplicates.

Common Mistakes #

  1. Hitting the same conflict target twice in one statement. Postgres aborts the whole command. Always dedup the batch on the natural key in memory before the INSERT.
  2. Updating unconditionally. Without the IS DISTINCT FROM guard, every re-crawl rewrites every row, generating dead tuples, bloating the table, and destroying real updated_at history.
  3. Row-by-row INSERT in a loop. One statement per record turns a 10k-row batch into 10k round trips; use execute_values or psycopg 3 pipeline mode.

Frequently Asked Questions #

When should I use a natural key versus a surrogate key as the conflict target? #

The ON CONFLICT target must be the natural key, because that is what identifies the same real-world entity across crawls and makes the upsert idempotent. Keep a surrogate id as the primary key so foreign keys and joins do not break when a natural key changes. In short: surrogate for internal relationships, natural key (with a UNIQUE constraint) for the upsert.

How large should each upsert batch be? #

Batches of a few hundred to a few thousand rows per statement give the best throughput; a page_size of 500 in execute_values is a solid default. Beyond a few thousand rows per statement you risk long-held locks and large transactions that block autovacuum. Tune by measuring rows-per-second, not by guessing.