Having talked about partitioning strategies and partition pruning this time we will have a look on how you can attach and detach partitions to and from an existing partitioned table. If you missed the last posts about partitioning in PostgreSQL here they are:
- PostgreSQL partitioning (1): Preparing the data set
- PostgreSQL partitioning (2): Range partitioning
- PostgreSQL partitioning (3): List partitioning
- PostgreSQL partitioning (4) : Hash partitioning
- PostgreSQL partitioning (5): Partition pruning
From time to time it might be required that you attach a partition to an existing partitioned table. A reason for that might be that you partitioned by year and suddenly you get data for the next year because your data is based on imported data from an external source. Of course you could just create a new partition for that and then load the data but there is a more elegant way of doing that.
Our range partitioned table currently has partitions for the years 2012 to 2020:
postgres=# d+ traffic_violations_p
Partitioned table "public.traffic_violations_p"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
seqid | text | | | | extended | |
date_of_stop | date | | | | plain | |
time_of_stop | time without time zone | | | | plain | |
agency | text | | | | extended | |
subagency | text | | | | extended | |
description | text | | | | extended | |
location | text | | | | extended | |
latitude | numeric | | | | main | |
longitude | numeric | | | | main | |
accident | text | | | | extended | |
belts | boolean | | | | plain | |
personal_injury | boolean | | | | plain | |
property_damage | boolean | | | | plain | |
fatal | boolean | | | | plain | |
commercial_license | boolean | | | | plain | |
hazmat | boolean | | | | plain | |
commercial_vehicle | boolean | | | | plain | |
alcohol | boolean | | | | plain | |
workzone | boolean | | | | plain | |
state | text | | | | extended | |
vehicletype | text | | | | extended | |
year | smallint | | | | plain | |
make | text | | | | extended | |
model | text | | | | extended | |
color | text | | | | extended | |
violation_type | text | | | | extended | |
charge | text | | | | extended | |
article | text | | | | extended | |
contributed_to_accident | boolean | | | | plain | |
race | text | | | | extended | |
gender | text | | | | extended | |
driver_city | text | | | | extended | |
driver_state | text | | | | extended | |
dl_state | text | | | | extended | |
arrest_type | text | | | | extended | |
geolocation | point | | | | plain | |
council_districts | smallint | | | | plain | |
councils | smallint | | | | plain | |
communities | smallint | | | | plain | |
zip_codes | smallint | | | | plain | |
municipalities | smallint | | | | plain | |
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
traffic_violations_p_default DEFAULT
Lets assume we suddenly get data for the year 2021 and we want that data to be part of the range partitioned table. If we keep the partitioned table as it is, new data would go to the default partition and this is not what we want. What we can do is to create a regular table with exactly the same structure as the partitioned table. With PostgreSQL’s create table command you have the “like_option” which is very handy for this use case:
postgres=# create table traffic_violations_p_2021 ( like traffic_violations_p including all );
CREATE TABLE
postgres=# d traffic_violations_p_2021
Table "public.traffic_violations_p_2021"
Column | Type | Collation | Nullable | Default
-------------------------+------------------------+-----------+----------+---------
seqid | text | | |
date_of_stop | date | | |
time_of_stop | time without time zone | | |
agency | text | | |
subagency | text | | |
description | text | | |
location | text | | |
latitude | numeric | | |
longitude | numeric | | |
accident | text | | |
belts | boolean | | |
personal_injury | boolean | | |
property_damage | boolean | | |
fatal | boolean | | |
commercial_license | boolean | | |
hazmat | boolean | | |
commercial_vehicle | boolean | | |
alcohol | boolean | | |
workzone | boolean | | |
state | text | | |
vehicletype | text | | |
year | smallint | | |
make | text | | |
model | text | | |
color | text | | |
violation_type | text | | |
charge | text | | |
article | text | | |
contributed_to_accident | boolean | | |
race | text | | |
gender | text | | |
driver_city | text | | |
driver_state | text | | |
dl_state | text | | |
arrest_type | text | | |
geolocation | point | | |
council_districts | smallint | | |
councils | smallint | | |
communities | smallint | | |
zip_codes | smallint | | |
municipalities | smallint | | |
For now this table has no relational at all to the partitioned table, it just has the very same structure, that’s it. Lets populate the new table with some data for 2021:
insert into traffic_violations_p_2021 (date_of_stop)
select * from generate_series ( date('01.01.2021')
, date('12.31.2021')
, interval '1 day');
Now that we have 365 rows in the new table we can attach it to the partitioned table (of course you could already attach it before loading the data as well):
alter table traffic_violations_p
attach partition traffic_violations_p_2021
for values from ('2021-01-01') to ('2022-01-01');
So you can prepare the whole new data without touching the partitioned table. Once you are happy with it a simple attach command is enough and the partitioned table contains the new partition holding the data:
postgres=# d+ traffic_violations_p
Partitioned table "public.traffic_violations_p"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
seqid | text | | | | extended | |
date_of_stop | date | | | | plain | |
time_of_stop | time without time zone | | | | plain | |
agency | text | | | | extended | |
subagency | text | | | | extended | |
description | text | | | | extended | |
location | text | | | | extended | |
latitude | numeric | | | | main | |
longitude | numeric | | | | main | |
accident | text | | | | extended | |
belts | boolean | | | | plain | |
personal_injury | boolean | | | | plain | |
property_damage | boolean | | | | plain | |
fatal | boolean | | | | plain | |
commercial_license | boolean | | | | plain | |
hazmat | boolean | | | | plain | |
commercial_vehicle | boolean | | | | plain | |
alcohol | boolean | | | | plain | |
workzone | boolean | | | | plain | |
state | text | | | | extended | |
vehicletype | text | | | | extended | |
year | smallint | | | | plain | |
make | text | | | | extended | |
model | text | | | | extended | |
color | text | | | | extended | |
violation_type | text | | | | extended | |
charge | text | | | | extended | |
article | text | | | | extended | |
contributed_to_accident | boolean | | | | plain | |
race | text | | | | extended | |
gender | text | | | | extended | |
driver_city | text | | | | extended | |
driver_state | text | | | | extended | |
dl_state | text | | | | extended | |
arrest_type | text | | | | extended | |
geolocation | point | | | | plain | |
council_districts | smallint | | | | plain | |
councils | smallint | | | | plain | |
communities | smallint | | | | plain | |
zip_codes | smallint | | | | plain | |
municipalities | smallint | | | | plain | |
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2012 FOR VALUES FROM ('2012-01-01') TO ('2013-01-01'),
traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
traffic_violations_p_default DEFAULT
Having the data for 2021 it might not be required anymore to have the data for 2012. Without partitioning you would need to delete from the table for getting rid of those rows. With partitioning you can detach the partition from the partitioned table:
postgres=# alter table traffic_violations_p detach partition traffic_violations_p_2012;
ALTER TABLE
postgres=# d+ traffic_violations_p
Partitioned table "public.traffic_violations_p"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
seqid | text | | | | extended | |
date_of_stop | date | | | | plain | |
time_of_stop | time without time zone | | | | plain | |
agency | text | | | | extended | |
subagency | text | | | | extended | |
description | text | | | | extended | |
location | text | | | | extended | |
latitude | numeric | | | | main | |
longitude | numeric | | | | main | |
accident | text | | | | extended | |
belts | boolean | | | | plain | |
personal_injury | boolean | | | | plain | |
property_damage | boolean | | | | plain | |
fatal | boolean | | | | plain | |
commercial_license | boolean | | | | plain | |
hazmat | boolean | | | | plain | |
commercial_vehicle | boolean | | | | plain | |
alcohol | boolean | | | | plain | |
workzone | boolean | | | | plain | |
state | text | | | | extended | |
vehicletype | text | | | | extended | |
year | smallint | | | | plain | |
make | text | | | | extended | |
model | text | | | | extended | |
color | text | | | | extended | |
violation_type | text | | | | extended | |
charge | text | | | | extended | |
article | text | | | | extended | |
contributed_to_accident | boolean | | | | plain | |
race | text | | | | extended | |
gender | text | | | | extended | |
driver_city | text | | | | extended | |
driver_state | text | | | | extended | |
dl_state | text | | | | extended | |
arrest_type | text | | | | extended | |
geolocation | point | | | | plain | |
council_districts | smallint | | | | plain | |
councils | smallint | | | | plain | |
communities | smallint | | | | plain | |
zip_codes | smallint | | | | plain | |
municipalities | smallint | | | | plain | |
Partition key: RANGE (date_of_stop)
Partitions: traffic_violations_p_2013 FOR VALUES FROM ('2013-01-01') TO ('2014-01-01'),
traffic_violations_p_2014 FOR VALUES FROM ('2014-01-01') TO ('2015-01-01'),
traffic_violations_p_2015 FOR VALUES FROM ('2015-01-01') TO ('2016-01-01'),
traffic_violations_p_2016 FOR VALUES FROM ('2016-01-01') TO ('2017-01-01'),
traffic_violations_p_2017 FOR VALUES FROM ('2017-01-01') TO ('2018-01-01'),
traffic_violations_p_2018 FOR VALUES FROM ('2018-01-01') TO ('2019-01-01'),
traffic_violations_p_2019 FOR VALUES FROM ('2019-01-01') TO ('2020-01-01'),
traffic_violations_p_2020 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
traffic_violations_p_2021 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'),
traffic_violations_p_default DEFAULT
Whatever you want to do with the data from now on: The old partition became a regular table and you can either drop, truncate or move it to low cost storage for archiving.
This was attaching and detaching of partitions. In the next post we’ll look at indexing and constraints when it comes to partitioned tables.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)