설문조사
PostgreSQL/PPAS 관련 듣고 싶은 교육은


총 게시물 167건, 최근 0 건
 

The Price of Partitioning

글쓴이 : 모델광 날짜 : 2024-06-15 (토) 11:42 조회 : 236
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".

set default_tablespace to '';

CREATE TABLE scott.customer_p (
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       boolean NULL,
sigungu_cd      varchar(5) NULL,
sido_cd          varchar(2) NULL,
CONSTRAINT customer_p_pk PRIMARY KEY (cust_no, register_dt)
)
partition by range(register_dt);


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

Aggregate  (cost=23863.00..23863.01 rows=1 width=8) (actual time=116.391..116.392 rows=1 loops=1)
  Buffers: shared hit=64 read=11299
  ->  Seq Scan on customer  (cost=0.00..21363.00 rows=1000000 width=0) (actual time=0.049..74.551 rows=1000000 loops=1)
        Buffers: shared hit=64 read=11299
Planning Time: 0.048 ms
Execution Time: 116.412 ms

explain (analyze, buffers)
select count(*) from customer_p;    --partitioned table

Aggregate  (cost=37500.00..37500.01 rows=1 width=8) (actual time=266.519..268.867 rows=1 loops=1)
  Buffers: shared read=20000
  ->  Append  (cost=0.00..35000.00 rows=1000000 width=0) (actual time=0.056..225.965 rows=1000000 loops=1)
        Buffers: shared read=20000
        ->  Seq Scan on cust_p_19970129 customer_p_1  (cost=0.00..3.00 rows=100 width=0) (actual time=0.055..0.064 rows=100 loops=1)
              Buffers: shared read=2
Planning Time: 123.377 ms
Execution Time: 328.807 ms


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

explain (analyze, buffers)
select * from customer_p where cust_nm = 'x';

Append  (cost=0.00..32550.00 rows=10000 width=51) (actual time=145.442..148.608 rows=0 loops=1)
  Buffers: shared read=20000
  ->  Seq Scan on cust_p_19970129 customer_p_1  (cost=0.00..3.25 rows=1 width=51) (actual time=0.106..0.106 rows=0 loops=1)
        Filter: ((cust_nm)::text = 'x'::text)
        Rows Removed by Filter: 100
        Buffers: shared read=2
Planning Time: 214.453 ms
Execution Time: 205.374 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:

max_locks_per_transaction * (max_connections + max_prepared_transactions)

 

postgresdba.com