Exercises – Partitioning Tables
Here is the exercise to get comfort with partitioning. We will be using range partitioning.
-
Use retail database. Make sure orders table already exists.
-
You can reset the database by running these commands.
-
Connect to retail database.
psql -U itversity_retail_user
-h localhost
-p 5432
-d itversity_retail_db
-W
-
Run these commands or scripts to reset the tables. It will take care of recreating orders table.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS departments;
i /data/retail_db/create_db_tables_pg.sql
i /data/retail_db/load_db_tables_pg.sql
Exercise 1¶
Create table orders_part with the same columns as orders.
-
Partition the table by month using range partitioning on order_date.
-
Add 14 partitions - 13 based up on the data and 1 default. Here is the naming convention.
-
Default - orders_part_default
-
Partition for 2014 January - orders_part_201401
-
Exercise 2¶
Let us load and validate data in the partitioned table.
-
Load the data from orders into orders_part.
-
Get count on orders_part as well as all the 14 partitions. You should get 0 for default partition and all the records should be distributed using the other 13 partitions.