Indexes are a basic feature of relational databases, and PostgreSQL offers a rich collection of options to developers and designers. To take advantage of these fully, users need to understand the basic concept of indexes, to be able to compare the different index types and how they apply to different application scenarios. Only then can you make an informed decision about your database index strategy and design.
One thing is for sure: not all indexes are appropriate for all circumstances, and using a â€˜wrong' index can have the opposite effect to that you intend, and problems might only surface once in production. Armed with more advanced knowledge, you can avoid this worst case scenario!
We'll take a look at how to use pg_stat_statment to find opportunities for adding indexes to your database. We'll take a look at when to add an index, and when adding an index is unlikely to result in a good solution.
So should you add an index to every column? Come and discover why this strategy is rarely recommended as we take a deep dive into PostgreSQL indexing.
Ibrar Ahmed is a Senior Software Engineer in Percona. Prior to coming to open source development, he had vast experience in software design and development. The main focus was on the system level embedded development. After joining EnterpriseDB, an Enterprise PostgreSQL's company in 2006 he started his career in open source development specifically in PostgreSQL. He has contributed to the PostgreSQL community as well as other open source communities. His contribution is from the main performance feature enhancements to various PostgreSQL's modules. In database field, he has experience in other well-known databases MySQL, Oracle, and NoSQL databases such as MongoDB and Hadoop. His experience is not limited to core databases, but with the tools related to databases like Hive, HBase and Spark. He also worked on integrating these tools with PostgreSQL. He joined Percona in the month of July 2018. He also authored multiple books on PostgreSQL.