标签
PostgreSQL , pgbench , 压测 , 变量 , 测试数据构建
背景
为了测试或验证需要，通常需要快速的构建测试数据。
PostgreSQL提供了一些非常有用的功能，可以帮助用户快速的构建测试数据。
有趣的功能
1、SRF
返回多条记录的函数。例如
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+---------------------+-----------------------------------+--------------------------------------------------------------------+--------
pg_catalog | generate_series | SETOF bigint | bigint, bigint | normal
pg_catalog | generate_series | SETOF bigint | bigint, bigint, bigint | normal
pg_catalog | generate_series | SETOF integer | integer, integer | normal
pg_catalog | generate_series | SETOF integer | integer, integer, integer | normal
pg_catalog | generate_series | SETOF numeric | numeric, numeric | normal
pg_catalog | generate_series | SETOF numeric | numeric, numeric, numeric | normal
pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | normal
pg_catalog | generate_series | SETOF timestamp with time zone | timestamp with time zone, timestamp with time zone, interval | normal
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer | normal
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer, boolean | normal
(10 rows)
返回一批数值、时间戳、或者数组的下标。
例子，生成一批顺序值。
postgres=# select id from generate_series(1,10) t(id);
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
2、随机数
random()
例子，生成一批随机整型
postgres=# select (random()*100)::int from generate_series(1,10);
int4
------
14
82
25
75
4
75
26
87
84
22
(10 rows)
3、随机字符串
md5(random()::text)
例子，生成一批随机字符串
postgres=# select md5(random()::text) from generate_series(1,10);
md5
----------------------------------
ba1f4f4b0073f61145a821c14437230d
a76b09292c1449ebdccad39bcb5864c0
d58f5ebe43f631e7b5b82e070a05e929
0c0d3971205dc6bd355e9a60b29a4c6d
bd437e87fd904ed6ecc80ed782abac7d
71aea571d8c0cd536de53fd2be8dd461
e32e105db58f9d39245e3e2b27680812
174f491a2ec7a3498cab45d3ce8a4277
563a7c389722f746378987b9c4d9bede
6e8231c4b7d9a5cfaae2a3e0cef22f24
(10 rows)
4、重复字符串
repeat('abc', 10)
例子，生成重复2次的随机字符串
postgres=# select repeat(md5(random()::text),2) from generate_series(1,10);
repeat
------------------------------------------------------------------
616d0a07a2b61cd923a14cb3bef06252616d0a07a2b61cd923a14cb3bef06252
73bc0d516a46182b484530f5e153085e73bc0d516a46182b484530f5e153085e
e745a65dbe0b4ef0d2a063487bbbe3d6e745a65dbe0b4ef0d2a063487bbbe3d6
90f9b8b18b3eb095f412e3651f0a946c90f9b8b18b3eb095f412e3651f0a946c
b300f78b20ac9a9534a46e9dfd488761b300f78b20ac9a9534a46e9dfd488761
a3d55c275f1e0f828c4e6863d4751d06a3d55c275f1e0f828c4e6863d4751d06
40e609dbe208fc66372b1c829018097140e609dbe208fc66372b1c8290180971
f661298e28403bc3005ac3aebae49e16f661298e28403bc3005ac3aebae49e16
10d0641e40164a238224d2e16a28764710d0641e40164a238224d2e16a287647
450e599890935df576e20c457691c421450e599890935df576e20c457691c421
(10 rows)
5、随机中文
create or replace function gen_hanzi(int) returns text as $$
declare
res text;
begin
if $1 >=1 then
select string_agg(chr(19968+(random()*20901)::int), '') into res from generate_series(1,$1);
return res;
end if;
return null;
end;
$$ language plpgsql strict;
postgres=# select gen_hanzi(10) from generate_series(1,10);
gen_hanzi
----------------------
騾歵癮崪圚祯骤氾準赔
縬寱癱办戾薶窍爉充環
鷊赶輪肸蒹焷尮禀漽湯
庰槖诤蜞礀链惧珿憗腽
憭釃轮訞陡切瀰煈瘐獵
韸琵慆蝾啈響夐捶燚積
菥芉阣瀤樂潾敾糩镽礕
廂垅欳事鎤懯劑搯蔷窡
覤綊伱鳪散噹镄灳毯杸
鳀倯鰂錾牓晟挗觑镈壯
(10 rows)
6、随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$
select array_agg((random()*$1)::int) from generate_series(1,$2);
$$ language sql strict;
postgres=# select gen_rand_arr(100,10) from generate_series(1,10);
gen_rand_arr
---------------------------------
{69,11,12,70,7,41,81,95,83,17}
{26,79,20,21,64,64,51,90,38,38}
{3,64,46,28,26,55,39,12,69,76}
{66,38,87,78,8,94,18,88,89,1}
{6,14,81,26,36,45,90,87,35,28}
{25,38,91,71,67,17,26,5,29,95}
{82,94,32,69,72,40,63,90,29,51}
{91,34,66,72,60,1,17,50,88,51}
{77,13,89,69,84,56,86,10,61,14}
{5,43,8,38,11,80,78,74,70,6}
(10 rows)
7、连接符
postgres=# select concat('a', ' ', 'b');
concat
--------
a b
(1 row)
8、自定义函数
通过自定义函数，可以生成很多有趣的数据。
随机身份证号
create or replace function gen_id(
a date,
b date
)
returns text as $$
select lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
lpad((random()*99)::int::text, 2, '0') ||
to_char(a + (random()*(b-a))::int, 'yyyymmdd') ||
lpad((random()*99)::int::text, 2, '0') ||
random()::int ||
(case when random()*10 >9 then 'X' else (random()*9)::int::text end ) ;
$$ language sql strict;
postgres=# select gen_id('1900-01-01', '2017-10-16') from generate_series(1,10);
gen_id
--------------------
25614020061108330X
49507919010403271X
96764619970119860X
915005193407306113
551360192005045415
430005192611170108
299138191310237806
95149919670723980X
542053198501097403
482334198309182411
(10 rows)
建模
建模是指根据业务需求，设计表、函数、视图等。
建模完成后，需要创建对象。
构建测试数据
根据业务提供的数据限定条件，构建测试数据。
那么用户需要提供什么呢？
用户除了提供结构，还需要提供数据的layout，这些LAYOUT用于帮助构建真实的测试数据。
那么layout包含什么呢？实际上就包含了统计信息中要的东西。
《PostgreSQL 统计信息pg_statistic格式及导入导出dump_stat - 兼容Oracle》
postgres=# \d pg_stats
View "pg_catalog.pg_stats"
Column | Type | Collation | Nullable | Default
------------------------+----------+-----------+----------+---------
schemaname | name | | | -- 对象所属的schema
tablename | name | | | -- 对象名
attname | name | | | -- 列名
inherited | boolean | | | -- 是否为继承表的统计信息（false时表示当前表的统计信息，true时表示包含所有继承表的统计信息）
null_frac | real | | | -- 该列空值比例
avg_width | integer | | | -- 该列平均长度
n_distinct | real | | | -- 该列唯一值个数（-1表示唯一，小于1表示占比，大于等于1表示实际的唯一值个数）
most_common_vals | anyarray | | | -- 该列高频词
most_common_freqs | real[] | | | -- 该列高频词对应的出现频率
histogram_bounds | anyarray | | | -- 该列柱状图（表示隔出的每个BUCKET的记录数均等）
correlation | real | | | -- 该列存储相关性（-1到1的区间），绝对值越小，存储越离散。小于0表示反向相关，大于0表示正向相关
most_common_elems | anyarray | | | -- 该列为多值类型（数组）时，多值元素的高频词
most_common_elem_freqs | real[] | | | -- 多值元素高频词的出现频率
elem_count_histogram | real[] | | | -- 多值元素的柱状图中，每个区间的非空唯一元素个数
例子
create table a -- 总共N条记录
(
id int primary key, -- 唯一值
c1 int, -- 取值范围，有多少唯一值，有多少空值，相关性，有哪些高频词
c2 text, -- 取值范围，有多少唯一值，有多少空值，相关性，平均长度，有哪些高频词
c3 timestamp -- .......
);
高吞吐构建测试数据的方法
想不想体验一下？1000万行/s的构建速度。
《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》
里面涉及的技术包括：
1、继承表
方便管理多个同类结构的表。
2、do language
可以写比较复杂的逻辑，例如创建一堆表。
3、pgbench
压测工具
4、UDF schemaless
通过UDF构建动态SQL，批量写入到多表。
更多例子，参考下面的文章。
参考
《PostgreSQL 11 preview - pgbench 变量、函数扩展 - 暨pgbench 自定义 benchmark讲解》
《PostgreSQL Oracle 兼容性 之 - 数据采样与脱敏》
《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》
《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》
