Sunday, April 28, 2024
No menu items!
HomeCloud ComputingProcess to drop partitions concurrently within PostgreSQL (Cloud SQL or AlloyDB)
Cloud Computing

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

By mullaned2002
0
542

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:

code_block[StructValue([(u’code’, u”CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);rnCREATE TABLE sales_2021_10 PARTITION OF sales FOR VALUES FROM (‘2021-10-01’) TO (‘2021-11-01’);rnCREATE TABLE sales_2021_11 PARTITION OF sales FOR VALUES FROM (‘2021-11-01’) TO (‘2021-12-01’);rnCREATE TABLE sales_2021_12 PARTITION OF sales FOR VALUES FROM (‘2021-12-01’) TO (‘2022-01-01’);rnCREATE TABLE sales_2022_01 PARTITION OF sales FOR VALUES FROM (‘2022-01-01’) TO (‘2022-02-01’);rnCREATE TABLE sales_2022_02 PARTITION OF sales FOR VALUES FROM (‘2022-02-01’) TO (‘2022-03-01’);rnCREATE TABLE sales_2022_03 PARTITION OF sales FOR VALUES FROM (‘2022-03-01’) TO (‘2022-04-01’);rnCREATE TABLE sales_2022_04 PARTITION OF sales FOR VALUES FROM (‘2022-04-01’) TO (‘2022-05-01’);rnCREATE TABLE sales_2022_05 PARTITION OF sales FOR VALUES FROM (‘2022-05-01’) TO (‘2022-06-01’);rnCREATE TABLE sales_2022_06 PARTITION OF sales FOR VALUES FROM (‘2022-06-01’) TO (‘2022-07-01’);rnCREATE TABLE sales_2022_07 PARTITION OF sales FOR VALUES FROM (‘2022-07-01’) TO (‘2022-08-01’);rnCREATE TABLE sales_2022_08 PARTITION OF sales FOR VALUES FROM (‘2022-08-01’) TO (‘2022-09-01’);rnCREATE TABLE sales_2022_09 PARTITION OF sales FOR VALUES FROM (‘2022-09-01’) TO (‘2022-10-01’);rnCREATE TABLE sales_2022_10 PARTITION OF sales FOR VALUES FROM (‘2022-10-01’) TO (‘2022-11-01’);rnCREATE TABLE sales_2022_11 PARTITION OF sales FOR VALUES FROM (‘2022-11-01’) TO (‘2022-12-01’);rnCREATE TABLE sales_2022_12 PARTITION OF sales FOR VALUES FROM (‘2022-12-01’) TO (‘2023-01-01’);rnCREATE TABLE sales_2023_01 PARTITION OF sales FOR VALUES FROM (‘2023-01-01’) TO (‘2023-02-01’);rnCREATE TABLE sales_2023_02 PARTITION OF sales FOR VALUES FROM (‘2023-02-01’) TO (‘2023-03-01′);”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e6c597a3910>)])]

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:

code_block[StructValue([(u’code’, u”–The table where we will store the generated detach and drop commandsrncreate table commands (com text);rn–Function to identify old partitions and generate the detach and drop commands rncreate or replace function drop_old_partitions()rn returns text as $$rnDeclarern–Current year, extracted from current_date as text rncur_year int;rn–Current month, extracted from current_date as text rncur_month int;t rnpar_out text default ”;rnold_par record;rn–Cursor to fetch the list of all partitions in the form of table name, partition name, partition month and partition yearrn–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 accordinglyrncur_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;rnbeginrnTruncate table commands;rnselect date_part(‘year’,current_date) into cur_year;rnselect date_part(‘month’,current_date) into cur_month;rn–open the cursorrnopen cur_old_pars;rnlooprn– fetch row into the recordrnfetch cur_old_pars into old_par;rn– exit when no more rows to fetchrnexit when not found;rn– Identify old partitions. If your retention policy is different then modify the if condition accordinglyrnif (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 rnpar_out := ‘alter table ‘ || old_par.tab_name || ‘ detach partition ‘ || old_par.par_name || ‘ concurrently;’;rnInsert into commands values (par_out);rnpar_out=’drop table ‘ || old_par.par_name || ‘;’;rnInsert into commands values (par_out);rnend if;rnend loop;rn–close the cursorrnclose cur_old_pars;rnreturn ‘function ended’;rnend; $$rnlanguage plpgsql;”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e6c5973cdd0>)])]

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

code_block[StructValue([(u’code’, u’export PGPASSWORD=<Your Password>’), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e6c5973cf90>)])]

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.

code_block[StructValue([(u’code’, u”psql -h <Database IP> -U <User> -d <Database Name> -c ‘Select drop_old_partitions()’rnpsql -h <Database IP> -U <User> -d <Database Name> -c ‘\copy commands TO ‘/tmp/drop.sql”rnpsql -h <Database IP> -U <User> -d <Database Name> -f ‘/tmp/drop.sql’rnrm /tmp/drop.sql”), (u’language’, u”), (u’caption’, <wagtail.wagtailcore.rich_text.RichText object at 0x3e6c5973ce90>)])]

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.

Cloud BlogRead More

Previous articleA roadmap to zero-trust maturity: 6 key insights from Forrester
Next articleJump start your future career with Google Cloud certifications
RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Load more

Recent Comments

EDITOR PICKS

POPULAR POSTS

POPULAR CATEGORY