From: Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu>
To: zoltan.sebestyen@netvisor.hu
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] index file's growing big
Date: Mon, 6 Mar 2000 10:24:33 -0600
Message-ID: <20000306102433.B27448@rice.edu> (raw)
In-Reply-To: <617185BB0AA2D111872400105AD899AD1490D6@netvisorpdc.intranet.netvisor.hu>
References: <617185BB0AA2D111872400105AD899AD1490D6@netvisorpdc.intranet.netvisor.hu>
Zoltan -
It sounds like this is a very active table in you app, with lots of
inserts and deletes, right? Basically what's happening is that deletes
from the underlying table don't remove entries from the index file,
just marks them unused. And inserts add a new record at the end.
This monotonic growth of index files is a known problem. I assume you're
using v. 6.5.3 or earlier: I believe the current 7.0 beta will reuse
index file entries, rather than append them, although it still doesn't
'vacuum' them of unused entries, if I understand correctly.
The dump->reload solves the problem for you by rebuilding the index. So,
rather than dumping the whole table, just drop and rebuild the index in
question. This can still take a long time if it's a big table, but my
guess is that it'll work for you.
Ross
On Mon, Mar 06, 2000 at 12:23:29PM +0100, zoltan.sebestyen@netvisor.hu wrote:
> Hi,
>
> I wrote a web+commandline based package with PostgreSQL database backend.
> My problem is that as time goes by the index file grows to a huge size
> [80Mb]. If I dump and then reload it the database in question I'll get the
> very same data with a quite small index file. So, there's no inconsistency
> or corrupt database, but it's still annoying that I must handle manually.
> What I think is tha the command-line app I wrote for populating the
> database uses some wrong strategy, I mean there's no erroneous SQL phares,
> just may be it uses wrong tactics that's why it grows the index file to a
> quite big size.
>
> Any ideas?
>
> > Zoltan Sebestyen
> > NETvisor Kft
> > 1111 Muegyetem rkpt.9. Fsz.8.
> > Budapest, Hungary.
> > Fax: (+36-1) 463-4396
> > Tel: (+36-1) 463-4392, 463-4393
> >
> > "Programming is an art, not a science,
> > and not all programmers are Picassos."
> >
> >
> <<Sebestyén Zoltán.vcf>>
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-sql@postgresql.org
Cc: reedstrm@wallace.ece.rice.edu, zoltan.sebestyen@netvisor.hu
Subject: Re: [SQL] index file's growing big
In-Reply-To: <20000306102433.B27448@rice.edu>
References: <617185BB0AA2D111872400105AD899AD1490D6@netvisorpdc.intranet.netvisor.hu> <20000306102433.B27448@rice.edu>
* 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