Data Denormalization meetup

denormalization.org

Data Denormalization

Data DeNormalization

What is Data Normalization?

What about data normal forms?

• Avoid data duplication to reduce data redundancy
  • Less data
• Avoid multiple updates - one source of truth
  • Avoid data editing anomaly
• A more representative data model

Sample

Employee	Phone	Floor	Room №
Alice	651435	1	20 b
Bob	4324234	3	30 a
John	534315	3	30 a

3NF

———-+———+———+ ——————-+——-
Employee	Phone	Room ID		Room ID	Room №	Floor
———-+———+———+ ——————-+——-
Alice	651435	1		1	20 b	1
Bob	4324234	2		2	30 a	3
John	534315	2	——————-+——-

Why denormalization?

Project on begin

Employee		Room
id		->	id
name			floor
phone			room№
room_id	–

Project on prod

Salary		Room
id		id	<—-+
employee	—-+	floor
date			room№
amount

DeparmentEmployee

->	id		->	id
	name			name
	head	-+	phone
	room_id	-+

+———————| department |

Project after 1 year

	Project		Customer
	———–		————
╔═>	id	╔═>	id
║	customer	═════╝	name
║	name		phone
║	logo
║
║	Member
╚══	project
	role
	employee	══════╗
║
	Salary	║	Room
	id	║	id	<══╗
	employee	════╣	floor	║
	date	║	room№	║
	amount	║ ║
║ ║
	Deparment	║	Employee	║
╔═>	id	╠═>	id	║
║	name	║	name	║
║	head	═╝	phone	║
║	room_id	═╝
╚═════════════════════	department

New feature

——————————————–

Search: __________________
John +1(991)33434 - Development Room 42
Year Salary: 100$ Lead: Max
Projects: QA in Cars, Dev in Toys
Ivan +1(991)4325355 - Management Room 36
Year Salary: 80$ Lead: Ken
Projects: QA in Cars, Manager in Food

——————————————–

SQL

-- pseudo sql =)
select
  employee
  , sum(salary)
  , array_agg(member)
  ...
from employee
join department on ..
join employee   on ..
join salary     on ..
join member     on ..
join project    on ..
group by employee
where
  employee      ilike $search
  or member     ilike $search
  or project    ilike $search
  or department ilike $search
  ...

Alter Employee

Employee
id
name
phone
room_id
department
=salary
=boss
=member[]
=fts

Add subs/hooks

Add subs/hooks for all related tables

• Salary
• Deparment
• Employee
• Project
• Member

Goals of denormalization

• Improve search
  • Avoid joins and aggregations
  • Avoid multiple or
• Simplify complex data relations and structure

How to denormalize?

Edit existing entity

Employee
id
name
phone
room_id
department
=salary
=boss
=member[]
=fts

Real case

Normal form data:

————– —————— ———————— —————–

Practitioner	<-+-	PractitionerRole	->	Organization	->	Organization
Иванов И.И.			Врач стоматолог		Стоматологическое отд.		Гор. стом. пол.

————– | —————— ———————— —————–

—————— ———————— —————–

-

PractitionerRole

->

Organization

->

Organization

Терапевт

Терапевт. отделение

ГКБ 1

—————— ———————— —————–

After denormalization:

————————-

Practitioner

Иванов И.И.

=Врач стоматолог

=Стоматологическое отд.

=Гор. стом. пол.

=Терапевт

=Терапевт. отделение

=ГКБ 1

————————-

Profit

• Super easy

Problems

• Standarts conflict
• Integration
• Conflict existing ui forms/controllers
• Business Logic
• Heavy write load

MVCC

———-

8kb page
1 —–
2 —–
3 —–

———-

Update row 1 and 2

———-

8kb page
-1 ——
-2 ——
3 ——
1*——

———-

8kb page
2*——

———-

TOAST - The Oversized-Attribute Storage Technique

———-

8kb page
1 —–
2 —–
3 —–

———-

Update row 1 insert too large object

———-

8kb page
-1 —–
2 —–
1*——-	—————-

———-

———- ————–

8kb page		Toast
———-		————–
-1 —–		1.1* ——-
2 —–		1.2* ——-
1* toast

———- ————–

Create new entity

EmployeeInfo
employee_id
=phone
=salary
=boss
=member[]
=fts

Real case

———– ————— ——— ——— ——– ————– —————–

Condition

<-

EpisodeOfCare

->

Patient

<-

Binding

->

Sector

->

Practitioner

->

PractitonerRole

———– ————— ——— ——— ——– ————– —————–

Separate administrative and clinical parts

Administrative part

——— ——— ——– ————– —————–

Patient

<-

Binding

->

Sector

->

Practitioner

->

PractitonerRole

——— ——— ——– ————– —————–

PatientBinding
patient_id
sector_id
practitionerrole_id

Clinical part

——— ————— ———–

Patient

<-

EpisodeOfCare

->

Condition

——— ————— ———–

PatientCondition
patient_id
condition_type

Profits

• Less and easiera update logic
• No update logic
• Explicit denormalization
• Less toast risk

Bloat protection =)

-- pseudo sql
drop table if exists denormalization.patientbinding;
CREATE TABLE if not exists denormalization.patientbinding (like public.patientbinding);

insert into denormalization.patientbinding (patient_id, sector_id, practitionerrole)
select patient.id ....
from patient
join binding on ..
join sector on ..
join practitioner on ..
join practitionerrole on ..;

create index patientbinding_patient_id on denormalization.patientbinding (patient_id);
create index patientbinding_sector_id on denormalization.patientbinding (sector_id);

BEGIN;
-- Drop target table
drop table public.patientbinding;

-- Replace target table on public schema wiht temp table
alter table denormalization.patientbinding set schema public;

COMMIT;

Conclusion

• Use denormalization careful
• Database maintenance
• Try to decomposit your complex task

References

PG bloating - https://www.youtube.com/watch?v=-GNHIHEHDmQ&t=2361s&ab_channel=HighLoadChannel PG autovacuum -https://www.youtube.com/watch?v=TDWC66qzxCs&ab_channel=HighLoadChannel