I’ve recently had to take over the role of DBA at work (our previous one left for a job at Google), and I’m trying to make the most of the situation (still have my job to do too) by restructuring the PostgreSQL-powered database at the core of our Web architecture.

Like so many enterprise projects, it’s grown exponentially, in both size and complexity, over the years and what I’m left in charge of today is less then ideal. Nonetheless it’s been serving us quasi-flawlessly, and I sure am happy my predecessor(s) made the choice to go with PostgreSQL as a database backend. The use of PostgreSQL in an enterprise environment was actually one of the reasons I started working at McGill, back in 2002.

What I’m doing these days involves modernizing and sanitizing a considerable number of tables, stored procedures and functions. All while staying fully backward compatible so that the countless pieces of software relying on the data can keep on running as if nothing changed. I’m of course modernizing the codebase I have access to so it all takes advantage of the improved data structure. But for the sake of phasing in the upgrade and to not force it on external developers whose schedule I have no control over, replicating the current base is a of the essence.

This is all proving to be a task our faithful PostgreSQL environment is truly shining at.

Through the use of temporary tables from queries, case-based views, rules and other assorted options, I am rather quickly and easily able to author scripts that handle the nasty stuff, all wrapped in the safety transactional DBs afford us. They create new tables, populate them from others, tweak the data, drop the old tables once ported, setup views to replace them just-in-time and more, all transparently.

All of this is of course also possible with many other RDBMS. I’m just dealing with PostgreSQL in this instance, and enjoying (almost) every minute of it! :)