Managing Partitions – Hash

Private: Mastering SQL using Postgresql Partitioning Tables and Indexes 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

Previous Topic

Back to Lesson

Next Topic