1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL 扩 Varchar 字段长度的奇怪现象

• 数据库

今天测试了下，有张大表字段需要扩充长度，即将 character varying(128) 扩大到 character varying(256)，因为以前有Oracle 经验，类似的操作在Oracle 库里瞬间就能完成。因为只涉及到更改数据字典，不更改物理数据。下面来看下PG里的情况。

现象

查看表大小

1
2
3
4
5
 
wapreader_log=> select pg_size_pretty(pg_relation_size('log_foot_mark'));  
pg_size_pretty  
----------------  
5441 MB  
(1 row)

查看表结构

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
wapreader_log=> /d log_foot_mark  
 Table "wapreader_log.log_foot_mark"  
 Column | Type | Modifiers  
-------------+-----------------------------+-----------  
id | integer | not null  
create_time | timestamp without time zone |  
sky_id | integer |  
url | character varying(1000) |  
refer_url | character varying(1000) |  
source | character varying(64) |  
users | character varying(64) |  
userm | character varying(64) |  
usert | character varying(64) |  
ip | character varying(32) |  
module | character varying(64) |  
resource_id | character varying(100) |  
user_agent | character varying(128) |  
Indexes:  
 "pk_log_footmark" PRIMARY KEY, btree (id)

扩字段长度

1
2
3
4
5
 
wapreader_log=> timing  
Timing is on.
wapreader_log=> ALTER TABLE wapreader_log.log_foot_mark ALTER column user_agent TYPE character varying(256);  
ALTER TABLE  
Time: 603504.835 ms

通常加字段的DDL是很快的，瞬间就能完成，为什么这次经历了十分钟，觉得很奇怪。那为什么PG里扩字段长度会花很长时间呢？难道在更改物理数据？带着这个疑问，做了以下测试,等下看结果。

测试

测试,创建一张表

1
2
 
wapreader_log=> create table test_1 (id integer ,remark varchar(32));  
CREATE TABLE

创建插入数据 function

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE FUNCTION wapreader_log.fun_ins_test_1()  
RETURNS integer  
LANGUAGE plpgsql  
AS $function$  
 DECLARE
 i INTEGER ;  
 BEGIN  
 for i in 1..100 loop  
 insert into test_1 values (1,'a');  
 end loop;  
 return 1;  
 END;  
 $function$

插入 100 条数据

1
2
3
4
5
 
wapreader_log=> select wapreader_log.fun_ins_test_1();  
fun_ins_test_1  
----------------  
 1  
(1 row)

查看数据，注意 ctid

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
wapreader_log=> select ctid,* from test_1 ;  
 ctid | id | remark  
---------+----+--------  
(0,1) | 1 | a  
(0,2) | 1 | a  
(0,3) | 1 | a  
(0,4) | 1 | a  
(0,5) | 1 | a  
(0,6) | 1 | a  
(0,7) | 1 | a  
(0,8) | 1 | a  
(0,9) | 1 | a  
(0,10) | 1 | a  
(0,11) | 1 | a  
(0,12) | 1 | a  
(0,13) | 1 | a  
(0,14) | 1 | a  
(0,15) | 1 | a  
(0,16) | 1 | a  
(0,17) | 1 | a  
(0,18) | 1 | a  
(0,19) | 1 | a  
(0,20) | 1 | a  
(0,21) | 1 | a  
(0,22) | 1 | a  
(0,23) | 1 | a  
(0,24) | 1 | a  
(0,25) | 1 | a  
(0,26) | 1 | a  
(0,27) | 1 | a  
(0,28) | 1 | a  
(0,29) | 1 | a  
(0,30) | 1 | a  
(0,31) | 1 | a  
(0,32) | 1 | a  
(0,33) | 1 | a  
(0,34) | 1 | a  
(0,35) | 1 | a  
(0,36) | 1 | a  
(0,37) | 1 | a  
(0,38) | 1 | a  
(0,39) | 1 | a  
(0,40) | 1 | a  
(0,41) | 1 | a  
(0,42) | 1 | a  
(0,43) | 1 | a  
(0,44) | 1 | a  
(0,45) | 1 | a  
(0,46) | 1 | a  
(0,47) | 1 | a  
(0,48) | 1 | a  
(0,49) | 1 | a  
(0,50) | 1 | a  
(0,51) | 1 | a  
(0,52) | 1 | a  
(0,53) | 1 | a  
(0,54) | 1 | a  
(0,55) | 1 | a  
(0,56) | 1 | a  
(0,57) | 1 | a  
(0,58) | 1 | a  
(0,59) | 1 | a  
(0,60) | 1 | a  
(0,61) | 1 | a  
(0,62) | 1 | a  
(0,63) | 1 | a  
(0,64) | 1 | a  
(0,65) | 1 | a  
(0,66) | 1 | a  
(0,67) | 1 | a  
(0,68) | 1 | a  
(0,69) | 1 | a  
(0,70) | 1 | a  
(0,71) | 1 | a  
(0,72) | 1 | a  
(0,73) | 1 | a  
(0,74) | 1 | a  
(0,75) | 1 | a  
(0,76) | 1 | a  
(0,77) | 1 | a  
(0,78) | 1 | a  
(0,79) | 1 | a  
(0,80) | 1 | a  
(0,81) | 1 | a  
(0,82) | 1 | a  
(0,83) | 1 | a  
(0,84) | 1 | a  
(0,85) | 1 | a  
(0,86) | 1 | a  
(0,87) | 1 | a  
(0,88) | 1 | a  
(0,89) | 1 | a  
(0,90) | 1 | a  
(0,91) | 1 | a  
(0,92) | 1 | a  
(0,93) | 1 | a  
(0,94) | 1 | a  
(0,95) | 1 | a  
(0,96) | 1 | a  
(0,97) | 1 | a  
(0,98) | 1 | a  
(0,99) | 1 | a  
(0,100) | 1 | a  
(100 rows)

查看表大小

1
2
3
4
5
 
wapreader_log=> select pg_size_pretty(pg_relation_size('test_1'));  
pg_size_pretty  
----------------  
8192 bytes  
(1 row)

更改字段长度

1
2
3
4
5
6
7
8
9
 
wapreader_log=> /d test_1  
 Table "wapreader_log.test_1"  
Column | Type | Modifiers  
--------+-----------------------+-----------  
id | integer |  
remark | character varying(32) |
  
wapreader_log=> alter table test_1 alter column remark type character varying(256);  
ALTER TABLE

再次查看表的 ctid 和表大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
wapreader_log=> select ctid,* from test_1;  
 ctid | id | remark  
---------+----+--------  
(0,1) | 1 | a  
(0,2) | 1 | a  
(0,3) | 1 | a  
(0,4) | 1 | a  
(0,5) | 1 | a  
(0,6) | 1 | a  
(0,7) | 1 | a  
(0,8) | 1 | a  
(0,9) | 1 | a  
(0,10) | 1 | a  
(0,11) | 1 | a  
(0,12) | 1 | a  
(0,13) | 1 | a  
(0,14) | 1 | a  
(0,15) | 1 | a  
(0,16) | 1 | a  
(0,17) | 1 | a  
(0,18) | 1 | a  
(0,19) | 1 | a  
(0,20) | 1 | a  
(0,21) | 1 | a  
(0,22) | 1 | a  
(0,23) | 1 | a  
(0,24) | 1 | a  
(0,25) | 1 | a  
(0,26) | 1 | a  
(0,27) | 1 | a  
(0,28) | 1 | a  
(0,29) | 1 | a  
(0,30) | 1 | a  
(0,31) | 1 | a  
(0,32) | 1 | a  
(0,33) | 1 | a  
(0,34) | 1 | a  
(0,35) | 1 | a  
(0,36) | 1 | a  
(0,37) | 1 | a  
(0,38) | 1 | a  
(0,39) | 1 | a  
(0,40) | 1 | a  
(0,41) | 1 | a  
(0,42) | 1 | a  
(0,43) | 1 | a  
(0,44) | 1 | a  
(0,45) | 1 | a  
(0,46) | 1 | a  
(0,47) | 1 | a  
(0,48) | 1 | a  
(0,49) | 1 | a  
(0,50) | 1 | a  
(0,51) | 1 | a  
(0,52) | 1 | a  
(0,53) | 1 | a  
(0,54) | 1 | a  
(0,55) | 1 | a  
(0,56) | 1 | a  
(0,57) | 1 | a  
(0,58) | 1 | a  
(0,59) | 1 | a  
(0,60) | 1 | a  
(0,61) | 1 | a  
(0,62) | 1 | a  
(0,63) | 1 | a  
(0,64) | 1 | a  
(0,65) | 1 | a  
(0,66) | 1 | a  
(0,67) | 1 | a  
(0,68) | 1 | a  
(0,69) | 1 | a  
(0,70) | 1 | a  
(0,71) | 1 | a  
(0,72) | 1 | a  
(0,73) | 1 | a  
(0,74) | 1 | a  
(0,75) | 1 | a  
(0,76) | 1 | a  
(0,77) | 1 | a  
(0,78) | 1 | a  
(0,79) | 1 | a  
(0,80) | 1 | a  
(0,81) | 1 | a  
(0,82) | 1 | a  
(0,83) | 1 | a  
(0,84) | 1 | a  
(0,85) | 1 | a  
(0,86) | 1 | a  
(0,87) | 1 | a  
(0,88) | 1 | a  
(0,89) | 1 | a  
(0,90) | 1 | a  
(0,91) | 1 | a  
(0,92) | 1 | a  
(0,93) | 1 | a  
(0,94) | 1 | a  
(0,95) | 1 | a  
(0,96) | 1 | a  
(0,97) | 1 | a  
(0,98) | 1 | a  
(0,99) | 1 | a  
(0,100) | 1 | a  
(100 rows)

wapreader_log=> select pg_size_pretty(pg_relation_size('wapreader_log.test_1'));  
pg_size_pretty  
----------------  
8192 bytes  
(1 row)

发现表大小，ctid 均无变化，说明扩字段长度操作没有更改物理数据, 上述猜想是不成立的。后来我把这个问题发到一国外论坛上，上面有个回复我觉得解释得比较好：原文如下，就不翻译了

When you alter a table, PostgreSQL has to make sure the old version doesn’t go away in some cases, to allow rolling back the change if the server crashes before it’s committed and/or written to disk. For those reasons, what it actually does here even on what seems to be a trivial change is write out a whole new copy of the table somewhere else first. When that’s finished, it then swaps over to the new one. Note that when this happens, you’ll need enough disk space to hold both copies as well
There are some types of DDL changes that can be made without making a second copy of the table, but this is not one of them. For example, you can add a new column that defaults to NULL quickly. But adding a new column with a non-NULL default requires making a new copy instead.

总结

针对PG在扩字段长度时会扫描全表且时间较长的问题，总结以下经验

  1. 尽可能熟悉应用，对于不确定长度的 Character Types ，建议不指定长度。
  2. 对于Character较长的字段可以采用 text类型。
  3. Oracle 在给 varchar2 类型字段加长时，瞬间就能完成，在这点上，PG有着与Oracle 不同的方式；

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/236361.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论