A Deep Dive into PostgreSQL Indexing
Ibrar Ahmed is a Senior Software Architect in Percona LLC. 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 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 modules. In the 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 gave more than fifteen PostgreSQL talks in the last one year in various parts of the World (PostgreConf-EU, PostgreConf-ASIA, PostgreConf-NY, Percona Lives, etc ). He also authored multiple books on PostgreSQL.
Indexes are a fundamental feature of relational databases, and PostgreSQL offers developers and designers a rich collection of options. To take advantage of these entirely, users need to understand the basic concept of indexes to compare the different index types and apply them 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 what 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 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.
- 2022 April 8 14:10 PDT
- 50 min
- Silicon Valley 2022