Re: Allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly

On 2018-Dec-27, Alexey Kondratov wrote:

> To summarize:
>
> 1) Alvaro and Michael agreed, that REINDEX with tablespace move may be
> useful. This is done in the patch attached to my initial email. Adding
> REINDEX to ALTER TABLE as new action seems quite questionable for me and not
> completely semantically correct. ALTER already looks bulky.

Agreed on these points.

> 2) If I am correct, 'ALTER TABLE ... CLUSTER ON ..., SET TABLESPACE ...'
> does exactly what I wanted to add to CLUSTER in my patch. So probably no
> work is necessary here.

Well, ALTER TABLE CLUSTER ON does not really cluster the table; it only
indicates which index to cluster on, for the next time you run
standalone CLUSTER. I think it would be valuable to have those ALTER
TABLE variants that rewrite the table do so using the cluster order, if
there is one, instead of the heap order, which is what it does today.

> 3) VACUUM FULL. It seems, that we can add special case 'ALTER TABLE ...
> VACUUM FULL, SET TABLESPACE ...', which will follow relatively the same path
> as with CLUSTER ON, but without any specific index. Relation should be
> rewritten in the new tablespace during phase 3.

Well, VACUUM FULL is just a table rewrite using the CLUSTER code that
doesn't cluster on any index: it just uses the heap order. So in
essence it's the same as a table-rewriting ALTER TABLE. In other words,
if you get the index-ordered table rewriting in ALTER TABLE, I don't
think this part adds anything useful; and it seems very confusing.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services