Sunday 7 December 2014

The story of pgAdmin

I’m often asked how I first became involved in PostgreSQL, and how the pgAdmin project got started. Much as I’m happy to tell the story over beer, it becomes tedious to do so over email after the first half-dozen or so times. So in a vain attempt to save my fingers from future carpal tunnel syndrome, here’s the tale…

I first discovered PostgreSQL in 1997. I was working at a social housing provider in the UK where, at the time, we were running Informix SE and Pick databases. We wanted to start building in-house web applications for managing additional areas of the business. There were no useful interfaces for Pick that could be used with any of the web technologies available to us at the time and the licensing model for Informix and its ODBC driver was prohibitively expensive, so I started looking for alternatives. 

After spending some time researching mSQL and MySQL, I eventually found PostgreSQL, which seemed to offer everything we needed—a SQL-based DBMS with ODBC and C interfaces, a liberal license, a rich set of features, and a vibrant community supporting it that was responsive to bug reports and open to feature requests. My colleagues and I developed a number of applications using PostgreSQL in the following years and the database became a critical part of our business. We had applications written in PHP for the web as well as Visual Basic for the desktop users. 

However, it was early on in our journey with Postgres that, as a Windows shop (on the desktop at least), we found ourselves wishing for a good graphical management tool for the database. At the time, the only option was pgAccess, which was a basic tool written in TCL/TK that was not easy to get to work on Windows. So I decided to write my own in Visual Basic. The first version was never released to the public and was modeled on the pgAccess design. Called pgManager, it was quickly abandoned as we found the UI to be restrictive and, well, not overly usable. The second incarnation was pgAdmin, which was redesigned from the ground up. It was released as an Open Source project and quickly became a popular tool amongst Postgres users (which was extremely gratifying as it was my first Open Source project). 

Some years later, we once again found ourselves suffering due to the way the application had been designed. I shut myself away for a couple of weeks whilst my family were away visiting relatives in Scotland and wrote pgAdmin II, using a far more structured and maintainable design that implemented a much more usable user interface. I was particularly proud of the design and cleanliness of that code, but by 2002, we needed to start over again. This time it wasn't the application design that was holding us back, but rather the choice of technology. Visual Basic didn't handle internationalization or localization well, nor did it run on platforms other than Windows without the use of WINE, under which it was never particularly stable. The hard decision was made to rewrite everything again, this time using C++ with the wxWidgets framework. pgAdmin III looked much like pgAdmin II, but it solved all of the problems our (in particular) Japanese Linux-using friends were experiencing. Now in its third incarnation, pgAdmin remains the most popular Open Source GUI tool for managing Postgres.

I continued to work at the housing provider and make good use of Postgres until 2007, at which time I moved to EnterpriseDB so I could concentrate on my Postgres work full time. At EDB, not only do I work on our own tools, I also continue to contribute to the PostgreSQL community in various ways. I have the privilege of having full support of the management at EDB for my community work, allowing me the freedom to work on whatever I need to in order to fulfill my community roles on the core team, the PostgreSQL Europe board, and of course, pgAdmin. One of the products I’m responsible for at EDB is Postgres Enterprise Manager, which has a fat client interface that’s based on pgAdmin. This has allowed us to put more effort into the maintenance of pgAdmin, with members of the PEM team at EDB regularly contributing code, fixing bugs and reviewing the patches from elsewhere. Ashesh Vashi has even been made a committer for his efforts.

Despite the hard work over the last 12 or so years, the pgAdmin developers have come to the conclusion that there is limited life left in the current code. Aside from it being extremely hard to find C++ developers these days, we’ve also run into numerous difficult-to-fix bugs that can be traced back to issues or design choices in our code, the underlying wxWidgets framework, and some versions of GTK (the choice of which, we typically have no control as we inherit GTK from the users’ Linux distribution). What’s more, the world is shifting to a web based model these days. Cloud deployments are becoming more widely used, as well as simple instances supplied through hosting providers. Users are getting used to being able to pickup their laptop or a tablet and do whatever they need to do without having to install software – open a browser, read and write email, build a spreadsheet or create a database!

Consequently, we're now starting to design pgAdmin 4. The plan is to build a single Python-based pgAdmin that users can either deploy on a web server or run from their desktop. It’ll be designed to “live query” databases rather than query and cache schema details like the current implementation (which was designed with use over a dialup connection in mind!), and we’ll be making the application much more task focused (and therefore usable) than it is at present, as well as implementing some of the oft-requested features that are hard to do in the current code such as direct data editing in the query tool and tabbed query windows. The new code will also be extensible from the ground up, allowing support for new object types or functionality to be simply “dropped in” and auto-discovered at runtime.

We're still in the proof of concept stage at the moment though, so there's nothing available to download just yet. But stay tuned as work kicks up a gear over the next 12 months!