Fun Work with Replicated PostgreSQL
Today has been kinda fun, my Greek Engine reads instrument static data from some legacy databases, and then merges these many sources into one useful source of data. It takes about a minute, which isn't horrible, but it's an expense in getting the logic right, and adding new features based on this data is a serious pain. Thankfully, my co-worker had the idea of making a "data service" that's really just a replicated PostgreSQL 9 database where the master is written to by update jobs, and the data is replicated to read-only slaves on each server box for local access.
It allows us to do all this convoluted logic in one place and let it be shared by all the groups and applications that need it. It doesn't have to be normalized, and it's probably better if it isn't - the queries will be simpler and faster without all the foreign keys.
So he's been spending time getting this master database up and populated and updating with data from a lot of different sources, and today I've been working on integrating this into my Greeks Engine. It's a lot of fun because PostgreSQL 9 is fast, simple, and the support tools are just amazing. It's been a favorite of mine for many years, and the new version, 9.1, is no exception.
Replication is fast, easy, and efficient. It's complete and so far as we can tell now, fool-proof. Wonderful news. The only wrinkle is the development tools - on my linux box, they have both 8.4 and 9.0 and there was a little confusion this morning about what was on and what wasn't. I needed to get that cleared up before anything I wrote worked.
Updating the code was pretty simple - and greatly simplified. No longer do I have to have 50 line SQL queries to get the data. It's there and it works. No more ODBC drivers, either. Holy Cow! What a relief.
I need to do a little more work in the morning, but it's a great start.