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
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
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.