How to Not be the One that Deploys that Slow Query to Production

Have you ever deployed code to production that slowed the database and the entire site down?
Did you get a feeling of anxiety when you just read that?

Well, it could be to do with your test environment.

Most places nowadays have test suites that will check for integration issues. But in very few will check for performance.

The issue lies with how developers are set up to develop code. They have their code on their local computer with a local copy of an empty database where they develop against. That setup will not give you any useful feedback about how your code will performs once its run against the production database.

How do you get Performance Feedback for your Queries?

Whenever you log into your database, lets say MySQL, you get 3 types of feedback:

1. Your result set
2. The number of rows
3. The time it took the query to run

(Postgres, by the way, has \timing.)

In order to get the right time for your query, you need to run it in on a database that is similar in hardware, parameters and more importantly, database size as your production database. 

Here is an example: if you take a SELECT query that you wrote that has 3 JOINs and you run it on a read-only slave DB server. You choose some decent sample variables from the existing data and you get a result of 0.3 seconds. Now, barring exceptions such as deadlocking, conflicts or server wide slowdown, there is a very high chance that that query will take 0.3 seconds when its run against the production database.

Once you have an environment to test against, you can run EXPLAIN on your query and make improvements till you are happy with it. 

But what do I do if I use an ORM?

Well, if you can output the query that the ORM will use and run that against a database, you will know how long it takes. Hopefully, you will be able to make improvements to your query through the ORM.

Conclusion

Make sure that in your company, you have a database to test against that is similar to production. If that is not available, see if you can get access to a read-only DB (sometimes there is one for back up purposes) and at least test your SELECT queries against it.

You can then relatively confidently, deploy it to live. At the very least, with a lot less stress.