Your PostgreSQL insert strategy deserves more thought than the default advice gives it. That advice has collapsed into one line, use an ORM and move on, and on high-volume workloads it quietly wrecks your throughput. I have spent years refactoring backend services where the clean-code approach became the bottleneck, turning what should be a 10-second ingestion into a 30-minute ordeal.
Too many devs chase micro-benchmarks without knowing which abstraction layers they are paying for. On a standard CRUD app, the ORM is your best friend. When you are backfilling analytics or syncing an external API, you need a feel for the spectrum between safety and raw throughput.
The performance spectrum: ORM, Core, and driver
Choosing an insert strategy is really choosing where to spend your CPU cycles: on mapping Python objects, or on moving raw bytes into the database.
- SQLAlchemy ORM is the Ferrari. Expensive, high-maintenance, beautiful. It tracks state and relationships for you, and that bookkeeping is exactly what makes bulk operations crawl.
- SQLAlchemy Core is the Jeep. Rugged and versatile. It abstracts the SQL dialect so you are not fighting syntax quirks, and it lands somewhere sensible between safety and speed.
- Psycopg3, the driver itself, is the firehose. Low-level power. If you need to insert 2 million records per second, you drop the abstractions and use
COPY.
On a recent data-heavy integration we were chasing API performance boosts. We started with plain ORM adds, which were fine at 100 rows. At 100,000 rows the session management leaked memory badly enough that we rewrote the whole ingestion pipeline.
Row by row vs. bulk optimization
The most common mistake is looping over a list and calling session.add() once per item. That creates a race condition for your database’s WAL (Write-Ahead Log) and burns time on network round-trips.
# The "Bad" Way - Row by Row
for record in large_dataset:
new_row = User(name=record['name'], email=record['email'])
session.add(new_row)
session.commit()
# The "Refactored" Way - Using Core for throughput
from sqlalchemy import insert
engine.execute(
insert(User.__table__),
large_dataset # List of dicts
)Dropping to Core skips the cost of building a Python object for every row. If you are really pushing the limits, Core can still be too slow, since it has to compile the SQL statement.
When to use Psycopg3 COPY
For a genuine firehose workload, skip INSERT syntax altogether. COPY is the fastest way to get data into Postgres. The official Psycopg3 documentation notes that write_row() on a copy object handles millions of records with minimal overhead.
# Using Psycopg3 COPY for maximum power
with cursor.copy("COPY users (name, email) FROM STDIN") as copy:
for record in dataset_tuples:
copy.write_row(record)That keeps the conversion work between Python and the database engine to a minimum. It also helps with REST API performance problems where the real cost is database write latency rather than the network.
If this kind of database tuning is eating your dev hours, I can take it on. I have been wrestling with WordPress and high-scale backend architecture since the 4.x days.
How I choose
Faster is not worth much if nobody can maintain the result. Use the ORM where the business logic lives and correctness matters most. Move to SQLAlchemy Core for ETL work and batch transformations. Go down to the raw driver only when you are up against hardware limits and every millisecond shows up in the numbers. There is no prize for driving a Ferrari through a forest.
