PostgresConf 2019 Summary

jeremy.schneider's picture
https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=600 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-2.jpg?w=150 150w" sizes="(max-width: 300px) 100vw, 300px" />

PostgresConf 2019 in New York City is a wrap! Now that I’ve had a few days to recover a little brain capacity, I think I’m ready to attempt a summary. I love conferences for the learning opportunities… inside of sessions and also outside of them. Being the largest PostgreSQL-centered conference in the United States, PostgresConf offers unique opportunities to:

  1. Watch seasoned technical presenters go deep on PostgreSQL internals.
  2. Hear serious users present the latest about how they are using PostgreSQL and what they’ve learned.
  3. Connect businesses who are doing redundant work so they can collaborate instead of each repeating the other’s work.

Last week I published a summary of the first two days of the conference which included the summits, tutorials and training sessions.

I tried to keep a few notes during the final three days of the conference as well so I could share a bit about my experience this year. But before I dive into the day-by-day summary… first, a few short high-level points.

Highlights

Who attends PostgresConf? During my session about Wait Events I asked two questions to scratch the surface a little bit. First, it seemed clear that the vast majority of session attendees had been working with relational databases for more than a decade and a number for more than 25 years. Second, it seemed clear that the vast majority of session attendees had worked seriously with a relational database other than PostreSQL as part of their job. Of course I expected these things would be true for some attendees, but it surprised me just how many – it seemed to me like almost everyone in the room.

https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-1-1.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />

What was great about PostgresConf? First and foremost, I give PostgresConf an “A-plus” … highest possible marks … on the two things I personally value the most from a user conference: technical content and user representation. I went to as many sessions as I could and I can personally vouch that there was quality material. And I met a bunch of top notch engineers from a wide variety of industries using PostgreSQL in serious ways.

Besides that, I can point out two other general things I thought PostgresConf did especially well:

  • Running a special track for regulated industries using PostgreSQL. They support this class of users not only in the topic selection, but even in the room setup – for example rules that prohibit recording.
  • Encouraging as many different people as possible to attend and participate. The conference code of conduct was taken seriously with a third party to independently receive, review and investigate any reports. There was an excellent general session and a panel discussion that started conversations – which may not have otherwise happened – around workplace dynamics and common assumptions we make. (If I could hazard summarizing one takeaway: I still do things at work which unintentionally assume that my co-workers are similar to me in subtle ways where they might actually differ!) Finally, on Friday afternoon, a time and space was provided for people to have conversations about career trajectories and the job market. With the rapidly growing demand I see for PostgreSQL skills, we’re going to need everyone who can help! So lets listen to (and become better advocates for) those in our industry who aren’t sure whether they can fully be part of it.

Wednesday March 20

Interesting conversations:

  • Two large, established software vendors whose software ships with embedded PostgreSQL as a default data store (one of these software packages supports a number of customer-managed DB backends but many customers use the default embedded PostgreSQL database).
  • Major University (over 150 years old) actively working to migrate critical backend business systems off commercial databases and onto open source. They have learned that you need to look at the stack as a whole; it proved infeasible to change the backend of applications that are tightly integrated with one specific database when both are sold by a single vendor. Changing the application itself is sometimes necessary.
  • Two FinTech companies: a credit/lending support company and a brokerage both already relying on PostgreSQL in their business.
  • Medium-sized non-profit (100ish people in IT), historically a commercial database shop, exploring open source databases and cloud-based architectures.
  • Two individual DBAs that I didn’t catch their industry. One was following up from Monday’s Hands-On Lab and the other was asking about how to identify applications which might be good starting points for migrating to cloud-based PostgreSQL. We talked about migration success factors like application complexity and database procedural code.

I was able to attend three sessions:

  • The opening general session. It was fun to hear the PostgresConf organizers talk a bit about how the conference is continuing to grow (mark your calendars: they already announced PostgresConf 2020 will be March 23rd through 27th!) and right after that to hear Marc Linster fill in the back-story with the changes and growth that happened over the last ten years. One thing I remember Marc saying was that the questions people ask have changed: five years ago people were trying to understand PostgreSQL and today they are increasingly asking questions about integrating with their larger environment.
  • Dennis Tighe talked about PostgreSQL Security Best Practices from the perspective of Amazon RDS. I was very impressed with this talk for its breadth of security-related topics: external roles/principles/access, data encryption and key management, sharing data, network segmentation and wire encryption, database account/role management, password management, auditing… and all of these topics covered from the perspective of PostgreSQL. I told Dennis several times that talks like this should be available at more user conferences!
  • Denish Patel gave a session on SQL performance tips titled: why isn’t my query using an index? Denish gave an overview of join types and optimizations, introduced the PostgreSQL Genetic Query Optimizer, and then dove into topics including cardinality, partial indexes, histograms and optimizer parameters. I especially enjoyed his samples drawn from the public flight arrival on-time statistics for 2018.


Thursday March 21

Interesting conversations:

  • Small international software vendor in the healthcare industry. Their application supports a couple configurations for the database backend but most of their customers use the default embedded PostgreSQL database. Great conversation about troubleshooting a database-related challenge one of their customers is facing (which they are supporting). Also discussed procedural languages, taking block dumps and viewing raw data from PostgreSQL data files, and the future of javascript in the database with current industry investment in WebAssembly.
  • Individual DBA talking about their current PostgreSQL configuration: 400-500 databases consolidated into a single PostgreSQL instance, typically running around 800-1000 connections but needing to support around 3000ish connections. IIRC each database had its own app server connecting to it. All of this running on a single bare metal server with only 8GB memory! (BTW, while this ratio of memory to connection count is working for them, it wouldn’t work for every workload.)
  • DBAs from my own rather large employer! I mention this because it was a real treat to have DBAs from a few different teams around who could share their own stories and lessons learned as they have been running real production workloads on PostgreSQL and migrating new workloads from commercial databases onto PostgreSQL. I love running into these guys at conferences!

Session I attended:

https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=600&h=450 600w, https://ardentperf.files.wordpress.com/2019/03/pgconf-3.jpg?w=150&h=113 150w" sizes="(max-width: 300px) 100vw, 300px" />
  • Baron Schwartz said we were the very first people to find out that he is re-titling his talk: “Everything You Need To Know About PostgreSQL EXPLAIN.” This was a high-quality, accessible talk introducing the topic of execution plans and describing why they are important. After introducing them and peeling back the layers a bit, he then discussed what can go wrong and how to troubleshoot.
  • Grant McAlister gave a session titled HOT – UNDERSTANDING THIS IMPORTANT UPDATE OPTIMIZATION. I did point out to Grant that it’s the only session at PostgresConf which is shouting in all capital letters; he wasn’t quite sure why that happened. But I’m not complaining, because if you like database internals as much as I do then this might have been the best presentation at the conference. I once told someone that I think Grant is a combination of Julian Dyke’s ability to make genius slide animations with Jonathan Lewis’ ability to construct simple tests demonstrating complex database behaviors.
  • My session about Wait Events in PostgreSQL! Around lunch time I went to go hide somewhere and review the content. As I was reviewing, it became evident that I likely had too much content… and I ended up a bit stuck for the next few hours trying to work on that. Well… ask anyone who came to the session… I did not succeed in trimming up my content enough!! But the session seemed successful anyway based on the positive feedback from attendees.

Work:

Like many other attendees I wasn’t quite able to get rid of all my responsibilities for the week. In case you wonder how much hands-on work I do… well I ended up spending a couple hours Thursday evening on a rather fun project I’ve had this year which involved writing several hundred lines of SQL against a very large Posgres-based column-store analytical database. I get to use CTEs & window functions and leverage a deep understanding of the physical layout to write queries that run in minutes instead of hours/days. Important strategies for this project: making a single pass on source data, leveraging push-down filter processing as much as possible, avoiding unnecessary sorts, and minimizing the need to transfer data over the network between cluster nodes during different stages of query execution. (I love this stuff!)

Friday March 22

The conversations I most remember from Friday were around user group and community organizing. Conferences in South Africa and Spain, user groups in Chicago and Denver and Seattle, and a contact in Raleigh.

In addition to the general sessions on Friday, I made sure to attend Jim Nasby’s talk titled: All The Dirt On Vacuum (PG 11 Edition). There’s a version of this talk from 2015 on YouTube but it’s always worth catching Nasby live if you can, because he’s constantly updating and improving the talk. The question that I most often ask about vacuum: “how much longer until this finishes?” The PostgreSQL docs list the Vacuum Phases but they don’t mention that some of these phases might get repeated multiple times. Nasby’s talk brings it all together so that you can understand the whole process and the configuration settings that impact it.

https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=600... 600w, https://ardentperf.files.wordpress.com/2019/03/all-the-buttons.jpg?w=150... 150w" sizes="(max-width: 300px) 100vw, 300px" />

Last but not least… last year I was one of the mentors helping high-school students all around the globe who made contributions to open source PostgreSQL through Google’s Code-In contest. One of the contributions these students made was to create some really cool sticker and pin designs that could be used to promote the open source project!

PostgresConf sponsored ordering a bunch of real stickers and pins with the designs created by these high school students. And we had some fun with this on the exhibition floor: each booth got one or two designs, and attendees who wanted to get all the sticker/pin designs would have to hunt around. In case you’re wondering whether anyone got them all… on Friday, I discovered at least one person who did: Ryan Lambert of RustProof Labs!

Growth Areas

So I’m fully aware (and JD recently reminded me just to make sure) that suggesting growth areas is tantamount to volunteering to implement them… I’m going to take that risk anyway and throw out two ideas for next year if the volunteer bandwidth exists to implement them. </p />
</p></div>
    <div class=»

To prevent automated spam submissions leave this field empty.