Skip to content

F22.11 — SQLAlchemy bulk work with explicit bounds

State Closed
Parent E22
Iteration Release 9
Start Date 2026-11-14
Closed Date 2026-11-16
Predecessors F22.3

Stories: 2 Closed

Story State Depends on
S22.11.1 — Settle count semantics and resource targets Closed (2026-11-14) —
S22.11.2 — Remove measured bulk overhead Closed (2026-11-16) S22.11.1

Keep the ORM layer small; make bulk-upsert cost and concurrency claims match what it can guarantee.

Context

upsert.py:61-82 builds keyed input, reads existing rows, builds a complete write list and then executes an upsert. _existing chunks reads at a fixed 900 parameters (:133-155). The docstring calls counts exact although classification happens before the write. Existing tests cover 33,000 rows and composite keys but not competing writers. The package is only 679 source lines; broad module splitting has no demonstrated benefit.

S22.11.1 — Settle count semantics and resource targets

State: Closed (2026-11-14)

Compare two-session concurrent upserts with serial execution on supported dialects. Specify whether counts describe the pre-write snapshot or require serialization. Measure allocations/query counts for empty, unchanged, mixed and large input.

Acceptance:

  • A decision story records observed competing-writer outcomes and chooses accurate documentation or a justified transaction strategy before changing the algorithm.
  • Baseline includes both single and composite keys, audit preservation, rollback and duplicate rejection.
  • An invalid empty key produces a deliberate documented error rather than division by zero; valid empty input does no unnecessary query.
  • Settled (owner, 2026-11-14): snapshot counts.
    • Counts describe the rows read before the write. A writer that commits between the read and the write can make them wrong; on SQLite, upsert reported created=1 while it updated the competing writer's row.
    • Callers serialize writers when exact counts matter. upsert takes no locks.
    • An empty key raises ValueError("key must not be empty").
  • Settled targets for S22.11.2 (33,000 rows; provisional until a PostgreSQL baseline exists):
    • at most 16 MiB traced peak for all-new rows, and 12 MiB for mostly unchanged rows, for both key shapes (today about 28.4/29.5 and 14.1/15.7 MiB, single/composite);
    • no more than 38/75 statements;
    • zero statements for empty input and rejected duplicates;
    • no write for unchanged rows;
    • no median time regression over five runs.

Outcome: baselines in tests/measure/test_upsert_resources.py; SQLite only, because no PostgreSQL service was available locally.

S22.11.2 — Remove measured bulk overhead

State: Closed (2026-11-16) · Depends on: S22.11.1

Use the measured result to reduce repeated materialization or bound writes without changing duplicate-key detection, skipped-row behavior or caller-owned transactions. Retain the focused SQLite and Alembic wrappers unless a concrete defect is found.

Acceptance:

  • Large input has a recorded peak-memory/query comparison; the change meets the resource target settled in S22.11.1.
  • SQLite and PostgreSQL cases preserve counts under the chosen semantics, audit columns, composite keys and rollback; PostgreSQL evidence comes from its actual integration service.
  • Keyset null ordering, mixed directions, malformed cursor rejection and page-token compatibility remain covered.

Settled contract: validation stays one full pass before any query; reads and classification run one chunk at a time; the write stays one INSERT … ON CONFLICT holding changed rows only, with audit values bound once. Owner, 2026-11-16: the S22.11.1 all-new target is revised from 16 MiB to at most 27 MiB (the remainder is the single write list; batching would break the 38/75 statement limit), and the measured +7–11% median wall changes are accepted in place of no regression.

Outcome: at 33,000 rows the all-new peak fell from 30.6/31.0 MB to 24.6/26.8 MB and the mostly-unchanged peak from 14.8/16.4 MB to 3.8/4.2 MB (single/composite), statements unchanged; PostgreSQL integration passed.

Acceptance

  • S22.11.1: A decision story records observed competing-writer outcomes and chooses accurate documentation or a justified transaction strategy before changing the algorithm.
  • S22.11.2: Large input has a recorded peak-memory/query comparison; the change meets the resource target settled in S22.11.1.