Part1: Steps to move data from a non partitioned table to partitioned table in postgresql

It is a multi part blog series of partitioning an existing table.

This is part 1 below is the scenario...

Scenario: A simple table without any referential constraint dependency or indexes. Let us move the data from this simple independent nonpartitioned table to other simple partitioned table.

Table Setup:

Command: create table test_nonpart_nofk_noidx as with t as ( select generate_series(0,100) lvl ) select t.lvl ,(mod(t.lvl,100)+date '1/1/2022') as dob ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl ,substr(md5(random()::text),15) as fn ,substr(md5(random()::text),15) as ln ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE from t;   Command output: postgres=# create table test_nonpart_nofk_noidx as postgres-# with t as postgres-# ( postgres(# select generate_series(0,100) lvl postgres(# ) postgres-# select t.lvl postgres-# ,(mod(t.lvl,100)+date '1/1/2022') as dob postgres-# ,lpad('A',(mod(t.lvl,20)+(20-mod(t.lvl,20))),chr(65+mod((t.lvl/26),26))) as hosptl postgres-# ,substr(md5(random()::text),15) as fn postgres-# ,substr(md5(random()::text),15) as ln postgres-# ,'UKCO'||chr(65+mod(t.lvl,26))||chr(65+mod((t.lvl/26),26)) as POSCODE postgres-# from t; SELECT 101 Time: 28.733 ms postgres=#

 B) Identifying the partition key & partitioning scheme:

1. We have to be sure the partition key chosen has business benefits or interests (like data archiving, data selection or partition pruning)

2. Technically a feasible column for the partition scheme of our choice

3. A right partitioning scheme

Look at the data below..

postgres=# select count(distinct(dob)) from test_nonpart_nofk_noidx;  count -------    100 (1 row)   Time: 3.090 ms postgres=# select count(distinct(hosptl)) from test_nonpart_nofk_noidx;  count -------      4 (1 row)   Time: 0.338 ms postgres=# select count(distinct(POSCODE)) from test_nonpart_nofk_noidx;  count -------    101 (1 row)   Time: 0.951 ms postgres=# select count(1) from test_nonpart_nofk_noidx;  count -------    101 (1 row)   Time: 0.399 ms

C) Make a decision from the above output...

1. we have dob a date field can be used for range partitioning

2. we have hosptl field which is a string type, can be used for list partitioning.

To keep this test case simple, we will choose option(2).

D) Note, we dont have any dependency in this scenario, so we will move forward with the next step..

But ensure to verify the depedency using DBeaver (CE - Community Edition) by right click and select diagram.

E) Use pg_dump (or DBeaver) to extract the DDL of the table

Pg_dump command: /usr/pgsql-14/bin/pg_dump --table='public.test_nonpart_nofk_noidx' --username=postgres --dbname=postgres --schema-only --file=/tmp/test_nonpart_nofk_noidx_ddl.sql --verbose   --table : filters the table we need this operation performed --username, --dbname : are username to connect to the designated dbname --schema-only : dump only the table skeleton which is DDL --file : Dump the content to file --verbose : throw pg_dump messages   Output snippet: pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas .. pg_dump: saving search_path = pg_dump: creating TABLE "public.test_nonpart_nofk_noidx"   Output file snippet: -- -- TOC entry 213 (class 1259 OID 16444) -- Name: test_nonpart_nofk_noidx; Type: TABLE; Schema: public; Owner: postgres --   CREATE TABLE public.test_nonpart_nofk_noidx (     lvl integer,     dob date,     hosptl text,     fn text,     ln text,     poscode text );     ALTER TABLE public.test_nonpart_nofk_noidx OWNER TO postgres;   -- Completed on 2023-04-03 19:06:14 BST

F) Let us create the partition table

Command: CREATE TABLE public.test_part_nofk_noidx (     lvl integer,     dob date,     hosptl text,     fn text,     ln text,     poscode text ) partition by list(hosptl);   CREATE TABLE public.test_part_nofk_noidx_a partition of public.test_part_nofk_noidx for values in ('AAAAAAAAAAAAAAAAAAAA'); CREATE TABLE public.test_part_nofk_noidx_b partition of public.test_part_nofk_noidx for values in ('BBBBBBBBBBBBBBBBBBBA'); CREATE TABLE public.test_part_nofk_noidx_c partition of public.test_part_nofk_noidx for values in ('CCCCCCCCCCCCCCCCCCCA'); CREATE TABLE public.test_part_nofk_noidx_d partition of public.test_part_nofk_noidx for values in ('DDDDDDDDDDDDDDDDDDDA'); CREATE TABLE public.test_part_nofk_noidx_default partition of public.test_part_nofk_noidx default;   Command output: postgres=# CREATE TABLE public.test_part_nofk_noidx ( postgres(#     lvl integer, postgres(#     dob date, postgres(#     hosptl text, postgres(#     fn text, postgres(#     ln text, postgres(#     poscode text postgres(# ) partition by list(hosptl); CREATE TABLE Time: 6.651 ms postgres=# CREATE TABLE public.test_part_nofk_noidx_a partition of public.test_part_nofk_noidx for values in ('AAAAAAAAAAAAAAAAAAAA'); CREATE TABLE Time: 6.445 ms postgres=# CREATE TABLE public.test_part_nofk_noidx_b partition of public.test_part_nofk_noidx for values in ('BBBBBBBBBBBBBBBBBBBA'); CREATE TABLE Time: 8.316 ms postgres=# CREATE TABLE public.test_part_nofk_noidx_c partition of public.test_part_nofk_noidx for values in ('CCCCCCCCCCCCCCCCCCCA'); CREATE TABLE Time: 6.457 ms postgres=# CREATE TABLE public.test_part_nofk_noidx_d partition of public.test_part_nofk_noidx for values in ('DDDDDDDDDDDDDDDDDDDA'); CREATE TABLE Time: 8.252 ms postgres=# CREATE TABLE public.test_part_nofk_noidx_default partition of public.test_part_nofk_noidx default; CREATE TABLE Time: 6.275 ms postgres=#

G) Partition list of created table:

Verify: postgres=# \d+ public.test_part_nofk_noidx                              Partitioned table "public.test_part_nofk_noidx"  Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description ---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------  lvl     | integer |           |          |         | plain    |             |              |  dob     | date    |           |          |         | plain    |             |              |  hosptl  | text    |           |          |         | extended |             |              |  fn      | text    |           |          |         | extended |             |              |  ln      | text    |           |          |         | extended |             |              |  poscode | text    |           |          |         | extended |             |              | Partition key: LIST (hosptl) Partitions: test_part_nofk_noidx_a FOR VALUES IN ('AAAAAAAAAAAAAAAAAAAA'),             test_part_nofk_noidx_b FOR VALUES IN ('BBBBBBBBBBBBBBBBBBBA'),             test_part_nofk_noidx_c FOR VALUES IN ('CCCCCCCCCCCCCCCCCCCA'),             test_part_nofk_noidx_d FOR VALUES IN ('DDDDDDDDDDDDDDDDDDDA'),             test_part_nofk_noidx_default DEFAULT   postgres=#

H)  Preserve the logging nature of the table and alter it to unlog it. This is to avoid unnecessary wal for first time data load into the target table.

Note: For partition table change this attribute for individual tables (using same command as table's just use partition name for table name). The partition table isnt impacted by this command; neither it changes the attribute from p to u.

postgres=# select relname,relpersistence from pg_class where relname like 'test%' order by 1;            relname            | relpersistence ------------------------------+----------------  test_nonpart_nofk_noidx      | p  test_part_nofk_noidx         | p  test_part_nofk_noidx_a       | p  test_part_nofk_noidx_b       | p  test_part_nofk_noidx_c       | p  test_part_nofk_noidx_d       | p  test_part_nofk_noidx_default | p  test_shrbuf                  | p  test_shrbuf_idx_lvl          | p (9 rows)   Time: 0.580 ms postgres=#   postgres=# alter table test_part_nofk_noidx_a set unlogged; ALTER TABLE Time: 12.750 ms postgres=# alter table test_part_nofk_noidx_b set unlogged; ALTER TABLE Time: 17.305 ms postgres=# alter table test_part_nofk_noidx_c set unlogged; ALTER TABLE Time: 20.743 ms postgres=# alter table test_part_nofk_noidx_d set unlogged; ALTER TABLE Time: 17.444 ms postgres=# alter table test_part_nofk_noidx_default set unlogged; ALTER TABLE Time: 15.323 ms   postgres=# select relname,relpersistence from pg_class where relname like 'test%' order by 1;            relname            | relpersistence ------------------------------+----------------  test_nonpart_nofk_noidx      | p  test_part_nofk_noidx         | p  test_part_nofk_noidx_a       | u  test_part_nofk_noidx_b       | u  test_part_nofk_noidx_c       | u  test_part_nofk_noidx_d       | u  test_part_nofk_noidx_default | u  test_shrbuf                  | p  test_shrbuf_idx_lvl          | p (9 rows)   Time: 0.787 ms postgres=#

I) Now load the data into the target table & analyze the table stats

Here we are using insert statement, but is recommended to use COPY or pg_dump as applies.

insert into test_part_nofk_noidx select * from test_nonpart_nofk_noidx; -- commit; -- auto commit   postgres=# insert into test_part_nofk_noidx postgres-# select * from test_nonpart_nofk_noidx; INSERT 0 101 Time: 1.461 ms postgres=#   select * from pg_stat_user_tables;    relid | schemaname |           relname            | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum | last_vacuum |        last_autovacuum        | last_analyze |       last_autoanalyze        | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count -------+------------+------------------------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+--------------------+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------  16475 | public     | test_part_nofk_noidx_default |        1 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |                  0 |             |                               |              |                               |            0 |                0 |             0 |                 0  16455 | public     | test_part_nofk_noidx_a       |        3 |            0 |          |               |        26 |         0 |         0 |             0 |         26 |          0 |                  26 |                 26 |             |                               |              |                               |            0 |                0 |             0 |                 0  16411 | public     | test_shrbuf                  |       59 |     20000020 |        2 |             1 |   1000001 |         0 |         0 |             0 |    1000001 |          0 |                   0 |                  0 |             | 2023-04-01 20:41:25.488159+01 |              | 2023-04-01 20:41:26.594851+01 |            0 |                1 |             0 |                 1  16470 | public     | test_part_nofk_noidx_d       |        1 |            0 |          |               |        23 |         0 |         0 |             0 |         23 |          0 |                  23 |                 23 |             |                               |              |                               |            0 |                0 |             0 |                 0  16460 | public     | test_part_nofk_noidx_b       |        1 |            0 |          |               |        26 |         0 |         0 |             0 |         26 |          0 |                  26 |                 26 |             |                               |              |                               |            0 |                0 |             0 |                 0  16452 | public     | test_part_nofk_noidx         |        0 |            0 |          |               |         0 |         0 |         0 |             0 |          0 |          0 |                   0 |                  0 |             |                               |              |                               |            0 |                0 |             0 |                 0  16465 | public     | test_part_nofk_noidx_c       |        1 |            0 |          |               |        26 |         0 |         0 |             0 |         26 |          0 |                  26 |                 26 |             |                               |              |                               |            0 |                0 |             0 |                 0  16444 | public     | test_nonpart_nofk_noidx      |       11 |         1111 |          |               |       101 |         0 |         0 |             0 |        101 |          0 |                   0 |                101 |             |                               |              | 2023-04-03 17:58:46.003797+01 |            0 |                0 |             0 |                 1 (8 rows)   Time: 12.779 ms   SELECT t.oid::regclass AS partition ,t.relname ,t.relpages ,t.reltuples ,t.relallvisible ,t.relpersistence ,t.relkind ,t.relispartition ,t.reloptions ,pg_get_expr(t.relpartbound, t.oid) AS bounds FROM pg_inherits AS i    JOIN pg_class AS t ON t.oid = i.inhrelid WHERE i.inhparent = 'test_part_nofk_noidx'::regclass;             partition           |           relname            | relpages | reltuples | relallvisible | relpersistence | relkind | relispartition | reloptions |                 bounds ------------------------------+------------------------------+----------+-----------+---------------+----------------+---------+----------------+------------+----------------------------------------  test_part_nofk_noidx_a       | test_part_nofk_noidx_a       |        0 |        -1 |             0 | u              | r       | t              |            | FOR VALUES IN ('AAAAAAAAAAAAAAAAAAAA')  test_part_nofk_noidx_b       | test_part_nofk_noidx_b       |        0 |        -1 |             0 | u              | r       | t              |            | FOR VALUES IN ('BBBBBBBBBBBBBBBBBBBA')  test_part_nofk_noidx_c       | test_part_nofk_noidx_c       |        0 |        -1 |             0 | u              | r       | t              |            | FOR VALUES IN ('CCCCCCCCCCCCCCCCCCCA')  test_part_nofk_noidx_d       | test_part_nofk_noidx_d       |        0 |        -1 |             0 | u              | r       | t              |            | FOR VALUES IN ('DDDDDDDDDDDDDDDDDDDA')  test_part_nofk_noidx_default | test_part_nofk_noidx_default |        0 |        -1 |             0 | u              | r       | t              |            | DEFAULT (5 rows)   Time: 0.999 ms postgres=#   analyze verbose table test_part_nofk_noidx;   postgres=# analyze verbose test_part_nofk_noidx; INFO:  analyzing "public.test_part_nofk_noidx" inheritance tree INFO:  "test_part_nofk_noidx_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  "test_part_nofk_noidx_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  "test_part_nofk_noidx_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  "test_part_nofk_noidx_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows INFO:  analyzing "public.test_part_nofk_noidx_a" INFO:  "test_part_nofk_noidx_a": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  analyzing "public.test_part_nofk_noidx_b" INFO:  "test_part_nofk_noidx_b": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  analyzing "public.test_part_nofk_noidx_c" INFO:  "test_part_nofk_noidx_c": scanned 1 of 1 pages, containing 26 live rows and 0 dead rows; 26 rows in sample, 26 estimated total rows INFO:  analyzing "public.test_part_nofk_noidx_d" INFO:  "test_part_nofk_noidx_d": scanned 1 of 1 pages, containing 23 live rows and 0 dead rows; 23 rows in sample, 23 estimated total rows INFO:  analyzing "public.test_part_nofk_noidx_default" INFO:  "test_part_nofk_noidx_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows ANALYZE Time: 14.814 ms postgres=#               partition           |           relname            | relpages | reltuples | relallvisible | relpersistence | relkind | relispartition | reloptions |                 bounds ------------------------------+------------------------------+----------+-----------+---------------+----------------+---------+----------------+------------+----------------------------------------  test_part_nofk_noidx_a       | test_part_nofk_noidx_a       |        1 |        26 |             0 | u              | r       | t              |            | FOR VALUES IN ('AAAAAAAAAAAAAAAAAAAA')  test_part_nofk_noidx_b       | test_part_nofk_noidx_b       |        1 |        26 |             0 | u              | r       | t              |            | FOR VALUES IN ('BBBBBBBBBBBBBBBBBBBA')  test_part_nofk_noidx_c       | test_part_nofk_noidx_c       |        1 |        26 |             0 | u              | r       | t              |            | FOR VALUES IN ('CCCCCCCCCCCCCCCCCCCA')  test_part_nofk_noidx_d       | test_part_nofk_noidx_d       |        1 |        23 |             0 | u              | r       | t              |            | FOR VALUES IN ('DDDDDDDDDDDDDDDDDDDA')  test_part_nofk_noidx_default | test_part_nofk_noidx_default |        0 |         0 |             0 | u              | r       | t              |            | DEFAULT (5 rows)   Time: 0.817 ms postgres=#

J) Rename the tables (here name doesnt make sense nonpart or part - activity is significant)..

1) Rename the original table to another name

alter table test_nonpart_nofk_noidx rename to test_nonpart_nofk_noidx_old;

alter table test_nonpart_nofk_noidx rename to test_nonpart_nofk_noidx_old;   postgres=# \d                           List of relations  Schema |             Name             |       Type        |  Owner --------+------------------------------+-------------------+---------- ..  public | test_nonpart_nofk_noidx_old  | table             | postgres  public | test_part_nofk_noidx         | partitioned table | postgres

2) Rename the partition table and its partition to align to the original table name

alter table test_part_nofk_noidx rename to test_nonpart_nofk_noidx;

alter table test_part_nofk_noidx rename to test_nonpart_nofk_noidx;                             List of relations  Schema |             Name             |       Type        |  Owner --------+------------------------------+-------------------+---------- ..  public | test_nonpart_nofk_noidx      | partitioned table | postgres  public | test_nonpart_nofk_noidx_old  | table             | postgres

3) Rename the partitions to align with table name

alter table test_part_nofk_noidx_a rename to test_nonpart_nofk_noidx_a;

alter table test_part_nofk_noidx_b rename to test_nonpart_nofk_noidx_b;

alter table test_part_nofk_noidx_c rename to test_nonpart_nofk_noidx_c;

alter table test_part_nofk_noidx_d rename to test_nonpart_nofk_noidx_d;

alter table test_part_nofk_noidx_default rename to test_nonpart_nofk_noidx_default;

alter table test_part_nofk_noidx_a rename to test_nonpart_nofk_noidx_a; alter table test_part_nofk_noidx_b rename to test_nonpart_nofk_noidx_b; alter table test_part_nofk_noidx_c rename to test_nonpart_nofk_noidx_c; alter table test_part_nofk_noidx_d rename to test_nonpart_nofk_noidx_d; alter table test_part_nofk_noidx_default rename to test_nonpart_nofk_noidx_default;   postgres=# \d                             List of relations  Schema |              Name               |       Type        |  Owner --------+---------------------------------+-------------------+---------- ..  public | test_nonpart_nofk_noidx         | partitioned table | postgres  public | test_nonpart_nofk_noidx_a       | table             | postgres  public | test_nonpart_nofk_noidx_b       | table             | postgres  public | test_nonpart_nofk_noidx_c       | table             | postgres  public | test_nonpart_nofk_noidx_d       | table             | postgres  public | test_nonpart_nofk_noidx_default | table             | postgres  public | test_nonpart_nofk_noidx_old     | table             | postgres

4) Try to perform a data selection and see if partition pruning is happening

explain analyze select * from test_nonpart_nofk_noidx where hosptl='AAAAAAAAAAAAAAAAAAAA';

postgres=# explain analyze select * from test_nonpart_nofk_noidx where hosptl='AAAAAAAAAAAAAAAAAAAA';                                                                   QUERY PLAN                                              ----------------------------------------------------------------------------------------------------------------------------------------------  Seq Scan on test_nonpart_nofk_noidx_a test_nonpart_nofk_noidx  (cost=0.00..1.32 rows=26 width=74) (actual time=0.008..0.017 rows=26 loops=1)    Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)  Planning Time: 0.213 ms  Execution Time: 0.033 ms (4 rows)   Time: 0.659 ms postgres=#

If we notice above, we selected only test_nonpart_nofk_noidx_a partition alone & the same is confirmed from the pg_stat_user_Tables view

postgres=# select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch from pg_stat_user_tables where relname like 'test_nonpart%' order by relname;              relname             | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch ---------------------------------+----------+--------------+----------+---------------  test_nonpart_nofk_noidx         |        0 |            0 |          |  test_nonpart_nofk_noidx_a       |        4 |           26 |          |                  <<<< we scanned this partition, rest partitions are showing no tuples read.  test_nonpart_nofk_noidx_b       |        1 |            0 |          |  test_nonpart_nofk_noidx_c       |        1 |            0 |          |  test_nonpart_nofk_noidx_d       |        1 |            0 |          |  test_nonpart_nofk_noidx_default |        1 |            0 |          |  test_nonpart_nofk_noidx_old     |       11 |         1111 |          | (7 rows)   Time: 12.038 ms postgres=#

Remember to restore the logging attribute on the table as soon as copy is done:

postgres=# select relname,relpersistence from pg_class where relname like 'test%' order by 1;

             relname             | relpersistence

---------------------------------+----------------

 test_nonpart_nofk_noidx         | p

 test_nonpart_nofk_noidx_a       | u

 test_nonpart_nofk_noidx_b       | u

 test_nonpart_nofk_noidx_c       | u

 test_nonpart_nofk_noidx_d       | u

 test_nonpart_nofk_noidx_default | u

 test_nonpart_nofk_noidx_old     | p

 test_shrbuf                     | p

 test_shrbuf_idx_lvl             | p

(9 rows)

Time: 1.361 ms

postgres=# alter table test_nonpart_nofk_noidx_a set logged;

ALTER TABLE

Time: 12.300 ms

postgres=# select relname,relpersistence from pg_class where relname like 'test%' order by 1;

             relname             | relpersistence

---------------------------------+----------------

 test_nonpart_nofk_noidx         | p

 test_nonpart_nofk_noidx_a       | p

 test_nonpart_nofk_noidx_b       | u

 test_nonpart_nofk_noidx_c       | u

 test_nonpart_nofk_noidx_d       | u

 test_nonpart_nofk_noidx_default | u

 test_nonpart_nofk_noidx_old     | p

 test_shrbuf                     | p

 test_shrbuf_idx_lvl             | p

(9 rows)

Time: 1.116 ms

postgres=# alter table test_nonpart_nofk_noidx_b set logged;

ALTER TABLE

Time: 13.030 ms

postgres=# alter table test_nonpart_nofk_noidx_c set logged;

ALTER TABLE

Time: 8.204 ms

postgres=# alter table test_nonpart_nofk_noidx_d set logged;

ALTER TABLE

Time: 9.367 ms

postgres=# alter table test_nonpart_nofk_noidx_default set logged;

ALTER TABLE

Time: 8.530 ms

postgres=# select relname,relpersistence from pg_class where relname like 'test%' order by 1;

             relname             | relpersistence

---------------------------------+----------------

 test_nonpart_nofk_noidx         | p

 test_nonpart_nofk_noidx_a       | p

 test_nonpart_nofk_noidx_b       | p

 test_nonpart_nofk_noidx_c       | p

 test_nonpart_nofk_noidx_d       | p

 test_nonpart_nofk_noidx_default | p

 test_nonpart_nofk_noidx_old     | p

 test_shrbuf                     | p

 test_shrbuf_idx_lvl             | p

(9 rows)

Time: 0.548 ms

postgres=#

This closes the first part of the multipart blog series.

Thanks