Andrew Atkinson

About the Speaker

• Tell us about yourself: career, family, passions

  My career has been in web application development, mostly for VC-backed consumer and business startup companies with Internet products. I live with my two girls and spouse in Minneapolis, MN, and besides loving my career in tech, I'm looking forward to spending time with my family this summer biking, going to the swimming pools, and taking vacations.

• What is your icebreaker for PostgreSQL events?

  Do you prefer UUID or Bigint? I'm on team Bigint.

• How do you prepare for an online presentation?

  For online, I try to think through logistics like lighting, audio, and having a quiet place to record. I plugged my 75 foot ethernet cord in, and fortunately have reliable home Internet. I made sure my lights were on since I work in a dimly lit lower level room. I turned off all my notifications, and prepped my consulting clients and family that I’d be away for a bit! Other than that, it's about the same, although surprisingly I had more jitters at home here, compared with recent in-person presentations I've given!

• Which book are you reading right now?

  I aspire to read and understand more PostgreSQL source code, so I’ve recently started with Effective C.

• What is your favorite hobby?

  While writing a book over the last two years, and now due to the demands of launching a solo consulting business, I cut out some hobbies like practicing piano or running that were favorites. I’m working on bringing those back into my life as they boost my mood and bring me some balance!

About the Talk

• What will your talk be about, exactly? Why this topic?

  My talk looks at building multi-tenant Ruby on Rails apps with PostgreSQL, and how application developers might leverage PostgreSQL capabilities, Horizontal Sharding, or Citus to scale up and out. For high-growth successful businesses, some database architecture planning will eventually be required and will include vertical and horizontal scalability planning. I find it helpful to create proofs of concept, to get hands-on experience, form some opinions based on first hand experience, then use that later in conversations at the appropriate time.

• What is the audience for your talk?

  The audience includes Rails developers and PostgreSQL DBAs or infra engineers, that want to know a little about the ecosystem or set of options for scaling out. In my experience, Citus hasn't been commonly used in Ruby on Rails apps I’ve worked on, but is quite useful and powerful, so I personally wanted to explore it more for possible future use. On the other hand, PostgreSQL DBAs may know nothing about Active Record Horizontal Sharding, which is fairly new, and is also general purpose and quite powerful. I like the idea of sharing and connecting different technology communities that have some shared goals and interests.

• What existing knowledge should the attendee have?

  Due to the limited time, this is not an introductory talk to Ruby on Rails, PostgreSQL, or sharding. Ideally the viewer is familiar with multi-tenancy in general, has experience building a B2B or B2C SaaS multi-tenant app with Ruby on Rails or something similar using PostgreSQL, and is curious about what these technologies are. Besides the slide deck, I’ll have a blog post and some code samples that people can read and play around with if they’d like.

• Which other talk at this year’s conference would you like to watch?

  Henrietta D.’s talk on tuning queries vs. parameters, David W.’s talk on extensions, Marco S.’s talk on data-intensive PostgreSQL, Bruce M.’s talk on joins and indexes, Paolo M.’s talk on Django and pgvector.

• How do you balance technical depth with engaging storytelling in your conference presentations?

  For Posette, I didn’t create much of a story due to the short timeline. I did use my book’s example app Rideshare, which does not currently have a multi-tenant design, as I prefer to have a concrete example to modify so that I can create and run samples, and others can as well. I described adding multi-tenancy to Rideshare, and how we might scale up and use native Postgres functionality, or scale out, expanding beyond a single instance. In past presentations, I made up stories about Paw Patrol pups scaling their imaginary applications, or suggesting attendees could be heroes like Neo in the Matrix, going on a hero’s journey of developing skills to solve query performance challenges!

About PostgreSQL

• What inspired you to work with PostgreSQL?

  I had aspired to become more skilled with SQL, PostgreSQL, and relational databases in the late 2010s, noticing they’re so prevalent and important, as I gained seniority as a backend engineer. Industry mentors and peers I looked up to chose PostgreSQL, so I considered it the best on the strength of their judgment. Things really took a turn for me personally in 2020 during the pandemic, when I was on a small team (Flipgrid, part of Microsoft) of around 5 app devs, with no DBA, and our platform was experiencing massive growth w/ 1000s of transactions/second. We had to squeeze as much as possible from our server instances, because we didn’t have the time for anything else. There was a period of rapid learning about Autovacuum, index optimization, query optimization, and database connections among other things, and my contributions were applying as much as possible so our servers could handle the load. For me I saw it as a rare opportunity, and a “pressure creates diamonds” moment, and I remember reading PostgreSQL 9.0 High Performance, attending MS and Citus team office hours, taking notes, excited to come into work and add any server instance headroom possible.

• What is your favorite PostgreSQL feature?

  It’s gotta be the query planner (aka optimizer). I’m on a multi-year journey to learn query planning really well, as I think when paired up with index design, these are high ROI activities for backend engineers to create efficient and "lean" queries, maximizing performance, scalability, and reliability.

• What is the single thing that you think differentiates PostgreSQL most from other databases?

  This is a tough question because I don’t know all databases in existence. Among the ones I do know though, and the ones that PostgreSQL might directly compete with, I’d say the extensibility (and the adoption of it in the form of extensions) created now and in the future, as a big differentiator!

• What is your favorite PostgreSQL extension or tool? And why?

  The not-new but critically-useful pg_stat_statements. Most web apps have a higher proportion of read only queries, and it’s critical to identify slow queries and high row counts to focus our optimization efforts, which pg_stat_statements helps us do.

• What advice would you give to someone starting their journey with PostgreSQL?

  This depends greatly on their goals. If they want to build high performance apps, then they’ll want to learn query planning, and efficient schema and index design. If they want to get involved with the project itself, I’d recommend attending a PgDay or other Postgres community event they can reach, meeting some people in the community, and learning about a few features they’re interested in and how they work.

• What are your favorite resources for learning about PostgreSQL?

  Postgres.fm podcast, 5mins of Postgres videos and podcast, Scaling Postgres podcast, SQL for Devs conferences, books, official documentation, running my own instances locally creating tables, populating data, creating indexes, and just generally tinkering and experimenting.

• Could you share a memorable experience or challenge you faced while working with PostgreSQL?

  A high pressure challenge was needing to change the primary key on a high-writes partitioned table, without taking it offline. The PK change causes a long-lived lock, while propagated to all children. We used a maneuver to “hide” the partitioned table so the operation and lock period happened in the background, by using a placeholder non-partitioned table. We willed the placeholder with enough data to answer queries for a bit. Once the hidden change happened in the background, we swapped it back to the “foreground” and no longer needed the placeholder table. It felt a little like “don’t cut the wrong wire,” but we prepared a bunch, pulled it off, and didn’t take any downtime for what would have otherwise caused lots of errors.

• In your opinion, what are the most common pitfalls or mistakes developers make when working with PostgreSQL?

  Not learning to optimize their current workload, to squeeze more out of their instances, or not being aware of the very broad feature set with Postgres and how they might be able to “just use postgres” for more of their work.

• Which skills are a must have for a PostgreSQL user/developer?

  At least basics of interpreting EXPLAIN plans, in order to understand the latency from IO, and how to make queries more efficient, and design efficient indexes.

• What is the most overlooked thing about PostgreSQL?

  How a global team of contributors and committers can pull off annual releases, coordinated mostly by email, without generally producing performance regressions, and still add loads of new features, to one of the most critical pieces of software running for businesses that are using it. I find it really impressive!

• PostgreSQL is open-source, did that ever help you in anyway and how?

  While writing my book, I read a LOT of the PostgreSQL documentation pages. I occasionally spot a very minor inaccuracy, or something that’s inconsistent that might affect a learner’s comprehension, and I’ve had a few of those kinds of doc patches be committed. I was also honored to be a volunteer on the talk selection committee for the 2024 PgDay Chicago conference.

• If you had a magic wand, what single thing would you change in PostgreSQL as it is today?

  A system view with EXPLAIN plans for a normalized query in pg_stat_statements. We could explore the plans, the parameters, their scan types, filter operations, and other information, to help optimize our queries.

About POSETTE & Events

• Have you enjoyed previous POSETTE (formerly Citus Con) conferences, either as an attendee or as a speaker?

  I enjoyed past years’ presentations which I watched on YouTube. I thought the quality was high, and was honored to be selected into this year’s program. I enjoyed having the opportunity to bring in some technologies from multiple communities.

• What motivated you to speak at this year’s POSETTE: An Event for Postgres?

  This year my book on PostgreSQL and Ruby on Rails is being published, and I’ve also launched a consulting practice, and am looking to add new clients. Speaking at conferences expands my reputation and influence, which hopefully helps me find new billable work, which can then support my efforts for PostgreSQL developer education and advocacy, which are otherwise more unpaid labors of love. I appreciate Microsoft is investing in community PostgreSQL, and have connected with a lot of employees at other in-person conferences, so I “see” the company being an active participant in the community.

• What other PostgreSQL events in 2024 are you excited about?

  I had a great time at PgDay Chicago 2024, presenting and connecting with friends. Although I won’t be attending this year, I’m keeping my eyes on what comes out of PGConf.dev this Spring, and looking at PGConf NYC this Fall.

• What advice would you give to fellow speakers preparing for a PostgreSQL conference?

  PostgreSQL conference programs include a wide variety of presentation topics. While topics could include very technical details, don’t discount your experience as a user of PostgreSQL, how you’re using it, what’s working, what could be better, ways to mix and match. Those kinds of stories help expand the reach of the project.

• What would be helpful for a first-time speaker?

  I’m not a “natural” speaker, so I have found focusing on the “outline” and main takeaway messages to be important. Frame things around what you want the attendee to learn. I also prepare with real live audiences when possible, which helps me iterate towards something better. I might cut out content, move things around, and try and get a flow going. Colleagues have been generous in reviewing slides and providing feedback, and I try to reciprocate. I also watch past talks on YouTube to learn, and form ideas around interesting topics. I also recommend creating sample databases, schema designs, queries and data, to test things out in a hands-on way.

• Could you share a memorable moment from a previous PostgreSQL conference you attended or spoke at?

  After PGConf NYC wound down, I joined 5-6 others for a late-night small-room karaoke session, where we bonded over belting out a wide variety of songs from Avril Lavigne to the Beatles to Broadway. :)