Databases

Process to drop partitions concurrently within PostgreSQL (Cloud SQL or AlloyDB)

March 28, 2023

Ahmad Adel

Solution Engineer, Google Cloud

Kiran Shenoy

Sr. Product Manager, Google Cloud Databases

Disclaimer:

Please note that this script is not by any means an official script released by Google Cloud, it can only be used for education and guidance purposes. Anyone who would like to use this automation method in their production environment must first customize the script and own it from a development and support perspective.

Background:

Many PostgreSQL database administrators prefer to dynamically maintain time series partitions by adding new partitions and / or dropping partitions with data beyond the retention period. The retention period is usually defined by an organization-level policy that mandates the length of time where the data must be stored. After the data reaches the end of its retention period, it usually makes sense to either move it to a cheaper storage or drop it completely, which helps reduce storage and management cost. In some cases, it also enhances the performance of the database.

To implement this, It’s highly recommended - especially in operational database environments - to detach the partition concurrently to minimize the transaction locking issue and impact on the transactions. One problem is that you can’t use the “concurrently” clause inside a transaction block, including procedures and functions. When you try you will get:

ERROR: ALTER TABLE ... DETACH CONCURRENTLY cannot be executed from a function

PostgreSQL extension PG_PARTMAN does a good job in automating adding and dropping partitions; however the problem is that it detaches the partitions without the “concurrently” clause. Another workaround is to use PG_BACKGROUND to run the ‘detach .. concurrently’ command in a background worker and bypass the restriction of running the command inside a transaction block. However, in the Cloud SQL and AlloyDB, PG_BACKGROUND is not a supported extension at the moment.

Solution:

1. To demonstrate the solution we will create a partitioned table as follows:

CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2021_10 PARTITION OF sales FOR VALUES FROM ('2021-10-01') TO ('2021-11-01');
CREATE TABLE sales_2021_11 PARTITION OF sales FOR VALUES FROM ('2021-11-01') TO ('2021-12-01');
CREATE TABLE sales_2021_12 PARTITION OF sales FOR VALUES FROM ('2021-12-01') TO ('2022-01-01');
CREATE TABLE sales_2022_01 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
CREATE TABLE sales_2022_02 PARTITION OF sales FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
CREATE TABLE sales_2022_03 PARTITION OF sales FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
CREATE TABLE sales_2022_04 PARTITION OF sales FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE TABLE sales_2022_05 PARTITION OF sales FOR VALUES FROM ('2022-05-01') TO ('2022-06-01');
CREATE TABLE sales_2022_06 PARTITION OF sales FOR VALUES FROM ('2022-06-01') TO ('2022-07-01');
CREATE TABLE sales_2022_07 PARTITION OF sales FOR VALUES FROM ('2022-07-01') TO ('2022-08-01');
CREATE TABLE sales_2022_08 PARTITION OF sales FOR VALUES FROM ('2022-08-01') TO ('2022-09-01');
CREATE TABLE sales_2022_09 PARTITION OF sales FOR VALUES FROM ('2022-09-01') TO ('2022-10-01');
CREATE TABLE sales_2022_10 PARTITION OF sales FOR VALUES FROM ('2022-10-01') TO ('2022-11-01');
CREATE TABLE sales_2022_11 PARTITION OF sales FOR VALUES FROM ('2022-11-01') TO ('2022-12-01');
CREATE TABLE sales_2022_12 PARTITION OF sales FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');
CREATE TABLE sales_2023_01 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE sales_2023_02 PARTITION OF sales FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

Now let’s assume that our retention policy is one year and we need to drop all partitions with older data.

2. Create the following table and function as follows:

--The table where we will store the generated detach and drop commands
create table commands (com text);
--Function to identify old partitions and generate the detach and drop commands 
create or replace function drop_old_partitions()
   returns text as $$
Declare
--Current year, extracted from current_date as text 
cur_year int;
--Current month, extracted from current_date as text 
cur_month int;	 
par_out text default '';
old_par   record;
--Cursor to fetch the list of all partitions in the form of table name, partition name, partition month and partition year
--The cursor uses substring to extract the partition month as the last 2 characters and the partition year as 4 --characters starting from the last 6th character. If you use a different naming convention for your partitions then --modify the substring parameters accordingly
cur_old_pars cursor for select inhparent::regclass::text as tab_name,inhrelid::regclass::text as par_name,substring(inhrelid::regclass::text,length(inhrelid::regclass::text)-1,2) as par_month,substring(inhrelid::regclass::text,length(inhrelid::regclass::text)-6,4) as par_year from pg_inherits;
begin
Truncate table commands;
select date_part('year',current_date) into cur_year;
select date_part('month',current_date) into cur_month;
--open the cursor
open cur_old_pars;
loop
-- fetch row into the record
fetch cur_old_pars into old_par;
-- exit when no more rows to fetch
exit when not found;
-- Identify old partitions. If your retention policy is different then modify the if condition accordingly
if (old_par.par_year::integer+1 < cur_year) or (old_par.par_year::integer < cur_year and old_par.par_month::integer <= cur_month)  then 
par_out := 'alter table ' || old_par.tab_name || ' detach partition ' || old_par.par_name || ' concurrently;';
Insert into commands values (par_out);
par_out='drop table ' || old_par.par_name || ';';
Insert into commands values (par_out);
end if;
end loop;
--close the cursor
close cur_old_pars;
return 'function ended';
end; $$
language plpgsql;

3. In order to be able to call psql commands inside a shell script, I will set the password in an environment variable. This is for the case of simplicity but of course not secure, for more secure approaches you have the following options:

http://www.postgresql.org/docs/current/static/libpq-pgpass.html
http://www.postgresql.org/docs/current/static/auth-methods.html#AUTH-TRUST
http://www.postgresql.org/docs/current/static/libpq-connect.html#AEN42532

4. Create a shell script file with the following commands on the client machine and make it executable. The first command will populate the table with the commands, the second will generate a SQL file and the third will run these commands.

5. Optionally schedule this script to run periodically using cron or any other scheduling tool.

Summary:

This blog showed how to control data growth within the PostgreSQL Database within Cloud SQL or AlloyDB in an automated manner using the script provided and scheduled to run at periodic intervals.
Try creating your own PostgreSQL instance with Cloud SQL.
For more such database related information, please have a look at our resources under Cloud Architecture Center.

Posted in