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,
upsertreportedcreated=1while it updated the competing writer's row. - Callers serialize writers when exact counts matter.
upserttakes no locks. - An empty
keyraisesValueError("key must not be empty").
- Counts describe the rows read before the write. A writer that commits
between the read and the write can make them wrong; on SQLite,
- 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.