PostgreSQL RAG Chunk Overlap Causing Duplicate Fragments After Event Trigger

Q: Root Cause – Overlap Misconfiguration Coupled with Non‑Idempotent Triggers

The pipeline uses a configurable chunk_size and overlap (in tokens). When overlap is non‑zero, the same text segment appears in two adjacent chunks. The insertion logic is executed inside an AFTER INSERT trigger that marks a row as processed = TRUE after the vector is stored.

Q: Debug – Step‑by‑Step Investigation 1. Examine the trigger definition

CREATE OR REPLACE FUNCTION rag_chunk_insert() RETURNS trigger AS $$ BEGIN -- Compute embedding INSERT INTO rag_chunks (document_id, chunk_index, chunk_text, embedding) VALUES (NEW.document_id, NEW.chunk_index, NEW.chunk_text, pgvector.embed(NEW.chunk_text)); -- Mark source row as processed UPDATE source_documents SET processed = TRUE WHERE id = NEW.document_id; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_rag_chunk AFTER INSERT ON source_documents FOR EACH ROW EXECUTE FUNCTION rag_chunk_insert(); 2. Reproduce the duplicate insert BEGIN; INSERT INTO source_documents (id, content, processed) VALUES (42, 'Long article …', FALSE); -- Trigger fires, inserts chunk 0 and chunk 1 (overlap = 200 tokens) COMMIT; -- Simulate serialization failure and retry BEGIN; INSERT INTO source_documents (id, content, processed) VALUES (42, 'Long article …', FALSE); COMMIT; Resulting rag_chunks table contains two rows with identical (document_id, chunk_index) values, triggering the error shown earlier.

Q: 3. Verify duplicate vectors exist

SELECT document_id, chunk_index, COUNT(*) FROM rag_chunks GROUP BY document_id, chunk_index HAVING COUNT(*) > 1; Output (example):

Q: 4. Check transaction isolation logs 2026-09-15 10:42:31.123 UTC [12345] LOG: could not serialize access due to concurrent update 2026-09-15 10:42:31.124 UTC [12345] DETAIL: Process 12346 waits for ShareLock on transaction 98765; blocked by process 12347. The application’s retry logic re‑issued the insert, causing the trigger to run again. Solution – Idempotent Trigger Logic and Proper Overlap Handling 1. Add a unique constraint on the chunk identifier

ALTER TABLE rag_chunks ADD CONSTRAINT uq_rag_chunk UNIQUE (document_id, chunk_index); 2. Make the trigger insertion idempotent CREATE OR REPLACE FUNCTION rag_chunk_insert() RETURNS trigger AS $$ BEGIN -- Skip if this chunk was already processed IF EXISTS ( SELECT 1 FROM rag_chunks WHERE document_id = NEW.document_id AND chunk_index = NEW.chunk_index ) THEN RETURN NULL; END IF; INSERT INTO rag_chunks (document_id, chunk_index, chunk_text, embedding) VALUES (NEW.document_id, NEW.chunk_index, NEW.chunk_text, pgvector.embed(NEW.chunk_text)) ON CONFLICT (document_id, chunk_index) DO NOTHING; UPDATE source_documents SET processed = TRUE WHERE id = NEW.document_id; RETURN NULL; END; $$ LANGUAGE plpgsql; 3. Adjust the chunking service to emit a single “processed” flag Instead of relying on the trigger to set processed = TRUE, have the upstream service set the flag only after the entire document’s chunks have been successfully stored. This prevents the trigger from seeing the same source row multiple times.

Q: 4. Use SERIALIZABLE isolation with explicit retry limits

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- Application retry loop (max 3 attempts) Combine with ON CONFLICT DO NOTHING to guarantee that even if a retry occurs, duplicate rows are not created.

Q: Verification – Confirming the Fix 1. Insert a document with overlapping chunks

BEGIN; INSERT INTO source_documents (id, content, processed) VALUES (99, 'Sample text …', FALSE); COMMIT; 2. Query for duplicates SELECT document_id, chunk_index, COUNT(*) FROM rag_chunks WHERE document_id = 99 GROUP BY document_id, chunk_index HAVING COUNT(*) > 1; Expected result: empty set.

Q: 3. Run a similarity search

SELECT document_id, chunk_index, embedding <=> query_vec AS distance FROM rag_chunks ORDER BY distance LIMIT 5; Check that the returned rows have distinct chunk_index values and that the distance distribution matches baseline (no inflated scores).

In this article

Table of Contents

Problem – Duplicate Fragments from Overlapping Chunk Inserts

In an event‑driven Retrieval‑Augmented Generation (RAG) pipeline, incoming documents are split into overlapping text windows (chunks) and each chunk is stored as a vector in PostgreSQL using the pgvector extension. After a recent deployment, retrieval queries began returning multiple identical fragments, inflating similarity scores and producing noisy answers.

Typical symptoms observed in production logs:

ERROR: duplicate key value violates unique constraint “rag_chunks_pkey” (document_id, chunk_index)

Log pattern: INSERT INTO rag_chunks ... repeated within the same transaction window

Warning: “Vector similarity search returned identical scores for multiple rows”

Impact includes:

Answer relevance drops (average cosine similarity decreased by ~0.12 in A/B tests).
Increased latency due to larger result sets.
Alert spikes for “duplicate fragment” thresholds.

Root Cause – Overlap Misconfiguration Coupled with Non‑Idempotent Triggers

The pipeline uses a configurable chunk_size and overlap (in tokens). When overlap is non‑zero, the same text segment appears in two adjacent chunks. The insertion logic is executed inside an AFTER INSERT trigger that marks a row as processed = TRUE after the vector is stored.

Two conditions caused duplication:

Trigger re‑execution on the same rows. Under high event burst, the trigger fired once per row, but the surrounding transaction was retried due to serialization failures (see PostgreSQL “could not serialize access due to concurrent update”). The retry re‑ran the trigger, inserting the overlapping chunk a second time.
Missing uniqueness guard for overlapping windows. The primary key is (document_id, chunk_index). Overlap does not change chunk_index, so each overlapping segment should be stored only once. Without a ON CONFLICT DO NOTHING clause or a separate processed flag check, the second trigger execution attempted a duplicate insert, leading to the error shown above.

Official PostgreSQL documentation on CREATE TRIGGER warns that “AFTER triggers fire after the triggering statement completes, but they are still part of the same transaction and will be re‑executed if the transaction is rolled back and retried.” The community GitHub issue “pgvector: overlapping chunk inserts cause duplicate results” confirms that overlapping windows must be de‑duplicated before insertion.

Debug – Step‑by‑Step Investigation

1. Examine the trigger definition


CREATE OR REPLACE FUNCTION rag_chunk_insert()
RETURNS trigger AS $$
BEGIN
    -- Compute embedding
    INSERT INTO rag_chunks (document_id, chunk_index, chunk_text, embedding)
    VALUES (NEW.document_id, NEW.chunk_index, NEW.chunk_text,
            pgvector.embed(NEW.chunk_text));

    -- Mark source row as processed
    UPDATE source_documents
    SET processed = TRUE
    WHERE id = NEW.document_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_rag_chunk
AFTER INSERT ON source_documents
FOR EACH ROW EXECUTE FUNCTION rag_chunk_insert();

2. Reproduce the duplicate insert


BEGIN;
INSERT INTO source_documents (id, content, processed)
VALUES (42, 'Long article …', FALSE);
-- Trigger fires, inserts chunk 0 and chunk 1 (overlap = 200 tokens)
COMMIT;
-- Simulate serialization failure and retry
BEGIN;
INSERT INTO source_documents (id, content, processed)
VALUES (42, 'Long article …', FALSE);
COMMIT;

Resulting rag_chunks table contains two rows with identical (document_id, chunk_index) values, triggering the error shown earlier.

3. Verify duplicate vectors exist


SELECT document_id, chunk_index, COUNT(*)
FROM rag_chunks
GROUP BY document_id, chunk_index
HAVING COUNT(*) > 1;

Output (example):


 document_id | chunk_index | count
-------------+-------------+-------
          42 |           0 |     2
          42 |           1 |     2

4. Check transaction isolation logs

2026-09-15 10:42:31.123 UTC [12345] LOG: could not serialize access due to concurrent update

2026-09-15 10:42:31.124 UTC [12345] DETAIL: Process 12346 waits for ShareLock on transaction 98765; blocked by process 12347.

The application’s retry logic re‑issued the insert, causing the trigger to run again.

Solution – Idempotent Trigger Logic and Proper Overlap Handling

1. Add a unique constraint on the chunk identifier


ALTER TABLE rag_chunks
ADD CONSTRAINT uq_rag_chunk UNIQUE (document_id, chunk_index);

2. Make the trigger insertion idempotent


CREATE OR REPLACE FUNCTION rag_chunk_insert()
RETURNS trigger AS $$
BEGIN
    -- Skip if this chunk was already processed
    IF EXISTS (
        SELECT 1 FROM rag_chunks
        WHERE document_id = NEW.document_id
          AND chunk_index = NEW.chunk_index
    ) THEN
        RETURN NULL;
    END IF;

    INSERT INTO rag_chunks (document_id, chunk_index, chunk_text, embedding)
    VALUES (NEW.document_id, NEW.chunk_index, NEW.chunk_text,
            pgvector.embed(NEW.chunk_text))
    ON CONFLICT (document_id, chunk_index) DO NOTHING;

    UPDATE source_documents
    SET processed = TRUE
    WHERE id = NEW.document_id;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

3. Adjust the chunking service to emit a single “processed” flag

Instead of relying on the trigger to set processed = TRUE, have the upstream service set the flag only after the entire document’s chunks have been successfully stored. This prevents the trigger from seeing the same source row multiple times.

4. Use `SERIALIZABLE` isolation with explicit retry limits


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Application retry loop (max 3 attempts)

Combine with ON CONFLICT DO NOTHING to guarantee that even if a retry occurs, duplicate rows are not created.

Verification – Confirming the Fix

1. Insert a document with overlapping chunks


BEGIN;
INSERT INTO source_documents (id, content, processed)
VALUES (99, 'Sample text …', FALSE);
COMMIT;

2. Query for duplicates


SELECT document_id, chunk_index, COUNT(*)
FROM rag_chunks
WHERE document_id = 99
GROUP BY document_id, chunk_index
HAVING COUNT(*) > 1;

Expected result: empty set.

3. Run a similarity search


SELECT document_id, chunk_index, embedding <=> query_vec AS distance
FROM rag_chunks
ORDER BY distance
LIMIT 5;

Check that the returned rows have distinct chunk_index values and that the distance distribution matches baseline (no inflated scores).

4. Monitor alerts

After deployment, verify that “duplicate fragment” alerts stay at zero for at least 24 hours and that relevance metrics (e.g., average cosine similarity) return to pre‑incident levels.

Prevention – Operational Guardrails

Schema safeguards: Keep the unique constraint on (document_id, chunk_index) and consider adding a checksum column with a UNIQUE index to catch identical text fragments.
Idempotent ingestion: Design the event handler to be safe for retries – use ON CONFLICT DO NOTHING and avoid side‑effects in triggers.
Transaction handling: Limit the number of automatic retries for SERIALIZABLE failures; log each retry with the document ID for audit.
Monitoring: Add a metric rag_chunks_total and a derived metric rag_chunks_duplicates (count of rows where COUNT(*) > 1 per document). Alert when the duplicate ratio exceeds 0.1 %.
Testing: Include unit tests that simulate overlapping chunk insertion with forced transaction aborts to verify that no duplicate rows appear.

Related Topic Hub: Data Infrastructure Troubleshooting Hub

FAQ

Why does the duplicate‑key error appear only under load?
Because high concurrency increases the chance of serialization failures, causing the application to retry the same INSERT. Without an idempotent trigger, each retry re‑inserts overlapping chunks.
Can I keep the overlap but avoid duplicates without a trigger?
Yes. Perform chunk generation and vector insertion in a single idempotent batch operation (e.g., INSERT ... ON CONFLICT DO UPDATE) and remove the AFTER INSERT trigger entirely.
Do I need to change the pgvector index type?
No. The issue is logical duplication, not index performance. A GIN or IVF‑PQ index works unchanged once duplicates are eliminated.
How do I detect existing duplicate fragments in a legacy database?
Run the duplicate query shown in the verification step, then de‑duplicate by keeping the row with the lowest ctid or by aggregating embeddings.
Is a processed flag on the source row sufficient to prevent re‑processing?
Only if the flag is set after the entire batch succeeds. Setting it per‑chunk (as the original trigger did) allows the trigger to see the same source row on retries, re‑creating duplicates.