Yesterday there was a discussion on the use of system partitioning and
what is the case in PostgreSQL 14. I like the constructive discussion because:
- You can always learn something new from more experienced people
- You pre-verify your opinions and analyze them
- The flow of ideas allow for development
Initially, it seemed that this is not possible in the core version of PostgreSQL 14. However, after a few tests / trials, it turned out that there is a light in the tunnel ….
What is system partitioning actually?
Briefly (with examples!) it is described in the note ID 785462.1
System partitioning preserves the administrative benefits of partitioning unfortunately along with the lack of performance advantages for tables where you can’t sensible to define a partitioning scheme.
The aforementioned note gives us the following properties of system partitioning:
- Provides the ability to create a single table that has many physical partitions
- No partition key is defined
- You define a specific number of partitions
- You must define which partition the data goes in when doing an insert of data
- Delete and update SQL does not require any special syntax
There are 2 types of partitioning in PostgreSQL – table inheritance and declarative partitioning. Let’s see how it works for declarative partitioning.
Attempt 1 – Let’s try not to define partitioning on the root table
CREATE TABLE p_measurement_system (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE p_measurement_system_p1 PARTITION OF p_measurement_system DEFAULT;
-- ERROR: "p_measurement_system" is not partitionedThis was to be expected.
Attempt 2 – let’s do a wide range of partitioning
CREATE TABLE p_measurement_system (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
CREATE TABLE p_measurement_system_p1 PARTITION OF p_measurement_system DEFAULT;
CREATE TABLE p_measurement_system_p2 PARTITION OF p_measurement_system FOR VALUES FROM ('1900-01-01') TO ('2999-01-01');
insert into p_measurement_system_p2 values (1,'2021-01-03',1,1);
CREATE TABLE p_measurement_system_p3 PARTITION OF p_measurement_system FOR VALUES FROM ('1901-01-01') TO ('2999-01-01');
ERROR: partition "p_measurement_system_p3" would overlap partition "p_measurement_system_p2"
LINE 1: ...ARTITION OF p_measurement_system FOR VALUES FROM ('1901-01-0...
Ok, boundaries are verified. In fact, it limits us to one partition in the table (a total of 2 – one for values and one default for out of range values). No advantage of partitioning and it is not compliant with point 3 from system partitions list. The conclusion is that system partitioning for declarative partitioning cannot be done.
What does this look like for table inheritance?
Here we can try to take advantage of the fact that we are fully responsible for guarding the boundaries between partitions.
CREATE TABLE p_measurement_system (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE p_measurement_system_p1 (
CHECK ( 1 = 1 )
) INHERITS (p_measurement_system);
CREATE TABLE p_measurement_system_p2 (
CHECK ( 1 = 1 )
) INHERITS (p_measurement_system);
insert into p_measurement_system_p1 values (1,'2021-01-03',1,1);
insert into p_measurement_system_p2 values (2,'2021-01-04',2,2);
select count(*) from p_measurement_system;
-- Returns 2 rowsAs in Oracle, we have to manually specify partition we would like to insert data.
Ok. Is there any change in DELETE / UPDATE syntax? No.
testdb=# delete from p_measurement_system_p1;
DELETE 1
testdb=# delete from p_measurement_system where city_id = 1;
DELETE 0
We don’t have to change syntax for DML (of course excluding INSERTs).
There is one difference between the Oracle implementation and the one presented here.
For table inheritance PostgreSQL planner always has to include possibility of data existence in root table.
explain delete from p_measurement_system where city_id = 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Delete on p_measurement_system (cost=0.00..66.34 rows=0 width=0)
Delete on p_measurement_system p_measurement_system_1
Delete on p_measurement_system_p1 p_measurement_system_2
Delete on p_measurement_system_p2 p_measurement_system_3
-> Append (cost=0.00..66.34 rows=19 width=10)
-> Seq Scan on p_measurement_system p_measurement_system_1 (cost=0.00..0.00 rows=1 width=10)
Filter: (city_id = 1)
-> Seq Scan on p_measurement_system_p1 p_measurement_system_2 (cost=0.00..33.12 rows=9 width=10)
Filter: (city_id = 1)
-> Seq Scan on p_measurement_system_p2 p_measurement_system_3 (cost=0.00..33.12 rows=9 width=10)
Filter: (city_id = 1)
In Oracle when we try insert to table with system partitioning we have below.
insert into sales values (1,100,2)
SQL Error: ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method
14701. 00000 - "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
*Cause: User attempted not to use partition-extended syntax
for a table partitioned by the System method
*Action: Must use of partition-extended syntax in contexts mentioned above.In PostgreSQL it is allowed
testdb=# insert into p_measurement_system values (3,'2019-01-04',3,1);
INSERT 0 1
If we want to have consistent behavior among both database engines we could use insert trigger.
create or replace function no_insert_p_measurement_system() returns trigger language plpgsql as $$
begin
RAISE 'Cannot insert data directly into p_measurement_system table';
return null;
end $$;
create or replace trigger inserting before insert on p_measurement_system for each row execute procedure no_insert_p_measurement_system();Let’s try to insert data there:
testdb=# select count(*) from p_measurement_system;
count
-------
2
(1 row)
testdb=# insert into p_measurement_system values (3,'2011-01-03',3,3);
ERROR: Cannot insert data directly into p_measurement_system table
CONTEXT: PL/pgSQL function no_insert_p_measurement_system() line 3 at RAISE
testdb=# select count(*) from p_measurement_system;
count
-------
2
(1 row)
We have workaround which is compliant with all system partitioning requirements. Oracle implementation is consistent, PostgreSQL more stapled, but looks good.
Yes, a lot of additional administration overhead associated with table inheritance, but because we don’t have routing trigger for each inserted row, performance penalty should not be significant.
Tomasz Ziss Oracle DBA blog 