pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedREINDEX vs VACUUM
8+ messages / 5 participants
[nested] [flat]
* REINDEX vs VACUUM
@ 2022-12-31 21:04 Hao Zhang <kennthhz@gmail.com>
2023-01-03 17:57 ` Re: REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
0 siblings, 2 replies; 8+ messages in thread
From: Hao Zhang @ 2022-12-31 21:04 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
@ 2023-01-03 17:57 ` Hao Zhang <kennthhz@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Hao Zhang @ 2023-01-03 17:57 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; pgsql-admin@lists.postgresql.org
+ pgsql-admin
Would appreciate any insights. Thanks
On Sat, Dec 31, 2022 at 1:04 PM Hao Zhang <kennthhz@gmail.com> wrote:
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
@ 2023-01-04 13:25 ` Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Re: REINDEX vs VACUUM Ron <ronljohnsonjr@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Rébeli-Szabó Tamás @ 2023-01-04 13:25 UTC (permalink / raw)
To: Hao Zhang <kennthhz@gmail.com>; pgsql-general@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
@ 2023-01-04 15:34 ` Ron <ronljohnsonjr@gmail.com>
2023-01-04 21:00 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-05 08:51 ` Re: REINDEX vs VACUUM Brad White <b55white@gmail.com>
2023-01-05 10:26 ` Re: REINDEX vs VACUUM Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 3 replies; 8+ messages in thread
From: Ron @ 2023-01-04 15:34 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Re: REINDEX vs VACUUM Ron <ronljohnsonjr@gmail.com>
@ 2023-01-04 21:00 ` Rébeli-Szabó Tamás <pub@rblst.info>
2 siblings, 0 replies; 8+ messages in thread
From: Rébeli-Szabó Tamás @ 2023-01-04 21:00 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
I have looked more into it and have found that VACUUM FULL (and CLUSTER)
does in fact rebuild indexes, see for example:
https://github.com/postgres/postgres/blob/c8e1ba736b2b9e8c98d37a5b77c4ed31baf94147/src/backend/comma...
I have also run some tests and have come to understand that REINDEX
seems to "vacuum" the index in the sense that a subsequent VACUUM on the
underlying table will not find any removable entries in the index.
Ron is right, the term "vacuum" may be misleading.
2023. 01. 04. 16:34 keltezéssel, Ron írta:
> 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
>>
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Re: REINDEX vs VACUUM Ron <ronljohnsonjr@gmail.com>
@ 2023-01-05 08:51 ` Brad White <b55white@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Brad White @ 2023-01-05 08:51 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 1/4/2023 9:34 AM, Ron wrote:
> 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.
Well s***.
That explains a lot. Thanks for clearing that up for me.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Re: REINDEX vs VACUUM Ron <ronljohnsonjr@gmail.com>
@ 2023-01-05 10:26 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-01-05 12:15 ` Re: REINDEX vs VACUUM Peter J. Holzer <hjp-pgsql@hjp.at>
2 siblings, 1 reply; 8+ messages in thread
From: Peter J. Holzer @ 2023-01-05 10:26 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-01-04 09:34:42 -0600, Ron wrote:
> 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.
Move all the stuff from the living room to the bedroom and then jettison
the living room.
Isn't that how you normally vacuum your living room?
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: REINDEX vs VACUUM
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Re: REINDEX vs VACUUM Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Re: REINDEX vs VACUUM Ron <ronljohnsonjr@gmail.com>
2023-01-05 10:26 ` Re: REINDEX vs VACUUM Peter J. Holzer <hjp-pgsql@hjp.at>
@ 2023-01-05 12:15 ` Peter J. Holzer <hjp-pgsql@hjp.at>
0 siblings, 0 replies; 8+ messages in thread
From: Peter J. Holzer @ 2023-01-05 12:15 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2023-01-05 12:34:08 +0100, Karsten Hilbert wrote:
> > Von: "Peter J. Holzer" <hjp-pgsql@hjp.at>
> > On 2023-01-04 09:34:42 -0600, Ron wrote:
> > > 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.
> >
> > Move all the stuff from the living room to the bedroom and then jettison
> > the living room.
> >
> > Isn't that how you normally vacuum your living room?
>
> Well, yeah, I wouldn't expect the table to be *copied*. After all, there's
> not that much use for more than one per living room.
Well, you won't have the old table anymore if you leave it in the living
room.
On a more serious note: It it pretty much impossible to move anything
inside a computer. To move something you have to
* copy it and then delete (overwrite) the original, or
* leave it where it is and refer to it from a different location (which
typically means copying and deleting the reference)
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-01-05 12:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-31 21:04 REINDEX vs VACUUM Hao Zhang <kennthhz@gmail.com>
2023-01-03 17:57 ` Hao Zhang <kennthhz@gmail.com>
2023-01-04 13:25 ` Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-04 15:34 ` Ron <ronljohnsonjr@gmail.com>
2023-01-04 21:00 ` Rébeli-Szabó Tamás <pub@rblst.info>
2023-01-05 08:51 ` Brad White <b55white@gmail.com>
2023-01-05 10:26 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-01-05 12:15 ` Peter J. Holzer <hjp-pgsql@hjp.at>
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