PostgreSQL Quickly Converts All Native Partition Tables to Ordinary Tables

By digoal

Background

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.

Example

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:

Prevent lock wait and wait for the direct rollback.
Disconnect the service and then perform the operation.

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.

What If Dependency Problems Exist

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;  
$$;

Community

PostgreSQL Quickly Converts All Native Partition Tables to Ordinary Tables

Background

Example

What If Dependency Problems Exist

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

PolarDB for PostgreSQL

Database Overview

AnalyticDB for PostgreSQL

Tablestore