我刚刚从小写字母表中选择了1,000,000个随机 "char" 表 . 获取频率分布( select count(*), field ... group by field )的查询大约需要650毫秒,而使用 text 字段的相同数据大约需要760 .
35
2016年更新基准(第9.5页)
并使用“纯SQL”基准测试(没有任何外部脚本)
使用UTF8的任何string_generator
主要基准:
2.1 . 插入
2.2 . SELECT比较和计数
CREATE FUNCTION string_generator(int DEFAULT 20,int DEFAULT 10) RETURNS text AS $f$
SELECT array_to_string( array_agg(
substring(md5(random()::text),1,$1)||chr( 9824 + (random()*10)::int )
), ' ' ) as s
FROM generate_series(1, $2) i(x);
$f$ LANGUAGE SQL IMMUTABLE;
准备具体测试(例子)
DROP TABLE IF EXISTS test;
-- CREATE TABLE test ( f varchar(500));
-- CREATE TABLE test ( f text);
CREATE TABLE test ( f text CHECK(char_length(f)<=500) );
执行基本测试:
INSERT INTO test
SELECT string_generator(20+(random()*(i%11))::int)
FROM generate_series(1, 99000) t(i);
和其他测试,
CREATE INDEX q on test (f);
SELECT count(*) FROM (
SELECT substring(f,1,1) || f FROM test WHERE f<'a0' ORDER BY 1 LIMIT 80000
) t;
...并使用 EXPLAIN ANALYZE .
UPDATED AGAIN 2018 (pg10)
少编辑添加2018的结果并强化建议 .
2016年和2018年的结果
平均而言,我的结果是在许多机器和许多测试中: all the same (统计学上较小的标准偏差) .
推荐
使用 text 数据类型, 避免旧 varchar(x) 因为有时它不是标准,例如在 CREATE FUNCTION 从句 varchar(x)≠varchar(y) .
使用 CHECK 中的 CHECK 子句
表达限制(具有相同的 varchar 性能!) 例如 CHECK(char_length(x)<=10) . INSERT / UPDATE中的性能损失可以忽略不计,您还可以控制范围和字符串结构 例如 CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')
TL;DR: 如果您使用没有约束的 text 类型并且在这些列上有索引,则很可能您的某些列达到此限制并在尝试插入数据时出现错误,但使用 varchar(n) 时,您可以阻止它 .
Some more details: 这里的问题是PostgreSQL在为 text type或 varchar(n) 创建索引时没有给出任何异常,其中 n 大于2712.但是,当尝试插入压缩大小大于2712的记录时,它会给出错误 . 这意味着您可以插入100.000个字符串,该字符串由重复字符组成,因为它将被压缩得很远低于2712但您可能无法插入一些包含4000个字符的字符串,因为压缩的大小大于2712字节 . 使用 varchar(n) ,其中 n 并不比2712大得多,您可以避免这些错误 .
8 回答
没有什么区别,在引擎盖下它全部是
varlena
(variable length array) .查看Depesz的这篇文章:http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/
几个亮点:
本文进行了详细的测试,以显示所有4种数据类型的插入和选择的性能是相似的 . 它还详细介绍了在需要时限制长度的其他方法 . 基于函数的约束或域提供了即时增加长度约束的优点,并且基于减少字符串长度约束很少,depesz得出结论,其中一个通常是长度限制的最佳选择 .
正如文档中的“Character Types”指出的那样,
varchar(n)
,char(n)
和text
都以相同的方式存储 . 唯一的区别是需要额外的周期来检查长度(如果给出一个),以及如果char(n)
需要填充所需的额外空间和时间 .但是,当您只需要存储单个字符时,使用特殊类型
"char"
(保留双引号 - 它们是类型名称的一部分)会有轻微的性能优势 . 您可以更快地访问该字段,并且不存在存储长度的开销 .我刚刚从小写字母表中选择了1,000,000个随机
"char"
表 . 获取频率分布(select count(*), field ... group by field
)的查询大约需要650毫秒,而使用text
字段的相同数据大约需要760 .2016年更新基准(第9.5页)
并使用“纯SQL”基准测试(没有任何外部脚本)
使用UTF8的任何string_generator
主要基准:
2.1 . 插入
2.2 . SELECT比较和计数
准备具体测试(例子)
执行基本测试:
和其他测试,
...并使用
EXPLAIN ANALYZE
.UPDATED AGAIN 2018 (pg10)
少编辑添加2018的结果并强化建议 .
2016年和2018年的结果
平均而言,我的结果是在许多机器和许多测试中: all the same
(统计学上较小的标准偏差) .
推荐
text
数据类型,避免旧
varchar(x)
因为有时它不是标准,例如在CREATE FUNCTION
从句varchar(x)
≠varchar(y)
.使用
CHECK
中的CHECK
子句varchar
性能!)例如
CHECK(char_length(x)<=10)
.INSERT / UPDATE中的性能损失可以忽略不计,您还可以控制范围和字符串结构
例如
CHECK(char_length(x)>5 AND char_length(x)<=20 AND x LIKE 'Hello%')
在PostgreSQL手册上
我通常使用文字
参考文献:http://www.postgresql.org/docs/current/static/datatype-character.html
在我看来,
varchar(n)
有它自己的优势 . 是的,他们都使用相同的基础类型和所有这些 . 但是,应该指出PostgreSQL中的索引每行的大小限制为 2712 bytes .TL;DR: 如果您使用没有约束的
text
类型并且在这些列上有索引,则很可能您的某些列达到此限制并在尝试插入数据时出现错误,但使用varchar(n)
时,您可以阻止它 .Some more details: 这里的问题是PostgreSQL在为
text
type或varchar(n)
创建索引时没有给出任何异常,其中n
大于2712.但是,当尝试插入压缩大小大于2712的记录时,它会给出错误 . 这意味着您可以插入100.000个字符串,该字符串由重复字符组成,因为它将被压缩得很远低于2712但您可能无法插入一些包含4000个字符的字符串,因为压缩的大小大于2712字节 . 使用varchar(n)
,其中n
并不比2712大得多,您可以避免这些错误 .text和varchar具有不同的隐式类型转换 . 我注意到的最大影响是处理尾随空格 . 例如 ...
如您所料,返回
true, false, true
而不是true, true, true
.有点OT:如果您使用Rails,网页的标准格式可能会有所不同 . 对于数据输入表单
text
框是可滚动的,但character varying
(Railsstring
)框是单行的 . 只要需要显示视图 .character varying(n)
,varchar(n)
- (两者都一样) . 值将被截断为n个字符而不会引发错误 .character(n)
,char(n)
- (两者都相同) . 固定长度,用空白填充直到长度的末端 .text
- 无限长度 .Example:
我们得到了结果: