Hands-On: Database Scaling

In Part 3, we found concrete proof: the SQLite version of task-tracker-api collapses completely under high load, with logs clearly pointing to one cause — QueuePool limit of size 5 overflow 10 reached, connection timed out. Caching in Part 4 helped absorb read load, but didn't fix this root cause. Now we get to the heart of it: migrating to Postgres, indexing, and sizing the connection pool correctly.

Why SQLite Has to Go for a Scalable System

SQLite is a single-file database — great for small apps, prototyping, or embedded systems, but by design it's not built for many processes accessing it concurrently over a network. Two main problems for what we need:

Postgres solves both: a database server running as a separate process, accessed over the network (so it can be shared by many application instances), with far more granular locking.

Migrating to Postgres

The change to our code is actually small, because SQLModel/SQLAlchemy already abstracts away most database-specific details. All that changes is the connection string and driver:

# app/database.py
from sqlmodel import Session, SQLModel, create_engine
from app.config import settings

engine = create_engine(
    settings.database_url,     # "postgresql+psycopg2://user:pass@host:5432/tasks"
    pool_size=settings.db_pool_size,
    max_overflow=settings.db_max_overflow,
    pool_pre_ping=True,
)

pool_pre_ping=True tells SQLAlchemy to check that a connection is still alive before using it — useful because a network connection to Postgres (unlike a local SQLite file) can drop mid-flight (a network blip, a database restart), and this prevents us from using a connection that's already dead.

For local development, run Postgres via Docker (we'll use the exact same docker-compose.yml in Part 7):

docker compose up -d postgres redis

Indexing — Helping the Database Find Data Without Scanning Everything

An index is an extra data structure that helps the database find the rows it's looking for without having to check EVERY row in the table — like a book's table of contents, versus having to read from page one to find a single topic.

Our Task model has an index on the done column, since that's the most commonly used filter:

class Task(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    title: str
    done: bool = Field(default=False, index=True)   # <- explicit index
    priority: Priority = Priority.medium
    created_at: datetime = Field(default_factory=lambda: datetime.now(timezone.utc))

To prove the difference, we fill the table with 205,000 rows (a realistic condition for an app that's been in use a while), then compare EXPLAIN ANALYZE for a query hitting an indexed column (done) vs. one hitting a column without an index (priority):

EXPLAIN ANALYZE SELECT * FROM task WHERE done = true;

Index Scan using ix_task_done on task  (cost=0.29..645.09 rows=4292 width=33)
                                        (actual time=0.031..2.134 rows=4065 loops=1)
  Index Cond: (done = true)
Execution Time: 2.384 ms

EXPLAIN ANALYZE SELECT * FROM task WHERE priority = 'high';

Seq Scan on task  (cost=0.00..4261.12 rows=65650 width=33)
                   (actual time=0.312..17.104 rows=66514 loops=1)
  Filter: (priority = 'high'::priority)
  Rows Removed by Filter: 138536
Execution Time: 19.159 ms

Two lines here matter most:

Rule of thumb: index columns that are frequently used in WHERE, ORDER BY, or JOIN clauses. But don't index every column carelessly — an index speeds up reads but slows down writes (every INSERT/UPDATE also has to update the index) and takes up extra storage. An index is a trade-off, not "more is always better."

Connection Pooling — Sizing It Correctly

Now back to the problem that made our SQLite setup collapse in Part 3: the connection pool. Its size is set explicitly via environment variables:

# app/config.py
class Settings(BaseSettings):
    db_pool_size: int = 5
    db_max_overflow: int = 10
    ...

pool_size is the number of connections kept permanently open (ready to use at any time). max_overflow is extra temporary connections opened once pool_size is full, then closed again once no longer needed. Total max concurrent connections = pool_size + max_overflow.

Let's test: with a reasonable dataset (50 rows) and the defaults (pool_size=5, max_overflow=10 → 15 max connections), we send 300 requests, 100 of them concurrent:

Success / Failed  : 300 / 0
Throughput        : 52.6 req/s
P95 latency         : 5051.1 ms

Now raise the pool to pool_size=30, max_overflow=40 (70 max connections) and repeat the exact same test:

Success / Failed  : 300 / 0
Throughput        : 50.3 req/s
P95 latency         : 4861.6 ms

Almost no difference at all. This is surprising — shouldn't the connection pool be the main bottleneck, exactly like the total collapse we saw in Part 3? It turns out that, in this case, enlarging the pool doesn't help much. Why?

The clue is in our own query — with only 50 rows of data, each database query finishes in a few milliseconds. A connection is used briefly then immediately returned to the pool, so the pool rarely stays truly full for long, even at a small size. That means there's ANOTHER bottleneck limiting how many requests can be processed concurrently — something that happens BEFORE the request ever touches the connection pool at all. We'll dig into exactly what that is in the next article, because the answer changes how we should read every benchmark we've run so far.

An important note about SQLite in Part 3: there, the pool collapsed completely (497 of 500 failed) — far worse than what we see here. The difference isn't just pool size (both defaulted to 15 connections); SQLite adds another layer of trouble — file locking that makes every operation much slower and prone to waiting on each other, which worsens the queue at the pool. Postgres, with far better concurrency control, means a pool that's "a bit short" doesn't immediately spiral into total collapse — it just gets slower. Still, sizing the pool sensibly for the expected load matters, especially once we have many application instances sharing one database in Part 7.

Summary