有些快递公司提供一次查询多个快递单号.

快递的状态表的特点, 我假象它是更新少, 查询多的一个特点.

并且更新不应该出现冲突, 即多个线程同时更新同一条记录. 

那么查询多, 更新少的话, 我的建议是在跟踪表中, 一个快递单子一条记录. 

这样做的好处是, 查询时很快, 只需要扫描2个块左右 , 1个索引块, 1个HEAP块.

但是如果一个快递单子多条记录, 即每个状态插入一条, 这样的带来的是大量的数据块的扫描, 基本上每条记录都会分布在不同的数据块上. 假设平均一个快递单的跟踪记录是15条, 数据块的扫描要多14个.

那么1条记录怎么存储呢?

快递单+复合类型数组(节点时间+节点ID+状态)+rec插入时间+rec更新时间

当然, 这里不用数组也行, 只要程序能根据存储的信息分隔每个节点状态即可, 这样做的好处是结构更加松散, 适合非结构话数据, 比方说在快递结束的地方会出现派件人, 派件人的电话等, 结构和前面不一样.

对应我这个例子是: 

3393108415, [(2013-12-12 17:37:27,安徽合肥市蜀山区包河三部公司字典表里的ID,收件状态ID), (下一个状态的复合类型...), ()], 记录插入时间, 记录的更新时间

新的状态录入时, 用array_append往数组后面追加数据即可.

我们举一个实际的例子(本例省去字典表, 只为体现单行和多行的查询性能) : 

为了使生成速度快点, 我们使用NOLOGGING表, 同时关闭 autovacuum.

digoal=# create unlogged table kuaidi_log1(id int8, crt_time timestamp, nodeid text, stat text);
CREATE TABLE
digoal=# create unlogged table kuaidi_log2(id int8, comp text);
CREATE TABLE

假设每天有500W个订单, 平均每个订单20条数据的话, 需要1亿条测试数据.

pg93@db-172-16-3-150-> vi test.sql
\setrandom id 1 5000000
insert into kuaidi_log1 values (:id, now(), '浙江省圆通快递杭州分公司', '已收件');

开启16个连接使用pgbench进行插入.

pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -t 6250000
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
number of transactions per client: 6250000
number of transactions actually processed: 100000000/100000000
tps = 79215.655001 (including connections establishing)
tps = 79216.552965 (excluding connections establishing)
statement latencies in milliseconds:
        0.001938        \setrandom id 1 5000000
        0.194045        insert into kuaidi_log1 values (:id, now(), '浙江省圆通快递杭州分公司', '已收件');
刚好1亿记录.

插入完后创建快递单的索引.

digoal=# create index idx_kuaidi_log1_1 on kuaidi_log1 (id);

kuaidi_log1表和索引的大小

digoal=# \dt+ kuaidi_log1
                         List of relations
  Schema  |    Name     | Type  |  Owner   |  Size   | Description 
----------+-------------+-------+----------+---------+-------------
 postgres | kuaidi_log1 | table | postgres | 8880 MB | 
(1 row)

digoal=# \di+ idx_kuaidi_log1_1 
                                   List of relations
  Schema  |       Name        | Type  |  Owner   |    Table    |  Size   | Description 
----------+-------------------+-------+----------+-------------+---------+-------------
 postgres | idx_kuaidi_log1_1 | index | postgres | kuaidi_log1 | 2142 MB | 
(1 row)

把这些记录合并到kuaidi_log2表, 每个快递单1条.

insert into kuaidi_log2 select id, string_agg(crt_time::text||','||nodeid||','||stat, ';' order by crt_time) from kuaidi_log1 group by id;
INSERT 0 5000000

创建kuaidi_log2表的快递单号索引.

digoal=# create index idx_kuaidi_log2_1 on kuaidi_log2 (id);

kuaidi_log2表和索引大小, 索引变小了20倍, 因为只有500万条记录.

digoal=# \dt+ kuaidi_log2
                         List of relations
  Schema  |    Name     | Type  |  Owner   |  Size   | Description 
----------+-------------+-------+----------+---------+-------------
 postgres | kuaidi_log2 | table | postgres | 7172 MB | 
(1 row)

digoal=# \di+ idx_kuaidi_log2_1 
                                  List of relations
  Schema  |       Name        | Type  |  Owner   |    Table    |  Size  | Description 
----------+-------------------+-------+----------+-------------+--------+-------------
 postgres | idx_kuaidi_log2_1 | index | postgres | kuaidi_log2 | 107 MB | 
(1 row)

数据对比 : 

digoal=# select * from kuaidi_log1 where id=1;
 id |          crt_time          |          nodeid          |  stat  
----+----------------------------+--------------------------+--------
  1 | 2013-12-13 08:40:21.116411 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:41:22.298613 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:42:38.141089 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:43:10.396533 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:43:14.961726 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:44:22.036227 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:24.767686 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:41.418713 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:46:48.03819  | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:47:32.8119   | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:47:48.996097 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:50:54.001399 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:52:00.678307 | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:52:14.56005  | 浙江省圆通快递杭州分公司 | 已收件
  1 | 2013-12-13 08:56:45.806082 | 浙江省圆通快递杭州分公司 | 已收件
(15 rows)

digoal=# select * from kuaidi_log2 where id=1;
 id |                                                                                                                               
                                                                                                                                    
                                                                                                                                    
                                                comp                                                                                
                                                                                                                                    
                                                                                                                                    
                                                                                               
----+-------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------
  1 | 2013-12-13 08:40:21.116411,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:41:22.298613,浙江省圆通快递杭州分公司,已收件;2013-12-
13 08:42:38.141089,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:43:10.396533,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:43:14.96
1726,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:44:22.036227,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:46:24.767686,浙江省圆
快递杭州分公司,已收件;2013-12-13 08:46:41.418713,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:46:48.03819,浙江省圆通快递杭州分公司,
已收件;2013-12-13 08:47:32.8119,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:47:48.996097,浙江省圆通快递杭州分公司,已收件;2013-12-1
3 08:50:54.001399,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:52:00.678307,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:52:14.560
05,浙江省圆通快递杭州分公司,已收件;2013-12-13 08:56:45.806082,浙江省圆通快递杭州分公司,已收件
(1 row)

测试kuaidi_log1的查询性能

vi test.sql
\setrandom id 1 5000000
select * from kuaidi_log1 where id=:id order by crt_time;
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 349163
tps = 11635.992258 (including connections establishing)
tps = 11641.693582 (excluding connections establishing)
statement latencies in milliseconds:
        0.003351        \setrandom id 1 5000000
        1.368531        select * from kuaidi_log1 where id=:id order by crt_time;

测试kuaidi_log2的查询性能, 流水已按时间排序, 所以不需要再次排序.

vi test.sql
\setrandom id 1 5000000
select * from kuaidi_log2 where id=:id;
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
pg93@db-172-16-3-150-> pgbench -M prepared -n -r -f ./test.sql -c 16 -j 4 -T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 1880690
tps = 62681.334228 (including connections establishing)
tps = 62711.904831 (excluding connections establishing)
statement latencies in milliseconds:
        0.002509        \setrandom id 1 5000000
        0.251047        select * from kuaidi_log2 where id=:id;