Lesson 7 - Pagination, Bulk Operations, and Performance

Welcome to Performance#

Fourteen employees fit comfortably in memory, and a query without an index runs instantly. Neither is true once a table has millions of rows. This lesson covers the patterns that matter once your data grows: paging through results instead of loading them all, inserting in bulk instead of row by row, indexing the columns you filter on most, and streaming a result set instead of materializing it all at once.

By the end of this lesson, you will be able to:

• Page through a large result set with limit() and offset()
• Insert many rows in a single round trip
• Speed up lookups on a column with Index
• Stream a result set in batches with yield_per() instead of loading it all into memory
• Log the exact SQL SQLAlchemy generates, to spot slow or unexpected queries

Let’s make this database ready to scale.

Pagination with limit() and offset()#

You met .limit() in Lesson 3 to cap a result to a fixed number of rows. Combined with .offset(), it lets you fetch a result set one “page” at a time — the same pattern behind any paginated API or infinite-scrolling list:

from sqlalchemy import create_engine, MetaData, Table, select

engine = create_engine("sqlite:///company.db")
metadata = MetaData()
employees = Table("employees", metadata, autoload_with=engine)

page_size = 5
with engine.connect() as conn:
    for page in range(3):
        stmt = (
            select(employees.c.name)
            .order_by(employees.c.id)
            .limit(page_size)
            .offset(page * page_size)
        )
        rows = conn.execute(stmt).fetchall()
        print(f"page {page + 1}:", [row.name for row in rows])

Output:

page 1: ['JOHNSON', 'HARDING', 'TAFT', 'LINCOLN', 'GARFIELD']
page 2: ['POLK', 'GRANT', 'JACKSON', 'FILLMORE', 'ADAMS']
page 3: ['WASHINGTON', 'MONROE', 'ROOSEVELT']

.offset(page * page_size) skips ahead by however many rows the previous pages already covered. An .order_by() is essential here — without one, SQL does not guarantee any particular row order, and consecutive pages could overlap or skip rows unpredictably.

Speeding Up Lookups with Index#

Every query in this module that filters on department_id has to scan the whole employees table to find matching rows — fine at 13 rows, unacceptably slow at 13 million. An Index tells the database to maintain a fast lookup structure for a column:

from sqlalchemy import Index, inspect

idx = Index("ix_employees_department_id", employees.c.department_id)
idx.create(engine, checkfirst=True)

print([i["name"] for i in inspect(engine).get_indexes("employees")])

Output:

['ix_employees_department_id']

checkfirst=True makes the call safe to run more than once — it skips creating the index if one with that name already exists, the same idea as metadata.create_all()’s behavior toward existing tables. Add an index to any column you filter or join on frequently; the cost is a small amount of extra storage and slightly slower writes, in exchange for much faster reads.

Bulk Inserts#

You already know how to pass a list of dictionaries to insert() — that is a bulk insert. The reason it matters for performance is worth spelling out: each .execute() call is a round trip to the database, so combining fifty rows into one call is far faster than fifty separate calls:

from datetime import date
from sqlalchemy import insert

new_rows = [
    {"id": 200 + i, "name": f"CANDIDATE{i}", "job": "INTERN", "hiredate": date(2024, 6, 1), "salary": 20000}
    for i in range(50)
]

with engine.connect() as conn:
    result = conn.execute(insert(employees), new_rows)
    print("rows inserted:", result.rowcount)
    conn.commit()

Output:

rows inserted: 50

Clean up so later work sees the same 13 employees:

with engine.connect() as conn:
    conn.execute(employees.delete().where(employees.c.job == "INTERN"))
    conn.commit()

Streaming Large Result Sets#

.fetchall() loads every matching row into memory at once — fine for a few thousand rows, risky for a query that might return millions. .yield_per(n) tells SQLAlchemy to fetch rows from the database in batches of n, and .partitions() lets you process one batch at a time instead of one row at a time:

with engine.connect() as conn:
    result = conn.execute(select(employees.c.name)).yield_per(5)
    batch_count = 0
    total_rows = 0
    for partition in result.partitions():
        batch_count += 1
        total_rows += len(partition)
        # process each batch here — write it out, transform it, etc.
    print("batches:", batch_count, "total rows:", total_rows)

Output:

batches: 3 total rows: 13

At 13 rows this is only a demonstration, but the pattern is exactly what you would use against a multi-million-row table: your program’s memory usage stays bounded by the batch size, not by the size of the whole result.

Logging Generated SQL#

When a query feels slower than it should, the fastest way to investigate is to see the actual SQL SQLAlchemy sent to the database. Enable it through Python’s standard logging module:

import logging

logging.basicConfig()
logging.getLogger("sqlalchemy.engine").setLevel(logging.INFO)

with engine.connect() as conn:
    conn.execute(select(employees.c.name).where(employees.c.department_id == 2)).fetchall()

Output:

BEGIN (implicit)
SELECT employees.name
FROM employees
WHERE employees.department_id = ?
[generated in 0.00016s] (2,)
ROLLBACK

The log shows the exact SELECT statement, the bound parameter value, and how long SQLAlchemy took to generate it — invaluable when you suspect a query is doing more work than you expect, or want to confirm an index is actually being used by comparing timings before and after adding one.

Practice Exercises#

Continue using company.db and the employees table.

Exercise 1: Page Through by Salary#

Write a paginated query, sorted by salary descending, with a page size of 4. Print the first two pages.

# Your code here

Exercise 2: Index a Different Column#

Create an index on job, since several queries in this module filter by it, then confirm it exists with inspect(engine).get_indexes("employees").

# Your code here

Exercise 3: Bulk Insert, Then Bulk Delete#

Insert 20 temporary rows with job="TEMP" in one call, confirm the count with func.count(), then delete them all in one call and confirm the count is back to 13.

# Your code here

Summary#

You paginated a result set with .limit() and .offset(), added an Index to speed up lookups on a frequently-filtered column, and confirmed bulk inserts save round trips compared to inserting one row at a time. You streamed a result set in batches with .yield_per() and .partitions() to bound memory usage, and turned on SQLAlchemy’s query logging to see the exact SQL and timing behind any query.

Key Concepts#

• .limit(n).offset(k) — fetches page k / n of an ordered result set; always pair with .order_by().
• Index(name, column) — creates a fast lookup structure for a column; checkfirst=True makes creation idempotent.
• Bulk insert — passing a list of dicts to insert() in one execute() call, avoiding one round trip per row.
• .yield_per(n) / .partitions() — streams a result set in batches instead of loading it all into memory at once.
• logging.getLogger("sqlalchemy.engine") — logs every SQL statement SQLAlchemy generates, along with its execution time.

Why This Matters#

Code that works correctly on a fourteen-row demo table can fall over in production the moment a table crosses a few million rows — not because the logic is wrong, but because it was never designed to bound its own memory use or database round trips. The patterns in this lesson are exactly the ones you reach for when that happens: page instead of loading everything, batch instead of looping row by row, index the columns your queries actually filter on, and log the SQL when something feels slow instead of guessing.

Continue Building Your Skills#

You have now covered every core technique SQLAlchemy offers, from connecting to a database through the ORM and performance tuning. In the final lesson, you will put all of it together in a guided project: loading real U.S. Census data, running aggregate queries with SQLAlchemy, and visualizing the results.