Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Craig Ringer <ringerc(at)ringerc(dot)id(dot)au>
Cc: Stanislaw Pankevich <s(dot)pankevich(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.
Date: 2012-07-06 15:57:05
Message-ID: CAMkU=1z+R81FqJ9N=u04ooWSGQ4xdGoV6o8LM6+WWzhJPWQQVg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Fri, Jul 6, 2012 at 4:29 AM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:
> On 07/03/2012 11:22 PM, Stanislaw Pankevich wrote:

> > 1) Truncate each table. It is too slow, I think, especially for empty
> > tables.
>
> Really?!? TRUNCATE should be extremely fast, especially on empty tables.
>
> You're aware that you can TRUNCATE many tables in one run, right?
>
> TRUNCATE TABLE a, b, c, d, e, f, g;

This still calls DropRelFileNodeAllBuffers once for each table (and
each index), even if the table is empty.

With large shared_buffers, this can be relatively slow.

Cheers,

Jeff

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Steve Crawford 2012-07-06 16:06:47 Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.
Previous Message Stanislaw Pankevich 2012-07-06 15:44:34 Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.