From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: REINDEX vs VACUUM
Date: Wed, 4 Jan 2023 09:34:42 -0600
Message-ID: <4dee765f-2196-90b9-efa1-896859552989@gmail.com> (raw)
In-Reply-To: <70615d25-201f-ad4a-1291-806999ca36fb@rblst.info>
References: <CAGXpB2nN=CGRDnQN94iB_oZOUYa76CP-EXjzqyuCxjYnpphWdQ@mail.gmail.com>
<70615d25-201f-ad4a-1291-806999ca36fb@rblst.info>
I don't think VACUUM FULL (copy the table, create new indices and other
metadata all in one command) actually vacuums tables. It's a misleading name.
Something like REBUILD TABLE would be a better name.
On 1/4/23 07:25, Rébeli-Szabó Tamás wrote:
> Here is my understanding:
>
> REINDEX recreates the index from scratch, using the data stored in the
> underlying table. It is the same as dropping and recreating the index
> manually, with regard to the impact on the index file. It can free up
> physical space in the file system. REINDEX will not vacuum the index.
>
> VACUUM does many different things. One of them is vacuuming indexes (for
> the underlying table that is being vacuumed). VACUUM will remove index
> entries that are pointing to dead rows in the underlying table. VACUUM
> will not rebuild the entire index.
>
> VACUUM recycles free index blocks (using FSM), but it does not (usually)
> free up space for the file system physically. VACUUM FULL does that.
>
> VACUUM FULL will vacuum the index, but it will do it by making a copy of
> the index (file) and reorganizing its content in order to free up space
> physically. In that regard, it is like REINDEX. Both VACUUM FULL and
> REINDEX will block reads from the index during the process (by taking an
> ACCESS EXCLUSIVE lock).
>
> Regards,
>
> tamas
>
> 2022. 12. 31. 22:04 keltezéssel, Hao Zhang írta:
>> What is the difference between reindex and vacuum's impact on index file?
>> I deleted an indexed row. Running either vacuum or reindex shows the
>> index entry for the row is removed from the index page. I was under the
>> impression that only reindex will remove dangling index entries. I am
>> guessing that vacuum will not shrink the index file and will only add
>> deleted index entries in the free space file for the index? But REINDEX
>> is recreating the index file from scratch so it is like vacuum full for
>> index?
>>
>> Thanks
>
>
--
Born in Arizona, moved to Babylonia.
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-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: REINDEX vs VACUUM
In-Reply-To: <4dee765f-2196-90b9-efa1-896859552989@gmail.com>
References: <CAGXpB2nN=CGRDnQN94iB_oZOUYa76CP-EXjzqyuCxjYnpphWdQ@mail.gmail.com> <70615d25-201f-ad4a-1291-806999ca36fb@rblst.info> <4dee765f-2196-90b9-efa1-896859552989@gmail.com>
* 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