我在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
我做错了什么吗?为什么大小都一样?正确的方法是什么?
Vacuum full
应该重写表格并删除所有已删除的列。 - Sami Kuhmonen