PGCon2009 - Final Release

PGCon 2009
The PostgreSQL Conference

Chris Hoover
Day Talks - second day - 2009-05-22
Room DMS 1160
Start time 13:30
Duration 01:00
ID 155
Event type Lecture
Track DBA
Language used for presentation English

Database Refactoring

An idea on how to redesign an existing database with minimal downtime

Most DBA's have areas of the databases that they manage that they would like to change. However, very few are actually given the liberty, resources, and time to make these changes due to the impact it would have on the existing system's code and the impact on the customers. What if there were a way to get around these obstacles and make the structure changes you want and need without requiring an application rewrite?

Many DBA's inherit databases with less than optimal designs. Unfortunately, most of these database are servicing pre-existing application/websites where management can not or will not provide the resources to migrate the existing code to a more normalized and optimized database structure, or there is not enough downtime available to do this conversion/migration. However, for dba's stuck in this position, there may be hope.

This presentation will show you how you can redesign and normalize your database without long periods of downtime and without having to have your developers rework all of the existing sql. We will demonstrate a way to use many of the concepts from materialized views and other areas of Postgres to make your desired changes behind the scenes. These changes should be quick and relatively painless and will allow your application to function without having all of your data migrated to the new schema. Best of all, we will show you how you can do this one table/area at a time!