Actually, this is a better definition of TRUNCATE than my previous post. From the doco:
TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table,
butsince it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather
thanrequiring a subsequent VACUUM operation. This is most useful on large tables.
Sam
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Scott Marlowe
Sent: Friday, 5 November 2010 2:04 PM
To: Jonathan Hoover
Cc: pgsql-admin@postgresql.org; Kenneth Marshall
Subject: Re: [ADMIN] Disk Performance Problem on Large DB
On Thu, Nov 4, 2010 at 9:03 PM, Jonathan Hoover <jhoover@yahoo-inc.com> wrote:
> 1. I have now set maintenance_work_mem to 256 MB (which was previously commented by the default config)
> 2. The version is PostgreSQL 8.1.18 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-46)
If performance matters, look at upgrading to at LEAST 8.3.latest or
8.4.latest. Preferably 9.0.latest.
> 3. What would be the best and cheapest thing I could for IO performance?
Get an SSD drive. Or a RAID controller with battery backed cache. If
you're data isn't valuable (i.e. you can reproduce it at will) then
turning off things like fsync and full page writes (I don't think 8.1
has the ability to turn off full page writes). can help a lot.
> 4. I need to read up on TRUNCATE, which I have not used before. Care to give a quick overview before I RTFM?
Truncate is basically a DDL (data definition language) command as
opposed to a DML (data manipulation language) command. It baseically
drops the table underneath the table def and recreates it as empty.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin