Pan-Chinese Electricity Grid System: Real-Time OLTP and OLAP Database Design

Business Requirement Analysis

Key Point Analysis

Architecture Design

Scalability Design

Detail Business Demo

Analysis + Scheduling Business Demo

Real-time Monitoring Demo

Table Structure Design

Create a Table and a Query

create table tbl_real_data (  
uid int primary key, -- Electricity meter ID
last_time timestamp, -- Last update time
curr_time timestamp, -- Current time
d1 float4, -- Reading at the beginning of a day, provided by business
d2 float4, -- Reading at the beginning of a month, provided by business
d3 float4, -- Reading at the beginning of a year, provided by business
d4 float4, -- Last reading
d5 float4 -- Current reading, provided by business
);
insert into tbl_real_data values ($1,$2,$3,$4,$5,$6,$7,$8)   
on conflict (uid) do
update set
last_time=tbl_real_data.curr_time,
curr_time=excluded.curr_time,
d1=coalesce(excluded.d1,tbl_real_data.d1),
d2=coalesce(excluded.d2,tbl_real_data.d2),
d3=coalesce(excluded.d3,tbl_real_data.d3),
d4=tbl_real_data.d5,
d5=excluded.d5
returning *;
insert into tbl_real_data values (  
1, -- Electricity meter ID
null, -- Last update time
now(), -- Current time
2, -- Reading at the beginning of a day, provided by business at the time of flipping
3, -- Reading at the beginning of a month, provided by business at the time of flipping
4, -- Reading at the beginning of a year, provided by business at the time of flipping
null, -- Last reading
6 -- Current reading
)
on conflict (uid) do
update set
last_time=tbl_real_data.curr_time,
curr_time=excluded.curr_time,
d1=coalesce(excluded.d1,tbl_real_data.d1),
d2=coalesce(excluded.d2,tbl_real_data.d2),
d3=coalesce(excluded.d3,tbl_real_data.d3),
d4=tbl_real_data.d5,
d5=excluded.d5
returning *;

uid | last_time | curr_time | d1 | d2 | d3 | d4 | d5
-----+-----------+----------------------------+----+----+----+----+----
1 | | 2017-08-26 12:19:54.486801 | 2 | 3 | 4 | | 6
(1 row)

insert into tbl_real_data values (
1, -- Electricity meter ID
null, -- Last update time
now(), -- Current time
null, -- Reading at the beginning of a day, provided by business at the time of flipping
null, -- Reading at the beginning of a month, provided by business at the time of flipping
null, -- Reading at the beginning of a year, provided by business at the time of flipping
null, -- Last reading
8 -- Current reading
)
on conflict (uid) do
update set
last_time=tbl_real_data.curr_time,
curr_time=excluded.curr_time,
d1=coalesce(excluded.d1,tbl_real_data.d1),
d2=coalesce(excluded.d2,tbl_real_data.d2),
d3=coalesce(excluded.d3,tbl_real_data.d3),
d4=tbl_real_data.d5,
d5=excluded.d5
returning *;

uid | last_time | curr_time | d1 | d2 | d3 | d4 | d5
-----+----------------------------+----------------------------+----+----+----+----+----
1 | 2017-08-26 12:19:54.486801 | 2017-08-26 12:20:01.452364 | 2 | 3 | 4 | 6 | 8
(1 row)

The last status is returned. Business implements real-time alerting based on the returned status information and configured rules.

Stress Testing

vi test.sql  

\set uid random(1,10000000)
\set d5 random(1,100000)
insert into tbl_real_data (uid,curr_time,d5) values (:uid, now(), :d5) on conflict (uid) do update set last_time=tbl_real_data.curr_time, curr_time=excluded.curr_time, d1=coalesce(excluded.d1,tbl_real_data.d1), d2=coalesce(excluded.d2,tbl_real_data.d2), d3=coalesce(excluded.d3,tbl_real_data.d3), d4=tbl_real_data.d5, d5=excluded.d5 returning *;
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120  

tps = 291094.784574 (including connections establishing)
tps = 291123.921221 (excluding connections establishing)

Performance Metrics

Summary

--

--

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