Create Table partitions in PostgreSQL

To create a table partition, follow these steps:
1. Create a master table
Create table users (uid int not null primary key, name varchar (20 ));
2. Create a partition table (the primary table above must be inherited)
Create table users_0 (check (uid> = 0 and uid <100) INHERITS (users );
Create table users_1 (check (uid >=100) INHERITS (users );
3. Create an index on the partition table. In fact, this step can be omitted.
Create index users_0_uidindex on users_0 (uid );
Create index users_1_uidindex on users_1 (uid );
4. Create RULE
Create rule users_insert_0
On insert to users WHERE
(Uid> = 0 and uid <100)
DO INSTEAD
Insert into users_0 VALUES (NEW. uid, NEW. name );
Create rule users_insert_1
On insert to users WHERE
(Uid >=100)
DO INSTEAD
Insert into users_1 VALUES (NEW. uid, NEW. name );
You can test data writing as follows:
S = # insert into users VALUES (100, 'smallfish ');
INSERT 0 0
S = # insert into users VALUES (20, 'aaaaa ');
INSERT 0 0
Postgres = # select * from users;
Uid | name
----- + -----------
20 | aaaaa
100 | smallfish
(2 Data columns)
S = # select * from users_0;
Uid | name
----- + -------
20 | aaaaa
(1 data column)
Postgres = # select * from users_1;
Uid | name
----- + -----------
100 | smallfish
(1 data column)
The table partition can be completed here, but there is still a need to modify it. Check the count query first.
Postgres = # explain select count (*) FROM users where uid <100;
QUERY PLAN
Bytes ---------------------------------------------------------------------------------------------
Aggregate (cost = 62. 75 .. 62.76 rows = 1 width = 0)
-> Append (cost = 6. 52 .. 60.55 rows = 879 width = 0)
-> Bitmap Heap Scan on users (cost = 6. 52 .. 20.18 rows = 293 width = 0)
Recheck Cond: (uid <100)
-> Bitmap Index Scan on users_pkey (cost = 0. 00 .. 6.45 rows = 293 width = 0)
Index Cond: (uid <100)
-> Bitmap Heap Scan on users_0 users (cost = 6. 52 .. 20.18 rows = 293 width = 0)
Recheck Cond: (uid <100)
-> Bitmap Index Scan on users_0_uidindex (cost = 0. 00 .. 6.45 rows = 293 width = 0)
Index Cond: (uid <100)
-> Bitmap Heap Scan on users_1 users (cost = 6. 52 .. 20.18 rows = 293 width = 0)
Recheck Cond: (uid <100)
-> Bitmap Index Scan on users_javasuidindex (cost = 0. 00 .. 6.45 rows = 293 width = 0)
Index Cond: (uid <100)
(14 data columns)
According to the original idea, the uid is less than 100. In theory, we should only query the users_0 table. Through the EXPLAIN command, we can see that other tables with all the partitions have been scanned.
Postgres = # SET constraint_exclusion = on;
SET
Postgres = # explain select count (*) FROM users where uid <100;
QUERY PLAN
Bytes ---------------------------------------------------------------------------------------------
Aggregate (cost = 41. 83 .. 41.84 rows = 1 width = 0)
-> Append (cost = 6. 52 .. 40.37 rows = 586 width = 0)
-> Bitmap Heap Scan on users (cost = 6. 52 .. 20.18 rows = 293 width = 0)
Recheck Cond: (uid <100)
-> Bitmap Index Scan on users_pkey (cost = 0. 00 .. 6.45 rows = 293 width = 0)
Index Cond: (uid <100)
-> Bitmap Heap Scan on users_0 users (cost = 6. 52 .. 20.18 rows = 293 width = 0)
Recheck Cond: (uid <100)
-> Bitmap Index Scan on users_0_uidindex (cost = 0. 00 .. 6.45 rows = 293 width = 0)
Index Cond: (uid <100)
(10 Data columns)
The entire process is OK!