Python and Postgres: Stop Pulling the Table Into Memory

The SQL article is the query. This is the Python that refuses to undo it. I keep seeing the same accident: a FastAPI route that looks clean, a SQLAlchemy model that looks clean, and a 200 MB working set because scalars().all() meant “all.” The database did its job. The app asked for a CSV of the warehouse.

I already wrote the five SQL shapes I use instead of a loop. This piece is the envelope: parameters, a pool, a timeout, and the discipline not to collect a table because the dataframe API is convenient.

1. The script that worked on 800 rows

Local dump, pandas, a groupby, a to_excel. Then someone points it at production “just for this report.” That is not a report. That is an extract of the primary database into a process that will be OOM-killed. If the answer is a number, a list of fifty rows, or a file you can stream, do not collect the table. I write the aggregation in SQL — or a materialized view if it is daily — and keep Python as the envelope: auth, parameters, format.

2. f-strings are not a query API

Building SELECT * FROM orders WHERE id = {order_id} with an f-string is how you donate the database. Use $1, %(id)s, or bound parameters. Every driver has them. SQLAlchemy text() has them. An LLM that concatenates SQL is the same bug with better manners. Same family: building IN lists by joining strings. Use ANY($1::int[]) or a proper bind. I do not “escape” my way out of this.

3. The ORM N+1 that looks like clean Python

for invoice in invoices: invoice.customer.name is a plus-one query per row. selectinload, a join, or a dedicated list query. “I’ll add a loader later” is how the list page dies after a trade show. I use an ORM for writes that match the unit of work — create order plus lines in one session. I use SQL for list pages and exports. Hybrid is not a failure. An ORM that hides four hundred queries is.

4. One connection per request is not a pool

Opening psycopg in every handler, or creating an engine inside the function, is how you hit max_connections during a deploy. One engine or one async pool for the process. A timeout on acquire. A checkout that cannot live across an await that calls another service. asyncpg or psycopg 3 for the hot path I control. SQLAlchemy if the team already thinks in sessions and I need migrations plus an identity map. I do not start three ORMs because a tutorial used Django.

5. Pandas is a second database — treat it like one

A pandas groupby on a two-gigabyte frame is a warehouse query you run on an API box. If you need pandas, the extract should already be narrow: the columns you use, the day you need, a COPY or a server-side cursor. chunksize exists. fetchmany exists. yield exists. I like pandas in analysis notebooks. I do not like it as the implementation of /stats. That route is SQL plus a typed dict.

6. What I actually ship

A FastAPI router. Parameters validated — dates, workspace id, a limit. A function that runs one parameterized query, often the window-function shapes from the other article. A pool. A statement timeout so a bad plan fails the request instead of the instance. No SELECT *. No silent .all() on an unbounded table. One request, one transaction, unless I have a named reason — outbox, a payment handoff. Isolation stays READ COMMITTED until I can name the anomaly I am afraid of.

Conclusion: glue is allowed, a second warehouse is not

Python is the best glue I have for HTTP and jobs. It becomes expensive when it impersonates Postgres. Write the query, bind the parameters, stream or aggregate, and keep the process small. The clever loop can wait. The database already had a better one — that is the other article.