本节简单介绍了PostgreSQL中的删除列操作。在PG中，删除列并不会释放空间，只是逻辑上把列标记为已删除，查询时不显示，插入时插入空值。 *创建数据表* ``` [local:/data/run/pg12]:5120 pg12@testdb=# create table t_drop(id int); CREATE TABLE [local:/data/run/pg12]:5120 pg12@testdb=# insert into t_drop select generate_series(1,10000000); INSERT 0 10000000 [local:/data/run/pg12]:5120 pg12@testdb=# [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 346 MB (1 row) ``` *新增列* ``` [local:/data/run/pg12]:5120 pg12@testdb=# \timing on Timing is on. [local:/data/run/pg12]:5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN c1 text DEFAULT md5( random()::text ); ALTER TABLE Time: 45769.146 ms (00:45.769) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 0.840 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` 新增列后,占用空间达到了651MB. *删除列* ``` [local:/data/run/pg12]:5120 pg12@testdb=# alter table t_drop drop c1; ALTER TABLE Time: 2.886 ms [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 1.788 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` 删除列,但空间没有释放. *数据字典* ``` [local:/data/run/pg12]:5120 pg12@testdb=# \d pg_attribute Table "pg_catalog.pg_attribute" Column | Type | Collation | Nullable | Default ---------------+-----------+-----------+----------+--------- attrelid | oid | | not null | attname | name | | not null | atttypid | oid | | not null | attstattarget | integer | | not null | attlen | smallint | | not null | attnum | smallint | | not null | attndims | integer | | not null | attcacheoff | integer | | not null | atttypmod | integer | | not null | attbyval | boolean | | not null | attstorage | "char" | | not null | attalign | "char" | | not null | attnotnull | boolean | | not null | atthasdef | boolean | | not null | atthasmissing | boolean | | not null | attidentity | "char" | | not null | attgenerated | "char" | | not null | attisdropped | boolean | | not null | attislocal | boolean | | not null | attinhcount | integer | | not null | attcollation | oid | | not null | attacl | aclitem[] | | | attoptions | text[] | C | | attfdwoptions | text[] | C | | attmissingval | anyarray | | | Indexes: "pg_attribute_relid_attnam_index" UNIQUE, btree (attrelid, attname) "pg_attribute_relid_attnum_index" UNIQUE, btree (attrelid, attnum) [local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t (8 rows) Time: 0.896 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` 查看数据字典,发现删除的c1列变为pg.dropped.2,逻辑标记为删除. 使用vacuum/vacuum full回收空间. ``` [local:/data/run/pg12]:5120 pg12@testdb=# vacuum t_drop; VACUUM Time: 2510.368 ms (00:02.510) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 651 MB (1 row) Time: 0.718 ms [local:/data/run/pg12]:5120 pg12@testdb=# vacuum full t_drop; VACUUM Time: 7996.658 ms (00:07.997) [local:/data/run/pg12]:5120 pg12@testdb=# SELECT pg_size_pretty( pg_relation_size( 't_drop' ) ); pg_size_pretty ---------------- 346 MB (1 row) Time: 1.258 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` 但数据字典仍保留删除列的信息 ``` [local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t (8 rows) Time: 0.757 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` 新增列,查看数据字典 ``` [local:/data/run/pg12]:5120 pg12@testdb=# ALTER TABLE t_drop ADD COLUMN c1 text DEFAULT md5( random()::text ); ALTER TABLE Time: 24483.254 ms (00:24.483) [local:/data/run/pg12]:5120 pg12@testdb=# select attrelid,attname,atttypid,attisdropped from pg_attribute where attrelid = 't_drop'::regclass; attrelid | attname | atttypid | attisdropped ----------+------------------------------+----------+-------------- 994249 | tableoid | 26 | f 994249 | cmax | 29 | f 994249 | xmax | 28 | f 994249 | cmin | 29 | f 994249 | xmin | 28 | f 994249 | ctid | 27 | f 994249 | id | 23 | f 994249 | ........pg.dropped.2........ | 0 | t 994249 | c1 | 25 | f (9 rows) Time: 1.067 ms [local:/data/run/pg12]:5120 pg12@testdb=# ``` **参考资料** [Why Dropping a Column does not Reclaim Disk Space? (or better, why is it so fast?)](https://fluca1978.github.io/2020/02/09/PostgreSQLDROPCOlumn.html)