By digoal
In some cases, developers create all tables as partition tables due to excessive business estimation, even if partitions are not needed.
1) Partition tables may incur optimizer loss in high concurrency.
2) If there are many partitions, the memory usage of the session RelCache will increase. A persistent connection and high concurrency with an unused huge page may cause OOM.
If this is the case, how can we quickly switch the partition table back to a non-partition table?
Note: This article does not consider the association between sequence and table and foreign key constraints caused by serial used in fields. If a partition table contains serial fields and the sequence that belongs to a partition table, deleting the old table will delete the sequence. The default values of the new table are also cleared.
create database db1;
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
create schema s1;
set search_path=s1;
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
create schema s2;
set search_path=s2;
create table tbl (id int, info text) partition by hash (id);
create table tbl0 partition of tbl for values with (modulus 2, remainder 0);
create table tbl1 partition of tbl for values with (modulus 2, remainder 1);
insert into tbl select generate_series(1,10000), md5(random()::text);
create table ttbl (id int, info text) partition by hash (id);
create table ttbl0 partition of ttbl for values with (modulus 2, remainder 0);
create table ttbl1 partition of ttbl for values with (modulus 2, remainder 1);
insert into ttbl select generate_series(1,10000), md5(random()::text);
create table "TTb" (id int, info text) partition by hash (id);
create table "TTb0" partition of "TTb" for values with (modulus 2, remainder 0);
create table "TTb1" partition of "TTb" for values with (modulus 2, remainder 1);
insert into "TTb" select generate_series(1,10000), md5(random()::text);
db1=# \dPt *.*
List of partitioned tables
Schema | Name | Owner | Parent name
--------+------+----------+-------------
public | TTb | postgres |
public | tbl | postgres |
public | ttbl | postgres |
s1 | TTb | postgres |
s1 | tbl | postgres |
s1 | ttbl | postgres |
s2 | TTb | postgres |
s2 | tbl | postgres |
s2 | ttbl | postgres |
(9 rows)
Use the following method to switch tables:
begin;
create table new_tbl (like tbl including all);
insert into new_tbl select * from tbl;
alter table tbl rename to old_tbl;
alter table new_tbl rename to tbl;
drop table old_tbl; -- 如果有依赖, 如前描述 这个表有被依赖的对象时(例如sequence, foreign table), 会导致drop 失败
end;
Follow the below steps to switch all partition tables at once:
set lock_timeout ='5s';
do language plpgsql $$
declare
v_nsp name;
v_tbl name;
v_exchange1 name := 'new'||md5(random()::text);
v_exchange2 name := 'old'||md5(random()::text);
begin
for v_nsp,v_tbl in
SELECT n.nspname as "Schema",
c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid
WHERE c.relkind IN ('p','')
AND n.nspname !~ '^pg_toast'
ORDER BY "Schema", "Name"
LOOP
execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);
execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);
-- 如果有依赖, 以下将报错, 导致整个事务回滚。
execute format('drop table %I.%I', v_nsp, v_exchange2);
END loop;
end;
$$;
db1=# \dPt *.*
List of partitioned tables
Schema | Name | Owner | Parent name
--------+------+-------+-------------
(0 rows)
If the partition table contains a large amount of data, this operation may take a long time to complete.
It is important to resolve the dependency before performing the above operation.
For example, when solving the problem of sequence dependency, remove the owner for the sequence related to each partition table and change the table to a new one.
alter sequence 序列 OWNED BY none;
The above do script is shown as follows:
do language plpgsql $$
declare
v_tbl_oid oid;
v_nsp name;
v_tbl name;
v_exchange1 name := 'new'||md5(random()::text);
v_exchange2 name := 'old'||md5(random()::text);
v_seq_nsp name;
v_seq_name name;
begin
for v_tbl_oid,v_nsp,v_tbl in
SELECT c.oid, n.nspname as "Schema",
c.relname as "Name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_inherits inh ON c.oid = inh.inhrelid
WHERE c.relkind IN ('p','')
AND n.nspname !~ '^pg_toast'
ORDER BY "Schema", "Name"
LOOP
execute format('create table %I.%I (like %I.%I including all)', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('insert into %I.%I select * from %I.%I', v_nsp, v_exchange1, v_nsp, v_tbl);
execute format('alter table %I.%I rename to %I', v_nsp, v_tbl, v_exchange2);
execute format('alter table %I.%I rename to %I', v_nsp, v_exchange1, v_tbl);
-- 如果有依赖, 以下将报错, 导致整个事务回滚。
-- 处理serial类型依赖, 设置owned by为none
for v_seq_nsp, v_seq_name in
select n.nspname as "Schema", c.relname as "Name" from pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
where c.oid in
(select objid from pg_depend where refobjid=v_tbl_oid)
and relkind='S'
AND n.nspname !~ '^pg_toast'
loop
execute format('alter sequence %I.%I owned by none', v_seq_nsp, v_seq_name);
end loop;
execute format('drop table %I.%I', v_nsp, v_exchange2);
END loop;
end;
$$;
digoal - May 16, 2019
ApsaraDB - August 7, 2023
digoal - April 12, 2019
digoal - April 20, 2021
digoal - March 25, 2020
ApsaraDB - October 20, 2020
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn MoreAn online MPP warehousing service based on the Greenplum Database open source program
Learn MoreA fully managed NoSQL cloud database service that enables storage of massive amount of structured and semi-structured data
Learn MoreAn on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal