Processing Large PostgreSQL Datasets Efficiently
Techniques for batching, indexing and safely processing large database tables with Python.
Scripts that work perfectly on a thousand rows often fail on ten million: memory runs out, queries time out or the database slows down for everyone else. These techniques keep large jobs fast and safe.
1. Never load the whole table
SELECT * FROM orders followed by fetchall() pulls every row into memory. Use a server-side cursor instead, which streams rows in chunks:
with conn.cursor(name="orders_cursor") as cur:
cur.itersize = 5000
cur.execute("SELECT id, total FROM orders")
for row in cur:
process(row)
2. Batch by key, not by OFFSET
LIMIT 1000 OFFSET 500000 gets slower with every page, because PostgreSQL still reads all skipped rows. Paginate with the primary key instead (keyset pagination):
SELECT id, total FROM orders
WHERE id > %(last_id)s
ORDER BY id
LIMIT 5000;
Remember the last id from each batch and continue from there. Every batch is equally fast.
3. Write in bulk
Inserting rows one at a time means one round trip per row. Use execute_values or COPY for large writes:
from psycopg2.extras import execute_values
execute_values(cur,
"INSERT INTO results (order_id, score) VALUES %s",
rows, page_size=1000)
COPY is faster still for millions of rows.
4. Index what you filter on
Check slow queries with EXPLAIN ANALYZE. If you see a Seq Scan on a large table for a filtered query, an index on the filtered column usually fixes it. Create indexes on live tables with CREATE INDEX CONCURRENTLY so writes are not blocked.
5. Keep transactions short
One giant transaction holds locks and can bloat the table. Commit after each batch, so a failure only loses the current batch and other users are not blocked.
6. Make jobs restartable
Store the last processed id in a small progress table. If the job stops, it continues where it left off instead of starting again.
Quick checklist
- Server-side cursors for reading
- Keyset pagination instead of OFFSET
- Bulk inserts or COPY for writing
- Indexes on filtered columns
- Small transactions and restartable jobs
Need help with a slow database or a large data pipeline? Get in touch.