Table partitioning

Database Design

Lis Sulmont

Curriculum Manager

Why partition?

Tables grow (100s Gb / Tb)

Problem: queries/updates become slower

Because: e.g., indices don't fit memory

Solution: split table into smaller parts (= partitioning)

Image of file archives by date

Data modeling refresher

1. Conceptual data model

2. Logical data model

For partitioning, logical data model is the same

3. Physical data model

Partitioning is part of physical data model

Vertical partitioning

Split table even when fully normalized

Vertical partitioning: an example

Vertical partitioning example

E.g., store long_description on slower medium

Horizontal partitioning

Horizontal partitioning: an example

Horizontal partitioning: example table

Horizontal partitioning: an example

Horizontal partitioning: example

CREATE TABLE sales (
    ...
    timestamp DATE NOT NULL
)
PARTITION BY RANGE (timestamp);


CREATE TABLE sales_2019_q1 PARTITION OF sales
    FOR VALUES FROM ('2019-01-01') TO ('2019-03-31');
...
CREATE TABLE sales_2019_q4 PARTITION OF sales
    FOR VALUES FROM ('2019-10-01') TO ('2020-01-31');

CREATE INDEX ON sales ('timestamp');

Pros/cons of horizontal partitioning

Pros

Indices of heavily-used partitions fit in memory
Move to specific medium: slower vs. faster
Used for both OLAP and OLTP

Cons

Partitioning existing table can be a hassle
Some constraints can not be set

Relation to sharding

Sharding

Let's practice!

Database Design