Does changing a Postgresql table's tablespace compact it like "vacuum full" or "cluster"?

Question

I need to reclaim disk space in a table in a Postgresql 9.3.2 database to be used for other tables. The standard suggestions to accomplish this are: "VACUUM FULL" or "CLUSTER" or copy the contents into a flat file and copy them back in. When moving an existing table to a new tablespace with an "ALTER TABLE" command, the table's contents are copied. Can anyone tell me if they are compacted during the "ALTER TABLE" process?

Answer 1

The comments so far are roughly correct, but to give an authoritative answer from looking at src/backend/tablecmds.c:

If you're only performing ALTER TABLE ... SET TABLESPACE ... ;, then ATExecSetTableSpace() will be invoked to handle the SET TABLESPACE, and it uses copy_relation_data() to perform a WAL-logged block-by-block copy of the table. However, if you were to specify additional actions to the ALTER TABLE command which require table rewriting, then the new copy of the table should be built (and compacted) in the new tablespace via ATRewriteTable().

Answer 2

The answer appears to be "moving the table to a different tablespace does not compact it like VACUUM FULL or CLUSTER".

I tested this by:

• creating a dummy table
• filling it with one million dummy rows to the tune of 38MB
• deleting all of the rows with "DELETE from DUMMY"
• moving table to a different tablespace with "ALTER TABLE DUMMY SET TABLESPACE overflow;"
• looking to see how big the table is on the new table space.

The query I used to determine the size of the table is this:

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size(C.oid) DESC;