Re: Database size Vs performance degradation - Mailing list pgsql-performance

From Mark Roberts
Subject Re: Database size Vs performance degradation
Date
Msg-id 1217432776.6288.40.camel@localhost
Whole thread Raw
In response to Re: Database size Vs performance degradation  ("Dave North" <DNorth@signiant.com>)
List pgsql-performance
On Wed, 2008-07-30 at 10:02 -0500, Dave North wrote:
> Thank you for the suggestion..much appreciated.  Alas, I don't think
> this will be possible without a change to the application but it's a
> good idea nonetheless.

Affirmative, Dave.  I read you.

If I were in your situation (not having access/desire to change the base
application), I'd write a sql script that does something like this:

- Create __new_table__ from old_table # Read lock on old table
- Rename old_table to __old_table__ # Access Exclusive Lock
- Rename __new_table__ to old_table # Access Exclusive Lock
- Commit # Now the application can write to the new table
- Sync newly written changes to the new table (these would be written
between the creation and access exclusive lock).
- Drop/Vacuum full/Archive old_table

Well, it would at least let you get the benefits of the rename approach
without actually altering the application.  Additionally, the
application's writes would only be blocked for the duration of the
rename itself.

This makes the assumption that these writes aren't strictly necessary
immediately (such as via a find or insert construct).  If this
assumption is false, you would need to lock the table and block the
application from writing while you create the temporary table.  This has
the advantage of not requiring the final sync step.

Sorry if all of this seems redundant, but best of luck!

-Mark


pgsql-performance by date:

Previous
From: Craig James
Date:
Subject: Re: Database size Vs performance degradation
Next
From: Matthew Wakeling
Date:
Subject: Re: Database size Vs performance degradation