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.
The sql extension is already loaded. To reload it, use:
%reload_ext sql
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
env: DATABASE_URL=postgresql://itversity_sms_user:sms_password@localhost:5432/itversity_sms_db
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
Error
We cannot have a default partition for hash partitioned table.
* 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](https://postgresql.itversity.com/_static/copy-button.svg)
Note
Let us add partitions using modulus as 8. For each remainder between 0 to 7. we need to add a partition.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
[]
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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.
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
3 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
![Copy to clipboard](https://postgresql.itversity.com/_static/copy-button.svg)
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 |