Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE TABLE

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Adriaan van Os" <postgres(at)microbizz(dot)nl>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: TRUNCATE TABLE
Date: 2007-07-11 22:15:52
Message-ID: 87ps2ypox3.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
"Adriaan van Os" <postgres(at)microbizz(dot)nl> writes:

> Recently, I have been doing extensive profiling of a version 8.1.4 Postgres DB
> with about 175 tables and 5 GB of data (the server running on Fedora Linux and
> the clients on Windows XP). Surprisingly, one of the bottlenecks is TRUNCATE
> TABLE and that command is really slow as compared to other operations. For
> example, we have operations like:

What filesystem is this? Some filesystems are notoriously slow at deleting
large files. The mythtv folk who face this problem regularly recommend either
JFS or XFS for this purpose.

Postgres generally doesn't really need to be able to delete large files
quickly. The only times files are deleted which come to mind are when you DROP
or TRUNCATE or possibly when you VACUUM a table.

> I noticed, by the way, that removing records in general is painfully slow, but
> I didn't do a detailed analysis of that issue yet.

That's strange. Deleting should be the *quickest* operation in Postgres. Do
you perchance have foreign key references referencing this table? Do you have
any triggers?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


In response to

Responses

pgsql-performance by date

Next:From: Greg SmithDate: 2007-07-11 23:01:10
Subject: Re: WALL on controller without battery?
Previous:From: Tom LaneDate: 2007-07-11 21:54:45
Subject: Re: TRUNCATE TABLE

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group