Estimating effect size of dropping columns in PostgreSQL

Question

I have a table in PostgreSQL with two columns containing images as text (not my decision ..). So, now I do not need these columns and I'm planning to delete them. But I'd faced a problem when I wanted to estimate the "effect" of dropping the columns, i.e. how much size of the table will change. The problem is that PostgreSQL shows the same size for old and new tables. Which is very weird because I'm dropping two "heavy" columns.

Here is the code of comparison:

-- Create two copies of the table
CREATE TABLE oldwords (LIKE "words" INCLUDING INDEXES); 
INSERT INTO oldwords SELECT * FROM "words";

CREATE TABLE newwords (LIKE "words" INCLUDING INDEXES); 
INSERT INTO newwords SELECT * FROM "words";

-- Drop columns containing images
ALTER TABLE "newwords"
   DROP COLUMN image_black,
   DROP COLUMN image_colored;

-- Update stats of the tables
VACUUM ANALYZE "oldwords";
VACUUM ANALYZE "newwords";

-- Compare size
SELECT
    relname as "Table",
    pg_size_pretty(pg_total_relation_size(relid)) As "Size",
    pg_size_pretty(pg_total_relation_size(relid) - 
    pg_relation_size(relid)) as "External Size"
FROM pg_catalog.pg_statio_user_tables 
WHERE relname LIKE '___words'
ORDER BY pg_total_relation_size(relid) DESC

-- RESULT
Table       Size    External Size
newwords    296 MB  205 MB
oldwords    296 MB  205 MB

Am I doing something wrong? Why are the sizes same? And what is the correct way to do it?

Vacuum full should rewrite the tables and remove all dropped columns — Sami Kuhmonen
– Sami Kuhmonen, Commented Jun 21, 2018 at 13:44

Community · Accepted Answer · 2020-06-20 09:12:55Z

7

From ALTER TABLE:

The DROP COLUMN form does not physically remove the column, but simply makes it invisible to SQL operations. Subsequent insert and update operations in the table will store a null value for the column.

Thus, dropping a column is quick but it will not immediately reduce the on-disk size of your table, as the space occupied by the dropped column is not reclaimed. The space will be reclaimed over time as existing rows are updated. (These statements do not apply when dropping the system oid column; that is done with an immediate rewrite.)

To force immediate reclamation of space occupied by a dropped column, you can execute one of the forms of ALTER TABLE that performs a rewrite of the whole table. This results in reconstructing each row with the dropped column replaced by a null value.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 21, 2018 at 13:40

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lathspell Over a year ago

Regarding the "over time", a VACUUM should reclaim the storage immediately.

Don Seiler Over a year ago

@lathspell I don't believe that is true. The dropped column will remain on existing columns. VACUUM only deletes dead rows. Now, if a row were updated, my understanding is that then a new version of the row would be written, without the dropped columns. Then when the old version of the row is cleaned up by VACUUM, the space will be freed. But just running VACUUM immediately after dropping columns won't change much.

Rustam Guliev Over a year ago

Thanks! That explains the reason. But my table is "static", i.e. the table is changed almost never. So, as I understand, I should force the space reclamation by some "fake" ALTER TABLE query. But what is the query I could use in such situation?

lathspell Over a year ago

According to dba.stackexchange.com/questions/117510/… a simple VACUUM ist not enough but a VACUUM FULL would be.

Collectives™ on Stack Overflow

Estimating effect size of dropping columns in PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related