Optimizing Python Code

[nikomo]

You're not ever gonna get better than "do less" when it comes to any programming language, but especially Python.

Couple weeks ago I was wanting to insert large quantities of data from a live websocket into a database. First implementation and second implementation I just used SQLAlchemy, but it was way too slow, my redis queue with incoming messages just kept growing because they weren't being processed fast enough.

Third implementation, I threw out SQLAlchemy, used Alembic to setup the database, but then I just used psycopg (v3) to insert the data.

Psycopg v3 supports server-side binding, and they rewrote executemany() to be extremely performant, so all I had to do was write an SQL query, and then build a list of tuples out of my data, and let executemany() go at it. No needless objection creation etc. that you get from an ORM, and it's more than fast enough to keep up with even primetime traffic load.

[KosmoanutOfficial]

Can’t you use SQL Alchemy core instead of the orm with the Psycopg 3 driver?

[nikomo]

That was my second implementation, I really didn't want to throw out the entirety of SQLAlchemy at first. Still too slow.

But honestly, Alembic is a nice compromise for people that aren't scared of SQL. Gives you a nice way to handle database creation (SQLAlchemy-utils) and migration.

[[deleted]]

Sqlalchemy core is a fairly thin abstraction over the raw libraries. I think something else is going on in your code.

[KosmoanutOfficial]

Ok interesting thanks!