Re: Savepoints in transactions for speed?

On Thu, Nov 29, 2012 at 9:38 AM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
> On Wed, Nov 28, 2012 at 9:48 PM, Claudio Freire <klaussfreire(at)gmail(dot)com>
> wrote:
>>
>> On Wed, Nov 28, 2012 at 8:28 PM, Jeff Davis <pgsql(at)j-davis(dot)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.