From: a3a18850@telus.net
To: Bill Chandler <billybobc1210@yahoo.com>
Cc: pgsql-perform <pgsql-performance@postgresql.org>
Subject: Re: Index bloat problem?
Date: Thu, 21 Apr 2005 11:41:48 -0700
Message-ID: <1114108908.4267f3ec2e0ea@webmail.telus.net> (raw)
In-Reply-To: <20050421170018.2998.qmail@web51403.mail.yahoo.com>
References: <20050421170018.2998.qmail@web51403.mail.yahoo.com>
Quoting Bill Chandler <billybobc1210@yahoo.com>:
> Running PostgreSQL 7.4.2, Solaris.
> Client is reporting that the size of an index is
> greater than the number of rows in the table (1.9
> million vs. 1.5 million). Index was automatically
> created from a 'bigserial unique' column.
> We have been running 'VACUUM ANALYZE' very regularly.
> In fact, our vacuum schedule has probably been
> overkill. We have been running on a per-table basis
> after every update (many per day, only inserts
> occurring) and after every purge (one per day,
> deleting a day's worth of data).
>
> What about if an out-of-the-ordinary number of rows
> were deleted (say 75% of rows in the table, as opposed
> to normal 5%) followed by a 'VACUUM ANALYZE'? Could
> things get out of whack because of that situation?
I gather you mean, out-of-the-ordinary for most apps, but not for this client?
In case nobody else has asked: is your max_fsm_pages big enough to handle all
the deleted pages, across ALL tables hit by the purge? If not, you're
haemorrhaging pages, and VACUUM is probably warning you about exactly that.
If that's not a problem, you might want to consider partitioning the data.
Take a look at inherited tables. For me, they're a good approximation of
clustered indexes (sigh, miss'em) and equivalent to table spaces.
My app is in a similar boat to yours: up to 1/3 of a 10M-row table goes away
every day. For each of the child tables that is a candidate to be dropped, there
is a big prologue txn, whichs moves (INSERT then DELETE) the good rows into a
child table that is NOT to be dropped. Then BANG pull the plug on the tables you
don't want. MUCH faster than DELETE: the dropped tables' files' disk space goes
away in one shot, too.
Just my 2c.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a3a18850@telus.net, billybobc1210@yahoo.com
Subject: Re: Index bloat problem?
In-Reply-To: <1114108908.4267f3ec2e0ea@webmail.telus.net>
* 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