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, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables. Sam -----Original Message----- From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Scott Marlowe Sent: Friday, 5 November 2010 2:04 PM To: Jonathan Hoover Cc: pgsql-admin@xxxxxxxxxxxxxx; Kenneth Marshall Subject: Re: Disk Performance Problem on Large DB On Thu, Nov 4, 2010 at 9:03 PM, Jonathan Hoover <jhoover@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
