Repartitioning - RangeΒΆ
Let us understand how we can repartition the existing partitioned table.
We will use users_range_part table. It is originally partitioned for each year.
Now we would like to partition for each month.
Here are the steps that are involved in repartitioning from year to month.
Detach all yearly partitions from users_range_part.
Add new partitions for each month.
Load data from detached partitions into the table with new partitions for each month.
Validate to ensure that all the data is copied.
Drop all the detached partitions.
%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
Note
Detach all yearly partitions
%%sql
ALTER TABLE users_range_part
DETACH PARTITION users_range_part_2016
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
ALTER TABLE users_range_part
DETACH PARTITION users_range_part_2017
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
ALTER TABLE users_range_part
DETACH PARTITION users_range_part_2018
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
ALTER TABLE users_range_part
DETACH PARTITION users_range_part_2019
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
ALTER TABLE users_range_part
DETACH PARTITION users_range_part_2020
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
Note
Add new partitions for every month between 2016 January and 2020 December.
!pip install psycopg2
Defaulting to user installation because normal site-packages is not writeable
Requirement already satisfied: psycopg2 in /opt/anaconda3/envs/beakerx/lib/python3.6/site-packages (2.8.6)
import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd
months = pd.date_range(start='1/1/2016', end='3/31/2016', freq='1M')
for month in months:
begin_date = month - MonthBegin(1)
end_date = month + MonthEnd(0)
print(str(month)[:7].replace('-', ''), end=':')
print(str(begin_date).split(' ')[0], end=':')
print(str(end_date).split(' ')[0])
201601:2016-01-01:2016-01-31
201602:2016-02-01:2016-02-29
201603:2016-03-01:2016-03-31
import psycopg2
import pandas as pd
from pandas.tseries.offsets import MonthBegin, MonthEnd
months = pd.date_range(start='1/1/2016', end='12/31/2020', freq='1M')
connection = psycopg2.connect(
host='localhost',
port='5432',
database='itversity_sms_db',
user='itversity_sms_user',
password='sms_password'
)
cursor = connection.cursor()
table_name = 'users_range_part'
query = '''
CREATE TABLE {table_name}_{yyyymm}
PARTITION OF {table_name}
FOR VALUES FROM ('{begin_date}') TO ('{end_date}')
'''
for month in months:
begin_date = month - MonthBegin(1)
end_date = month + MonthEnd(0)
print(f'Adding partition for {begin_date} and {end_date}')
cursor.execute(
query.format(
table_name=table_name,
yyyymm=str(month)[:7].replace('-', ''),
begin_date=str(begin_date).split(' ')[0],
end_date=str(end_date).split(' ')[0]
), ()
)
connection.commit()
cursor.close()
connection.close()
Adding partition for 2016-01-01 00:00:00 and 2016-01-31 00:00:00
Adding partition for 2016-02-01 00:00:00 and 2016-02-29 00:00:00
Adding partition for 2016-03-01 00:00:00 and 2016-03-31 00:00:00
Adding partition for 2016-04-01 00:00:00 and 2016-04-30 00:00:00
Adding partition for 2016-05-01 00:00:00 and 2016-05-31 00:00:00
Adding partition for 2016-06-01 00:00:00 and 2016-06-30 00:00:00
Adding partition for 2016-07-01 00:00:00 and 2016-07-31 00:00:00
Adding partition for 2016-08-01 00:00:00 and 2016-08-31 00:00:00
Adding partition for 2016-09-01 00:00:00 and 2016-09-30 00:00:00
Adding partition for 2016-10-01 00:00:00 and 2016-10-31 00:00:00
Adding partition for 2016-11-01 00:00:00 and 2016-11-30 00:00:00
Adding partition for 2016-12-01 00:00:00 and 2016-12-31 00:00:00
Adding partition for 2017-01-01 00:00:00 and 2017-01-31 00:00:00
Adding partition for 2017-02-01 00:00:00 and 2017-02-28 00:00:00
Adding partition for 2017-03-01 00:00:00 and 2017-03-31 00:00:00
Adding partition for 2017-04-01 00:00:00 and 2017-04-30 00:00:00
Adding partition for 2017-05-01 00:00:00 and 2017-05-31 00:00:00
Adding partition for 2017-06-01 00:00:00 and 2017-06-30 00:00:00
Adding partition for 2017-07-01 00:00:00 and 2017-07-31 00:00:00
Adding partition for 2017-08-01 00:00:00 and 2017-08-31 00:00:00
Adding partition for 2017-09-01 00:00:00 and 2017-09-30 00:00:00
Adding partition for 2017-10-01 00:00:00 and 2017-10-31 00:00:00
Adding partition for 2017-11-01 00:00:00 and 2017-11-30 00:00:00
Adding partition for 2017-12-01 00:00:00 and 2017-12-31 00:00:00
Adding partition for 2018-01-01 00:00:00 and 2018-01-31 00:00:00
Adding partition for 2018-02-01 00:00:00 and 2018-02-28 00:00:00
Adding partition for 2018-03-01 00:00:00 and 2018-03-31 00:00:00
Adding partition for 2018-04-01 00:00:00 and 2018-04-30 00:00:00
Adding partition for 2018-05-01 00:00:00 and 2018-05-31 00:00:00
Adding partition for 2018-06-01 00:00:00 and 2018-06-30 00:00:00
Adding partition for 2018-07-01 00:00:00 and 2018-07-31 00:00:00
Adding partition for 2018-08-01 00:00:00 and 2018-08-31 00:00:00
Adding partition for 2018-09-01 00:00:00 and 2018-09-30 00:00:00
Adding partition for 2018-10-01 00:00:00 and 2018-10-31 00:00:00
Adding partition for 2018-11-01 00:00:00 and 2018-11-30 00:00:00
Adding partition for 2018-12-01 00:00:00 and 2018-12-31 00:00:00
Adding partition for 2019-01-01 00:00:00 and 2019-01-31 00:00:00
Adding partition for 2019-02-01 00:00:00 and 2019-02-28 00:00:00
Adding partition for 2019-03-01 00:00:00 and 2019-03-31 00:00:00
Adding partition for 2019-04-01 00:00:00 and 2019-04-30 00:00:00
Adding partition for 2019-05-01 00:00:00 and 2019-05-31 00:00:00
Adding partition for 2019-06-01 00:00:00 and 2019-06-30 00:00:00
Adding partition for 2019-07-01 00:00:00 and 2019-07-31 00:00:00
Adding partition for 2019-08-01 00:00:00 and 2019-08-31 00:00:00
Adding partition for 2019-09-01 00:00:00 and 2019-09-30 00:00:00
Adding partition for 2019-10-01 00:00:00 and 2019-10-31 00:00:00
Adding partition for 2019-11-01 00:00:00 and 2019-11-30 00:00:00
Adding partition for 2019-12-01 00:00:00 and 2019-12-31 00:00:00
Adding partition for 2020-01-01 00:00:00 and 2020-01-31 00:00:00
Adding partition for 2020-02-01 00:00:00 and 2020-02-29 00:00:00
Adding partition for 2020-03-01 00:00:00 and 2020-03-31 00:00:00
Adding partition for 2020-04-01 00:00:00 and 2020-04-30 00:00:00
Adding partition for 2020-05-01 00:00:00 and 2020-05-31 00:00:00
Adding partition for 2020-06-01 00:00:00 and 2020-06-30 00:00:00
Adding partition for 2020-07-01 00:00:00 and 2020-07-31 00:00:00
Adding partition for 2020-08-01 00:00:00 and 2020-08-31 00:00:00
Adding partition for 2020-09-01 00:00:00 and 2020-09-30 00:00:00
Adding partition for 2020-10-01 00:00:00 and 2020-10-31 00:00:00
Adding partition for 2020-11-01 00:00:00 and 2020-11-30 00:00:00
Adding partition for 2020-12-01 00:00:00 and 2020-12-31 00:00:00
Note
Load data from detached yearly partitions into monthly partitioned table.
%%sql
INSERT INTO users_range_part
SELECT * FROM users_range_part_2016
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
[]
%%sql
INSERT INTO users_range_part
SELECT * FROM users_range_part_2017
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
[]
%%sql
INSERT INTO users_range_part
SELECT * FROM users_range_part_2018
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
[]
%%sql
INSERT INTO users_range_part
SELECT * FROM users_range_part_2019
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
1 rows affected.
[]
%%sql
INSERT INTO users_range_part
SELECT * FROM users_range_part_2020
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
0 rows affected.
[]
%%sql
SELECT * FROM users_range_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 |
|---|---|---|---|---|---|---|---|---|---|
| 3 | Mickey | Mouse | mickey@mouse.com | False | None | U | False | 2017-06-22 | 2020-11-24 12:12:27.094936 |
| 1 | Scott | Tiger | scott@tiger.com | False | None | U | False | 2018-10-01 | 2020-11-24 12:12:27.094936 |
| 2 | Donald | Duck | donald@duck.com | False | None | U | False | 2019-02-10 | 2020-11-24 12:12:27.094936 |
%%sql
SELECT * FROM users_range_part_201706
* 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:12:27.094936 |
%%sql
SELECT * FROM users_range_part_201810
* 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:12:27.094936 |
%%sql
SELECT * FROM users_range_part_201902
* 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:12:27.094936 |
Note
As we are able to see the data in the monthly partitioned table, we can drop the tables which are created earlier using yearly partitioning strategy.
%%sql
DROP TABLE users_range_part_2016
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
DROP TABLE users_range_part_2017
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
DROP TABLE users_range_part_2018
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
DROP TABLE users_range_part_2019
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
DROP TABLE users_range_part_2020
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
Done.
[]
%%sql
SELECT table_catalog,
table_schema,
table_name FROM information_schema.tables
WHERE table_name ~ 'users_range_part_'
ORDER BY table_name
* postgresql://itversity_sms_user:***@localhost:5432/itversity_sms_db
61 rows affected.
| table_catalog | table_schema | table_name |
|---|---|---|
| itversity_sms_db | public | users_range_part_201601 |
| itversity_sms_db | public | users_range_part_201602 |
| itversity_sms_db | public | users_range_part_201603 |
| itversity_sms_db | public | users_range_part_201604 |
| itversity_sms_db | public | users_range_part_201605 |
| itversity_sms_db | public | users_range_part_201606 |
| itversity_sms_db | public | users_range_part_201607 |
| itversity_sms_db | public | users_range_part_201608 |
| itversity_sms_db | public | users_range_part_201609 |
| itversity_sms_db | public | users_range_part_201610 |
| itversity_sms_db | public | users_range_part_201611 |
| itversity_sms_db | public | users_range_part_201612 |
| itversity_sms_db | public | users_range_part_201701 |
| itversity_sms_db | public | users_range_part_201702 |
| itversity_sms_db | public | users_range_part_201703 |
| itversity_sms_db | public | users_range_part_201704 |
| itversity_sms_db | public | users_range_part_201705 |
| itversity_sms_db | public | users_range_part_201706 |
| itversity_sms_db | public | users_range_part_201707 |
| itversity_sms_db | public | users_range_part_201708 |
| itversity_sms_db | public | users_range_part_201709 |
| itversity_sms_db | public | users_range_part_201710 |
| itversity_sms_db | public | users_range_part_201711 |
| itversity_sms_db | public | users_range_part_201712 |
| itversity_sms_db | public | users_range_part_201801 |
| itversity_sms_db | public | users_range_part_201802 |
| itversity_sms_db | public | users_range_part_201803 |
| itversity_sms_db | public | users_range_part_201804 |
| itversity_sms_db | public | users_range_part_201805 |
| itversity_sms_db | public | users_range_part_201806 |
| itversity_sms_db | public | users_range_part_201807 |
| itversity_sms_db | public | users_range_part_201808 |
| itversity_sms_db | public | users_range_part_201809 |
| itversity_sms_db | public | users_range_part_201810 |
| itversity_sms_db | public | users_range_part_201811 |
| itversity_sms_db | public | users_range_part_201812 |
| itversity_sms_db | public | users_range_part_201901 |
| itversity_sms_db | public | users_range_part_201902 |
| itversity_sms_db | public | users_range_part_201903 |
| itversity_sms_db | public | users_range_part_201904 |
| itversity_sms_db | public | users_range_part_201905 |
| itversity_sms_db | public | users_range_part_201906 |
| itversity_sms_db | public | users_range_part_201907 |
| itversity_sms_db | public | users_range_part_201908 |
| itversity_sms_db | public | users_range_part_201909 |
| itversity_sms_db | public | users_range_part_201910 |
| itversity_sms_db | public | users_range_part_201911 |
| itversity_sms_db | public | users_range_part_201912 |
| itversity_sms_db | public | users_range_part_202001 |
| itversity_sms_db | public | users_range_part_202002 |
| itversity_sms_db | public | users_range_part_202003 |
| itversity_sms_db | public | users_range_part_202004 |
| itversity_sms_db | public | users_range_part_202005 |
| itversity_sms_db | public | users_range_part_202006 |
| itversity_sms_db | public | users_range_part_202007 |
| itversity_sms_db | public | users_range_part_202008 |
| itversity_sms_db | public | users_range_part_202009 |
| itversity_sms_db | public | users_range_part_202010 |
| itversity_sms_db | public | users_range_part_202011 |
| itversity_sms_db | public | users_range_part_202012 |
| itversity_sms_db | public | users_range_part_default |