PostgreSQL主键膨胀使用CONCURRENTLY维护

最新推荐文章于 2024-03-14 09:38:15 发布

weixin_34381666

最新推荐文章于 2024-03-14 09:38:15 发布

阅读量106

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/aven92/blog/487913

版权

为什么80%的码农都做不了架构师？>>>

一、维护前

postgres=# \d+ test
                                                        Table
"public.test"
  Column |            Type             |                     Modifiers
| Storage | Stats target | Description
--------+-----------------------------+----------------------------------------------------+---------+--------------+-------------
  id     | integer                     | not null default
nextval('test_id_seq'::regclass) | plain   |              |
  val    | timestamp without time zone | default now()
| plain   |              |
Indexes:
     "test_pkey" PRIMARY KEY, btree (id)

二、创建新的unique index

postgres=# CREATE UNIQUE INDEX CONCURRENTLY ON test USING btree(id);
CREATE INDEX

三、替换主键

postgres=# BEGIN;
BEGIN
postgres=# ALTER TABLE test DROP CONSTRAINT test_pkey;
ALTER TABLE
postgres=# ALTER TABLE test ADD CONSTRAINT test_id_idx PRIMARY KEY USING
INDEX test_id_idx;
ALTER TABLE
postgres=# COMMIT;
COMMIT

四、维护后

postgres=# \d+ test
                                                        Table
"public.test"
  Column |            Type             |                     Modifiers
| Storage | Stats target | Description
--------+-----------------------------+----------------------------------------------------+---------+--------------+-------------
  id     | integer                     | not null default
nextval('test_id_seq'::regclass) | plain   |              |
  val    | timestamp without time zone | default now()
| plain   |              |
Indexes:
     "test_id_idx" PRIMARY KEY, btree (id)

这样的好处是，可以在白天负载低的时候维护主键

转载于:https://my.oschina.net/aven92/blog/487913