digoal
2020-09-04
PostgreSQL , 区块链
每一瓶茅台有一个唯一ID, 每次变更状态(例如出库、入库、销售等)会扫描这个ID并存储记录它的变更信息, 未来可以追溯一瓶茅台的整个生命周期, 过去任意时刻的状态.
同时可以统计任意时刻, 每个仓库、商店的存货.
可以查询每个仓库、商店的流水.
以交易品(例如茅台)为KEY, 设计一个方便存储它的最终状态的结构, 便于上区块链.
建表
create table tbl (
id int8 primary key, -- 主键
repo int, -- 仓库ID
src int, -- 来源ID
maotai_id text, -- 酒二维码
ops int, -- 操作(入库非0, 出库0)
ts timestamp -- 操作时间
);
create sequence seq;
索引
create index idx_tbl_1 on tbl (ts);
create index idx_tbl_2 on tbl (repo,ts);
create index idx_tbl_3 on tbl (maotai_id,ts);
1、进出库操作
模拟1000个仓库, 100万瓶酒的进出库操作
vi test.sql
\set repo random(1,999)
\set maotai_id random(1,1000000)
\set ops random(0,10)
insert into tbl(id,repo,src,maotai_id,ops,ts) values (nextval('seq'), :repo, :repo+1, md5(:maotai_id::text), :ops, clock_timestamp()+((random()*1000)::int::text||' hour')::interval);
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 52 -j 52 -T 600
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 52
number of threads: 52
duration: 600 s
number of transactions actually processed: 121164819
latency average = 0.257 ms
latency stddev = 0.660 ms
tps = 201941.041061 (including connections establishing)
tps = 201942.903863 (excluding connections establishing)
statement latencies in milliseconds:
0.000 \set repo random(1,999)
0.000 \set maotai_id random(1,1000000)
0.000 \set ops random(0,10)
0.257 insert into tbl(id,repo,src,maotai_id,ops,ts) values (nextval('seq'), :repo, :repo+1, md5(:maotai_id::text), :ops, clock_timest
2、初始化盘点统计
create table tbl_stat (
stat_date date, -- 统计日期
repo int, -- 仓库ID
cnt_j int, -- 进数量
cnt_c int, -- 出数量
cnt_s int, -- 剩数量
maotai_id_j text[], -- 进酒唯一标示
maotai_id_c text[], -- 出酒唯一标示
maotai_id_s text[], -- 剩酒唯一标示
primary key (stat_date,repo)
);
create or replace function arr_add(text[] , text[]) returns text[] as $$
select array(select * from unnest($1||$2) where unnest is not null group by 1);
$$ language sql strict immutable parallel safe;
create or replace function arr_min(text[] , text[]) returns text[] as $$
select array(select * from unnest($1) where unnest is not null except select * from unnest($2) where unnest is not null group by 1);
$$ language sql strict immutable parallel safe;
初始盘点
do language plpgsql $$
declare
v1 date; -- 日期
v2 int; -- 仓库ID
v_repo int := -99999; -- 计算态中间仓库ID
v_maotai_id_s text[]; -- 计算态中间 剩酒唯一标示
begin
for v1,v2 in select date(ts), repo from tbl group by repo,date(ts) order by repo, date(ts)
loop
if v2 <> v_repo then
insert into tbl_stat select
date(ts),
v2,
sum(case when ops <> 0 then 1 else 0 end), -- 进
sum(case when ops = 0 then 1 else 0 end), -- 出
sum(case when ops <> 0 then 1 else 0 end), -- 剩
array_agg(case when ops <> 0 then maotai_id else null end ) filter (where case when ops <> 0 then maotai_id else null end is not null), -- 进酒唯一标示
array_agg(case when ops = 0 then maotai_id else null end ) filter (where case when ops = 0 then maotai_id else null end is not null), -- 出酒唯一标示
array_agg(case when ops <> 0 then maotai_id else null end ) filter (where case when ops <> 0 then maotai_id else null end is not null) -- 剩酒唯一标示
from
(
select repo, maotai_id, ops, ts, row_number() over w as rn from
tbl
where ts >= v1 and ts < (date(v1)+1)
and repo=v2
window w as (partition by maotai_id order by ts desc)
) t
where rn=1
group by date(ts) , v2
returning repo, maotai_id_s into v_repo, v_maotai_id_s ;
else
insert into tbl_stat select
date(ts),
v2,
sum(case when ops <> 0 then 1 else 0 end), -- 进
sum(case when ops = 0 then 1 else 0 end), -- 出
array_length(arr_add(arr_min(v_maotai_id_s , array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null)), array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null)), 1), -- 剩
array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null), -- 进酒唯一标示
array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null), -- 出酒唯一标示
arr_add(arr_min(v_maotai_id_s , array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null)), array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null)) -- 剩酒唯一标示
from
(
select repo, maotai_id, ops, ts, row_number() over w as rn from
tbl
where ts >= v1 and ts < (date(v1)+1)
and repo=v2
window w as (partition by maotai_id order by ts desc)
) t
where rn=1
group by date(ts) , v2
returning repo, maotai_id_s into v_repo, v_maotai_id_s ;
end if;
end loop;
end;
$$;
3、任意时间点库存状态查询 - 基于盘点统计数据
do language plpgsql $$
declare
v_stat_date date;
maotai_id_s text[];
v_ts timestamp := ;
v_repo int := ;
r1 int;
r2 int;
r3 int;
r4 int;
r5 text[];
r6 text[];
r7 text[];
begin
select stat_date,maotai_id_s into v_stat_date,v_maotai_id_s
from tbl_stat
where stat_date <= v_ts
and repo = v_repo;
order by stat_date desc limit 1;
select
repo,
sum(case when ops <> 0 then 1 else 0 end), -- 进
sum(case when ops = 0 then 1 else 0 end), -- 出
array_length(arr_add(arr_min(v_maotai_id_s , array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null)), array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null)), 1), -- 剩
array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null), -- 进酒唯一标示
array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null), -- 出酒唯一标示
arr_add(arr_min(v_maotai_id_s , array_agg(case when ops = 0 then maotai_id else null end) filter (where case when ops = 0 then maotai_id else null end is not null)), array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null)) -- 剩酒唯一标示
into r1,r2,r3,r4,r5,r6,r7
from
(
select repo, maotai_id, ops, ts, row_number() over w as rn from
tbl
where ts >= v_stat_date and ts <= v_ts
and repo = v_repo
window w as (partition by maotai_id order by ts desc)
) t
where rn=1
group by repo;
raise notice 'r1: %, r2: %, r3: %, r4: %, r5: %, r6: %, r7: % ', r1,r2,r3,r4,r5,r6,r7;
end;
$$;
4、任意时间点库存状态查询 - 基于全量进出库数据
select
repo,
sum(case when ops <> 0 then 1 else 0 end), -- 剩
array_agg(case when ops <> 0 then maotai_id else null end) filter (where case when ops <> 0 then maotai_id else null end is not null) -- 剩酒唯一标示
from
(
select repo, maotai_id, ops, ts, row_number() over w as rn from
tbl
where ts <= '2020-09-25 00:23:05.510729'
and repo = 785
window w as (partition by maotai_id order by ts desc)
) t
where rn=1
group by repo ;
5、某瓶酒的历史变更记录
select maotai_id, json_agg(tbl order by ts) from tbl where maotai_id='ff47908aafddd72d4e655016520d277e' group by maotai_id;
postgres=# select maotai_id, json_agg(tbl order by ts) from tbl where maotai_id='ff47908aafddd72d4e655016520d277e' group by maotai_id;
-[ RECORD 1 ]---------------------------------------------------------------------------------------------------------------------------------
maotai_id | ff47908aafddd72d4e655016520d277e
json_agg | [{"id":34735305,"repo":807,"src":808,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-09-04T21:23:56.244449"}, +
| {"id":74130193,"repo":783,"src":784,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-05T21:27:11.796736"}, +
| {"id":37453385,"repo":205,"src":206,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-06T02:24:09.714835"}, +
| {"id":97343247,"repo":296,"src":297,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-09-07T01:29:05.530061"}, +
| {"id":91058470,"repo":744,"src":745,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-07T23:28:35.017644"}, +
| {"id":145525713,"repo":258,"src":259,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-08T03:33:05.408539"}, +
| {"id":31760042,"repo":936,"src":937,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-08T05:23:41.581655"}, +
| {"id":131883723,"repo":145,"src":146,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-08T11:31:57.62569"}, +
| {"id":65545540,"repo":872,"src":873,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-08T19:26:29.506013"}, +
| {"id":29520695,"repo":802,"src":803,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-09T08:23:30.693287"}, +
| {"id":114343558,"repo":800,"src":801,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-09-10T03:30:32.885349"}, +
| {"id":97694263,"repo":174,"src":175,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-10T09:29:09.601173"}, +
| {"id":75594874,"repo":975,"src":976,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-10T13:27:18.86642"}, +
| {"id":56500968,"repo":821,"src":822,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-10T18:25:44.13518"}, +
| {"id":67973729,"repo":362,"src":363,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-10T23:26:41.377913"}, +
| {"id":135713857,"repo":852,"src":853,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-11T03:32:16.427158"}, +
| {"id":46408124,"repo":920,"src":921,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-11T12:24:53.666793"}, +
| {"id":31993367,"repo":808,"src":809,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-11T13:23:42.717786"}, +
| {"id":118375741,"repo":51,"src":52,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-11T14:30:52.289315"}, +
| {"id":71201199,"repo":689,"src":690,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-11T22:26:57.34038"}, +
| {"id":126328527,"repo":316,"src":317,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-11T22:31:30.597398"}, +
| {"id":111140486,"repo":78,"src":79,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-12T16:30:15.685569"}, +
| {"id":73654209,"repo":759,"src":760,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-13T01:27:09.507913"}, +
| {"id":70790150,"repo":500,"src":501,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-13T02:26:55.260108"}, +
| {"id":122274055,"repo":424,"src":425,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-13T03:31:11.0396"}, +
| {"id":24637335,"repo":950,"src":951,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-13T05:23:06.814588"}, +
| {"id":33443489,"repo":772,"src":773,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-13T18:23:49.767513"}, +
| {"id":69024248,"repo":454,"src":455,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-13T19:26:46.555273"}, +
| {"id":87314565,"repo":589,"src":590,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-13T23:28:16.291879"}, +
| {"id":119816263,"repo":340,"src":341,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-14T00:30:59.230107"}, +
| {"id":42001856,"repo":246,"src":247,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-09-14T08:24:32.325776"}, +
| {"id":134046427,"repo":329,"src":330,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-14T10:32:08.298175"}, +
| {"id":121832203,"repo":99,"src":100,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-14T16:31:08.909409"}, +
| {"id":125701252,"repo":822,"src":823,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-16T00:31:27.606611"}, +
| {"id":111055113,"repo":204,"src":205,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-16T01:30:15.264525"}, +
| {"id":135598083,"repo":673,"src":674,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-09-16T19:32:15.861006"}, +
| {"id":121195194,"repo":423,"src":424,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-16T20:31:05.836418"}, +
| {"id":75257227,"repo":891,"src":892,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-17T04:27:17.198278"}, +
| {"id":85706189,"repo":221,"src":222,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-09-17T12:28:08.263981"}, +
| {"id":127087782,"repo":713,"src":714,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-17T13:31:34.229452"}, +
| {"id":88268511,"repo":834,"src":835,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-17T19:28:20.878802"}, +
| {"id":138764473,"repo":862,"src":863,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-18T09:32:32.485209"}, +
| {"id":38158596,"repo":612,"src":613,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-18T20:24:13.118435"}, +
| {"id":44438924,"repo":87,"src":88,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-19T08:24:44.058027"}, +
| {"id":70826460,"repo":495,"src":496,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-20T16:26:55.432691"}, +
| {"id":72320192,"repo":840,"src":841,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-09-20T19:27:02.98942"}, +
| {"id":142457084,"repo":990,"src":991,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-21T06:32:50.343622"}, +
| {"id":81858212,"repo":953,"src":954,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-09-21T07:27:49.425403"}, +
| {"id":124988598,"repo":219,"src":220,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-09-21T09:31:24.147777"}, +
| {"id":113514899,"repo":140,"src":141,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-21T21:30:28.643159"}, +
| {"id":72178420,"repo":665,"src":666,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-09-21T23:27:02.302363"}, +
| {"id":68530676,"repo":392,"src":393,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-09-22T04:26:44.06506"}, +
| {"id":103026047,"repo":785,"src":786,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-23T09:29:35.681348"}, +
| {"id":69426830,"repo":335,"src":336,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-24T03:26:48.653118"}, +
| {"id":77245188,"repo":26,"src":27,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-24T13:27:27.255357"}, +
| {"id":134145748,"repo":252,"src":253,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-24T13:32:08.788629"}, +
| {"id":64885812,"repo":66,"src":67,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-25T01:26:26.245651"}, +
| {"id":122725534,"repo":510,"src":511,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-25T02:31:13.206985"}, +
| {"id":51715896,"repo":216,"src":217,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-26T03:25:20.101389"}, +
| {"id":117806646,"repo":171,"src":172,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-09-26T05:30:49.531657"}, +
| {"id":70384084,"repo":517,"src":518,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-26T06:26:53.29374"}, +
| {"id":53866080,"repo":588,"src":589,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-09-26T08:25:31.245462"}, +
| {"id":140970173,"repo":502,"src":503,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-27T01:32:43.092909"}, +
| {"id":109760786,"repo":792,"src":793,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-28T20:30:08.879153"}, +
| {"id":66699542,"repo":108,"src":109,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-29T00:26:35.219546"}, +
| {"id":57348327,"repo":269,"src":270,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-29T02:25:48.295598"}, +
| {"id":85145917,"repo":920,"src":921,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-09-29T09:28:05.567424"}, +
| {"id":99721835,"repo":991,"src":992,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-09-29T09:29:19.660319"}, +
| {"id":104043294,"repo":715,"src":716,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-29T09:29:40.648687"}, +
| {"id":29485525,"repo":507,"src":508,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-09-29T13:23:30.519872"}, +
| {"id":89419389,"repo":147,"src":148,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-09-29T13:28:27.068515"}, +
| {"id":39624017,"repo":260,"src":261,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-29T14:24:20.205159"}, +
| {"id":94251946,"repo":335,"src":336,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-09-30T02:28:50.455361"}, +
| {"id":116227736,"repo":847,"src":848,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-09-30T03:30:41.88761"}, +
| {"id":99661650,"repo":33,"src":34,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-09-30T05:29:19.368156"}, +
| {"id":106100064,"repo":77,"src":78,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-30T10:29:50.701379"}, +
| {"id":71397694,"repo":102,"src":103,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-09-30T20:26:58.518229"}, +
| {"id":99553224,"repo":880,"src":881,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-09-30T20:29:18.849346"}, +
| {"id":138008124,"repo":994,"src":995,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-09-30T21:32:28.304702"}, +
| {"id":107309009,"repo":34,"src":35,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-09-30T23:29:56.570351"}, +
| {"id":128057892,"repo":115,"src":116,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-10-01T01:31:38.9838"}, +
| {"id":61004950,"repo":438,"src":439,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-10-01T03:26:06.654677"}, +
| {"id":93953032,"repo":753,"src":754,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-01T03:28:49.008266"}, +
| {"id":82484773,"repo":703,"src":704,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-10-01T12:27:52.606078"}, +
| {"id":120141335,"repo":659,"src":660,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-10-01T22:31:00.792038"}, +
| {"id":52572769,"repo":607,"src":608,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-10-02T10:25:24.58388"}, +
| {"id":124855400,"repo":327,"src":328,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-10-02T11:31:23.51715"}, +
| {"id":26935433,"repo":479,"src":480,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-02T13:23:17.956488"}, +
| {"id":115944661,"repo":908,"src":909,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-10-02T17:30:40.53365"}, +
| {"id":35402269,"repo":736,"src":737,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":7,"ts":"2020-10-02T22:23:59.484653"}, +
| {"id":140366111,"repo":294,"src":295,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-10-02T22:32:40.200811"}, +
| {"id":125340097,"repo":451,"src":452,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-10-03T08:31:25.824191"}, +
| {"id":106433545,"repo":754,"src":755,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-10-03T10:29:52.346749"}, +
| {"id":118365482,"repo":992,"src":993,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-05T18:30:52.239271"}, +
| {"id":80814647,"repo":765,"src":766,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-10-06T11:27:44.396902"}, +
| {"id":46630882,"repo":544,"src":545,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-10-06T13:24:54.760104"}, +
| {"id":119565956,"repo":806,"src":807,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-10-06T15:30:58.036786"}, +
| {"id":145414631,"repo":194,"src":195,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-10-06T16:33:04.865023"}, +
| {"id":76654740,"repo":357,"src":358,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-10-07T10:27:24.173847"}, +
| {"id":111801255,"repo":248,"src":249,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-10-08T03:30:19.254933"}, +
| {"id":77281253,"repo":692,"src":693,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-08T04:27:27.428198"}, +
| {"id":57985099,"repo":669,"src":670,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-10-08T06:25:51.37862"}, +
| {"id":77549848,"repo":174,"src":175,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-10-08T17:27:28.698152"}, +
| {"id":131626685,"repo":573,"src":574,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-10-08T20:31:56.360539"}, +
| {"id":35738874,"repo":886,"src":887,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-10-08T23:24:01.127106"}, +
| {"id":85818155,"repo":128,"src":129,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-10-09T20:28:08.800363"}, +
| {"id":80141981,"repo":283,"src":284,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":0,"ts":"2020-10-09T22:27:41.090715"}, +
| {"id":113801503,"repo":309,"src":310,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-09T23:30:30.199567"}, +
| {"id":29850954,"repo":594,"src":595,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-10-10T02:23:32.298866"}, +
| {"id":95300079,"repo":858,"src":859,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-10T02:28:55.492665"}, +
| {"id":32188216,"repo":703,"src":704,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-10T03:23:43.656867"}, +
| {"id":76489810,"repo":213,"src":214,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-10-10T07:27:23.289712"}, +
| {"id":110110605,"repo":111,"src":112,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":4,"ts":"2020-10-10T20:30:10.557874"}, +
| {"id":91068027,"repo":752,"src":753,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-10-10T23:28:35.064065"}, +
| {"id":108889953,"repo":129,"src":130,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-11T11:30:04.680044"}, +
| {"id":56234471,"repo":650,"src":651,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-11T17:25:42.849833"}, +
| {"id":115533593,"repo":263,"src":264,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-11T17:30:38.587551"}, +
| {"id":123231720,"repo":108,"src":109,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-10-12T16:31:15.64879"}, +
| {"id":118615053,"repo":5,"src":6,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":10,"ts":"2020-10-13T09:30:53.436333"}, +
| {"id":118059286,"repo":348,"src":349,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-10-14T14:30:50.738637"}, +
| {"id":29969289,"repo":694,"src":695,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":3,"ts":"2020-10-14T19:23:32.880148"}, +
| {"id":59308401,"repo":514,"src":515,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":1,"ts":"2020-10-14T22:25:58.158332"}, +
| {"id":64955133,"repo":492,"src":493,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":8,"ts":"2020-10-15T01:26:26.624283"}, +
| {"id":120735166,"repo":380,"src":381,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":9,"ts":"2020-10-15T14:31:03.652749"}, +
| {"id":82500025,"repo":289,"src":290,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":5,"ts":"2020-10-15T19:27:52.677637"}, +
| {"id":108609107,"repo":251,"src":252,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":2,"ts":"2020-10-16T02:30:03.317684"}, +
| {"id":137212562,"repo":76,"src":77,"maotai_id":"ff47908aafddd72d4e655016520d277e","ops":6,"ts":"2020-10-16T03:32:23.928613"}]
-- 区块链结构1
create table tbl_maotai1 (
maotai_id text primary key, -- 酒唯一标示
status jsonb, -- 所有历史变更记录
ts timestamp -- 修过时间
);
vi t1.sql
\set repo random(1,999)
\set maotai_id random(1,1000000)
\set ops random(0,10)
insert into tbl_maotai1 values (md5(:maotai_id::text), to_jsonb(row(nextval('seq'), :repo, :repo+1, md5(:maotai_id::text), :ops, clock_timestamp()+((random()*1000)::int::text||' hour')::interval )::tbl), clock_timestamp())
on conflict (maotai_id)
do update set status = jsonb_build_array(tbl_maotai1.status , excluded.status), ts=excluded.ts;
-- 区块链结构2
create table tbl ( -- 主类型结构 , 也可以使用create type代替之
id int8 primary key, -- 主键
repo int, -- 仓库ID
src int, -- 来源ID
maotai_id text, -- 酒二维码
ops int, -- 操作(入库非0, 出库0)
ts timestamp -- 操作时间
);
create sequence seq;
主数据结构
create table tbl_maotai2 (
maotai_id text primary key, -- 酒唯一标示
status tbl[], -- 所有历史变更记录
ts timestamp -- 修过时间
);
vi t2.sql
\set repo random(1,999)
\set maotai_id random(1,1000000)
\set ops random(0,10)
insert into tbl_maotai2 values (md5(:maotai_id::text), array[row(nextval('seq'), :repo, :repo+1, md5(:maotai_id::text), :ops, clock_timestamp()+((random()*1000)::int::text||' hour')::interval )::tbl], clock_timestamp())
on conflict (maotai_id)
do update set status = array_cat(tbl_maotai2.status , excluded.status), ts=excluded.ts;
pgbench -M prepared -n -r -P 1 -f ./t2.sql -c 52 -j 52 -T 600
采用方案1, 优势: 没有更新和删除, 不需要垃圾回收, 性能最好; 不限每瓶酒的操作次数.
方案2,3缺陷: 更新会产生垃圾, 需要垃圾回收(当然如果更新的并发量在每秒1000量级别, 垃圾回收几乎没有任何影响). 每条记录跟踪所有的生命周期, 最大1GB每个字段, 极端情况下可能超过一个商品的可维持内容, 例如更踪100万次变更后可能会达到1GB.
《PostgreSQL 家族图谱、社交图谱、树状关系、藤状分佣、溯源、等场景实践 - 递归,with recursive query (有向无环 , 有向有环)》
《PostgreSQL index include - 类聚簇表与应用(append only, IoT时空轨迹, 离散多行扫描与返回)》
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
