postgresql 给普通表添加主键的最优方案

os: centos 7.6db: postgresql 13版本# cat /etc/centos-releaseCentOS Linux release 7.6.1810 (Core)## su - postgres$ psql -c "select version();"version-

数据库人生

10090人浏览 · 2021-07-26 11:11:33

数据库人生 · 2021-07-26 11:11:33 发布

os: centos 7.6
db: postgresql 13

版本

# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core) 
# 
# su - postgres
$ psql -c "select version();"
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

原始表

$ psql
psql (13.3)
Type "help" for help.

postgres=# create table tmp_t0 (
  id varchar(50),
  name varchar(100)
  );

postgres=# insert into tmp_t0(id,name) 
select id::varchar,
       md5(id::varchar)
  from generate_series(1,10000) as id
;

postgres=# insert into tmp_t0(id,name) 
select '',
       md5(id::varchar)
  from generate_series(10001,20000) as id
;

postgres=# 
postgres=# select * from tmp_t0 limit 10;
 id |               name               
----+----------------------------------
 1  | c4ca4238a0b923820dcc509a6f75849b
 2  | c81e728d9d4c2f636f067f89cc14862c
 3  | eccbc87e4b5ce2fe28308fd9f2a7baf3
 4  | a87ff679a2f3e71d9181a67b7542122c
 5  | e4da3b7fbbce2345d7772b0674a318d5
 6  | 1679091c5a880faf6fb5e6087eb1b2dc
 7  | 8f14e45fceea167a5a36dedd4bea2543
 8  | c9f0f895fb98ab9159f51fd0297e236d
 9  | 45c48cce2e2d7fbdea1afc51c7c6ad26
 10 | d3d9446802a44259755d38e6d163e820
(10 rows

添加主键

第一步、更新 id 列为null、或者空的行

postgres=# update tmp_t0 set id=name where id is null or id = '';

postgres=# select count(1) from tmp_t0 where id is null or id = '';
 count 
-------
     0
(1 row)

第二步、对 id 列添加非空约束 (毫秒级,只修改数据字典)

postgres=# alter table tmp_t0 alter column id set not null;

第三步、创建唯一性索引
可以通过 ALTER TABLE 为 parallel_workers 设置一个值直接控制着 CREATE INDEX 的并行，创建完后再恢复。
记得一定要添加 concurrently 关键字

postgres=# create unique index concurrently pk_tmp_t0_id on tmp_t0(id);

第四步、创建主键约束
对前面创建的索引做了一次 rename

postgres=# alter table tmp_t0 ADD CONSTRAINT pk_tmp_t0  PRIMARY KEY using index pk_tmp_t0_id;
NOTICE:  ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "pk_tmp_t0_id" to "pk_tmp_t0"
ALTER TABLE

查看表的定义

postgres=# \d tmp_t0
                      Table "public.tmp_t0"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | character varying(50)  |           | not null | 
 name   | character varying(100) |           |          | 
Indexes:
    "pk_tmp_t0" PRIMARY KEY, btree (id)

参考:
http://postgres.cn/docs/13/install-procedure.html