Let us learn Oracle Database Administration skills: partitioning

Showing posts with label partitioning. Show all posts

Wednesday, April 5, 2023

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

Objective: Steps to move a non-partition table to partition table with indexes

This is a multipart series and in this part, we are going to draft the steps involved in converting a non-partitioned table to partitioned table with indexes.

A) Table Setup:

create table test_nofk 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;

create index test_nofk_lvl_idx on test_nofk(lvl);

analyze verbose test_nofk;

Table creation output:

postgres=# create table test_nofk 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

postgres=#

postgres=# create index test_nofk_lvl_idx on test_nofk(lvl);
CREATE INDEX
postgres=#

postgres=#
postgres=# analyze verbose test_nofk;
INFO: analyzing "public.test_nofk"
INFO: "test_nofk": scanned 2 of 2 pages, containing 101 live rows and 0 dead rows; 101 rows in sample, 101 estimated total rows
ANALYZE
postgres=#
postgres=#

B) In part 1 we already identified the partkey column, so let us proceed with hosptl as part key column.

C) Let us collect dependency diagram (ER), even now we dont have any tables dependent on this table.So let us move forward.

D) Let us get the ddl of the table dumped using pg_dump.

F) Let us use COPY method to copy the data from existing table to the new table.

copy test_nofk to '/tmp/test_nofk_05apr23.dmp';
copy test_part_nofk from '/tmp/test_nofk_05apr23.dmp';
analyze verbose test_part_nofk;

Output:
postgres=# copy test_nofk to '/tmp/test_nofk_05apr23.dmp';
COPY 101
postgres=# copy test_part_nofk from '/tmp/test_nofk_05apr23.dmp';
COPY 101
postgres=#

postgres=# analyze verbose test_part_nofk;
INFO: analyzing "public.test_part_nofk" inheritance tree
INFO: "test_part_nofk_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_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_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_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_a"
INFO: "test_part_nofk_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_b"
INFO: "test_part_nofk_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_c"
INFO: "test_part_nofk_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_d"
INFO: "test_part_nofk_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_default"
INFO: "test_part_nofk_default": scanned 0 of 0 pages, containing 0 live rows and 0 dead rows; 0 rows in sample, 0 estimated total rows
ANALYZE
postgres=#

Post load:
postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test_part_nofk%' order by 1;
relname | relpages | reltuples | relallvisible | relispartition
------------------------+----------+-----------+---------------+----------------
test_part_nofk | -1 | 101 | 0 | f
test_part_nofk_a | 1 | 26 | 0 | t
test_part_nofk_b | 1 | 26 | 0 | t
test_part_nofk_c | 1 | 26 | 0 | t
test_part_nofk_d | 1 | 23 | 0 | t
test_part_nofk_default | 0 | 0 | 0 | t
(6 rows)
postgres=#

postgres=# select relname,seq_scan,seq_tup_read,n_tup_ins,n_tup_upd,n_tup_del,n_live_tup,n_mod_since_analyze from pg_stat_user_Tables where relname like 'test_part_nofk%' order by 1;
relname | seq_scan | seq_tup_read | n_tup_ins | n_tup_upd | n_tup_del | n_live_tup | n_mod_since_analyze
------------------------+----------+--------------+-----------+-----------+-----------+------------+---------------------
test_part_nofk | 0 | 0 | 0 | 0 | 0 | 0 | 0
test_part_nofk_a | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_nofk_b | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_nofk_c | 0 | 0 | 26 | 0 | 0 | 26 | 0
test_part_nofk_d | 0 | 0 | 23 | 0 | 0 | 23 | 0
test_part_nofk_default | 0 | 0 | 0 | 0 | 0 | 0 | 0
(6 rows)
postgres=#
postgres=#

G) Let us create the index.
We have 2 choices...
1. Create ONLY index and take care of adding index partition manually each time; this doesnt require exclusive lock on the table
2. Create a normal index and the partition scheme is applied automatically.

create index test_part_nofk_lvl_idx ON public.test_part_nofk USING btree (lvl);

Output:
postgres=# create index test_part_nofk_lvl_idx ON public.test_part_nofk USING btree (lvl);
CREATE INDEX
postgres=#

postgres=# select relname,relpages,reltuples,relallvisible,relispartition from pg_class where relname like 'test_part_nofk%' order by 1;
relname | relpages | reltuples | relallvisible | relispartition
--------------------------------+----------+-----------+---------------+----------------
test_part_nofk | -1 | 101 | 0 | f
test_part_nofk_a | 1 | 26 | 0 | t
test_part_nofk_a_lvl_idx | 2 | 26 | 0 | t <<< notice index already has stats
test_part_nofk_b | 1 | 26 | 0 | t
test_part_nofk_b_lvl_idx | 2 | 26 | 0 | t
test_part_nofk_c | 1 | 26 | 0 | t
test_part_nofk_c_lvl_idx | 2 | 26 | 0 | t
test_part_nofk_d | 1 | 23 | 0 | t
test_part_nofk_d_lvl_idx | 2 | 23 | 0 | t
test_part_nofk_default | 0 | 0 | 0 | t
test_part_nofk_default_lvl_idx | 1 | 0 | 0 | t
test_part_nofk_lvl_idx | 0 | 0 | 0 | f
(12 rows)
postgres=#

postgres=# select * from pg_indexes where tablename like 'test_part_nofk%' order by 1,2;
schemaname | tablename | indexname | tablespace | indexdef
------------+------------------------+--------------------------------+------------+------------------------------------------------------------------------------------------------
public | test_part_nofk | test_part_nofk_lvl_idx | | CREATE INDEX test_part_nofk_lvl_idx ON ONLY public.test_part_nofk USING btree (lvl)
public | test_part_nofk_a | test_part_nofk_a_lvl_idx | | CREATE INDEX test_part_nofk_a_lvl_idx ON public.test_part_nofk_a USING btree (lvl)
public | test_part_nofk_b | test_part_nofk_b_lvl_idx | | CREATE INDEX test_part_nofk_b_lvl_idx ON public.test_part_nofk_b USING btree (lvl)
public | test_part_nofk_c | test_part_nofk_c_lvl_idx | | CREATE INDEX test_part_nofk_c_lvl_idx ON public.test_part_nofk_c USING btree (lvl)
public | test_part_nofk_d | test_part_nofk_d_lvl_idx | | CREATE INDEX test_part_nofk_d_lvl_idx ON public.test_part_nofk_d USING btree (lvl)
public | test_part_nofk_default | test_part_nofk_default_lvl_idx | | CREATE INDEX test_part_nofk_default_lvl_idx ON public.test_part_nofk_default USING btree (lvl)
(6 rows)
postgres=#
H) Now we have both non-partition and partition tables available. Let us try to query the tables using preference clause to see the performance difference.
explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
postgres=# explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN |
Seq Scan on test_nofk (cost=0.00..3.26 rows=26 width=74) (actual time=0.011..0.041 rows=26 loops=1)
Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)
Rows Removed by Filter: 75
Planning Time: 0.203 ms
Execution Time: 0.057 ms

postgres=# explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on test_nofk (cost=0.00..3.26 rows=26 width=74) (actual time=0.013..0.043 rows=26 loops=1)
Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)
Rows Removed by Filter: 75
Planning Time: 0.054 ms
Execution Time: 0.059 ms
(5 rows)
postgres=#

The above result is consistent after couple of runs.

explain analyze select * from public.test_part_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';

postgres=# explain analyze select * from public.test_part_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on test_part_nofk_a test_part_nofk (cost=0.00..1.32 rows=26 width=74) (actual time=0.012..0.021 rows=26 loops=1)
Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)
Planning Time: 0.148 ms
Execution Time: 0.039 ms
(4 rows)
postgres=# explain analyze select * from public.test_part_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Seq Scan on test_part_nofk_a test_part_nofk (cost=0.00..1.32 rows=26 width=74) (actual time=0.011..0.020 rows=26 loops=1)
Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)
Planning Time: 0.089 ms
Execution Time: 0.037 ms
(4 rows)
postgres=#

So we get .037ms with partitioned table vs 0.059 for a nonpartitioned table.
We could see the same # of rows processed but there is nothing to filter in partition case.
I) Let us rename the nonpart table and index now...

alter table test_nofk rename to test_nofk_old;
alter index test_nofk_lvl_idx rename to test_nofk_old_lvl_idx;

postgres=# alter table test_nofk rename to test_nofk_old;
ALTER TABLE
postgres=#

postgres=# alter index test_nofk_lvl_idx rename to test_nofk_old_lvl_idx;
ALTER INDEX
postgres=#

postgres=# select * from pg_indexes where tablename='test_nofk_old';
schemaname | tablename | indexname | tablespace | indexdef
------------+---------------+-----------------------+------------+------------------------------------------------------------------------------
public | test_nofk_old | test_nofk_old_lvl_idx | | CREATE INDEX test_nofk_old_lvl_idx ON public.test_nofk_old USING btree (lvl)
(1 row)
postgres=#
J) Let us rename the partitioned table to the actual table name..
alter table test_part_nofk rename to test_nofk;
alter table test_part_nofk_a rename to test_nofk_a;
alter table test_part_nofk_b rename to test_nofk_b;
alter table test_part_nofk_c rename to test_nofk_c;
alter table test_part_nofk_d rename to test_nofk_d;
alter table test_part_nofk_default rename to test_nofk_default;

Output:
postgres=# alter table test_part_nofk rename to test_nofk;
ALTER TABLE
postgres=# alter table test_part_nofk_a rename to test_nofk_a;
ALTER TABLE
postgres=# alter table test_part_nofk_b rename to test_nofk_b;
ALTER TABLE
postgres=# alter table test_part_nofk_c rename to test_nofk_c;
ALTER TABLE
postgres=# alter table test_part_nofk_d rename to test_nofk_d;
ALTER TABLE
postgres=# alter table test_part_nofk_default rename to test_nofk_default;
ALTER TABLE
postgres=#

K) Index rename:

alter index test_part_nofk_lvl_idx rename to test_nofk_lvl_idx;
alter index test_part_nofk_a_lvl_idx rename to test_nofk_a_lvl_idx;
alter index test_part_nofk_b_lvl_idx rename to test_nofk_b_lvl_idx;
alter index test_part_nofk_c_lvl_idx rename to test_nofk_c_lvl_idx;
alter index test_part_nofk_d_lvl_idx rename to test_nofk_d_lvl_idx;
alter index test_part_nofk_default_lvl_idx rename to test_nofk_default_lvl_idx;

Output:

postgres=# alter index test_part_nofk_lvl_idx rename to test_nofk_lvl_idx;
ALTER INDEX
postgres=# alter index test_part_nofk_a_lvl_idx rename to test_nofk_a_lvl_idx;
ALTER INDEX
postgres=# alter index test_part_nofk_b_lvl_idx rename to test_nofk_b_lvl_idx;
ALTER INDEX
postgres=# alter index test_part_nofk_c_lvl_idx rename to test_nofk_c_lvl_idx;
ALTER INDEX
postgres=# alter index test_part_nofk_d_lvl_idx rename to test_nofk_d_lvl_idx;
ALTER INDEX
postgres=# alter index test_part_nofk_default_lvl_idx rename to test_nofk_default_lvl_idx;
ALTER INDEX
postgres=#

Post the action:

postgres=# select * from pg_indexes where indexname like 'test_nofk%' order by indexname;
schemaname | tablename | indexname | tablespace | indexdef
------------+-------------------+---------------------------+------------+--------------------------------------------------------------------------------------
public | test_nofk_a | test_nofk_a_lvl_idx | | CREATE INDEX test_nofk_a_lvl_idx ON public.test_nofk_a USING btree (lvl)
public | test_nofk_b | test_nofk_b_lvl_idx | | CREATE INDEX test_nofk_b_lvl_idx ON public.test_nofk_b USING btree (lvl)
public | test_nofk_c | test_nofk_c_lvl_idx | | CREATE INDEX test_nofk_c_lvl_idx ON public.test_nofk_c USING btree (lvl)
public | test_nofk_d | test_nofk_d_lvl_idx | | CREATE INDEX test_nofk_d_lvl_idx ON public.test_nofk_d USING btree (lvl)
public | test_nofk_default | test_nofk_default_lvl_idx | | CREATE INDEX test_nofk_default_lvl_idx ON public.test_nofk_default USING btree (lvl)
public | test_nofk | test_nofk_lvl_idx | | CREATE INDEX test_nofk_lvl_idx ON ONLY public.test_nofk USING btree (lvl)
public | test_nofk_old | test_nofk_old_lvl_idx | | CREATE INDEX test_nofk_old_lvl_idx ON public.test_nofk_old USING btree (lvl)
(7 rows)
postgres=#

L) Test the performance of the renamed table...

explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';

Output:
postgres=# explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on test_nofk_a test_nofk (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.181 ms
Execution Time: 0.033 ms
(4 rows)
Time: 0.659 ms
postgres=#
...
postgres=# explain analyze select * from public.test_nofk where hosptl='AAAAAAAAAAAAAAAAAAAA';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on test_nofk_a test_nofk (cost=0.00..1.32 rows=26 width=74) (actual time=0.010..0.019 rows=26 loops=1)
Filter: (hosptl = 'AAAAAAAAAAAAAAAAAAAA'::text)
Planning Time: 0.089 ms
Execution Time: 0.035 ms
(4 rows)
Time: 0.374 ms
postgres=#

Around .0033 secs or so. Not much different from prerename state.

Thanks

Monday, April 3, 2023

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

-------

(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"

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

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;

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

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;

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

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=#

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

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;

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

Wednesday, April 5, 2023

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

Monday, April 3, 2023

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

OKV platform certificate rotation - pitfall , awareness!!!!