Can a clustered index survive being moved to a different tablespace?

Question

I have a table that is initially partitioned by day. At the end of every day no more records will be added to that partition, so I cluster the index and then I then do a lot of number crunching and aggregation on that table (using the index I clustered):

CLUSTER table_a_20181104 USING table_a_20181104_index1;

After a few days (typically a week) I merge the partition for one day into a larger partition that contains all the days data for that month. I use this SQL to achieve this:

WITH moved_rows AS 
( 
    DELETE FROM table_a_20181104 
    RETURNING * 
) 
INSERT INTO table_a_201811 
SELECT * FROM moved_rows; 

After maybe a month or too I change the tablespace to move the data from an SSD disk to a conventional magnetic hard disk.

ALTER TABLE ... SET TABLESPACE ...

My initial clustering of the index at the end of the day definitely improves the performance of the queries run against it.

I know that clustering is a one-off command and needs to be repeated if new records are added/removed.

My questions are:

• Do I need to repeat the clustering after merging the 'day' partition into the 'month' partition?
• Do I need to repeat the clustering after altering the tablespace?
• Do I need to repeat the clustering if I VACUUM the partition?

Answer 1

• Moving the data from one partition to the other will destroy the clustering, so you'll need to re-cluster after it.

• ALTER TABLE ... SET TABLESPACE will just copy the table files as they are, so clustering will be preserved.

• VACUUM does not move the rows, so clustering will also be preserved.