3

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?

  • Very good question; depends on whether the relfilenodes are copied or it's done as a relation rewrite. I'd have to check the source code. If you don't get a definitive answer soon, at-reply me and I'll go check. – Craig Ringer Dec 29 '14 at 17:18
  • I could very easily be wrong, but I was under the impression that postgres executed the alter tablespace on the table by simply moving the corresponding data file to the correct folder at the file-system level. If what I said above is true, then I doubt it would reclaim any space. – Joishi Bodio Dec 29 '14 at 23:24
4

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().

|improve this answer|||||
  • Are there any trivial "additional actions" that will cause ATRewriteTable? By "trivial" I mean "actions that won't break existing sql code". Perhaps changing a column name to the same value as it is presently? – sevzas Jan 2 '15 at 16:51
  • @sevzas it's going to depend on your exact PostgreSQL version and column type, but using something like SET DATA TYPE some_int_col USING some_int_col::bigint::int should probably work (just casting the column to its own type probably won't be enough to force a rewrite, depending on your PG version). – Josh Kupershmidt Jan 2 '15 at 17:16
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;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.