Ramblings on optimizations, anti patterns and N+1
A lot of people ask me to teach them how to do query analysis and performance. The truth is: there isn’t a script to follow. The following paragraphs are a brain dump on what usually goes on my mind when I am debugging and analyzing.
Please comment on what you think I should focus on to cover here.
- It’s just a messy post with database-y stuff
- This post doesn’t have a conclusion, it is just me laying down my thoughts on performance and optimizations.
Query performance is a really difficult subject to talk about. Mostly because because SQL is a declarative language, leaving it up to the Optimizer to decide which way is the best to retrieve the information needed and that is based in so many variables.
The most common problem regarding optimization I see, comes not from the Database itself, but how we handle the requests on the application layer, the following for instance would cause N+1 problems:
Although seemingly innocent at first, this code could easily slow down performance on the database due to the amount of requests that would be made.
You also need to know about the intricacies of indexes, which one is the best, if you have a composite index, which should go first, and what happens if I only use one of the fields of a two column indexes in my search? Does it still uses the index somehow? Another rule of thumb is that if an index is a
BTREE, on a single column, you can use it either
Or better yet: why my transactions are taking so long to complete? Does it have too many indexes on the table? Is any other query locking table X?
Even a single
INNER JOIN could be highly costly if joining two large tables.
Why are you saving that JSON in a
TEXT field? Since we are on the subject, you really need the JSON in the relational database and not in a document store?
You don’t need to port all your data from PostgreSQL/MySQL to MongoDB if you want to have MongoDB on your stack. Everything has its place, relational data on relational databases and non-relational data on non relational databases. I even find unfair benchmarks between a SQL database and a NoSQL one. They were made to solve different problems, you can’t possibly have the same use case for both of them.
No, it’s not ok to have
category_n as columns on your
Avoid as much as possible nullable fields.
Relationships should also explicitly live on the RDBMS, not only on your model, if you have a
user_id on your
addresses table, tell the database so, naming it
user_id doesn’t automatically create the foreign key.
Or your migration should look something like this:
Line 24: adds to the table
addresses a foreign key from
And you, what you think is missing in this blogpost? What do you want to get deeper on?