postgresql - 估计在 PostgreSQL 中删除列的影响大小

Question

我在 PostgreSQL 中有一个表，其中有两列包含图像作为文本(不是我的决定..)。所以，现在我不需要这些列，我打算删除它们。但是当我想估计删除列的“效果”时，我遇到了一个问题，即表的大小会改变多少。问题是 PostgreSQL 显示新旧表的大小相同。这很奇怪，因为我删除了两个“重”列。

比较代码如下:

-- 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

我做错了什么吗？为什么尺寸都一样？正确的做法是什么？

Answer 1

来自 ALTER TABLE :

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

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

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.