Andrew Dunstan

Tags:   postgresql    2ndquadrant    buildfarm    testing    json    australia   
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, your hobbies and where you are from.

Originally from Australia, now living in North Carolina, USA. Dual citizen. Age 66, making me one of the oldest people in the Postgres community. Principal pastime: walking.

Andrew Dunstan

Andrew Dunstan

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

When did you start using PostgreSQL, and why?

In 2002 I was tasked with selecting a reference database that the company I then worked for could ship with our product. PostgreSQL met the requirements in all respects in terms of license and capabilities, but didn’t have a Windows port. Nevertheless, we started using it internally as a result of my recommendation, and I fell in love with it, and started contributing.

Do you remember which version of PostgreSQL you started with?

I started with 7.2 or 7.3, my first contributions were in 7.4, and then a lot of contributions (e.g. CSV import/export, and a completely rewritten initdb) in 8.0.

I have a major in Computer Science (as well as one in History). I later taught computer science at the University of Adelaide for several years, during which I learned far more than when I was studying. And yes, it’s often helped me. For instance, knowledge and understanding of the Recursive Descent parser pattern was invaluable when I came to rewrite the PostgreSQL JSON parser back in 9.3.

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

I use MySQL and SQLite occasionally when needed by other software I’m using. Other than that I always use PostgreSQL.

Mostly some developments I’m not yet ready to talk about publicly :-)

How do you contribute to PostgreSQL?


  • Reviewing and committing patches
  • Developing features
  • The PostgreSQL Build Farm

Some significant features I have contributed to in whole or in part:

  • Windows port
  • Dollar quoting
  • CSV import/export
  • log_line_prefix
  • CSV logging
  • Parallel pg_restore
  • JSON features
  • Enum extensibilty
  • Redis Foreign Data Wrapper

Any contributions to PostgreSQL which do not involve writing code?

Conference talks and webinars, but mostly it’s code one way or another.

What is your favorite PostgreSQL extension?

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

The whole vacuuming and freezing kabuki dance. It’s annoying and necessary and doesn’t add a single cent to your bottom line. There are some opportunities to ameliorate it, but fixing it completely would be very difficult.

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


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

Ability to partition a large table without taking it offline for an extended period. Frequently, you don’t realize you need partitioning on a table until it’s too late. Recovering from that design decision is far too hard.

Could you describe your PostgreSQL development toolbox?

Fairly low-level tools. Almost everything is done from the command line. I use emacs for any substantial editing work. Development platform is Fedora except for Windows work. On Windows I mostly use msys or (more recently) msys2. For any substantial development I usually run the code through the buildfarm client first, which does a fairly comprehensive set of tests (more even than make check-world). I hate debuggers, and only use them as a last resort. During development, I will usually decorate the code with lots of trace writes to discover what’s going on. These get removed before the patch is submitted or committed.

If I need to test things out in an isolated environment I use vagrant, with the Virtualbox provider or using the vagrant-aws plugin to run on Amazon. Occasionally I also use Docker.

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

Assuming here you mean a developer using PostgreSQL rather than a developer of PostgreSQL. For any relational database they should understand the fundamentals of relational algebra and database normal forms. They should know when they have a many to many relationship and when they have a many to one relationship, and how to go about representing those. They should be familiar with SQL and its intricacies.

For PostgreSQL, they should understand the different join types, and their performance characteristics, and how to influence the type of execution plan that PostgreSQL will choose for a given query. They also need to understand the rich array of data types and index types that PostgreSQL offers, and when they need to be used.

And they need to understand the whole PostgreSQL ecology, including third party extensions and utilities.

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

I do any non-trivial development on a separate branch. That way I can switch between development and committer tasks easily.

On development branches I usually commit a lot, so I can roll back easily if a development line doesn’t work out.

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

pgCon, and some conferences in the USA. Yes, I almost always submit talks.

Do you think Postgres has a high entry barrier?

These days there are some fairly good GUIs (e.g. ) if you like that sort of thing. These can ease the pain a good deal. Installation of PostgreSQL is pretty much a piece of cake on just about any platform. You don’t need to understand everything about PostgreSQL to get started. You do need to understand the fundamentals of tables, and the basic data types. PostgreSQL can be intimidating because of its wealth of features, but you don’t need most of the features to get going.

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

Read the pgsql-hackers mailing list. Start by reviewing patches. Learn the community coding conventions. It’s often best to make your first contribution a small one. Maybe something of a hundred lines of code or less. Then build up to the huge headline feature that will make you a legend in the community. Also note that our attitude is often “show me the code.” You get more respect in the community the more you contribute, and you will be listened to more accordingly.

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

I expect it to be here longer than I am. Beyond that I have no idea.

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

Yes, both.

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

Yes, also -committers and -performance. I don’t read others regularly.

What other places do you hang out?

None external to 2ndquadrant. I used to hang out on IRC but got out of the habit.

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

To the extent that it’s a separate project, the PostgreSQL Buildfarm. This was something I created in 2004 and have maintained ever since. At the time it was fairly revolutionary, and even in these days of Jenkins and Appveyor it’s still fairly unique as a cross-platform testing system. Before we got it we had no idea, sometimes for weeks or months, if some committed patch broke a platform that we didn’t use. Now we find out about it in a matter of hours. It’s one of the things I’m proudest of, and has probably had the most profound effect on PostgreSQL of anything I have done. (And stand by for some significant improvements real soon).

Many years ago I created a database independent abstract schema system for Bugzilla, making it possible to support PostgreSQL as a main line backend - before that only MySQL was supported.

Anything else you like to add?

Postgres is fun! Not many people are paid well for doing what they love. I’m one of the lucky ones who are. And they don’t pay me for this, but I should say that 2ndQuadrant is a great company to work for.