Intermediate Query Optimization Tips

1. Never fixate your mind on one model and think it’s the best. Always be open and try out different models and load test each model and do a POC always before deciding on a model. You never know what the results would be.

2. Query plan - Query plan is your best bet in optimizing queries. Depending on your db, try to optimize queries, learn about different joins and how the order of joins might affect your query. This is very specific to db, optimizing a query for one db might not work for another.

3. Criteria - always try to use indexed columns in criteria. Try to avoid full text search in db. Always use lucene (or any other similar things) for text search and do all your ranking or text search based on your requirement. Quick tip: Try to use indexed columns and try to change your product behaviour for some valid cases, for example for searching tags - Let’s say we have a table for beauty products and we have tags such as skincare, haircare and bodycare. Now if you want to search for bodycare products. Make sure your search bar for tags searches for tags alone first and then allow the user to select an entire tag bodycare - instead of say they type “body” and you try to list all products that has tags that contain the letters “body”. You might think this is very obvious but when you learn about your product - you can step out and think if we can use tags here, we can optimize it.

4. Do not index all columns, indexing is costly - if you index all columns it’s not useful - it affects your write/delete speed. Try to be mindful of indexes and if indexes are not used remove it.

5. If you have a large table and you’re facing performance issues, try to think of cleanups. Think how much of the data is actually being used - check only if recent data is consumed a lot. If yes, try to archive old data in a different table.

6. Try to avoid unions as much as possible. Unions are very costly for sorting and searching.

7. Always add a limit constraint to queries whenever it’s possible. Depending on your product use case, your limit could be modified.

8. Query hints are also a nice way to optimize certain queries - you can read your query plan and then if nothing works, you can go for query hints and optimize it based on your query plan. They are really useful in certain cases.

Also I’m attaching some of the resources that were really helpful.

1. https://www.youtube.com/watch?v=XB2lF_Z9cbs - Zerodha - these guys have pushed PostgreSQL to its limits and it’s really great how much they have scaled with just PostgreSQL. Again, their use case could be very different from a lot of products and we cannot afford to do what they do, but nevertheless, what they have achieved is super inspiring.

2. https://www.postgresql.org/docs/ - Best thing to learn about db is their docs. Spend some mundane time reading the docs, you’ll never regret it.