David E. Wheeler

Tags:   postgresql    pgxn    sqitch    pgtap    citext   
Category:   Interviews   
Interviewed by: Andreas Scherbaum

PostgreSQL is the World’s most advanced Open Source Relational Database. The interview series “PostgreSQL Person of the Week” presents the people who make the project what it is today. Read all interviews here.

Please tell us about yourself, and where you are from.

I’m a long-time open-source hacker in his early fifties. I’ve lived and worked in San Francisco, Portland OR, and now New York City.

David E Wheeler

David E Wheeler

How do you spend your free time? What are your hobbies?

Since the early summer, after three months locked down in the pandemic, I started walking all around Upper Manhattan, mainly for exercise, but also to get to know The City better. It’s the most rewarding thing I’ve done all year. I also enjoy baking sourdough bread, reading, and pizza/movie night with my family (also started during the pandemic).

Any Social Media channels of yours we should be aware of?

Last book you read? Or a book you want to recommend to readers?

I’ve been reading Caste. Far and away the most important and devastating book I’ve read in a long time, perhaps ever.

Any favorite movie, or show?

How would your ideal weekend look like?

  • Sleep in late
  • Brunch
  • Wander around an art, history, or natural history museum
  • Cocktails
  • Dinner with friends

What’s still on your bucket list?

Our whole family wants to visit Italy, perhaps spend a summer there.

When did you start using PostgreSQL, and why?

I started using Postgres around 1999. I picked it up because it was open-source and seemed to be high quality. It became essential when we switched to it for the CMS we were developing at work, which was later released as Bricolage.

Do you remember which version of PostgreSQL you started with?

6.1 or 6.2 I think.

I have a BA and an MA in Anthropology, with a focus on Ancient Near Eastern Archaeology. It was not related to computers, but it certainly helps me with my current job, as it gives me more of an ethnographic view of people and cultures. Technologists are a curious people.

What other databases are you using? Which one is your favorite?

Over the years, I’ve used SQLite, MySQL, Oracle, SQL Server, and Snowflake. SQLite is far and away my favorite, but Snowflake is a pretty incredible tool at the other end of the scale.

I’m not working very actively on Postgres projects lately, but continue to maintain PGXN, Sqitch, pgTAP, and a few extensions.

How do you contribute to PostgreSQL?

Today, principally by maintaining the above projects. I’ve been less involved in the project in the last few years, but in the past I regularly attended and thoroughly enjoyed PGCon, and tried to help get new features vetted and integrated, such as CITEXT and JSONB.

Any contributions to PostgreSQL which do not involve writing code?

I’ve given a number of presentations at PGCon over the years, as well as to various user groups. In the past I’ve spent a lot of time discussing changes and issues on pgsql-hackers and haunted the #postgresql channel on Freenode, but have been away for a while.

What is your favorite PostgreSQL extension?

I’m rather fond of pgTAP, my extension for unit-testing PostgreSQL databases. pgcrypto is an essential part of my database toolkit.

What is the most annoying PostgreSQL thing you can think of? And any chance to fix it?

Most of my annoyances have been fixed over the years, like lack of upsert (added in 9.5) and poor unstructured data types (fixed by JSON and JSONB and the addition of all the functions over the years). Did you know it used to be that you couldn’t alter column types in Postgres? I had to write upgrades that would add a new column, copy the data from an old column, rename the old column, and name the new column to the original name. Those were the days!

What is the feature you like most in the latest PostgreSQL version?

gen_random_uuid() is finally in-core!

Adding to that, what feature/mechanism would you like to see in PostgreSQL? And why?

I would very much like to see in-core multi-directional logical replication. I’d really like my next big database to be a multi-node, multi-DC cluster with local quorums, a 3x replication factor and automatic node management for removing (or failing) nodes, adding new nodes, and auto-balancing. Something like Cassandra does, but Postgres.

Actually, that might not be doable in-core, but a complementary project that runs native Postgres, uses its in-core logical replication features, and does all the node handling with Zookeeper or something similar would be great.

Could you describe your PostgreSQL development toolbox?

I use Sqitch for database change management, both as an iterative development and a deployment tool.. I write my SQL in a text editor (VSCode lately, but I’m not picky) and use psql to interact directly with a database. I heavily rely on pgTAP to test my databases, and make sure I don’t screw things up when I make changes.

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

  • Curiosity
  • Kindness
  • Empathy
  • Persistence
  • Patience

Do you use any git best practices, which makes working with PostgreSQL easier?

I use Git in combination with Sqitch to manage all but the simplest databases. Sqitch is what makes managing a database in Git sensible, since it keeps a plan file in an orderly fashion that’s friendly to Git’s line-oriented conflict management. It also rely on no special numbering or ordering system for changes, aside from the order in which changes appear in the plan file. In truth, it has made working with databases in Git and managing their deployment almost enjoyable — though it’s still possible to make something so complex over the years that it gradually becomes more uncomfortable, sometimes painful. But the horizon for that discomfort is much farther out than otherwise. It’s far and away the project I’m most proud of.

Oh, and it works with other databases, too. PostgreSQL is the first among many.

Which PostgreSQL conferences do you visit? Do you submit talks?

I haven’t attended PGCon since 2017 or so. I should go back. I have not submitted talks since 2015 or so. Maybe I’ll look into it for 2021 (or 2022, depending on Pandemic status). I miss it.

Do you think Postgres has a high entry barrier?

Hard for me to say, since I entered over 20 years ago. I find the community welcoming and super helpful, if you know where to go for help. The ability to run Postgres containers has also reduced the time and effort required to get started. Containerization has changed just about everything for technology development, entirely for the better.

What is your advice for people who want to start PostgreSQL developing - as in, contributing to the project. Where and how should they start?

I was gonna suggest lurking on pgsql-hackers, but honestly that’s pretty daunting (I subscribed and unsubscribed several times in the early years, as it was way over my head). The folks on the Freenode #postgresql channel tend to be super friendly and helpful, and I have often seen ideas for simple extensions and features go by. Perhaps hang out there, help some folks and get your questions answered. And then, when you have an idea for something, perhaps a custom data type, create an extension and release it on PGXN. I’ve long appreciated PostgreSQL’s extensibility, and the direct support for extensions has made it fairly easy to get started.

Do you think PostgreSQL will be here for many years in the future?

I do. It has been around in its current form for 25 years, and continues to improve markedly every year, at an incredible pace. I’m looking forward to building projects on Postgres for the next 25 years.

Would you recommend Postgres for business, or for side projects?

Both, absolutely. The cases where I would recommend against it tend to be narrow, with very specific requirements not (currently) provided by Postgres. But overall they’re the exception.

Are you reading the -hackers mailinglist? Any other list?

Not for the last couple years, alas.

What other places do you hang out?

The Freenode #postgresql channel.

Which other Open Source projects are you involved or interested in?

Anything else you like to add?

I think I’ve talked long enough, don’t you?