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

--

--

--

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

Mastering Data Variety

Properties in Python: Fundamentals for Data Scientists

How to analyze energy data usage with Home Assistant

The Trials of Deciding on a Process

Analysis of Recent Admits and Rejects of Top 30 US Universities

Data Science Project | Predicting Weather Data

Data Science for the Thickness of Oil Spills

E-commerce Product Recommendation System (Content Based)

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

E2E Dashboard at LitmusChaos

Database Cache Consistency Strategies

Analysis of different cloud based MongoDB solutions

Elastic Search with AEMAsCS