PostgreSQL

Australian IT News has picked up on my recent post about Oracle shutting down their buildfarm servers. It's good publicity for PostgreSQL but I doubt it will shame them into continuing to support us.

Meanwhile, EnterpriseDB through Dave Page have added some more Solaris members. Thanks, guys.

I don't really have a good title for this post, so I just thought I'd title it with one of my favourite Pink Floyd songs.

A few years ago, the author of Rails somewhat famously opined thus:

Active Record is opinionated software, just like the rest of Rails. This is a matter of opinion, not constraints. And the opinion goes as follows: I don't want my database to be clever! Keep those crayons firmly in place, please.

Unlike Christopher, I consider stored procedures and constraints vile and reckless destroyers of coherence. No, Mr. Database, you can not have my business logic. Your procedural ambitions will bear no fruit and you'll have to pry that logic from my dead, cold object-oriented hands.

I was reminded for the umpteenth time yesterday just how really, really silly this is. The only part of it that is spot on is the first clause, and I suspect that's because he didn't know that the actual meaning of the word opinionated is "unduly adhering to one's own opinion or to preconceived notions". Calling someone opinionated is not a compliment - it's an insult.

Here's what happened. Like a great many times before, a client sent me some stuff that was running very slowly and asked me how to speed it up. It was a piece of client side code that made some calls to the database to retrieve data and perform some checks on it and report on those by stashing the exception cases in a table to be later reviewed by the Data Quality team. It's a classic piece of business logic, and according to the dictum above does not belong inside the database. I rewrote it as a PL/pgSQL stored procedure. I didn't change the logic in any significant way. I simple stopped all that data being shipped out to a client to perform checks on it. The result was a reduction in execution time from the client's reported 6 minutes to 3 seconds.

This repeats a pattern I have seen hundreds or thousands of times. So here's my alternative dictum:

Let the database be good at what it's good at, including smart processing of bulk data. If you're lucky enough to be using PostgreSQL, you can implement the business logic in your favourite OO language (e.g. Ruby). Avoid shipping data to an external client program just to process and ship stuff back to the database. Only fetch data from the database if you need it to display or send to another system.

Or keep doing what you're doing, so people will continue to pay people like me to fix it

This weekend there will be a full pg_dump backup of the buildfarm database. After that I'm going to purge old log data (anything older than six months). Then we we're going to move it to PostgreSQL 8.4 as soon as possible - within a week I hope.

After that I hope to be able to implement Full Text Search on the logs.

The upgrade will involve some downtime, naturally. Buildfarm owners will be advised beforehand. But it shouldn't take too long to restore the 10Gb or less of data I am expecting.

I'm also planning for migrating my buildfarm animals to the new git repo when it comes online. I'll actually be travelling at that time, so I hope I can get it done without too much difficulty remotely. The one animal I have left building from CVS I will point at the git-cvs mirror. The remainder I will point directly at the public git repo initially. Later on I'll set up a local clone of the public repo and have them all pull from that. I hope that we don't have too many buildfarm members go quiet when CVS gets frozen.

At least one of my lessons from today is that the PostgreSQL JDBC driver now has support for COPY. I wonder how I missed that.

Three weeks ago, Oracle/Sun turned off five of the seven buildfarm members they had been running. Two weeks ago they turned off the other two. So they really do know how to play nicely in the open source community.

Luckily, there are other people who care about supporting PostgreSQL on Solaris / SPARC, and I'm happy that today we've registered two new buildfarm members for this platform. Thanks to Dave Page.

Some years ago I proposed that we should branch the source code tree much earlier than we have been doing. I didn't get much support at that time, but this year it was proposed again and we have in fact now done it, if not as early as I'd like, at least earlier than we have been in recent release cycles.

Apart from anything else that means buildfarm owners need to add REL9_0_STABLE to the list of branches they are now building. A few people have done that, but we need to add to the list. I have changed two of my regular buildfarm members, and will get the rest done today. It's important to get this coverage as quickly as possible as we run up to the release date for 9.0.