注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL9.2Beta: Reduce need to rebuild tables and indexes for various ALTER TABLE  

2012-05-23 18:05:04|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 

      今天在读 PostgreSQL9.2Beta  Release Note 过程中,发现了令人振奋的消息,这里用振奋来形容,是因为这个
特性让解决了生产维护过程中 PostgreSQL 的一个软肋, 一个曾经让人非常蛋疼的特性。Release Note是这么说的
"Reduce need to rebuild tables and indexes for various ALTER TABLE operations (Noah Misch) DUPLICATE"
,意思是 9.2 版本中对于 Alter table DDL 语句,已经有部分不需要重写表和索引了,注意这里只是部分,而不是
全部。而之前的版本 PostgreSQL 对于扩字段长度,例如 varchar,numeric 类型扩字段长度需要重写表,对于生产
库的大表而言,这需要花费大量的时间,而且在DDL 语句过程中,全表锁,这无疑是不可接受的,关于之前版本需要
重写表的内容,可以参考之前写的BLOG
http://francs3.blog.163.com/blog/static/4057672720111158654916/
http://francs3.blog.163.com/blog/static/405767272011823105316813/


       今天测试 PostgreSQL9.2Beta 版一些 "Alter table" 语句,看看哪些不需要 rewrite 表了。
     
一 测试场景一 ( varchar 字段扩长: 字段上无索引 )
PostgreSQL9.2Beta 版
 

 --创建测试表     
[postgres@redhat6 pg_root]$ psql francs francs
psql (9.2beta1)
Type "help" for help.

francs=> create table test_alter (id integer,name varchar(32),flag numeric(1,0));
CREATE TABLE

francs=> insert into test_alter select generate_series(1,2000000),'francs',0;
INSERT 0 2000000


francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
 flag   | numeric(1,0) 
 
 francs=> select * from test_alter limit 1;
 id |  name  | flag
----+--------+------
  1 | francs |    0
(1 row)

 

--varchar 字段扩长
francs=> alter table test_alter alter name type character varying(64);
ALTER TABLE
Time: 1161.613 ms
        |
francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(64) |
 flag   | numeric(1,0)          |

   

    备注: PostgreSQL9.2 版本, 这里 varchar(32) 扩长到  varchar(64)  花费了 1 秒左右。


PostgreSQL 9.1.2

 --创建测试表
[postgres@redhat6 tf]$ psql skytf skytf
psql (9.1.2)
Type "help" for help.

skytf=> create table test_alter (id integer,name varchar(32),flag numeric(1,0));
CREATE TABLE

skytf=>  insert into test_alter select generate_series(1,2000000),'francs',0;
INSERT 0 2000000

skytf => \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
 flag   | numeric(1,0) 
 
skytf=>  alter table test_alter alter name type character varying(64);
ALTER TABLE
Time: 7844.453 ms

skytf=>  \d test_alter
          Table "skytf.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(64) |
 flag   | numeric(1,0)          |

   

   备注:PostgreSQL 9.1 版本中 ,varchar(32) 字段扩长到 varchar(64) 花费了 7 秒左右,显然重写了表,这个表数据只有

            200 万,如果表更大,这个时间会更长。


二 测试场景二 ( varchar 字段扩长: 字段上有索引 )
PostgreSQL9.2Beta

 francs=> create index idx_test_alter_name on test_alter using btree (name);
CREATE INDEX

francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(64) |
 flag   | numeric(2,0)          |
Indexes:
    "idx_test_alter_name" btree (name)

francs=> alter table test_alter alter column name type character varying(128);
ALTER TABLE
Time: 142.443 ms

   


PostgreSQL9.1

 --创建索引
skytf=>  create index idx_test_alter_name on test_alter using btree (name);
CREATE INDEX
Time: 12472.807 ms

skytf=> alter table test_alter alter column name type character varying(128);
ALTER TABLE
Time: 17125.181 ms   

   

   备注:varchar 字段上有索引时, PostgreSQL9.2 在扩字段长度时依然不需要重写表,而 PostgreSQL 9.1 版本需要重写表。

 

三 测试场景三 ( numeric 字段扩长 )
PostgreSQL9.2Beta

 --numeric 字段扩字
francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(64) |
 flag   | numeric(1,0)          |


francs=> alter table test_alter alter column flag type numeric (2,0);
ALTER TABLE
Time: 79.782 ms

 

francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(64) |
 flag   | numeric(2,0)          |

   

PostgreSQL9.1

 skytf=> \d test_alter
          Table "skytf.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(64) |
 flag   | numeric(1,0)          |

skytf=> alter table test_alter alter column flag type numeric (2,0);
ALTER TABLE
Time: 13236.818 ms

   
     备注:numerica 字段扩长时, PostgreSQL9.2 版本花费 79 ms,而 PostgreSQL 9.1 版本花费 13236 毫秒,显然重写表了。
 
 
 四 测试场景四 ( varchar(64) --> text  )
PostgreSQL9.2Beta
 francs=> \d test_alter
         Table "francs.test_alter"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               | not null
 name   | character varying(64) |
 flag   | numeric(2,0)          |
Indexes:
    "idx_test_alter_name" btree (name)


francs=> alter table test_alter alter column name type text;
ALTER TABLE
Time: 1405.116 ms

 
 francs=> \d test_alter
     Table "francs.test_alter"
 Column |     Type     | Modifiers
--------+--------------+-----------
 id     | integer      | not null
 name   | text         |
 flag   | numeric(2,0) |
Indexes:
    "idx_test_alter_name" btree (name)

   


PostgreSQL9.1

 skytf=>  alter table test_alter alter column name type text;
ALTER TABLE
Time: 13487.664 ms


skytf=> \d test_alter
     Table "skytf.test_alter"
 Column |     Type     | Modifiers
--------+--------------+-----------
 id     | integer      |
 name   | text         |
 flag   | numeric(2,0) |
Indexes:
    "idx_test_alter_name" btree (name)

    备注:当 varchar 字段扩长到 text 字段时, PostgreSQL9.2 版本没重写表, PostgreSQL9.1 版本需要重写。

 

五附

      在 PostgreSQL9.2中不需要重写表的“ALTER TABLE “ 场景:

  • varchar(x) to varchar(y) when y>=x. It works too if going from varchar(x) to varchar or text (no size limitation)
  • numeric(x,z) to numeric(y,z) when y>=x, or to numeric without specifier
  • varbit(x) to varbit(y) when y>=x, or to varbit without specifier
  • timestamp(x) to timestamp(y) when y>=x or timestamp without specifier
  • timestamptz(x) to timestamptz(y) when y>=x or timestamptz without specifier
  • interval(x) to interval(y) when y>=x or interval without specifier
  •  

 

六总结

        大体上说 PostgreSQL9.2 版本大多数字段扩长DDL语句不需要写表了,这解决了之前版非常致命的软肋,

非常感谢 Noah Misch 的贡献。

   

  评论这张
 
阅读(27601)| 评论(6)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016