Re: CLUSTER, VACUUM, and TABLESPACEs (oh my)

Hi,

Le lun. 25 janv. 2021 à 01:38, Demitri Muna <postgresql(at)demitri(dot)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.

> 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
>
>