TRENDING

TECHNOLOGY NEWS FROM AROUND THE WORLD

→
How to prevent duplicate database records permanently
Software Engineering · 2026Photo: Unsplash / Unsplash ↗
Software Engineering

How to prevent duplicate database records permanently

Repeatedly processing the same external source should update a single record, not multiply it.

Yazılımcının GünlüğüPublished: Nov 15, 20264 min read

Define record identity first

Re-reading a source is normal. Creating another copy of the same unchanged record on every run is not. Use a stable source identifier when one exists, or document a defensible composite key and normalization rules.

Unique indexes protect concurrent workers

Checking for an existing record in application code is not sufficient when two workers act simultaneously. A database unique constraint forms the final integrity barrier. Upsert operations can then safely update a known record instead of inserting duplicates.

CREATE UNIQUE INDEX IF NOT EXISTS idx_reviews_source
ON comments (source, source_review_id);

INSERT INTO comments (source, source_review_id, body)
VALUES ('google', ?, ?)
ON CONFLICT(source, source_review_id) DO UPDATE SET
  body = excluded.body;

Make processing outcomes understandable

Records discovered, records discarded and distinct records stored are different metrics. Users should not be asked to interpret processing attempts as business outcomes. Keep a concise reason for each update or rejection.

Conceptual workflow — original editorial illustration

Handle deletion carefully

A missing result might mean a temporary source outage, not an intentional removal. Track provenance, timestamps and publication state so data can be reviewed before permanent deletion.

Identity rules prevent multiplication

Running the same review collector five times should not turn ten distinct reviews into fifty records. Define record identity first. Use a stable source ID when available, or a carefully justified composite key involving source, property, author, timestamp and normalized content. Identical text is not always proof that two events are the same. Document normalization and matching decisions, and do not merge ambiguous cases automatically. Data quality is broader than deleting rows that happen to look similar.

The database must protect concurrent writes

Two workers can both check for a record, see that it does not exist, and insert duplicates simultaneously. Application-level checks are insufficient without a UNIQUE constraint or index. Where appropriate, use INSERT ... ON CONFLICT to upsert the one canonical record. A stable idempotency key makes retries predictable after network timeouts. These constraints keep identity consistent even when multiple agents or scheduled tasks process the same source concurrently.

Synchronization and deletion pitfalls

A review temporarily missing from an external source is not necessarily deleted. Pagination limits, verification challenges and access failures can produce incomplete results. Track last-seen timestamps, source state and publication status instead of deleting immediately. Verify the evidence and relevant retention requirements before irreversible changes. Show discovered comments, distinct stored comments and rejected records as separate metrics in the admin UI. The number of scraping attempts is not evidence of higher content quality.

0 reads

Comments

0 real comments
FICTIONAL EXAMPLEDeniz · 2026 (sample)

Hiding duplicate rows in the UI is insufficient. A unique database constraint and an idempotent upsert need to be designed together.

Illustrative reader note and date; not a real comment.
✎ Write a comment

Comments are reviewed before publication.