JJON's StorY

- 버젼 : Aurora PostgreSQL 13.12

파티션 테이블 관련하여 튜닝을 진행하였는데, 운영에서 이전 보다 오히려 성능이 떨어지는 상황이 발생하였다.

이에 대해 정리 해본다.

대략적인 테이블 구조

CREATE TABLE test_lgct_inrk_test (
	lgct_inrk_no numeric(16) NOT NULL, 
	lgct_inrk_dtm timestamp NOT NULL, 
	lgct_rcpt_no varchar(20) NULL, 
	lgct_rcpt_dtm timestamp NULL, 
	or_no varchar(15) NULL, 
	or_seq numeric(5) NULL, 
	or_cl_no varchar(15) NULL, 
	dlv_fom_cd varchar(2) NULL, 
	ls_updr_id varchar(20) NOT NULL, 
	ls_updt_dtm timestamp NOT NULL, 
	lgct_inrk_rslt_cd varchar(10) NULL, 
	lgct_inrk_rslt_cn text NULL, 
	CONSTRAINT pk_test_lgct_inrk_test PRIMARY KEY (lgct_inrk_no, lgct_inrk_dtm)
)
PARTITION BY RANGE (lgct_inrk_dtm);
CREATE INDEX ix01_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (or_no, or_seq);
CREATE INDEX ix02_test_lgct_inrk_test ON ONLY test_lgct_inrk_test USING btree (lgct_inrk_dtm, or_no);

-- Partitions
CREATE TABLE test_lgct_inrk_test_pt_202501 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-01-01 00:00:00') TO ('2025-02-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202502 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-02-01 00:00:00') TO ('2025-03-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202503 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-03-01 00:00:00') TO ('2025-04-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202504 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-04-01 00:00:00') TO ('2025-05-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202505 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-05-01 00:00:00') TO ('2025-06-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202506 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-06-01 00:00:00') TO ('2025-07-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202507 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-07-01 00:00:00') TO ('2025-08-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202508 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-08-01 00:00:00') TO ('2025-09-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202509 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-09-01 00:00:00') TO ('2025-10-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202510 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-10-01 00:00:00') TO ('2025-11-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202511 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-11-01 00:00:00') TO ('2025-12-01 00:00:00');
CREATE TABLE test_lgct_inrk_test_pt_202512 PARTITION OF test_lgct_inrk_test  FOR VALUES FROM ('2025-12-01 00:00:00') TO ('2026-01-01 00:00:00');

원본 sql

        update test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'

update 쿼리를 select 쿼리로 변경하여 test를 진행하였고, 아래와 같이 변경 하였다.

튜닝 전 sql

explain analyze
    select ls_updr_id, 
      ls_updt_dtm,
      lgct_rcpt_no,
      lgct_rcpt_dtm,
      lgct_inrk_rslt_cd
    from test_lgct_inrk_test
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01';

QUERY PLAN                                                                                                                                                                                                 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append  (cost=0.43..238.62 rows=12 width=126) (actual time=5.370..8.010 rows=7 loops=1)                                                                                                                    |
  ->  Index Scan using test_lgct_inrk_test_pt_202501_or_no_or_seq_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1  (cost=0.43..47.33 rows=1 width=40) (actual time=3.234..3.234 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202502_or_no_or_seq_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2  (cost=0.43..47.26 rows=1 width=41) (actual time=1.627..1.627 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3  (cost=0.43..50.76 rows=1 width=40) (actual time=0.507..1.068 rows=7 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4  (cost=0.29..27.87 rows=1 width=41) (actual time=1.983..1.983 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5  (cost=0.14..8.17 rows=1 width=170) (actual time=0.013..0.013 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6  (cost=0.14..8.17 rows=1 width=170) (actual time=0.014..0.014 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7  (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1) |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10  (cost=0.14..8.17 rows=1 width=170) (actual time=0.012..0.012 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11  (cost=0.14..8.17 rows=1 width=170) (actual time=0.011..0.011 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
  ->  Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12  (cost=0.14..8.17 rows=1 width=170) (actual time=0.010..0.010 rows=0 loops=1)|
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                     |
Planning Time: 3.169 ms                                                                                                                                                                                    |
Execution Time: 8.119 ms                                                                                                                                                                                   |

튜닝 후 sql

explain analyze
		select ls_updr_id, 
			ls_updt_dtm,
			lgct_rcpt_no,
			lgct_rcpt_dtm,
			lgct_inrk_rslt_cd
		from test_lgct_inrk_test
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                  and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Append  (cost=0.44..161.47 rows=12 width=126) (actual time=0.036..0.070 rows=7 loops=1)                                                                                                                                                                                                      |
  Subplans Removed: 10                                                                                                                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1  (cost=0.43..51.06 rows=1 width=40) (actual time=0.036..0.055 rows=7 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2  (cost=0.29..27.97 rows=1 width=41) (actual time=0.013..0.013 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 1.031 ms                                                                                                                                                                                                                                                                      |
Execution Time: 0.112 ms                                                                                                                                                                                                                                                                     |

and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                     and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

조건을 추가하여 partition pruning 이 동작하도록 하였다.

이렇게 마무리 하였고, 운영에 적용 하였더니 성능이 미세하게 더 나빠졌다.

다시 확인

explain analyze
        update dis01.test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD')
                                  and to_timestamp(substr('2025033013890', 1, 8), 'YYYYMMDD') + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                                   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test  (cost=0.44..161.53 rows=12 width=339) (actual time=0.571..0.573 rows=0 loops=1)                                                                                                                                                                               |
  Update on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9                                                                                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10                                                                                                                                                                                                                             |
  Update on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11                                                                                                                                                                                                                             |
  Update on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12                                                                                                                                                                                                                             |
  ->  Index Scan using test_lgct_inrk_test_pt_202501_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202501 test_lgct_inrk_test_1  (cost=0.44..8.47 rows=1 width=310) (actual time=0.011..0.011 rows=0 loops=1)                                                                            |
        Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text))                                                         |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202502_lgct_inrk_dtm_or_no_idx on test_lgct_inrk_test_pt_202502 test_lgct_inrk_test_2  (cost=0.44..8.47 rows=1 width=310) (actual time=0.012..0.012 rows=0 loops=1)                                                                            |
        Index Cond: ((lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)) AND ((or_no)::text = '2025033013890'::text))                                                         |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))                                                                                                                                                                       |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_3  (cost=0.43..51.07 rows=1 width=310) (actual time=0.026..0.042 rows=7 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_4  (cost=0.29..27.98 rows=1 width=310) (actual time=0.018..0.018 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202505_or_no_or_seq_idx on test_lgct_inrk_test_pt_202505 test_lgct_inrk_test_5  (cost=0.14..8.19 rows=1 width=354) (actual time=0.012..0.012 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202506_or_no_or_seq_idx on test_lgct_inrk_test_pt_202506 test_lgct_inrk_test_6  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202507_or_no_or_seq_idx on test_lgct_inrk_test_pt_202507 test_lgct_inrk_test_7  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202508_or_no_or_seq_idx on test_lgct_inrk_test_pt_202508 test_lgct_inrk_test_8  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202509_or_no_or_seq_idx on test_lgct_inrk_test_pt_202509 test_lgct_inrk_test_9  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                   |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202510_or_no_or_seq_idx on test_lgct_inrk_test_pt_202510 test_lgct_inrk_test_10  (cost=0.14..8.19 rows=1 width=354) (actual time=0.013..0.013 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202511_or_no_or_seq_idx on test_lgct_inrk_test_pt_202511 test_lgct_inrk_test_11  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
  ->  Index Scan using test_lgct_inrk_test_pt_202512_or_no_or_seq_idx on test_lgct_inrk_test_pt_202512 test_lgct_inrk_test_12  (cost=0.14..8.19 rows=1 width=354) (actual time=0.010..0.010 rows=0 loops=1)                                                                                  |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                                                  |
        Filter: (((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text) AND (lgct_inrk_dtm >= to_timestamp('20250330'::text, 'YYYYMMDD'::text)) AND (lgct_inrk_dtm <= (to_timestamp('20250330'::text, 'YYYYMMDD'::text) + '5 days'::interval)))|
Planning Time: 18.737 ms                                                                                                                                                                                                                                                                     |
Execution Time: 0.962 ms                                                                                                                                                                                                                                                                     |

partition pruning이 되지 않는다.

여기 저기 검색해보니, postgreSQL의 경우 update시 partition pruning 제약 사항이 상당히 있어 보인다.

서브 쿼리 X
함수 사용 X
예약어 X

explain analyze
        update dis01.test_lgct_inrk_test
        set ls_updr_id = 'ADMIN'
        , ls_updt_dtm = now()
            , lgct_rcpt_no = '3654573'
            , lgct_rcpt_dtm = now()
            , lgct_inrk_rslt_cd = '9999' 
        where 1=1
        and or_no = '2025033013890'
        and coalesce(or_cl_no,'') = coalesce(null,'')
        and dlv_fom_cd = '01'
            and lgct_inrk_dtm between '20250330'::timestamp
                                  and '20250330'::timestamp + interval '5 day';

QUERY PLAN                                                                                                                                                                                                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Update on test_lgct_inrk_test  (cost=0.43..78.82 rows=2 width=310) (actual time=0.371..0.372 rows=0 loops=1)                                                                                                                                                              |
  Update on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1                                                                                                                                                                                                           |
  Update on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2                                                                                                                                                                                                           |
  ->  Index Scan using test_lgct_inrk_test_pt_202503_or_no_or_seq_idx on test_lgct_inrk_test_pt_202503 test_lgct_inrk_test_1  (cost=0.43..50.89 rows=1 width=310) (actual time=0.027..0.040 rows=7 loops=1)                                                               |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                               |
        Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
  ->  Index Scan using test_lgct_inrk_test_pt_202504_or_no_or_seq_idx on test_lgct_inrk_test_pt_202504 test_lgct_inrk_test_2  (cost=0.29..27.92 rows=1 width=310) (actual time=0.013..0.014 rows=0 loops=1)                                                               |
        Index Cond: ((or_no)::text = '2025033013890'::text)                                                                                                                                                                                                               |
        Filter: ((lgct_inrk_dtm >= '2025-03-30 00:00:00'::timestamp without time zone) AND (lgct_inrk_dtm <= '2025-04-04 00:00:00'::timestamp without time zone) AND ((COALESCE(or_cl_no, ''::character varying))::text = ''::text) AND ((dlv_fom_cd)::text = '01'::text))|
Planning Time: 0.375 ms                                                                                                                                                                                                                                                   |
Execution Time: 0.407 ms                                                                                                                                                                                                                                                  |

.

Amazon EventBridge Scheduler 를 통하여 lambda 함수를 호출하여, 특정 시간에 dms를 기동, 중지 하려고 합니다.

1. dms 기동 함수

import boto3

# DMS 클라이언트 초기화
dms_client = boto3.client('dms')

def lambda_handler(event, context):
    # DMS Task 이름
    dms_task_name = "dms-one"
    
    try:
        # DMS Task 시작 요청
        response = dms_client.start_replication_task(
            ReplicationTaskArn='arn:aws:dms:ap-northeast-2:344:task:dms-one',  # DMS Task ARN을 설정
            StartReplicationTaskType='reload-target'  # 전체 로드 후 지속적 복제
        )
        
        # 작업 결과 로그
        return {
            "statusCode": 200,
            "body": {
                "message": f"DMS Task '{dms_task_name}' has been started successfully.",
                "response": response
            }
        }
    except Exception as e:
        return {
            "statusCode": 500,
            "body": {
                "message": f"Failed to start DMS Task '{dms_task_name}'.",
                "error": str(e)
            }
        }

2. dms 중지 함수

import boto3

# DMS 클라이언트 초기화
dms_client = boto3.client('dms')

def lambda_handler(event, context):
    # DMS Task 이름
    dms_task_name = "dms-one"
    
    try:
        # DMS Task 중지 요청
        response_stop = dms_client.stop_replication_task(
            ReplicationTaskArn='arn:aws:dms:ap-northeast-2:344:task:dms-one'
        )
        
        # 작업 결과 로그
        return {
            "statusCode": 200,
            "body": {
                "message": f"DMS Task '{dms_task_name}' has been stopped successfully.",
                "response": response_stop
            }
        }
    except Exception as e:
        return {
            "statusCode": 500,
            "body": {
                "message": f"Failed to stop DMS Task '{dms_task_name}'.",
                "error": str(e)
            }
        }

3. IAM 역할 설정

{
	"Version": "2020-05-12",
	"Statement": [
		{
			"Effect": "Allow",
			"Action": [
				"lambda:InvokeFunction",
				"dms:CreateReplicationInstance",
				"dms:CreateReplicationTask",
				"dms:StartReplicationTask",
				"dms:StopReplicationTask"
			],
			"Resource": "*"
		}
	]
}

.

DMS로 주기적으로 full load 되는 테이블에 날짜 열을 추가하여, 언제 load 되었는지 확인 하려고 합니다.

DMS는 기본적으로 소스 테이블 구조를 타겟에 복제합니다. 하지만 컬럼 추가 및 특정 값 설정과 같은 추가 작업은 Transformation Rule과 Mapping Rule을 사용해야 합니다.

1. DMS 테스크에 적용

AWS DMS 테스크를 생성하거나 기존 테스크를 수정할 때, 위의 Transformation Rule을 포함시켜야 합니다. 이 설정은 아래와 같이 적용됩니다.

1.1 CLI를 사용하는 경우

TableMappings JSON 파일에 Transformation Rule을 추가합니다

   {
      "rule-type": "transformation",
      "rule-id": "3864735",
      "rule-name": "3864735",
      "rule-target": "column",
      "object-locator": {
        "schema-name": "%",
        "table-name": "%"
      },
      "rule-action": "add-column",
      "value": "dms_time",
      "old-value": null,
      "expression": "datetime ()",
      "data-type": {
        "type": "datetime"
      }
    }

1.2 콘솔에서 설정하는 경우

1. DMS 콘솔에서 테스크를 생성하거나 수정합니다.
2. Table Mapping 설정 단계로 이동합니다.
3. Transformation Rule 섹션에서 위와 같이 'add-column' 작업을 선택하고, 'dms_time' 값을 추가하고 데이터형식을 'datetime'로 설정합니다.

2. 타겟 테이블 사전 준비

DMS는 Transformation Rule로 새 컬럼을 추가하지 않습니다. 따라서, 대상 테이블에 'dms_time' 컬럼을 미리 추가해야 합니다.

ALTER TABLE app.table1 ADD COLUMN dms_time TIMESTAMP ;

3. DMS 테스크 실행

테스크를 실행하면 'dms_time' 컬럼에 DMS 마이그레이션 실행 시점의 타임스탬프가 자동으로 추가됩니다.

4. 추가 고려 사항

• CDC 작업 시: CDC(Change Data Capture)를 활성화한 경우, 삽입된 레코드만 영향을 받습니다. 기존 레코드의 'dms_time' 값은 수동으로 업데이트해야 할 수도 있습니다.
• 대상 DB: 대상 DB가 MySQL, PostgreSQL, Oracle 등 지원 여부를 확인하세요. 데이터 형식이나 함수(CURRENT_TIMESTAMP)가 호환되지 않을 수 있습니다.

기타 변환 규칙은 공식 문서 참조하세요
https://docs.aws.amazon.com/ko_kr/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Expressions.html

.

현재 CDC 사용중에 복제 delay 가 종종 발생하여, 임계치에 대한 기록을 남긴다.

복제 인스턴스 사양 : dms.t3.large

복제 작업 지표

CDCIncomingChanges : 대상에 적용되기를 기다리고 있는 특정 시점의 총 변경 이벤트 수입니다. 이 값은 소스 엔드포인트의 트랜잭션 변경 비율 측정치와 같지 않습니다. 이 측정치의 값이 크면 일반적으로 AWS DMS가 캡처한 변경 사항을 적시에 적용할 수 없어서 대상 지연 시간이 길어짐을 의미합니다.

CDCThroughputRowsSource : 원본의 수신 작업 수신 변경 사항 개수(초당 행 수 단위).

CDCThroughputRowsTarget : 대상의 발신 작업 변경 사항 개수(초당 행 수 단위).

CDCLatencySource : 소스 엔드포인트에서 캡처한 마지막 이벤트와 AWS DMS 인스턴스의 현재 시스템 타임스탬프 간의 간격(초)입니다. CDCLatencySource는 소스 인스턴스와 복제 인스턴스 간의 지연 시간을 나타냅니다. CDCLatencySource가 높으면 소스에서 변경 사항을 캡처하는 프로세스가 지연되었음을 나타냅니다. 지속적 복제의 지연 시간을 식별하기 위해 이 지표를 CDCLatencyTarget과 함께 확인할 수 있습니다. CDCLatencySource와 CDCLatencyTarget이 모두 높으면 CDCLatencySource를 먼저 조사하세요.

소스 인스턴스와 복제 인스턴스 간에 복제 지연이 없는 경우 CDCSourceLatency는 0이 될 수 있습니다. 복제 태스크에서 소스의 트랜잭션 로그에서 다음 이벤트를 읽으려고 시도하고 소스에서 마지막으로 읽은 때와 비교하여 새 이벤트가 없는 경우에도 CDCSourceLatency는 0이 될 수 있습니다. 이 경우 태스크는 CDCSourceLatency를 0으로 재설정합니다.

CDCLatencyTarget : 대상에 커밋되기를 대기하는 첫 이벤트 타임스탬프와 AWS DMS 인스턴스의 현재 타임스탬프 사이의 간격(초)입니다. 대상 지연 시간은 복제 인스턴스 서버 시간과 대상 구성 요소에 전달된 확인되지 않은 가장 오래된 이벤트 ID 간의 차이입니다. 즉, 대상 지연 시간은 복제 인스턴스와 적용되었지만 TRG 엔드포인트에서 확인되지 않은 가장 오래된 이벤트 간의 타임스탬프 차이입니다(99%). CDCLatencyTarget이 높으면 대상에 변경 이벤트를 적용하는 프로세스가 지연되었음을 나타냅니다. 지속적 복제의 지연 시간을 식별하기 위해 이 지표를 CDCLatencySource와 함께 확인할 수 있습니다. CDCLatencyTarget은 높지만 CDCLatencySource는 높지 않은 경우 다음을 조사하세요.

.

.