One cool example of when to do partitioning is, tenant based partitioning 🤯
Another example is to move out the old / less frequently accessed data to a different table (LOCAL PARTITIONING)
CREATE TABLE temperatures (
at date,
city text,
mintemp integer,
maxtemp integer
);
CREATE TABLE temperatures_2019 (LIKE temperatures);
INSERT INTO temperatures_2019 SELECT * FROM temperatures WHERE
extract(year from at) = 2019;
DELETE FROM temperatures WHERE extract(year from at) = 2019;
CREATE TABLE temperatures (
at date,
city text,
mintemp integer,
maxtemp integer
)
PARTITION BY RANGE (at);
CREATE TABLE temperatures_2017
PARTITION OF temperatures
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');
CREATE TABLE temperatures_2018
PARTITION OF temperatures
FOR VALUES FROM ('2018-01-01') TO ('2019-01-01');
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-# VALUES ('2018-08-03', 'London', 63, 90);
INSERT 0 1
temp=# INSERT INTO temperatures (at, city, mintemp, maxtemp)
temp-# VALUES ('2017-08-03', 'London', 59, 70);
INSERT 0 1
temp=# SELECT * FROM temperatures;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2017-08-03 | London | 59 | 70
2018-08-03 | London | 63 | 90
(2 rows)
temp=# SELECT * FROM temperatures_2017;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2017-08-03 | London | 59 | 70
(1 row)
temp=# SELECT * FROM temperatures_2018;
at | city | mintemp | maxtemp
------------+--------+---------+---------
2018-08-03 | London | 63 | 90
(1 row)
CREATE TABLE temperatures_2017
PARTITION OF temperatures (
mintemp NOT NULL,
maxtemp NOT NULL,
CHECK (mintemp <= maxtemp),
PRIMARY KEY (at, city)
)
FOR VALUES FROM ('2017-01-01') TO ('2018-01-01');