时间戳

首页 > 解决方案 > 估计 PostgreSQL 中删除列的影响大小

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

问题描述

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

难道我做错了什么？为什么尺寸一样？正确的方法是什么？

标签： postgresql

解决方案

来自ALTER TABLE：

DROP COLUMN 表单不会物理删除列，只是使其对 SQL 操作不可见。表中的后续插入和更新操作将存储该列的空值。

因此，删除列很快，但不会立即减少表的磁盘大小，因为被删除列占用的空间不会被回收。随着现有行的更新，空间将随着时间的推移而被回收。（这些语句在删除系统 oid 列时不适用；这是通过立即重写来完成的。）

要强制立即回收被删除的列占用的空间，您可以执行 ALTER TABLE 的一种形式，该形式执行整个表的重写。这将导致重建每一行，并将删除的列替换为空值。

推荐阅读