PostgreSQL 如何快速构建 海量 逼真 测试数据

3 minute read

背景

为了测试或验证需要,通常需要快速的构建测试数据。

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 兼容性 之 - 数据采样与脱敏》

《PostgreSQL 巧妙的数据采样方法》

《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) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

Flag Counter

digoal’s 大量PostgreSQL文章入口