CLUSTER, VACUUM, and TABLESPACEs (oh my)
Hi,
I would like to request a little clarification on the CLUSTER and VACUUM commands. My use case here (partially) is when my disk runs out of space and I want to move a table to a newly created tablespace. These questions somewhat overlap. Let’s say I am starting with a table that is not CLUSTERed on a given index, but I want it to be.
* If I run “CLUSTER table USING idx” on a table, is VACUUM FULL required/useful afterwards, or should I assume that the cluster operation did the equivalent of a VACUUM FULL?
* If I have previously run a CLUSTER command on a table, will future VACUUM FULL commands rewrite the table in the order specified in the previous CLUSTER?
* If I want to move a table to a new tablespace, is it possible to CLUSTER+VACUUM in the same step since the whole table will be rewritten anyway? This would be very useful in low-disk space scenarios. I did find this answer, but it’s dated several years ago and was hoping for something better supported. https://dba.stackexchange.com/a/87457/121020
The documentation is somewhat silent on these details, so I thought I’d ask here. Right now I move a table to a new tablespace, cluster on an index, and then do a full vacuum which results in three full copies of the table being written, which seems less than optimal where one should only be needed as far as I understand things.
Cheers,
Demitri
Hi,
Le lun. 25 janv. 2021 à 01:38, Demitri Muna <postgresql@demitri.com> a
écrit :
Hi,
I would like to request a little clarification on the CLUSTER and VACUUM
commands. My use case here (partially) is when my disk runs out of space
and I want to move a table to a newly created tablespace. These questions
somewhat overlap. Let’s say I am starting with a table that is not
CLUSTERed on a given index, but I want it to be.* If I run “CLUSTER table USING idx” on a table, is VACUUM FULL
required/useful afterwards, or should I assume that the cluster operation
did the equivalent of a VACUUM FULL?
The cluster operation is a VACUUM FULL with a sort step, so you don't need
a VACUUM FULL after a CLUSTER.
* If I have previously run a CLUSTER command on a table, will future
VACUUM FULL commands rewrite the table in the order specified in the
previous CLUSTER?
No, you still need CLUSTER.
* If I want to move a table to a new tablespace, is it possible to
CLUSTER+VACUUM in the same step since the whole table will be rewritten
anyway? This would be very useful in low-disk space scenarios. I did find
this answer, but it’s dated several years ago and was hoping for something
better supported. https://dba.stackexchange.com/a/87457/121020
No, but this is something being worked on. See
https://commitfest.postgresql.org/31/2269/ for details.
Show quoted text
The documentation is somewhat silent on these details, so I thought I’d
ask here. Right now I move a table to a new tablespace, cluster on an
index, and then do a full vacuum which results in three full copies of the
table being written, which seems less than optimal where one should only be
needed as far as I understand things.Cheers,
Demitri
On 1/24/21 7:50 PM, Guillaume Lelarge wrote:
Hi,
Le lun. 25 janv. 2021 à 01:38, Demitri Muna <postgresql@demitri.com
<mailto:postgresql@demitri.com>> a écrit :
[snip]
* If I have previously run a CLUSTER command on a table, will future
VACUUM FULL commands rewrite the table in the order specified in the
previous CLUSTER?No, you still need CLUSTER.
VACUUM FULL unclusters the table??
--
Angular momentum makes the world go 'round.
Le lun. 25 janv. 2021 à 02:54, Ron <ronljohnsonjr@gmail.com> a écrit :
On 1/24/21 7:50 PM, Guillaume Lelarge wrote:
Hi,
Le lun. 25 janv. 2021 à 01:38, Demitri Muna <postgresql@demitri.com> a
écrit :[snip]
* If I have previously run a CLUSTER command on a table, will future
VACUUM FULL commands rewrite the table in the order specified in the
previous CLUSTER?No, you still need CLUSTER.
VACUUM FULL unclusters the table??
It will rebuild the table without sorting the data according to the index
used with CLUSTER (without any sorting actually).
Show quoted text
On Jan 24, 2021, at 9:03 PM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
VACUUM FULL unclusters the table??
It will rebuild the table without sorting the data according to the index used with CLUSTER (without any sorting actually).
Thank you for the clarification; that’s very helpful. For the case above. If I CLUSTER a table, add/delete no rows, then VACUUM, will the existing order remain or be changed?
Demitri
Le lun. 25 janv. 2021 à 03:27, Demitri Muna <postgresql@demitri.com> a
écrit :
On Jan 24, 2021, at 9:03 PM, Guillaume Lelarge <guillaume@lelarge.info>
wrote:
VACUUM FULL unclusters the table??
It will rebuild the table without sorting the data according to the
index used with CLUSTER (without any sorting actually).
Thank you for the clarification; that’s very helpful. For the case above.
If I CLUSTER a table, add/delete no rows, then VACUUM, will the existing
order remain or be changed?
As long as you don't insert or update any rows, the order should remain
even after a VACUUM FULL.