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!

16 comments:

  1. Fantastic news. Good luck to the team!

    ReplyDelete
  2. Thank you for sharing that story and, of course, for this outstanding work (pgAdmin). I'll be waiting for pgAdmin 4 (or pgAdmin IV?)

    ReplyDelete
  3. Great news and Hood luci!
    Thank you forma the great job zone so far.
    Nave considered using Perl? Several pg-related tools are written in such language and this could ne the starting point forma a better integrati in.

    ReplyDelete
    Replies
    1. None of the existing pgAdmin team particularly like Perl that I'm aware of, and it's not renowned for having high quality web frameworks.

      Delete
    2. Do you believe could it be worthing joining the efforts of PostgreSQL Studio, which is also web based (even if stands on Java technology)?

      Delete
    3. The fact that, as you say, it's a java app and it's Tomcat based puts me off immediately as I'm not a fan of those technologies. I also have no idea what the code is like structurally - having written 3 different implementations of pgAdmin in the past, I have some very specific ideas about how it needs to be done to be maintainable over the long term.

      Delete
  4. This is great. I use pgAdmin every day. How can I get involved as a programmer in pgAdmin 4?

    ReplyDelete
    Replies
    1. Once we have the initial infrastructure code in place it'll be made available in the git repo, at which point the answer becomes "in the same way as pgAdmin 3" - e.g. choose a task you wish to work on, propose it on the -hackers list, and when accepted, get coding :-).

      Delete
  5. That's great! I look forward to trying it out.

    - gabrielle

    ReplyDelete
  6. Did you consider the Eclipse Data Tooling project as a platform for the new pgAdmin4 ?

    ReplyDelete
    Replies
    1. Eclipse isn't web based is it? Besides, Java isn't a preferred language for many of us I'm afraid.

      Delete
  7. First, thanks a lot for really cool tool. We use pgAdmin day in day out and it served us well over the course of the years. Are you really sure about going that python/web-way ? I am a c++ programmer and do a fair amount of python as well and experience shows (my experience at least), that even in the wonderful world of the web few things are able to beat a good old native client. Especially when you are dealing with heavy datasets and memory intensive workloads. The web/python model tends to become dog-slow and finally just falls apart. We have more than once seen prototypes work and seen final products break apart under load. If the toolset is the problem, have you looked at QT ? We have done a few custom-postgres-frontend things with that and it cuts through million-row datasets quite nicely. Is that architecture-decision already been made ? Anyway, thanks again.

    ReplyDelete
    Replies
    1. Handling million row datasets is not something pgAdmin was ever intended to do anyway. How can anyone realistically examine that much data? We have looked at QT - and in fact the prototype runtime for the desktop distribution of the pgAdmin 4 is written using it. It doesn't help us fulfill our goal of a web based deployment option.

      Delete
    2. You are right. The million-row set was just to determine limits and see how far we would get. I just wanted to point out that c++ still offers lots of power and performance, something that python, in our setting, has not always been able to deliver. But do i understand you correctly that there will still be a native client ?

      Delete
    3. Of sorts - we'll provide some sort of runtime and include a "desktop mode" to enable you to run it as a desktop application.

      Delete