pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: autovacuum for large periodic deletes
Date: Wed, 17 May 2006 21:48:16 +0000
Message-ID: <60y7x07273.fsf@dba2.int.libertyrms.com> (raw)
References: <6992E470F12A444BB787B5C937B9D4DF045AF177@ca-mail1.cis.local>

sdandapani@counterpane.com ("Sriram Dandapani") writes:
> About 5-10 million rows stay after deletion. There are a few other
> tables where the daily deletion totals about 3-6 million. 

It would appear there is something fairly not-sane about the process,
then.  You delete about 90% of the day's data from the table each day?
That's a *lot*, and you are quite likely to have trouble with this
table blowing out the Free Space Map as a result.

> Would a vacuum full/cluster affect other operations. These tables
> have a 24x7 high data insertion rate.

Yes, VACUUM FULL and CLUSTER would block other operations while they
run.

The real Right Answer probably involves having data flow into some
sort of "queue" table, created fresh each day, for that day's
activities, where, at the end of the day, all of the data either gets
purged or moved to the "final destination" table, so that a new table
can be created, the next day.
-- 
"cbbrowne","@","cbbrowne.com"
http://cbbrowne.com/info/unix.html
CBS News report on Fort Worth tornado damage:
"Eight major downtown buildings were severely damaged and 1,000 homes
were damaged, with 95 uninhabitable.  Gov. George W. Bush declared
Tarrant County a disaster area.  Federal Emergency Management Agency
workers are expected to arrive sometime next week after required
paperwork is completed."



view thread (8+ messages)  latest in thread

Message-ID: <60y7x07273.fsf@dba2.int.libertyrms.com>
Permalink:  ../60y7x07273.fsf@dba2.int.libertyrms.com/
Also on:    postgresql.org/message-id/60y7x07273.fsf@dba2.int.libertyrms.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: cbbrowne@acm.org
  Subject: Re: autovacuum for large periodic deletes
  In-Reply-To: <60y7x07273.fsf@dba2.int.libertyrms.com>
  References: <6992E470F12A444BB787B5C937B9D4DF045AF177@ca-mail1.cis.local> <60y7x07273.fsf@dba2.int.libertyrms.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox