>
PostgreSQL, Technical

PostgresConf 2019 Summary

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.

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:

  • 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.

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. 😉

  1. Surveys: getting data to better understand the conference and better grow it. While the agenda app did have basic survey/feedback support, I’m not sure how broad the participation was.
    • Understanding attendees – usual stuff like primary job role, geography, industry, size of company.
    • Understanding attendees – how they heard about postgresconf. Every conference and user group organizer I know is always thinking about which strategies are most effective for driving attendance so they can constantly improve their growth. The more data, the better.
    • Feedback for speakers – we put a lot of work into our talks! We want to know how we can continually improve both our content and our delivery.
    • Feedback on session titles/descriptions – it’s disappointing when attendees have mismatches expectations about a session. Especially if they end up in a talk which is largely about a product that’s not relevant to them. I didn’t hear a lot of complaints about this, but it’s important to keep a handle on it as we grow: making sure session content lines up with titles.
    • Lots of participation! Standard slide template distributed to speakers ahead of time? Mention it in the opening and closing general sessions? Signs with URLs plastered to the doors as you enter and exit each session?
  2. BOFs: an initial step toward more tracks or SIGs
    • Common themes I noticed getting discussed:
      • PostgreSQL embedded in a software package
      • In-house or OSS implementations of AWR/ASH (and other gaps) by customers migrating from commercial systems
      • Market for independent consulting/contracting: performance tuning, training, health checks, etc
      • Local user group organization, conference organization, and postgresql content or tracks in developer/system user groups and conferences.
      • What other topics am I missing?
    • Eventually there might be room for tracks or special interest groups on some of these topics. An easy starting point for next year could be to schedule a “Birds of a Feather” lunch where we have tables/rooms organized around topics. No leader or agenda; just allow people to sit and eat lunch with someone else sharing an interest.

And now that I’ve spent waaaaay too long writing this blog post… I’ll open the floor for comments.

If you were at PostgresConf this year then I hope that you’ll not only consider coming next year – but consider pitching in to make it an even better conference. I’ll see you there!

About Jeremy

Building and running reliable data platforms that scale and perform. about.me/jeremy_schneider

Discussion

One thought on “PostgresConf 2019 Summary

  1. Hi,
    Thanks for the summary, enjoyed reading it.
    Regards
    Abbas

    Liked by 1 person

    Posted by gabbasb | April 3, 2019, 1:55 am

Leave a New Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Disclaimer

This is my personal website. The views expressed here are mine alone and may not reflect the views of my employer.

contact: 312-725-9249 or schneider @ ardentperf.com


https://about.me/jeremy_schneider

oaktableocmaceracattack

(a)

Enter your email address to receive notifications of new posts by email.

Join 33 other followers

%d bloggers like this: