Re: Savepoints in transactions for speed? - Mailing list pgsql-performance

From Mike Blackwell
Subject Re: Savepoints in transactions for speed?
Date
Msg-id CANPAkgu4Hspd8+DebGUHaU7smzGW6889uA=v_OnYkvx933VH9A@mail.gmail.com
Whole thread Raw
In response to Re: Savepoints in transactions for speed?  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-performance
Ah.  So it does.  Testing with two psql sessions locks as you said, and moving the DROP INDEX to a separate transaction give the results I was looking for.

Thanks,
Mike

__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com





On Thu, Nov 29, 2012 at 10:54 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Nov 29, 2012 at 9:38 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
> On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfreire@gmail.com>
> wrote:
>>
>> On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql@j-davis.com> wrote:
>> >
>> > The main problem with a long-running delete or update transaction is
>> > that the dead tuples (deleted tuples or the old version of an updated
>> > tuple) can't be removed until the transaction finishes. That can cause
>> > temporary "bloat", but 1.5M records shouldn't be noticeable.
>>
>> Not really that fast if you have indices (and who doesn't have a PK or
>> two).
>>
>> I've never been able to update (update) 2M rows in one transaction in
>> reasonable times (read: less than several hours) without dropping
>> indices. Doing it in batches is way faster if you can't drop the
>> indices, and if you can leverage HOT updates.
>
>
> What I'm trying at this point is:
>
> BEGIN;
> DROP INDEX -- only one unique index exists
> DELETE FROM table;
> COPY table FROM STDIN;
> COMMIT;
> CREATE INDEX CONCURRENTLY;
>
> Do I understand correctly that DROP/CREATE index are not transactional, and
> thus the index will disappear immediately for other transactions?  Am I
> better off in that case moving the DROP INDEX outside the transaction?
>
> The access pattern for the table is such that I can afford the occasional
> stray hit without an index during the reload time.  It's been pretty quick
> using the above.

Drop / create index ARE transactional, like most other things in
postgresql (only drop / create database and drop / create tablespace
are non-transactional).  Your current sequence will result in the
table you are dropping the index on being locked for other
transactions until commit or rollback.  Run two psql sessions and test
it to see.

pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Comparative tps question
Next
From: Jeff Janes
Date:
Subject: Re: Savepoints in transactions for speed?