Обсуждение: index file's growing big

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

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

Uh, why not just "vacuum" the table?

You want to do this periodically anyway, as Postgres does not re-use TABLE
entries either, and the table file itself will grow without bound if you
don't do this once in a while.

Further, if the tables are large you want to use "vacuum analyze" to reorder
the indices in a fashion that makes queries more efficient.

--
-- 
Karl Denninger (karl@denninger.net)  Web: http://childrens-justice.org
Isn't it time we started putting KIDS first?  See the above URL for
a plan to do exactly that!


On Mon, Mar 06, 2000 at 10:24:33AM -0600, Ross J. Reedstrom wrote:
> 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>> 
> 
> 
> 
> ************
> 

On Mon, Mar 06, 2000 at 10:46:19AM -0600, Karl Denninger wrote:
> Uh, why not just "vacuum" the table?
> 
> You want to do this periodically anyway, as Postgres does not re-use TABLE
> entries either, and the table file itself will grow without bound if you
> don't do this once in a while.
> 
> Further, if the tables are large you want to use "vacuum analyze" to reorder
> the indices in a fashion that makes queries more efficient.
> 

Well, with a recent version, that will help keep the index from growing as
fast, but it will still grow. That's the point of my garbled description.

However, if Zoltan is _not_ vacuuming regularly, that's a problem as well.
If he wasn't, though, I'd have expected him to complain about the size
of the table file, not the index file.

Ross

-- 
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu> 
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005

Karl Denninger <karl@Denninger.Net> writes:
> Further, if the tables are large you want to use "vacuum analyze" to reorder
> the indices in a fashion that makes queries more efficient.

Just a side comment here: VACUUM ANALYZE has nothing whatever to do
with "reordering" indexes.  What it does that a plain VACUUM does
not do is gather statistics about the table contents (minimum, maximum,
and most common value of each column, at present).  These stats are then
used by the optimizer to make better planning choices.  At least we'd
like to think they're better, Lord willin' an' the creek don't rise...
        regards, tom lane