Thread: VACUUM and index

VACUUM and index

From
DANTE Alexandra
Date:
Hello List,

Sorry to send an other e-mail on this subject but it is become a little
bit vague for me.
Imagine you have a database on which a lot of transactions are done
daily : the tables will contain a lot of dead tuples and the B-tree
index too.

If I do a VACUUM on this database, will the B-tree index be scanned,
space reclaimed and made available for re-use ?
Does this action only do by a VACUUM FULL ?
Or is it necessary to do a REINDEX on the index to retrieve a
well-balanced tree ?

Thank you for your help.

Regards,
Alexandra DANTE

Re: VACUUM and index

From
Martijn van Oosterhout
Date:
On Wed, Jul 19, 2006 at 03:03:43PM +0200, DANTE Alexandra wrote:
> Imagine you have a database on which a lot of transactions are done
> daily : the tables will contain a lot of dead tuples and the B-tree
> index too.
>
> If I do a VACUUM on this database, will the B-tree index be scanned,
> space reclaimed and made available for re-use ?

Old versions didn't, but recent versions do.

> Does this action only do by a VACUUM FULL ?

Also by VACUUM FULL, but plain VACIIM is enough.

> Or is it necessary to do a REINDEX on the index to retrieve a
> well-balanced tree ?

There are always corner case situations. In older versions a REINDEX
helped balance the tree but recent versions are much better.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: VACUUM and index

From
DANTE Alexandra
Date:
Thank you Martijn for your answer !
I use the 8.1.3 release of PostgreSQL.

Will it be sensible to launch an ANALYZE after the VACUUM to update
statistics ?

Regards,
Alexandra

Martijn van Oosterhout wrote:

>On Wed, Jul 19, 2006 at 03:03:43PM +0200, DANTE Alexandra wrote:
>
>
>>Imagine you have a database on which a lot of transactions are done
>>daily : the tables will contain a lot of dead tuples and the B-tree
>>index too.
>>
>>If I do a VACUUM on this database, will the B-tree index be scanned,
>>space reclaimed and made available for re-use ?
>>
>>
>
>Old versions didn't, but recent versions do.
>
>
>
>>Does this action only do by a VACUUM FULL ?
>>
>>
>
>Also by VACUUM FULL, but plain VACIIM is enough.
>
>
>
>>Or is it necessary to do a REINDEX on the index to retrieve a
>>well-balanced tree ?
>>
>>
>
>There are always corner case situations. In older versions a REINDEX
>helped balance the tree but recent versions are much better.
>
>Have a nice day,
>
>


Re: VACUUM and index

From
Martijn van Oosterhout
Date:
On Wed, Jul 19, 2006 at 03:23:08PM +0200, DANTE Alexandra wrote:
> Thank you Martijn for your answer !
> I use the 8.1.3 release of PostgreSQL.

You'll be fine...

> Will it be sensible to launch an ANALYZE after the VACUUM to update
> statistics ?

Or do both at the same time (VACUUM ANALYZE).

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment