I found a repeated-query problem while profiling a bulk import preview with synthetic data. Application-side instrumentation showed that every valid parsed row caused one duplicate-lookup statement. A 500-row all-valid case produced 500 such statements before the preview could report which results already existed.
I changed the function to send valid identities in one statement and map each answer back to its original input position. A non-empty valid batch now executes one duplicate-lookup statement.
What counts as the same result
The importer defines a duplicate with four values: user ID, LOINC code, full test timestamp, and canonical source label. Their schema types and nullability are:
user_id INTEGER NOT NULL
loinc_code VARCHAR(20)
test_datetime TIMESTAMPTZ NOT NULL
source_lab VARCHAR(255)
The relevant database migration created this constraint:
ALTER TABLE lab_results
ADD CONSTRAINT lab_results_user_loinc_datetime_source_key
UNIQUE (user_id, loinc_code, test_datetime, source_lab);
The two NOT NULL declarations reject missing users and timestamps. The unique constraint permits at most one stored match when all four values are non-null. LOINC and source remain nullable, so that uniqueness guarantee does not cover incomplete tuples. The batching change kept the earlier validation and equality rules for those inputs.
Source canonicalization still happens before duplicate lookup, with no change to the existing alias rules.
From the per-row query to an ordered batch
The baseline executed this query once for each valid result:
cursor.execute("""
SELECT id FROM lab_results
WHERE user_id = %s AND loinc_code = %s
AND test_datetime = %s AND source_lab = %s
LIMIT 1
""", (user_id, loinc_code, test_datetime, source_lab))
row = cursor.fetchone()
checked.append({
"is_duplicate": row is not None,
"existing": row[0] if row else None,
})
The following sample factors the existing inline timestamp and source parsing into normalize_identity() and derives the parameter lists from tuples. That helper returns the three parsed fields, which may include an incomplete nullable field, or None when the preview cannot form an identity. The batching SQL and ordinality mapping have only been reformatted.
def check_duplicates(db, user_id, results):
cursor = db.cursor()
checked = [
{"is_duplicate": False, "existing": None}
for _ in results
]
identities = []
for position, result in enumerate(results):
identity = normalize_identity(result)
if identity is not None:
identities.append((position, *identity))
if not identities:
return checked
positions = [item[0] for item in identities]
loinc_codes = [item[1] for item in identities]
test_datetimes = [item[2] for item in identities]
source_labs = [item[3] for item in identities]
cursor.execute("""
SELECT incoming.ordinality, existing.id
FROM unnest(%s::text[], %s::timestamptz[], %s::text[])
WITH ORDINALITY AS incoming(
loinc_code, test_datetime, source_lab, ordinality
)
LEFT JOIN lab_results AS existing
ON existing.user_id = %s
AND existing.loinc_code = incoming.loinc_code
AND existing.test_datetime = incoming.test_datetime
AND existing.source_lab = incoming.source_lab
ORDER BY incoming.ordinality
""", (loinc_codes, test_datetimes, source_labs, user_id))
for ordinality, existing_id in cursor.fetchall():
original_position = positions[ordinality - 1]
checked[original_position] = {
"is_duplicate": existing_id is not None,
"existing": existing_id,
}
return checked
Psycopg2 2.9.10 binds three Python lists and the user ID through the parameter tuple. unnest pairs elements at the same list position, while WITH ORDINALITY numbers those rows. Deriving every list from the same tuple collection keeps their lengths aligned; PostgreSQL otherwise pads shorter parallel arrays with nulls. Explicit text[] and timestamptz[] casts tell PostgreSQL how to interpret the lists. Timestamp parsing is outside the sample and was not changed by this work.
Filtering invalid inputs creates a compacted valid set. positions[ordinality - 1] maps each database answer back into the complete output list. ORDER BY incoming.ordinality keeps query output easy to inspect. The mapping uses the returned ordinality directly.
At PostgreSQL’s Read Committed isolation level, the former sequence of statements could observe a commit between rows. The batched statement gets one statement snapshot. A write can still arrive after the preview, so the complete non-null identity constraint remains the final check when data is stored.
I avoided separate predicates such as loinc_code = ANY(codes) alongside test_datetime = ANY(times). That shape could combine fields taken from different incoming identities, producing a false composite match.
One mixed batch
Here is one representative batch. A and B stand for two LOINC codes, and the timestamps and source labels are synthetic.
| Original index | Parsed identity | SQL ordinality | Preview output |
|---|---|---|---|
| 0 | A, invalid time, Source One |
— | default; no existing ID |
| 1 | A, 2026-09-01T10:00Z, Source One |
1 | duplicate; ID 101 |
| 2 | B, 2026-09-02T10:00Z, Source Two |
2 | default; no existing ID |
| 3 | repeat of index 1 | 3 | duplicate; ID 101 |
The compacted code array is [A, B, A]; timestamps and sources have the matching three positions. PostgreSQL returns [(1, 101), (2, NULL), (3, 101)]. Mapping those ordinalities through positions = [1, 2, 3] reconstructs the four-entry preview. An entirely invalid input returns its defaults without executing SQL.
What I verified
The unit tests inspect the bound arrays and reconstructed outputs. A batch containing a stored match, a non-match, and the first identity repeated returns the existing ID in the first and third positions and the default result in the second. Another test places one valid match after three malformed inputs and expects its result in the fourth output slot. When every identity is invalid, the test expects zero SQL executions.
I also ran the old and new functions against a disposable PostgreSQL database with the displayed constraint and an unchanged committed state. In the synthetic 500-valid-row fixture, half the identities already existed; that fixture returned the same ordered duplicate flags and existing IDs from both functions. Application instrumentation counted calls to cursor.execute inside this lookup only: 500 for the earlier function and one for the batch. Connection, schema-setup, and transaction statements were excluded. The test’s PostgreSQL version was not retained, and no execution-plan claim depends on the unique index. These figures describe statement round trips from the function rather than end-to-end preview speed.
This lookup does not identify two identical new rows when neither is stored; within-file duplicate detection remains separate. Arrays were a manageable fit for the tested 500-row case because psycopg2 already bound the lists and ordinality supplied the position. I have not established a larger production threshold, so a much bigger input would need its own payload and chunking measurement.