Meeting in Montreal: Developer U plan(ner) patches

Remember our internal training program for up-and-coming PostgreSQL developers? Last week, most of the group met up in person for the third time! Exactly one month after PGConf.Dev in Vancouver, where Andrew Dunstan shared some great takeaways from running the program, Canada once again became the ultimate meeting spot for our aspiring hackers. And soccer fanatics.

The program

After Amul Sul and Alvaro Herrera joined as trainers for the first week, and Peter Eisentraut for week 2, last week Robert Haas joined to give an introduction to the PostgreSQL Optimizer, to PostgreSQL IPC and Shared Memory, and the Wire Protocol.

Robert Haas also runs the PostgreSQL Hacking Discord and frequent workshops for the broader community. That Robert has experience with mentoring shows: he was active in the group’s Slack channel asking for feedback on what people were looking to learn from him specifically, as well as doling out “homework”. Robert: “we had good discussions, I enjoy teaching.”

“Robert's sessions on the planner, the wire protocol and IPC were just terrific”. Andrew was very content. Andrew himself talked about creating extensions, using an example he has worked on (and plans to blog about) called pgyaml. He also covered Table Access Methods and Index Access Methods, and some of the current limitations of those access methods.

Jonathan Gonzalez, one of the students, presented a review harness for assessing AI generated code. Because the group was already familiar with tools like his harness, they immediately began exploring customization options for their specific use case. In a demo the harness was pointed at one of Andrew Dunstan’s patches and quickly found some improvements.

Ian Barwick’s (Senior Staff SDE I) view: “The AI-assisted review system Jonathan’s been working on, is sure to be very useful.”

Jonathan’s work is not public yet, but it might be in the near future.

Feedback from the participants

Asked about what stood out to him from this week, Ian answers that the opportunity to meet up in person with co-workers is something he very rarely gets the chance to do. Even virtually, since Ian is based in Japan.

For Vaibhav Dalvi (Staff SDE) a major highlight was the deep-dive session on the Optimizer led by Robert Haas. “As someone who usually works remotely, having the opportunity to discuss these complex internals in person was fantastic. Robert walked us through how the PostgreSQL planner generates all possible paths and ultimately selects the best execution plan. He also mapped out the key internal functions and data structures the optimizer relies on, and explained the dynamic programming principles Postgres uses before diving into the core code.” Ian adds: “What really set this session apart was the provision of some advance "homework" by Robert Haas, an excellent opportunity to dive into the subject matter in advance.”

Ian: “Planner-related issues is an area of PostgreSQL which is challenging to get into from scratch, but Robert shared very useful insights which will help me in the future.”

Mario Gonzalez (Senior Staff SDE II) says the deep dives into the wire protocol and IPC were super valuable. “Because I like OS level stuff, I think what I learnt about this could help me find areas where I can sink my teeth into.”

Once he’s back at his desk, Vaibhav plans to dive deeper into the optimizer source code to solidify his understanding of the execution paths discussed. He also wants to review the presentations on Postgres IPC and Shared Memory. “While I now clearly understand the architectural differences between Main Shared Memory and Dynamic Shared Memory, my next step is to trace their specific use cases directly within the Postgres codebase to see how they operate in practice.”

Since Mario learned that PostgreSQL sends WAL files in a sequential way, he’s been thinking about whether it'd be worth it to evaluate if one could send multiple files at once. That'd be especially useful in situations when a replica is behind and it needs to catch up. “Sometimes the network allows sending more data and the disks/units allow writing data faster but it seems that PostgreSQL would be self-causing a bottle neck by not sending more data.” In addition to that bit of research Mario is working on the pg_plan_advice module to suggest adding cardinality() to the planner.

Ian also hopes to find time to look at the PostgreSQL planner-related stuff. “I maintain one of PostgreSQL's foreign data wrappers, which does hook into the planner system, and I think there are some improvements I could make with deeper knowledge about that.”

Bryan Green unfortunately had to sit this one out but has exciting news. Since joining the Developer U program he moved to Andrew’s team and gets to spend significantly more time directly on PostgreSQL development.

What’s next?

In the immediate future, Ian plans to submit his patch for a retail DDL function. “The issue with these is that it took a while for the first patches to be submitted and accepted, as they required some common infrastructure to be added, which needs to be there before further patches can be submitted.” He was pleased to see that the "regdatabase" function he contributed earlier to PG 19 is used in the pg_get_database_ddl() function, which is already committed!

Vaibhav plans to start reviewing a patch suggested by Robert Haas regarding the planner, which focuses on logging the plan of the currently running query, but only after his v19 commitments for EPAS conclude. He also wants to spend time working on a patch idea Robert suggested as a learning project for Vaibhav specifically: adding an enable_unparameterized_nestloop GUC.

At PGConf.Dev this year Andrew Dunstan shared the motivation behind the program, and the lessons learned along the way. He believes any company could potentially run a similar program and that would be a good (great) thing for the sustainability of the project, bringing new people in. In any case EDB will be continuing the effort, and we’ll continue to report on this blog.