Real-Time Multi-Stream Aggregation with JSON

tbl has already included records (0, 1, 'test0', now())  

session A:

insert into tbl (pk, caseid, info, crt_time) values (1, 1, 'test1', now());

session B:

insert into tbl (pk, caseid, info, crt_time) values (2, 1, 'test2', now());

If session A and session B are launched simultaneously, the records written may become the following:

(1, 1, 'test0_test1', now());

(2, 1, 'test0_test2', now());
However, the intended results are the two following records

(1, 1, 'test0_test1', now());

(2, 1, 'test0_test1_test2', now());

JSON Write Performance

create table tbl_ord (  
ordid int8, -- Order no.
appid int, -- Application ID
info jsonb, -- Content
crt_time timestamp -- Write time
);

create index idx_tbl_ord on tbl_ord(ordid, crt_time),
vi test.sql  

\set ordid random(1,10000000)
\set appid random(1,10)
insert into tbl_ord (ordid,appid,info,crt_time) values (:ordid,:appid,jsonb '{"a" : 1, "b" : 2}',now());

pgbench -M prepared -n -r -P 1 -f ./test.sql -c 40 -j 40 -t 2500000
transaction type: ./test.sql  
scaling factor: 1
query mode: prepared
number of clients: 40
number of threads: 40
number of transactions per client: 2500000
number of transactions actually processed: 100000000/100000000
latency average = 0.170 ms
latency stddev = 0.498 ms
tps = 234047.009786 (including connections establishing)
tps = 234060.902533 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set ordid random(1,10000000)
0.001 \set appid random(1,10)
0.168 insert into tbl_ord (ordid,appid,info,crt_time) values (:ordid,:appid,jsonb '{"a" : 1, "b" : 2}',now());

JSON Full Field Index

GIN indexes can be used to efficiently search for keys or key/value pairs occurring within   a large number of jsonb documents (datums).   

Two GIN "operator classes" are provided, offering different performance and flexibility trade-offs.

The default GIN operator class for jsonb supports queries with top-level key-exists operators
?, ?& and ?| operators and path/value-exists operator @>.

(For details of the semantics that these operators implement,

see Table 9.44.) An example of creating an index with this operator class is:

CREATE INDEX idxgin ON api USING GIN (jdoc);

The non-default GIN operator class jsonb_path_ops supports indexing the @> operator only.

An example of creating an index with this operator class is:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);
create index idx_tbl_ord_2 on tbl_ord using gin (info);
-- Find documents in which the key "company" has value "Magnafone"  
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

Check a Point and Use JSON Aggregation to Obtain Snapshots at Any Time Point

select caseid, jsonb_agg((pk,info,crt_time) order by crt_time) from tbl where caseid=? and crt_time<=? group by caseid;
postgres=# create type typ1 as (c1 int, c2 int);  
CREATE TYPE

postgres=# select jsonb_agg((c1,c2)::typ1 order by c1 desc) from (values (1,2),(2,3)) t(c1,c2);
jsonb_agg
------------------------------------------
[{"c1": 2, "c2": 3}, {"c1": 1, "c2": 2}]
(1 row)
create type typ2 as (appid int, info jsonb, crt_time timestamp);  

postgres=# select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=1 and crt_time<=now() group by ordid;

-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ordid | 1
jsonb_agg | [{"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:24:56.659672"}, {"info": {"a": 1, "b": 2}, "appid": 5, "crt_time": "2017-12-09T23:25:13.073163"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:25:49.94649"}, {"info": {"a": 1, "b": 2}, "appid": 10, "crt_time": "2017-12-09T23:26:23.523946"}, {"info": {"a": 1, "b": 2}, "appid": 2, "crt_time": "2017-12-09T23:26:49.900199"}, {"info": {"a": 1, "b": 2}, "appid": 7, "crt_time": "2017-12-09T23:27:10.643058"}, {"info": {"a": 1, "b": 2}, "appid": 8, "crt_time": "2017-12-09T23:27:20.937021"}, {"info": {"a": 1, "b": 2}, "appid": 8, "crt_time": "2017-12-09T23:27:21.446752"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:29:19.10536"}, {"info": {"a": 1, "b": 2}, "appid": 7, "crt_time": "2017-12-09T23:29:56.192353"}, {"info": {"a": 1, "b": 2}, "appid": 1, "crt_time": "2017-12-09T23:30:07.879201"}, {"info": {"a": 1, "b": 2}, "appid": 6, "crt_time": "2017-12-09T23:30:31.487457"}]

Time: 0.696 ms
vi test.sql  

\set ordid random(1,10000000)
select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=:ordid and crt_time<=now() group by ordid;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 28 -j 28 -T 120  

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 28
number of threads: 28
duration: 120 s
number of transactions actually processed: 4677282
latency average = 0.718 ms
latency stddev = 0.463 ms
tps = 38977.016281 (including connections establishing)
tps = 38982.209839 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set ordid random(1,10000000)
0.717 select ordid, jsonb_agg((appid,info,crt_time)::typ2 order by crt_time) from tbl_ord where ordid=:ordid and crt_time<=now() group by ordid;

Excellent Performance of Batch-Obtaining Records by Using JSON Indexes and Parallel Computing in PostgreSQL 10

postgres=# select count(*) from tbl_ord;  
count
-----------
100000000
(1 row)

Time: 1014.201 ms (00:01.014)
postgres=# explain (analyze,verbose,timing,costs,buffers) SELECT * from tbl_ord WHERE info @> '{"a": 5}';  
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=32241.40..142872.70 rows=100000 width=61) (actual time=3.878..3.878 rows=0 loops=1)
Output: ordid, appid, info, crt_time
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Bitmap Heap Scan on public.tbl_ord (cost=32241.40..142872.70 rows=100000 width=61) (actual time=0.158..0.158 rows=0 loops=1)
Output: ordid, appid, info, crt_time
Recheck Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)
Buffers: shared hit=6
Worker 0: actual time=0.158..0.158 rows=0 loops=1
Buffers: shared hit=6
-> Bitmap Index Scan on idx_tbl_ord_2 (cost=0.00..32216.40 rows=100000 width=0) (actual time=0.153..0.153 rows=0 loops=1)
Index Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)
Buffers: shared hit=6
Worker 0: actual time=0.153..0.153 rows=0 loops=1
Buffers: shared hit=6
Planning time: 0.092 ms
Execution time: 4.836 ms
(18 rows)

Time: 5.416 ms
postgres=# set max_parallel_workers_per_gather =0;
SET
Time: 0.202 ms
postgres=# explain (analyze,verbose,timing,costs,buffers) SELECT * from tbl_ord WHERE info @> '{"a": 5}';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.tbl_ord (cost=32241.40..142872.70 rows=100000 width=61) (actual time=0.062..0.062 rows=0 loops=1)
Output: ordid, appid, info, crt_time
Recheck Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)
Buffers: shared hit=6
-> Bitmap Index Scan on idx_tbl_ord_2 (cost=0.00..32216.40 rows=100000 width=0) (actual time=0.060..0.060 rows=0 loops=1)
Index Cond: (tbl_ord.info @> '{"a": 5}'::jsonb)
Buffers: shared hit=6
Planning time: 0.091 ms
Execution time: 0.098 ms
(9 rows)

Time: 0.539 ms

Connect Batch Data Results to Other Business Platform by Writing to OSS

Summary

References

--

--

--

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to mount SMB share behind OpenVPN to Pod on GKE

Python Interfaces

READ/DOWNLOAD#[ Siemens Step 7 (TIA Portal) Programming, a Practical Approach FULL BOOK PDF & FULL…

Summer Sprint: Q&A with Ro Engineering Fellows of 2021

8. Triple Constraint & Planning for IT Projects

Which number will replace the question make (?) in the following serie

The three University Degrees — Which one to achieve for Computer Science and Software Engineering…

Alibaba Cloud PolarDB: 5 Stages of Cloud Database Adoption

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alibaba Cloud

Alibaba Cloud

Follow me to keep abreast with the latest technology news, industry insights, and developer trends. Alibaba Cloud website:https://www.alibabacloud.com

More from Medium

Redis: Cluster vs Sentinel, Speed vs Cost

Keycloak Admin REST API

Build and run microservices with Kubernetes, Helm and Skaffold locally

Kafka—Going serverless