20th September, 2007

Insanity

Filed under: database — admin @ 5:29 pm

Today I’ve come across a new definition of insanity. It is this;

Expecting an update of every row in a 13 million row table (which is subject to frequent updates) to complete in one pass without timing out.

Of course, this wouldn’t be necessary if the system in question was running a version of PostgreSQL that was released after 2003. Damn those risk averse system administrators.

I should probably explain. In versions of PostgreSQL before release 8 you cannot increase the size of a column. Therefore to increase the size of one of the columns in our production database we have to add a new column, copy the contents of the old column to the new column, rename both columns and then drop the old column. Now that is what I call a database refactoring.

6 Comments

  1. Yea i feel your pain. Could you not have pd_dumped the data and created copied the old table structure and pg_restore to the new structure???
    I’ve tried this a couple of times with good results but not with 13 M rows!!!.

    Comment by Chris Clarke — 21/09/2007 @ 9:57 pm

  2. Yea i feel your pain. Could you not have pd_dumped the data and created copied the old table structure and pg_restore to the new structure???
    I’ve tried this a couple of times with good results but not with 13 M rows!!!.

    Comment by Chris Clarke — 21/09/2007 @ 9:57 pm

  3. We looked at it, but bearing in mind that other updates are happening to the table in real time it was a little impractical.

    Comment by Andy Todd — 21/09/2007 @ 9:59 pm

  4. We looked at it, but bearing in mind that other updates are happening to the table in real time it was a little impractical.

    Comment by Andy Todd — 21/09/2007 @ 9:59 pm

  5. [...] Insanity [...]

    Pingback by Python Bytes - Today’s Top Blog Posts on Python - Powered by SocialRank — 01/10/2007 @ 8:04 pm

  6. [...] Insanity [...]

    Pingback by Python Bytes - Today’s Top Blog Posts on Python - Powered by SocialRank — 01/10/2007 @ 8:04 pm

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress