Database Essentials - Partitioning Tables and Indexes - Managing Partitions - List

Databases Postgres
1

Let us understand how to manage partitions for a partitioned table using users_part.

  • All users data with user_role as ‘U’ should go to one partition by the name users_part_u.
  • All users data with user_role as ‘A’ should go to one partition by the name users_part_a.
  • We can add a partition to an existing partitioned table using CREATE TABLE partition_name PARTITION OF table_name.
  • We can have a partition for default values so that all the data that does not satisfy the partition condition can be added to it.
  • We can have a partition for each value or for a set of values.
    • We can have one partition for U as well as A and the default partition for all other values.
    • We can have individual partitions for U, A respectively and default partition for all other values.
    • We can use FOR VALUES IN (val1, val2) as part of CREATE TABLE partition_name PARTITION OF table_name to specify values for the respective table created for the partition.
  • Once partitions are added, we can insert data into the partitioned table.
  • We can detach using ALTER TABLE and drop the partition or drop the partition directly. To drop the partition, we need to use the DROP TABLE command.
Here is how we can create a partition for default values for a list partitioned table **users_part**.

%load_ext sql
%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@pg.itversity.com:5432/itversity_sms_db
%%sql

CREATE TABLE users_part_default
PARTITION OF users_part DEFAULT

All the 3 records will go to the default partition as we have not defined any partition for user_role 'U'.

%%sql

INSERT INTO users_part (user_first_name, user_last_name, user_email_id, user_role)
VALUES
(‘Scott’, ‘Tiger’, ‘scott@tiger.com’, ‘U’),
(‘Donald’, ‘Duck’, ‘donald@duck.com’, ‘U’),
(‘Mickey’, ‘Mouse’, ‘mickey@mouse.com’, ‘U’)

%%sql

SELECT * FROM users_part_default

%%sql

CREATE TABLE users_part_a
PARTITION OF users_part
FOR VALUES IN (‘A’)

%%sql

UPDATE users_part
SET
user_role = ‘A’
WHERE user_email_id = ‘scott@tiger.com’

%%sql

SELECT * FROM users_part

%%sql

SELECT * FROM users_part_a

%%sql

SELECT * FROM users_part_default

This will fail as there are records with user_role 'U' in the default partition.

%%sql

CREATE TABLE users_part_u
PARTITION OF users_part
FOR VALUES IN (‘U’)

We can detach the partition, add a partition for 'U' and load the data from the detached partition into the new partition created.

%%sql

ALTER TABLE users_part
DETACH PARTITION users_part_default

%%sql

CREATE TABLE users_part_u
PARTITION OF users_part
FOR VALUES IN (‘U’)

%%sql

INSERT INTO users_part
SELECT * FROM users_part_default

%%sql

SELECT * FROM users_part_a

%%sql

SELECT * FROM users_part_u

We can drop and create a partition for default or truncate and attach the existing default partition.

%%sql

DROP TABLE users_part_default

%%sql

CREATE TABLE users_part_default
PARTITION OF users_part DEFAULT

Watch the video tutorial here