Re: Help tuning postgres - Mailing list pgsql-performance

First of all thanks all for the input.

I probably can't afford even the reindex till Christmas, when we have
about 2 weeks of company holiday... but I guess I'll have to do
something until Christmas.

The system should at least look like working all the time. I can have
downtime, but only for short periods preferably less than 1 minute. The
tables we're talking about have ~10 million rows the smaller ones and
~150 million rows the bigger ones, and I guess reindex will take quite
some time.

I wonder if I could device a scheme like:

 - create a temp table exactly like the production table, including
indexes and foreign keys;
 - create triggers on the production table which log all inserts,
deletes, updates to a log table;
 - activate these triggers;
 - copy all data from the production table to a temp table (this will
take the bulk of the time needed for the whole operation);
 - replay the log on the temp table repeatedly if necessary, until the
temp table is sufficiently close to the original;
 - rename the original table to something else, and then rename the temp
table to the original name, all this in a transaction - this would be
ideally the only visible delay for the user, and if the system is not
busy, it should be quick I guess;
 - replay on more time the log;

All this should happen in a point in time when there's little traffic to
the data base.

Replaying could be as simple as a few delete triggers on the log table,
which replay the deleted record on the production table, and the replay
then consisting in a delete operation on the log table. This is so that
new log entries can be replayed later without replaying again what was
already replayed.

The big tables I should do this procedure on have low probability of
conflicting operations (like insert and immediate delete of the same
row, or multiple insert of the same row, multiple conflicting updates of
the same row, etc.), this is why I think replaying the log will work
fine... of course this whole set up will be a lot more work than just
reindex...

I wonder if somebody tried anything like this and if it has chances to
work ?

Thanks,
Csaba.

On Tue, 2005-10-18 at 17:18, Robert Treat wrote:
> reindex should be faster, since you're not dumping/reloading the table
> contents on top of rebuilding the index, you're just rebuilding the
> index.
>
>
> Robert Treat
> emdeon Practice Services
> Alachua, Florida
>
> On Wed, 2005-10-12 at 13:32, Steve Poe wrote:
> >
> > Would it not be faster to do a dump/reload of the table than reindex or
> > is it about the same?
> >
> > Steve Poe
> >
> > On Wed, 2005-10-12 at 13:21 -0400, Tom Lane wrote:
> > > Emil Briggs <emil@baymountain.com> writes:
> > > >> Not yet, the db is in production use and I have to plan for a down-time
> > > >> for that... or is it not impacting the activity on the table ?
> > >
> > > > It will cause some performance hit while you are doing it.
> > >
> > > It'll also lock out writes on the table until the index is rebuilt,
> > > so he does need to schedule downtime.
> > >
> > >             regards, tom lane
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 1: if posting/reading through Usenet, please send an appropriate
> > >        subscribe-nomail command to majordomo@postgresql.org so that your
> > >        message can get through to the mailing list cleanly
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings