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.