pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (8+ messages)  latest in thread

Message-ID: <4dee765f-2196-90b9-efa1-896859552989@gmail.com>
Permalink:  ../4dee765f-2196-90b9-efa1-896859552989@gmail.com/
Also on:    postgresql.org/message-id/4dee765f-2196-90b9-efa1-896859552989@gmail.com

 · 

reply

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