Re: Database size Vs performance degradation

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