Re: Subject: bool / vacuum full bug followup part 2

Well, my keys aren't changing and the index is growing like they are.
I.e. the update statement is:

update table set field1=y where field1=y;

I'm not adding rows, I'm not deleting rows, and I'm not even changing the
value really.  I'm sure this is the problem the earlier poster was
complaining about making him dump and restore his whole database every few
weeks.

I'd say the indexes keep growing whether the data they are indexing
changes or not from my observations.  Do your own tests if you want, but
so far this looks like a serious issue for joe average DBA.  He expects
vacuum [full] to reclaim all his unused space, but indexes, which are
constantly growing, are not reclaimed, and his store fills up.  Leading
him to dump / restore instead of just reindexing.

We should at least add something to the administrator docs to say you need
to reindex heavily updated indexes to save space.

Here's my quick and dirty php script to reindex all indexes in all
databases on a given server.  It's ugly, but it works.

#!/usr/local/bin/php -q
<?php
$conn0 = pg_connect("dbname=template1 user=postgres");
$res0 = pg_exec($conn0,"select datname from pg_database where datname not
like 'template%'");
$drows = pg_numrows($res0);
for ($j=0;$j<$drows;$j++){
        $datname = pg_result($res0,$j,'datname');
        print 'database: '.$datname."n";
        $conn1 = pg_connect("dbname=$datname user=postgres");
        $res1 = pg_exec($conn1,"select indexname from pg_indexes where
indexname not like 'pg_%'");
        $rows = pg_numrows($res1);
        for ($i=0;$i<$rows;$i++){
                $indexname = pg_result($res1,$i,indexname);
                $query = "reindex index $indexname";
                print 'table: '.$indexname."\n";
                pg_exec($conn1,$query);
        }
}
?>

On Fri, 3 May 2002, Tom Lane wrote:

> Scott Marlowe <scott.marlowe@ihs.com> writes:
> > And reclaimed the space.  Is that the official way, short of dropping and
> > recreating an index to reclaim its space?  Is there a plan to make vacuum
> > reclaim unused space in indexes?
>
> Yes, and yes, but don't hold your breath on the latter part --- that
> TODO item has been around for awhile.  And it's gotten harder now that
> we have lazy VACUUM; that means we need to be able to condense indexes
> concurrently with other index operations.
>
> AFAIK there's not a big problem with index growth if the range of index
> keys remains reasonably static.  The problem comes in if you have a
> range of values that keeps growing (eg, you are indexing a SERIAL or
> timestamp column).  The right end of the btree keeps growing, but
> there's no mechanism to collapse out no-longer-used space at the left
> end.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>