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?
4
-
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– Craig Ringer2014-12-29 17:18:13 +00:00Commented Dec 29, 2014 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– Joishi Bodio2014-12-29 23:24:20 +00:00Commented Dec 29, 2014 at 23:24
-
@CraigRinger as a PG contributor, would you know how hard would that be to implement in future version? Many use cases for changing tablespaces are "disk full" problems where the ALTER TABLE would immediately be followed by a VACUUM FULL.Matthieu– Matthieu2024-08-08 10:54:12 +00:00Commented Aug 8, 2024 at 10:54
-
@Matthieu Honestly no idea, I haven't dug much into that part of the storage manager and haven't been as active in Pg recently. You'd want to ask on the mailing lists.Craig Ringer– Craig Ringer2024-08-11 23:08:21 +00:00Commented Aug 11, 2024 at 23:08
Add a comment
|
2 Answers
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().
-
1Are 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– sevzas2015-01-02 16:51:39 +00:00Commented Jan 2, 2015 at 16:51
-
1@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::intshould 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– Josh Kupershmidt2015-01-02 17:16:48 +00:00Commented Jan 2, 2015 at 17:16 -
Do you think that would still hold 9 years later?Matthieu– Matthieu2024-08-08 11:16:24 +00:00Commented Aug 8, 2024 at 11:16
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;
-
Did you have a chance to test @JoshKupershmidt's suggestion? (adding dummy statement to make the compaction)Matthieu– Matthieu2024-08-08 11:18:11 +00:00Commented Aug 8, 2024 at 11:18