In my experience most people creating CRUD websites with Django will hit a CPU bottleneck.
You have to work pretty hard to hit a CPU bottleneck, and I can't imagine how you'd do that building a simple CRUD website.
Can you explain a bit more about how the people you know are hitting that bottleneck? I mean I've literally built CRUD apps on an esp32 microcontroller using python, and they work performantly. If real python with a real web-framework can't do the same then something is going horrible wrong.
But then maybe your problems are deeper that choice of language.
So many people immediately dismiss relational databases, then re-implement all that functionality in their apps with various bugs and performance bottlenecks.
ORM is another bad technique. It supposedly promises you that you don't need to know SQL to use, and that is true for the simplest examples, but you absolutely have to know it for anything less trivial, then you have to figure out how to write query in ORM to get a desired SQL statement (it makes it very difficult to use advanced SQL functionality), and that's not the end. ORM constantly will make unnecessary SQL queries and by default request all fields, even if you don't use them, adding additional performance bottleneck.
In my current workplace thanks to ORM we make on average 10 queries per request and at peak generate 1Gbps throughput from/to database because of those inefficiencies.
I think the way to go is SQL support in IDE, I recently saw PyCharm with DataGrip where if you configure it to connect to a database it will start recognizing SQL in string statements and start treating it like code (so you now have autocomplete, refactoring etc). I think this is probably the proper way to do it and I wish that other IDEs would have similar features.
please support this assertion with an ORM whose documentation promises this.
> ORM constantly will make unnecessary SQL queries and by default request all fields,
as does "SELECT * FROM table" if you don't write out the fields and use a buffering database adapter (which is the case for nearly all Python database adapters), so, when using an ORM, you need to give it instructions over what columns you need to fetch. This is not unusual nor even anything a library could possibly guess for you if you do not give it this intent.
ORMs that are more honest about this, such as SQLAlchemy, are generally better than those that try to pretend that you really are dealing with entities.
But anyway even query builder is not that great. Recently used PyCharm's integration with DataGrip. Basically the way it works is that if you configured a database in your project and let PyCharm fetch its schema, suddenly the IDE started recognizing the SQL statements providing autocomplete not only the statements but also table names etc, it also offered refactoring which created migration scripts.
After using that I think that's the proper way of solving the impedance mismatch and at that point you no longer need ORM or query builders. I hope other IDEs will start doing the same thing.
Good point: I'm no longer doing object mapping in that case. :) Though I think the most complicated part of the process that the Django "ORM" does for you is build the query. Maybe it should be called Django query generator.
I don't think any ORM would officially claim that, but many people decide to go that way to avoid SQL. For many simple examples it looks simpler than SQL.
I also experimented myself and wrote my code using SQLAlchemy's ORM. Then edited my code to use psycopg2 natively. I realized that SQLAlchemy didn't save any code for me, in fact it was more verbose. It also heavily encouraged me to get the raw data from database and do processing of it in the application. It also did unnecessary extra queries.
So with it now I need to:
1. still understand SQL
2. trying to figure how to write the SQL statement using ORM
3. figure out how to make ORM not to do extra queries
> as does "SELECT * FROM table" if you don't write out the fields and use a buffering database adapter (which is the case for nearly all Python database adapters), so, when using an ORM, you need to give it instructions over what columns you need to fetch. This is not unusual nor even anything a library could possibly guess for you if you do not give it this intent.
My problem is that in addition to knowing SQL, I also need to know how to do in that specific ORM that's for the specific language I'm using.
Some things:
- to make my application scalable I want two endpoints one that is read only, that goes to replicas and one that goes to master for modification. That way in my application I have full control which queries go to replica and which need master
- use datatypes that are recommended by the database
- do upsert
- do select and only return fields that I need
- perform insert and return only fields that I want (my ORM was performing another select)
- ORM also does bunch of queries that I don't need, I don't want it to add extra load to database if I never use the results of it
Those are very simple things, but I'm also curious how you can do some advanced operations like group by having, window functions, CTEs, aggregate functions that return JSONB structure (so you don't have to send duplicate data and avoid N+1 queries). How do you use PostGIS?
You don't! That is not what an ORM is for. Use SQL. Any decent ORM with allow you to integrate SQL.
Check out the Fluent (query builder) and Eloquent (ORM) from Laravel. They allow all the features you want.
It’s possible, of course, but I’ve usually seen it as a symptom of not having a good culture around monitoring and troubleshooting — e.g. I remember someone porting an entire site to Jinja2 alleging performance wins, and it’s true that template rendering got (IIRC) 10-15% faster but they’d missed that 99.999% of the total runtime was being taken up by unoptimized database access generating many thousands of queries.
What I mean is: when you start optimising, no matter how well you do, you will eventually hit a CPU performance wall. Then you will realise that only a rewrite will get you out of that hole, and by then it will be late.