Repartitioning – Range

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

Previous Topic

Back to Lesson

Next Topic