From: Mark Roberts <mailing_lists@pandapocket.com>
To: Dave North <DNorth@signiant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Database size Vs performance degradation
Date: Wed, 30 Jul 2008 08:46:16 -0700
Message-ID: <1217432776.6288.40.camel@localhost> (raw)
In-Reply-To: <35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>
References: <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
<276541.4189.qm@web25805.mail.ukl.yahoo.com>
<35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mailing_lists@pandapocket.com, DNorth@signiant.com
Subject: Re: Database size Vs performance degradation
In-Reply-To: <1217432776.6288.40.camel@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox