Partitioning is not free. It comes with its own set of limitations. Some of the limitations with partitioning are the lock-related overhead and increased planning time.
Let's dive into an example to illustrate this point. Here is the script to create the test table and insert some data:
--tested on PostgreSQL 16.3 create table customer ( cust_no int not null, cust_nm character varying(100), register_date timestamp(0), register_dt varchar(8), cust_status_cd varchar(1), register_channel_cd varchar(1), cust_age smallint, active_yn boolean, sigungu_cd varchar(5), sido_cd varchar(2), constraint customer_pk primary key(cust_no) );
insert into customer select i, chr(65+mod(i,26))||i::text||'CUST_NM' , '20240615'::date - mod(i,10000) , to_char(('20240615'::date - mod(i,10000)),'yyyymmdd') as register_dt , mod(i,5)+1 as cust_status_cd , mod(i,2)+1 as register_channel_cd , trunc(random() * 100) +1 as age , case when mod(i,22) = 0 then false else true end as active_yn , case when mod(i,1000) = 0 then '11007' when mod(i,1000) = 1 then '11006' when mod(i,1000) = 2 then '11005' when mod(i,1000) = 3 then '11004' when mod(i,1000) = 4 then '11003' when mod(i,1000) = 5 then '11002' else '11001' end as sigungu_cd , case when mod(i,4) = 0 then '01' when mod(i,4) = 1 then '02' else '03' end as sido_cd
from generate_series(1,1000000) a(i);
Imagine running a business since 1997 and having customer data from every single day since then. The min and max dates from our table are:
select min(register_dt), max(register_dt) from customer; min |max | --------+--------+
19970129|20240615|
To partition this data by each day, we'd create partitions like this:
--I have created a partitioned table "customer_p".
Note that the maximum value of the partition key is '20240615' and the minimum value of the partition key is '19970129'.
The following query creates a script to create the table as a partition of the customer_p table.
select 'create table cust_p_'||to_char('19970129'::date+i,'yyyymmdd')|| ' partition of customer_p for values from ('''||to_char('19970129'::date+i -1,'yyyymmdd')||''|| ''') to ('''||to_char('19970129'::date+i,'yyyymmdd')||''');'
from generate_series(1, 10000) a(i);
The output of the above query is as follows:
create table cust_p_19970130 partition of customer_p for values from ('19970129') to ('19970130'); create table cust_p_19970131 partition of customer_p for values from ('19970130') to ('19970131'); ... ... create table cust_p_20240615 partition of customer_p for values from ('20240614') to ('20240615'); create table cust_p_20240616 partition of customer_p for values from ('20240615') to ('20240616');
You have to run the above query and verify that you have 1000 partitions:
It takes around 3 minuites to complete the creation of the 1000 partitions.
select * from pg_class where relispartition is true and relname like 'cust%';
Now, when you run a simple SELECT query on this partitioned table, you might hit a snag:
select * from customer_p;
SQL Error [53200]: ERROR: out of shared memory Hint: You might need to increase max_locks_per_transaction.
This error occurs because each partition requires its own locks, and the default settings can not handle the load. The max_locks_per_transaction setting controls the number of object locks each transaction can use. By default, it is set to 64, which is not enough for our scenario.
show max_locks_per_transaction; max_locks_per_transaction| -------------------------+ 64 | This experiment shows us that partitioning tables can significantly increase the number of locks in a shared memory area. Operatioins on partitioned tables involve acquring locks on multiple partitions. For example, a query that needs to scan or modify several partitions will require acquring locks for each accessed partition. As the number of partitions increases, so does the potential lock-related overhead.
In order to move on, let's set max_locks_per_transaction to 1000, and restart the PostgreSQL engine. We must change the value in the postgresql.conf file and restart the engine.
Let's insert some data into the partitioned table:
insert into customer_p select * from customer;
The Impact of Partitioning Now let's see the perfomance difference between a partitioned table and a non-partitioned table:
explain (analyze, buffers)
select count(*) from customer; --non-partitioned table
The planning time and execution time for counting rows in a partitioned table are much higher than for a non-partitioned table. The planning time skyrocketed due to the many partitions involved.
Searching by Name Let's compare a simple search by name in both tables:
explain (analyze, buffers)
select * from customer where cust_nm = 'x';
Seq Scan on customer (cost=0.00..23863.00 rows=1 width=51) (actual time=64.356..64.357 rows=0 loops=1) Filter: ((cust_nm)::text = 'x'::text) Rows Removed by Filter: 1000000 Buffers: shared hit=354 read=11009 Planning Time: 0.052 ms Execution Time: 64.371 ms
In the partitioned table, the planning time is significantly higer, and the query execution time is also much longer.
Yearly Partitions To mitigate some of these issues, let's create partitions by year instead of by day:
REATE TABLE scott.customer_y ( cust_no int4 NOT NULL, cust_nm varchar(100) NULL, register_date timestamp(0) NULL, register_dt varchar(8) NULL, cust_status_cd varchar(1) NULL, register_channel_cd varchar(1) NULL, cust_age int2 NULL, active_yn bool NULL, sigungu_cd varchar(5) NULL, sido_cd varchar(2) NULL, CONSTRAINT customer_y_pk PRIMARY KEY (cust_no, register_dt) ) partition by range(register_dt); select 'create table cust_y_'||(1997 +i-1)::text|| ' partition of customer_y for values from ('''||(1997 +i-1)::text||''|| ''') to ('''||(1997 +i)::text||''');' from generate_series(1, 30) a(i); insert into customer_y select * from customer;
Now, let's compare the performance again:
explain (analyze, buffers)
select * from customer where cust_nm = 'x';
Seq Scan on customer (cost=0.00..23863.00 rows=1 width=51) (actual time=64.356..64.357 rows=0 loops=1) Filter: ((cust_nm)::text = 'x'::text) Rows Removed by Filter: 1000000 Buffers: shared hit=354 read=11009 Planning Time: 0.052 ms Execution Time: 64.371 ms
Ring buffer was used.
show shared_buffers; explain (analyze, buffers) select * from customer_y where cust_nm = 'x'; Append (cost=0.00..23894.90 rows=30 width=69) (actual time=60.331..60.341 rows=0 loops=1) Buffers: shared hit=11369 -> Seq Scan on cust_y_1995 customer_y_1 (cost=0.00..356.25 rows=1 width=51) (actual time=0.965..0.965 rows=0 loops=1) Filter: ((cust_nm)::text = 'x'::text) Rows Removed by Filter: 14900 Buffers: shared hit=170 ........ Planning: Buffers: shared hit=2 Planning Time: 0.631 ms
Execution Time: 60.443 ms
With yearly partitions, the planning time is about 10 times longer than the non-partitioned table, but the execution time has dropped.
If the partitioning criteria is not part of the query or if many partitions have to be touched in general, runtime will suffer. It is also important to note that using too many partitions will significantly increase the time the planner needs to do its job.
Having hundreds of partitions can easily lead to a real disaster.
CONCLUSION 1. As the number of partitions in a table increases, the potential lock-related overhead also increases. 2. Using too many partitions increases the planning time significanly. 3. If the partitioning criteria is not part of the query, the query will touch all partitions, resuling in longer runtime.
FOOTNOTE
You might increase the max_connections setting to achieve the same effect of increasing the max_locks_per_transactions value. PostgreSQL calculates the total lock slots as:
