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>>