Skip to content

Batch PgReceiptRepo.insert_receipt line inserts (avoid N+1) #51

@JumpTechCode

Description

@JumpTechCode

Summary

PgReceiptRepo.insert_receipt inserts receipt lines one row per round trip:

# src/quartermaster/adapters/postgres/unit_of_work.py
for line in lines:
    await self._conn.execute(
        receipt_line.insert().values(
            receipt_id=line.receipt_id,
            sku_id=line.sku_id,
            expected_qty=line.expected,
            received_qty=line.received,
        )
    )

A receipt with N lines issues N separate INSERT statements, i.e. N Postgres round trips inside the receiving transaction. For a large inbound receipt (e.g. 50 lines) that is 50 round trips where one suffices.

Context

The outbound sibling PgOrderRepo.insert_order already uses the batched executemany form (closed via #18) and documents the asyncpg subtlety in a comment: passing a list of dicts alongside the bare insert() construct triggers executemany, whereas table.insert().values(list) does not. The receipt path was simply missed when the order path was converted, so the two inbound/outbound insert routines are now inconsistent.

Proposed fix

Mirror insert_order exactly — a single multi-row insert, guarded against an empty line list:

if lines:
    await self._conn.execute(
        receipt_line.insert(),
        [
            {
                "receipt_id": line.receipt_id,
                "sku_id": line.sku_id,
                "expected_qty": line.expected,
                "received_qty": line.received,
            }
            for line in lines
        ],
    )

Notes

  • Behaviour is unchanged; this is purely a round-trip reduction on the receiving command path.
  • The if lines: guard matches insert_order and avoids executing with an empty parameter list.
  • Existing insert_receipt coverage should continue to pass; consider asserting the batched form the way Batch PgOrderRepo.insert_order line inserts (avoid N+1) #18 did for orders if that test exists.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions