Re: How to improve insert speed with index on text column

From: Claudio Freire <klaussfreire(at)gmail(dot)com>
To: Rosser Schwarz <rosser(dot)schwarz(at)gmail(dot)com>
Cc: Saurabh <saurabh(dot)b85(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How to improve insert speed with index on text column
Date: 2012-02-01 03:49:09
Message-ID: CAGTBQpYZY=0KjBXQK5fRDbHJ+RvKk9RiS6eFM+ELOcfCojwWGw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Feb 1, 2012 at 12:29 AM, Rosser Schwarz
<rosser(dot)schwarz(at)gmail(dot)com> wrote:
> Remember, DDL is transactional in PostgreSQL.  In principle, you
> should be able to drop the index, do your inserts, and re-create the
> index without affecting concurrent users, if you do all of that inside
> an explicit transaction.  Doing the inserts inside a transaction may
> speed them up, as well.

Creating an index requires an update lock on the table, and an
exclusive lock on the system catalog.
Even though with "CONCURRENTLY" it's only for a short while.
So it does affect concurrent users.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Claudio Freire 2012-02-01 03:51:03 Re: How to improve insert speed with index on text column
Previous Message Rosser Schwarz 2012-02-01 03:29:18 Re: How to improve insert speed with index on text column