- Student: Ahmed Ibrahim
- Organisation: PostgreSQL
- Mentors: Gurjeet
pg_adviser / index_adviser: Recommend Potentially Useful Indexes
Google Summer of Code Project could be found here
1- The Index Adviser (pg_adviser) is an extension for PostgreSQL that suggests potentially useful indexes for given queries. This project aims to improve the extension by adding support for all active versions of PostgreSQL and improving the recommendation of new index types (GIN, GiST and others)
2- Enhancing Database Restoration with FORCE Option in pg_dump and pg_restore
The implementation phase of the project focused on enhancing the Index Adviser (pg_adviser) extension for PostgreSQL. This involved adding support for all active versions of PostgreSQL.
The implementation was done in C, with a strong emphasis on code refactoring and compatibility adjustments across different PostgreSQL versions.
- Version Support The first major aspect of the implementation was to ensure compatibility with all active versions of PostgreSQL, ranging from version 11 to version 16. This involved in-depth analysis of the changes that occurred in the PostgreSQL codebase over these versions.
This included
- Identifying deprecated functions, obsolete syntax, and deprecated API calls.
- Replacing them with the equivalent code constructs for each version of PostgreSQL.
This was critical to ensure that the pg_adviser extension remained functional and efficient across the entire spectrum of supported PostgreSQL versions.
The traditional approach to restoring a database dump involves dropping the existing database and then recreating it from the dumped data. However, when dealing with live or production databases, dropping the database would disconnect active users, causing disruptions and potential data integrity issues. PostgreSQL introduced the FORCE option for the DROP DATABASE command starting from version 13, allowing users to forcibly terminate active connections before dropping the database. So the target here was to add this FORCE option to pg_restore.
Participating in GSoC has been an enriching journey that deepened my understanding and skills in several aspects:
-
Database Internals: Gaining insights into PostgreSQL internals, understanding how indexes function, how query costs are calculated, and knowing how the query planner work have expanded my knowledge of database systems.
-
Soft Skills: This experience has honed my soft skills, particularly communication. Engaging with mentors and the PostgreSQL community improved my ability to communicate and collaborate effectively.
-
Feedback Incorporation: Learning to receive and apply feedback from people and the community has strengthened my abilities a lot.
I'm very grateful to my mentor, Gurjeet, and the PostgreSQL community for their invaluable support throughout this project. Weekly meetings with Gurjeet have provided essential guidance, enhancing my skills and understanding of PostgreSQL internals. This achievement wouldn't have been possible without his help. I look forward to staying connected with PostgreSQL and continuing to contribute to this fantastic organization.