From: Jeff Davis <pgsql@j-davis.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Mike Blackwell <mike.blackwell@rrd.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Savepoints in transactions for speed?
Date: Wed, 28 Nov 2012 15:18:42 -0800
Message-ID: <1354144722.1766.74.camel@sussancws0025> (raw)
In-Reply-To: <CAGTBQpZ-NLHeZPnD9m2O-UZfMba14t1aU2K73c0tOvz_w232LQ@mail.gmail.com>
References: <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
<2000E26E-35FC-4801-90F3-3CFBDA7B41DF@yahoo.com>
<CANPAkgsod2N+=oviHmhC0uXPAZixe4PPbMt_cx0iRLanyQdnHg@mail.gmail.com>
<CAGTBQpZ-NLHeZPnD9m2O-UZfMba14t1aU2K73c0tOvz_w232LQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Tue, 2012-11-27 at 22:16 -0300, Claudio Freire wrote:
> Updates, are faster if batched, if your business logic allows it,
> because it creates less bloat and creates more opportunities for with
> HOT updates. I don't think it applies to inserts, though, and I
> haven't heard it either.
Huge updates (e.g. UPDATE with no WHERE clause) are less likely to
benefit from HOT. HOT has two main optimizations:
1. Remove dead tuples faster without waiting for VACUUM -- this only
works if the transaction that updated/deleted the tuple actually
finished (otherwise the tuple can't be removed yet), so it only benefits
the *next* update to come along. But if it's one big update, then VACUUM
is probably just as good at cleaning up the space.
2. Doesn't make new index entries for the new tuple; reuses the old
index entries -- this only works if the update is on the same page, but
large updates tend to fill pages up (because of the buildup of dead
tuples) and force new to go to new pages.
HOT is essentially designed for lots of small updates, which didn't
perform well before PG 8.3.
Batching of inserts/updates/deletes has a big benefit over separate
transactions, but only up to a point, after which it levels off. I'm not
sure exactly when that point is, but after that, the downsides of
keeping a transaction open (like inability to remove the previous
version of an updated tuple) take over.
Regards,
Jeff Davis
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: pgsql@j-davis.com, klaussfreire@gmail.com, mike.blackwell@rrd.com
Subject: Re: Savepoints in transactions for speed?
In-Reply-To: <1354144722.1766.74.camel@sussancws0025>
* 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