PostgreSQL主键膨胀使用CONCURRENTLY维护
http://my.oschina.net/u/2426299/blog/487913
1. 维护前
1. 维护前
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Java代码 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) |
2. 创建新的unique index
|
1 2 3 |
Java代码 postgres=# CREATE UNIQUE INDEX CONCURRENTLY ON test USING btree(id); CREATE INDEX |
3. 替换主键
|
1 2 3 4 5 6 7 8 9 10 |
Java代码 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 |
4. 维护后
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Java代码 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) |
这样的好处是，可以在白天负载低的时候维护主键
