ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(164) - pgAdmin(Drop column)

PostgreSQL DBA(164) - pgAdmin(Drop column)

原创 PostgreSQL 作者:husthxd 时间:2020-02-13 18:42:17 0 删除 编辑

本节简单介绍了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?)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2675498/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1488
  • 访问量
    3925793