This sort of thing hasn't really done much to make me like ORMs.
It seems like a lot of code to generate the tables in the first place and you STILL need to read the output scripts just to ensure the ORM isn't generating some garbage you didn't want.
That seems like a lot of extra effort when a simple migration service (such as liquibase) could do the same work running SQL directly. No question on "which indexes are getting created and why". No deep knowledge of Django interactions with sql. Instead, it's just directly running the SQL you want to run.
I’ve done a lot of interviewing and I’ve discovered that many devs (even experienced ones) don’t understand the difference between indexes and foreign keys.
My assumption is that people have used orms that automatically add the index for you when you create a relationship so they just conflate them all. Often they’ll say that a foreign key is needed to improve the performance and when you dig into it, their mental model is all wrong. The sense they have is that the other table gets some sort of relationship array structure to make lookups fast.
It’s an interesting phenomenon of the abstraction.
Don’t get me wrong, I love sqlalchemy and alembic but probably because I understand what’s happening underneath so I know the right way to hold it so things are efficient and migrations are safe.
An index is one thing (and important and good), but an FK allows you to completely eschew IO if done right.
In other words "I guarantee that all values in this list exist in that list" is a great simple optimization path and some sql engines can use it to avoid joining data or checking for existence at all.
During a work meeting I once suggested using a non-PK column in a Postgres database for a foreign key. A coworker confidently said that we shouldn't because joins would be slow. I pointed out that we could create an index on that column and they rebutted by claiming that PKs created some kind of "special" index. I didn't want to burn goodwill and so didn't push it further but it always struck me as silly.
Depending upon the database storage engine, available memory, and table size I could see there being _some_ performance hit if only PKs are used for statistics but I'd think that modern RDBMSes are smart enough to cache appropriately. Am I missing something?
I don't want to defend Django here, surely this should be categorized as a bug. But on the other hand, for this situation to come up you have to be the following:
- The kind of person to dive into the schema and worry about an unnecessary index
- Smart enough to heed Django's warnings and use `Meta.UniqueConstraint`
- Dumb enough to ignore Django's warnings and not use `Meta.Indexes`
I think it's funny that the kind of dev that 100% relies on the ORM and would benefit from this warning would probably never find themselves in this gritty optimization situation in the first place.
That being said, I enjoyed the article and learned something so maybe I'm the target audience and not them.
This seems like a fairly ORM 100-level thing. Read the documentation that comes with your ORM I guarantee it has a best practices section and a performance considerations section. N+1 is trivial and widely covered by every ORM as it’s a common thing. nb I use entity framework so ymmv, but EF Core especially newer versions with one-way updates and deletes have been good to me and migrations aren’t half bad either. The code is I think much easier on the eyes than python/django but that’s a personal pref.
> Don't use unique_together, use UniqueConstraint instead
> Always explicitly set db_index and add a comment
> Always check the SQL generated by migrations before applying them
> Provide reverse migration operations whenever possible
> Use concurrent index operations in busy systems
> Indexes on foreign keys are used indirectly by deletes
> Use partial indexes when possible
> Adjust the order of migration operations to reduce impact on live systems
> Explicitly state tables to lock when using select_for_update
> Use no_key=True when selecting a row for update to allow referencing objects to be created
This seems like you need much more non-essential knowledge than simply knowing SQL. Just not using an ORM bypasses so many footguns!
TBH, looking at the code to do this, it seems simpler to just bypass the ORM and use a thin wrapper to the DB.
Since the language in use here supports reflection, every query can automagically return a proper object with typed fields, at which point you gotta ask yourself "what value am I getting out of the ORM in exchange for all these footguns?"
12 comments
[ 3.1 ms ] story [ 42.2 ms ] threadThis is nice to know if you're using Django, but as important to note is that neither Postgres nor SQLAlchemy / Alembic will do this automatically.
For example, does the FK need to be the first field in a unique together?
It seems like a lot of code to generate the tables in the first place and you STILL need to read the output scripts just to ensure the ORM isn't generating some garbage you didn't want.
That seems like a lot of extra effort when a simple migration service (such as liquibase) could do the same work running SQL directly. No question on "which indexes are getting created and why". No deep knowledge of Django interactions with sql. Instead, it's just directly running the SQL you want to run.
My assumption is that people have used orms that automatically add the index for you when you create a relationship so they just conflate them all. Often they’ll say that a foreign key is needed to improve the performance and when you dig into it, their mental model is all wrong. The sense they have is that the other table gets some sort of relationship array structure to make lookups fast.
It’s an interesting phenomenon of the abstraction.
Don’t get me wrong, I love sqlalchemy and alembic but probably because I understand what’s happening underneath so I know the right way to hold it so things are efficient and migrations are safe.
Depending upon the database storage engine, available memory, and table size I could see there being _some_ performance hit if only PKs are used for statistics but I'd think that modern RDBMSes are smart enough to cache appropriately. Am I missing something?
- The kind of person to dive into the schema and worry about an unnecessary index
- Smart enough to heed Django's warnings and use `Meta.UniqueConstraint`
- Dumb enough to ignore Django's warnings and not use `Meta.Indexes`
I think it's funny that the kind of dev that 100% relies on the ORM and would benefit from this warning would probably never find themselves in this gritty optimization situation in the first place.
That being said, I enjoyed the article and learned something so maybe I'm the target audience and not them.
> Don't use unique_together, use UniqueConstraint instead
> Always explicitly set db_index and add a comment
> Always check the SQL generated by migrations before applying them
> Provide reverse migration operations whenever possible
> Use concurrent index operations in busy systems
> Indexes on foreign keys are used indirectly by deletes
> Use partial indexes when possible
> Adjust the order of migration operations to reduce impact on live systems
> Explicitly state tables to lock when using select_for_update
> Use no_key=True when selecting a row for update to allow referencing objects to be created
This seems like you need much more non-essential knowledge than simply knowing SQL. Just not using an ORM bypasses so many footguns!
TBH, looking at the code to do this, it seems simpler to just bypass the ORM and use a thin wrapper to the DB.
Since the language in use here supports reflection, every query can automagically return a proper object with typed fields, at which point you gotta ask yourself "what value am I getting out of the ORM in exchange for all these footguns?"