Chapter 19 · Application Integration, Connectors, Pools, ORMs, and Reliability Patterns
ORM Query Generation, N+1 Problems, Pagination, Bulk Writes, and Plan Visibility
Make ORM-generated SQL observable, reproduce and repair N+1 amplification, compare keyset with deep OFFSET pagination, batch writes deliberately, and route generated SQL back through MySQL plan evidence.
Learning outcomes
ServiceHub adopts an Object-Relational Mapper (ORM) to improve developer productivity. API latency then grows with the number of work orders returned. One endpoint issues one query for parents and another query for each parent’s notes—the classic N+1 pattern. The ORM did not make MySQL slow; hidden query multiplication did.
Capture ORM-generated SQL and bind values safely enough to reason about query count and shape.
Reproduce N+1 lazy loading and compare an eager/batched loading strategy on the same dataset.
Compare deep OFFSET pagination with keyset/seek pagination while preserving deterministic ordering.
Batch writes inside bounded transactions instead of one commit per row or one unbounded giant transaction.
Take generated SQL back to EXPLAIN/EXPLAIN ANALYZE and separate database time from ORM/application overhead.
This lesson uses SQLAlchemy 2.0.51. Its current MySQL documentation warns that the Oracle MySQL Connector/Python SQLAlchemy dialect still has upstream regression risk, so the mandatory ORM example uses the well-supported mysql+pymysql dialect. Direct Connector/Python examples remain the baseline in Lessons 1–3.
Seed enough parent/child rows to make query multiplication visible
USE servicehub_app_lab;INSERT IGNORE INTO work_orders(customer_id,idempotency_key,status,priority,summary)SELECT 1, CONCAT('orm-',LPAD(n,4,'0')), IF(MOD(n,5)=0,'closed','open'), 1+MOD(n,5), CONCAT('ORM workload order ',n)FROM ( WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 120 ) SELECT n FROM seq) AS s;INSERT INTO work_order_notes(work_order_id,note_text)SELECT w.work_order_id, CONCAT('ORM note A for ',w.work_order_id)FROM work_orders wWHERE w.idempotency_key LIKE 'orm-%' AND NOT EXISTS (SELECT 1 FROM work_order_notes n WHERE n.work_order_id=w.work_order_id AND n.note_text LIKE 'ORM note A%');ANALYZE TABLE work_orders, work_order_notes;The recursive sequence is intentionally small and deterministic. If your server uses a lower recursion setting, reduce the row count rather than changing global configuration just for the lab.
Model the relationship and turn on SQL visibility
import osfrom sqlalchemy import create_engine, ForeignKey, String, Integer, BigInteger, eventfrom sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationshipengine = create_engine( "mysql+pymysql://servicehub_app@127.0.0.1/servicehub_app_lab?charset=utf8mb4", connect_args={"password": os.environ["MYSQL_PASSWORD"]}, pool_size=4, max_overflow=2, pool_pre_ping=True, pool_recycle=1800, echo=True, # lab only: inspect SQL; avoid sensitive bind logging in production)counter = {"count": 0}@event.listens_for(engine, "before_cursor_execute")def count_statements(conn, cursor, statement, parameters, context, executemany): counter["count"] += 1class Base(DeclarativeBase): passclass WorkOrder(Base): __tablename__ = "work_orders" work_order_id: Mapped[int] = mapped_column(BigInteger, primary_key=True) customer_id: Mapped[int] = mapped_column(BigInteger) status: Mapped[str] = mapped_column(String(16)) priority: Mapped[int] = mapped_column(Integer) summary: Mapped[str] = mapped_column(String(240)) notes: Mapped[list["WorkOrderNote"]] = relationship(back_populates="work_order")class WorkOrderNote(Base): __tablename__ = "work_order_notes" note_id: Mapped[int] = mapped_column(BigInteger, primary_key=True) work_order_id: Mapped[int] = mapped_column(ForeignKey("work_orders.work_order_id")) note_text: Mapped[str] = mapped_column(String(500)) work_order: Mapped[WorkOrder] = relationship(back_populates="notes")SQL logging is diagnostic. Production logs should redact or suppress sensitive bind values and have retention/access controls.
Reproduce N+1, then repair it with select-in eager loading
Lazy loading a one-to-many collection emits a SELECT when each collection is first accessed. For 30 parent rows, the endpoint can issue 31 SELECTs. SQLAlchemy’s selectinload() typically loads the parent set and then loads the related collection with an additional SELECT using parent keys.
from sqlalchemy import selectfrom sqlalchemy.orm import Session, selectinloadcounter["count"] = 0with Session(engine) as session: orders = session.scalars( select(WorkOrder) .where(WorkOrder.status == "open") .order_by(WorkOrder.work_order_id) .limit(30) ).all() total_notes = sum(len(o.notes) for o in orders) # lazy loads: N+1 shape print("lazy parents", len(orders), "notes", total_notes, "queries", counter["count"]) assert counter["count"] == 1 + len(orders)counter["count"] = 0with Session(engine) as session: orders = session.scalars( select(WorkOrder) .options(selectinload(WorkOrder.notes)) .where(WorkOrder.status == "open") .order_by(WorkOrder.work_order_id) .limit(30) ).all() total_notes = sum(len(o.notes) for o in orders) print("selectin parents", len(orders), "notes", total_notes, "queries", counter["count"]) assert counter["count"] == 2Do not declare eager loading universally faster. joinedload() can multiply rows for large collections; selectinload() adds at least one query and has parameter-set considerations. Choose from observed cardinality and response shape.
Deep OFFSET versus keyset pagination
LIMIT 50 OFFSET 100000 asks MySQL to find/order and discard many preceding rows before returning the page. Keyset pagination uses the last ordering key from the previous page as a seek boundary. It works best with stable deterministic ordering and an aligned index.
EXPLAIN ANALYZESELECT work_order_id, created_at, summaryFROM servicehub_app_lab.work_ordersWHERE status='open'ORDER BY created_at DESC, work_order_id DESCLIMIT 20 OFFSET 80;-- Suppose the previous page ended at these values; bind them in an app.EXPLAIN ANALYZESELECT work_order_id, created_at, summaryFROM servicehub_app_lab.work_ordersWHERE status='open' AND (created_at < '2026-08-16 00:00:00' OR (created_at='2026-08-16 00:00:00' AND work_order_id < 1000))ORDER BY created_at DESC, work_order_id DESCLIMIT 20;The small lab OFFSET is not expensive; it demonstrates the semantics. On production-sized data, compare actual rows examined/iterator timing with representative offsets. Keyset pagination changes API semantics: clients need a cursor/last key, and arbitrary page-number jumps become less direct.
Bounded bulk writes: avoid commit-per-row and giant transactions
from sqlalchemy import insertfrom sqlalchemy.orm import Sessionrows = [ {"work_order_id": 1, "note_text": f"bulk note {i}"} for i in range(1, 101)]BATCH = 25for start in range(0, len(rows), BATCH): chunk = rows[start:start+BATCH] with Session(engine) as session: with session.begin(): session.execute(insert(WorkOrderNote), chunk)Batch size is a measured operating choice. Too small increases round trips and commit overhead; too large increases transaction duration, undo/redo, locks, replication transaction size, memory, and recovery/retry cost.
Return generated SQL to MySQL plan evidence
SHOW INDEX FROM servicehub_app_lab.work_orders;EXPLAIN FORMAT=TREESELECT work_order_id, customer_id, status, priority, summaryFROM servicehub_app_lab.work_ordersWHERE status='open'ORDER BY work_order_idLIMIT 30;EXPLAIN ANALYZESELECT work_order_id, customer_id, status, priority, summaryFROM servicehub_app_lab.work_ordersWHERE status='open'ORDER BY work_order_idLIMIT 30;EXPLAIN ANALYZE executes the statement. Use it only when execution is safe. If SQL itself is fast but endpoint time is slow, profile row decoding, object construction, serialization, network transfer, and application code rather than adding indexes blindly.
Wrong fix: raise the pool size because N+1 is slow
More concurrent connections can make N+1 worse by multiplying query pressure. First reduce query count/rows, verify the plan, and measure database versus application time. Pool capacity is a separate concurrency budget.
Production judgment and bridge to Lesson 5
Track query count per request, rows returned, statement digests, p95/p99 endpoint latency, pool wait time, ORM object/serialization time, and plan regressions. The final lesson coordinates those application behaviors with replicas, migrations, health checks, and controlled degradation during failures.
Knowledge check
- What is N+1?
- Why is selectinload not automatically best for every relationship?
- What does keyset pagination trade away?
- Why can one giant bulk transaction be risky?
- Why should generated ORM SQL be sent back through EXPLAIN?
Reveal answers
- One query loads a parent set and then additional queries are emitted for each parent/relationship access, multiplying round trips with result size.
- Loader choice depends on collection cardinality, row multiplication, parameter counts, query count, and response shape.
- It avoids large discard offsets but typically uses cursor/last-key navigation rather than arbitrary page-number jumps.
- Long transactions amplify locks, undo/redo, replication/recovery cost and make retries larger.
- The database optimizes actual SQL, not ORM intent; plan evidence reveals access paths and actual execution behavior.