Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Partition Types:
1. Range
2. List
3. Hash -->Support PostgreSQL 11
4. Table Inheritance
partition 10 and 11 difference:
1. Update Moves Rows Across Partitions
2. Create Default Partitions
3. Automatic Index Creation
4. Foreign Key Support
5. Unique Indexes
RANGE PARTITION:
postgres=# create table rparent(id int,name text,doj date) partition by range(doj);
CREATE TABLE
postgres=# create table rc1 partition of rparent for values from ('01-jan-2011') to ('01-jan-2012');
CREATE TABLE
postgres=# create table rc2 partition of rparent for values from ('01-jan-2012') to ('01-jan-2013');
CREATE TABLE
postgres=# create table rc3 partition of rparent for values from ('01-jan-2013') to (maxvalue);
CREATE TABLE
postgres=# insert into rparent values (1,'aaa','21-may-2011');
INSERT 0 1
postgres=# insert into rparent values (2,'bbb','01-jun-2012');
INSERT 0 1
postgres=# insert into rparent values (3,'ccc','01-jun-2020');
INSERT 0 1
postgres=# select * from rparent ;
id | name | doj
----+------+------------
1 | aaa | 2011-05-21
2 | bbb | 2012-06-01
3 | ccc | 2020-06-01
(3 rows)
postgres=# select * from rc1;
id | name | doj
----+------+------------
1 | aaa | 2011-05-21
(1 row)
postgres=# select * from rc2;
id | name | doj
----+------+------------
2 | bbb | 2012-06-01
(1 row)
postgres=# select * from rc3;
id | name | doj
----+------+------------
3 | ccc | 2020-06-01
(1 row)
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+----------+---------+-------------
public | rc1 | table | postgres | 16 kB |
public | rc2 | table | postgres | 16 kB |
public | rc3 | table | postgres | 16 kB |
public | rparent | table | postgres | 0 bytes |
public | t1 | table | postgres | 4360 kB |
public | t2 | table | postgres | 4360 kB |
(6 rows)
postgres=# drop table rparent ;
DROP TABLE
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | t1 | table | postgres | 4360 kB |
public | t2 | table | postgres | 4360 kB |
(2 rows)
LIST PARTITION:
postgres=# create table lparent(id int,name text,place text)partition by list(place);
CREATE TABLE
postgres=# create table lc1 partition of lparent for values in ('chennai');
CREATE TABLE
postgres=# create table lc2 partition of lparent for values in ('madurai','theni');
CREATE TABLE
postgres=# create table lc3 partition of lparent default;
CREATE TABLE
postgres=# insert into lparent values (1,'aaa','chennai');
INSERT 0 1
postgres=# insert into lparent values (1,'aaa','madurai');
INSERT 0 1
postgres=# insert into lparent values (1,'aaa','theni');
INSERT 0 1
postgres=# insert into lparent values (1,'aaa','covai');
INSERT 0 1
postgres=# insert into lparent values (1,'aaa','thiruchy');
INSERT 0 1
postgres=# select * from lparent ;
id | name | place
----+------+----------
1 | aaa | chennai
1 | aaa | madurai
1 | aaa | theni
1 | aaa | covai
1 | aaa | thiruchy
(5 rows)
postgres=# select * from lc1 ;
id | name | place
----+------+---------
1 | aaa | chennai
(1 row)
postgres=# select * from lc2 ;
id | name | place
----+------+---------
1 | aaa | madurai
1 | aaa | theni
(2 rows)
postgres=# select * from lc3 ;
id | name | place
----+------+----------
1 | aaa | covai
1 | aaa | thiruchy
(2 rows)
postgres=# create index in_t on lparent (place);
CREATE INDEX
postgres=# \d+ lparent
Table "public.lparent"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
id | integer | | | | plain | |
name | text | | | | extended | |
place | text | | | | extended | |
Partition key: LIST (place)
Indexes:
"in_t" btree (place)
Partitions: lc1 FOR VALUES IN ('chennai'),
lc2 FOR VALUES IN ('madurai', 'theni'),
lc3 DEFAULT
HASH PARTITION:
postgres=# create table hparent (id int,name text) partition by hash(id);
CREATE TABLE
postgres=# create table hc1 partition of hparent for values with (MODULUS 3, REMAINDER 0);
CREATE TABLE
postgres=# create table hc2 partition of hparent for values with (MODULUS 3, REMAINDER 1);
CREATE TABLE
postgres=# create table hc3 partition of hparent for values with (MODULUS 3, REMAINDER 2);
CREATE TABLE
postgres=# INSERT INTO hparent values (generate_series(1,20),'asas');
INSERT 0 20
postgres=# select * from hparent ;
id | name
----+------
2 | asas
4 | asas
6 | asas
8 | asas
15 | asas
16 | asas
18 | asas
19 | asas
20 | asas
3 | asas
7 | asas
10 | asas
13 | asas
14 | asas
1 | asas
5 | asas
9 | asas
11 | asas
12 | asas
17 | asas
(20 rows)
postgres=# select * from hc1 ;
id | name
----+------
2 | asas
4 | asas
6 | asas
8 | asas
15 | asas
16 | asas
18 | asas
19 | asas
20 | asas
(9 rows)
postgres=# select * from hc2 ;
id | name
----+------
3 | asas
7 | asas
10 | asas
13 | asas
14 | asas
(5 rows)
postgres=# select * from hc3 ;
id | name
----+------
1 | asas
5 | asas
9 | asas
11 | asas
12 | asas
17 | asas
(6 rows)
postgres=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------+-------+----------+---------+-------------
public | hc1 | table | postgres | 16 kB |
public | hc2 | table | postgres | 16 kB |
public | hc3 | table | postgres | 16 kB |
public | hparent | table | postgres | 0 bytes |
public | lc1 | table | postgres | 16 kB |
public | lc2 | table | postgres | 16 kB |
public | lc3 | table | postgres | 16 kB |
public | lparent | table | postgres | 0 bytes |
public | t1 | table | postgres | 4360 kB |
public | t2 | table | postgres | 4360 kB |
(10 rows)
TABLE INHERITS:
postgres=# create table iparent(id int,name text);
CREATE TABLE
postgres=# create table ic1(id int,name text,age int)inherits(iparent);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "name" with inherited definition
CREATE TABLE
postgres=# create table ic2(id int,name text,dob date)inherits(iparent);
NOTICE: merging column "id" with inherited definition
NOTICE: merging column "name" with inherited definition
CREATE TABLE
postgres=# insert into iparent values (1,'sa');
INSERT 0 1
postgres=# insert into ic1 values (1,'sa',12);
INSERT 0 1
postgres=# insert into ic2 values (1,'sa','03-jan-2012');
INSERT 0 1
postgres=# select * from iparent ;
id | name
----+------
1 | sa
1 | sa
1 | sa
(3 rows)
postgres=# select * from ic1 ; id | name | age
----+------+-----
1 | sa | 12
(1 row)
postgres=# select * from ic2 ;
id | name | dob
----+------+------------
1 | sa | 2012-01-03
(1 row)
Comments
Post a Comment