From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Fwd: (Re)-indexing on updates
Date: Sun, 21 Aug 2005 21:59:11 +0200
Message-ID: <e0ca8bdefa68e65f3b2a77374a77396d@implements.be> (raw)
My DB is quite simple. It holds data about printjobs that come from
the windows eventlog.
The data is shown on a website. I have one main table: tblPrintjobs.
We add some extra data to it. Like the applicationtype, based on rules
we define in other tables.
When a rule changes, the updates take place (and take so long).
Also, when new records are added, this takes place.
For instance, rule 1 and rule 2 are changing positions in importance.
(1 was before 2, now 2 before 1)
The records that hold reference to rule 1 are reset to null (one field)
Rule 2 is assigned, then rule 1 is assigned.
What I could do is also:
delete all from tblRefRules where rule is 1
insert all from tblPrintjobs that are not yet in RefRules for Rule2,
then insert all for rule2
That would be a workaround for the MVCC. Not ?
BTW: The good rule is: drop index, update, vacuum, create index ?
I think I mistook the purpose of vacuum.
If I index before the vacuum, my marked records will still be in the
index ? Even if all transactions are finished ?
Begin forwarded message:
> From: "Jeffrey W. Baker" <jwbaker@acm.org>
> Date: Sun 21 Aug 2005 21:36:16 CEST
> To: Yves Vindevogel <yves.vindevogel@implements.be>
> Subject: Re: [PERFORM] (Re)-indexing on updates
>
> On Sun, 2005-08-21 at 21:18 +0200, Yves Vindevogel wrote:
>>
>>
>> ______________________________________________________________________
>>
>> Ok, this is a major setback in some of my procedures.
>> From time to time, I must update one field in about 10% of the
>> records.
>> So this will take time.
>>
>> How can I work around that ?
>>
>> Some personal opinions ...
>> 1) Drop indexes, run update, create indexes, vacuum
>
> Drop index, update, vacuum, create index
>
> -or-
>
> update, vacuum, reindex
>
>> 2) Move the field to another table and use joins ? I could delete the
>> records when needed and add them again
>
> I'm not familiar with your application, but you could try it and tell
> us
> if this works :)
>
>>
>> This mechanism, of inserting a new record and marking the old one, is
>> that data kept somewhere where I can "see" it ?
>
> This is MVCC: multi-version cuncurrency. The old record is kept
> because
> there could be an old transaction that can still see it, and cannot yet
> see the updated record. And no other transaction can see your record
> until you commit. The old row isn't removed until you vacuum.
>
>> I need for one app a trace of all my changes in the database. I have
>> a set of triggers to do that for the moment on each table.
>> Could I use that mechanism somehow to avoid my triggers ?
>> Any documentation on that mechanism (hacker stuff like what tables are
>> used) ?
>
> You could search the postgresql documentation (or the web) for MVCC.
>
> Regards,
> jwb
>
>
>
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) downloadview image
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: yves.vindevogel@implements.be
Subject: Re: Fwd: (Re)-indexing on updates
In-Reply-To: <e0ca8bdefa68e65f3b2a77374a77396d@implements.be>
* 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