Managing Partitions - HashΒΆ

Let us understand how to manage partitions using table users_hash_part which is partitioned using hash.

  • We would like to divide our data into 8 hash buckets.

  • While adding partitions for hash partitioned table, we need to specify modulus and remainder.

  • For each and every record inserted, following will happen for the column specified as partitioned key.

    • A hash will be computed. Hash is nothing but an integer.

    • The integer generated will be divided by the value specified in modulus.

    • Based up on the remainder, the record will be inserted into corresponding partition.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db

Error

We cannot have a default partition for hash partitioned table.

%%sql

CREATE TABLE users_hash_part_default
PARTITION OF users_hash_part DEFAULT

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
(psycopg2.errors.InvalidTableDefinition) a hash-partitioned table may not have a default partition

[SQL: CREATE TABLE users_hash_part_default PARTITION OF users_hash_part DEFAULT]
(Background on this error at: http://sqlalche.me/e/13/f405)

Note

Let us add partitions using modulus as 8. For each remainder between 0 to 7. we need to add a partition.

%%sql

CREATE TABLE users_hash_part_0_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 0)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_1_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 1)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_2_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 2)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_3_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 3)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_4_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 4)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_5_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 5)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_6_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 6)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

CREATE TABLE users_hash_part_7_of_8
PARTITION OF users_hash_part
FOR VALUES WITH (modulus 8, remainder 7)

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.

[]

%%sql

INSERT INTO users_hash_part
    (user_first_name, user_last_name, user_email_id, created_dt)
VALUES 
    ('Scott', 'Tiger', 'scott@tiger.com', '2018-10-01'),
    ('Donald', 'Duck', 'donald@duck.com', '2019-02-10'),
    ('Mickey', 'Mouse', 'mickey@mouse.com', '2017-06-22')

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.

[]

Note

user_id is populated by sequence. The hash of every sequence generated integer will be divided by modulus (which is 8) and based up on the remainder data will be inserted into corresponding partition.

%%sql

SELECT * FROM users_hash_part

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
1 Scott Tiger scott@tiger.com False None U False 2018-10-01 2020-11-24 12:13:06.353736
3 Mickey Mouse mickey@mouse.com False None U False 2017-06-22 2020-11-24 12:13:06.353736
2 Donald Duck donald@duck.com False None U False 2019-02-10 2020-11-24 12:13:06.353736 
%%sql

SELECT * FROM users_hash_part_0_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
1 Scott Tiger scott@tiger.com False None U False 2018-10-01 2020-11-24 12:13:06.353736 
%%sql

SELECT * FROM users_hash_part_1_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
3 Mickey Mouse mickey@mouse.com False None U False 2017-06-22 2020-11-24 12:13:06.353736 
%%sql

SELECT * FROM users_hash_part_2_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
2 Donald Duck donald@duck.com False None U False 2019-02-10 2020-11-24 12:13:06.353736 
%%sql

SELECT * FROM users_hash_part_3_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts 
%%sql

SELECT * FROM users_hash_part_4_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts 
%%sql

SELECT * FROM users_hash_part_5_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts 
%%sql

SELECT * FROM users_hash_part_6_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts 
%%sql

SELECT * FROM users_hash_part_7_of_8

 * postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
user_id user_first_name user_last_name user_email_id user_email_validated user_password user_role is_active created_dt last_updated_ts
Hash Partitioning Usage Scenarios