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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

Error

We cannot have a default partition for hash partitioned table.

%%sql

CREATE TABLE users_hash_part_default
PARTITION OF users_hash_part DEFAULT

Copy to clipboard

 * 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)

Copy to clipboard

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)

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%sql

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

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

%%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')

Copy to clipboard

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

Copy to clipboard

[]

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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