I had the chance to present on Table Partitioning this year at PGDay Chicago 2023, and had a great time. I wanted to recap my experience.

Presenting at Pg Day Chicago Andrew Atkinson Presenting at PGDay Chicago 2023

Call For Papers

Since the Call for Papers was open back in Winter, and I submitted a proposal based on an upcoming project that I expected to do but wasn’t yet done! This motivated me to get the project done. I wanted the talk to be based on real world experience, with the good and the bad, so I was very motivated to get the project done.

This was risky because there were organizational factors beyond my control that could have blocked the project. In the end it worked out but it was down to the wire, as we only completed the project a week before the deadline!

I had also submitted the talk to RailsConf 2023 and unfortunately it was not accepted. This talk was originally intended more for Rails developers. The talk makes the case for how table partitioning helps, and how to perform an online non-destructive conversion using a CLI Ruby gem called pgslice 1. Being a Ruby gem codebase, it might be friendlier than a PostgreSQL extension for Rails developers to pick up and customize.

Dry Run with Crunchy Data

Elizabeth Garrett Christensen (@sqlliz) and I were messaging and I mentioned doing a dry run. Elizabeth offered to open it up to her team members at Crunchy Data!

Elizabeth and I have supported each other with presentation reviews and rubber ducking on PostgreSQL topics, and I really appreciate having her in my network of peer reviewers and supporters (and recommend building yours!).

I gave the talk first there and received helpful feedback. Liz also introduced me to Chris, Keith, and more team members. Thanks Liz!

Pre-conference Activities

I arrived the day before and caught up with some Fountain team members in Chicago for lunch and dinner! This was great!

The speaker’s dinner was a nice hang and I appreciated meeting other presenters in a small group setting.

My Presentation Experience

I felt prepared to give the presentation. It was a big relief to have completed the project that the presentation was based on. 😅 I was excited to share our experience and learn from feedback.

We manage around 10 production databases, but one is 10x the size of the next largest. Although the table partitioning was applied to all 10, the benefits were intended mainly for the large database.

The large database was about 4 TB in size with the unpartitioned table being 1.5 TB and having 2 billion rows.

Once partitioned by month, each child table was around 20 GB in size with around 50 million rows. More manageable.

The Conference and Venue

The conference was organized well. It’s a 1 day conference with 3 tracks, and jam packed full of content.

Kudos to all the planning that went into it and the staff, signage, organization, timing, everything was very smooth from my perspective.

The Convene venue was very nice.

Although I woke up early to exercise and prepare, my \timing cut things close. I walked in, set up my laptop, and was announced about 30 seconds later as the next presenter! 😅

Community and Sessions

At the smaller conferences it’s easier to meet people and form new professional relationships.

These relationships have paid off in my career.

One attendee I met and hit it off with was Brian. When Brian said his website was Brian Likes Postgres I was so excited! Not only does Brian like PostgreSQL (woo!), he’s also an educator and into PostgreSQL advocacy.

I asked Brian to be a Technical Reviewer for my PostgreSQL for Rails book and he accepted!

This opportunity would not have happened without PGDay Chicago. Brian is an awesome member of the community and I’m glad we met!

Speaking of great people at the conference, let me highlight some more folks.

  • Alfredo Rodriguez (founder of Wolfgres) gave a great talk on High Availability, Disaster Recovery, RTOs and RPOs. Alfredo gave handmade gifts to the presenters from Mexico as well which was very thoughtful!
  • Derk van Veen spoke just me also on Table Partitioning at the Dutch company Adyen.
  • Henrietta, conference organizer, Chicago PostgreSQL Meetup Group, and author! It was nice to catch up a bit with her about writing her book. (Recommended!) PostgreSQL Query Optimization
  • Stephen Frost and David Christensen presented on Transparent Data Encryption (TDE). Missed this and need to catch up on this useful capability.
  • Phillip Merrick and Denis Lussier have cofounded a new PostgreSQL company called pgEdge, including an open source product Spock. Really interesting challenges being solved in PostgreSQL at the network edge, using multi-active architecture and Logical Replication.
  • Michael Banck gave a talk on HA and Patroni deployment patterns. Michael is also a Prince fan and was interested to know that Paisley Park is now a museum open to visitors here in Minnesota!
  • Jimmy gave a fun talk on “Don’t Do This” in PostgreSQL.
  • I met the Community Slack legend Robert Treat!
  • Lætitia gave a fun talk exploring advanced PostgreSQL concepts by solving Advent of Code puzzles.
  • Bruce Momjian gave an amazing introduction to Window Functions.

See the full schedule.

What could have been better?

I thought everything about the conference was excellent. My only suggestion would be to record the sessions if there is a budget and resources to do so. I’d happily give my consent.

As a presenter, I’d like having recorded talks that I can share online to help grow my presence and credibility. Since PGDay Chicago was a multi-track conference, I also could only pick one session per time slot and without recorded sessions, can’t go back and see the ones I missed.

Slides

I do have the slides posted and would love feedback. Please leave a comment or contact me.

Use the short link http://bit.ly/PartPG2023 2 for the slides. Download the PDF for links. Search Twitter with #PartPG2023 to find tweets. The slides are also embedded below!

What’s next

  • I’m going to keep telling people how awesome PostgreSQL Declarative Partitioning is, and why developers should use it more. With PostgreSQL 16 coming up later this year, I’m curious to see if there are more enhancements on the way.
  • I’m working on more proposals and hope to present or attend another conference with these same organizers, they did a great job!

And while I have your attention: I’m writing a book called High Performance PostgreSQL for Rails to be published by Pragmatic Programmers in 2023. Please subscribe to get more information!

Thanks!

  1. https://github.com/ankane/pgslice ↩

  2. http://bit.ly/PartPG2023 ↩