PostgreSQL Column alter Test, 속도 테스트

PostgreSQL에서 Table을 Alter 했을 때 속도를 테스트 해보자.

 

1. 파티션 테이블을 만든다

 

CREATE TABLE test(
No INTEGER,
ID BIGINT,
Sigma NUMERIC(22,2),
AddTime TIMESTAMP
) PARTITION BY RANGE (Addtime);

 

 

2. 7일짜리 Range Partition을 생성한다.

 

CREATE TABLE test_20211101 PARTITION OF test FOR VALUES FROM ('20211101') TO ('20211102');

CREATE TABLE test_20211102 PARTITION OF test FOR VALUES FROM ('20211102') TO ('20211103');

CREATE TABLE test_20211103 PARTITION OF test FOR VALUES FROM ('20211103') TO ('20211104');

CREATE TABLE test_20211104 PARTITION OF test FOR VALUES FROM ('20211104') TO ('20211105');

CREATE TABLE test_20211105 PARTITION OF test FOR VALUES FROM ('20211105') TO ('20211106');

CREATE TABLE test_20211106 PARTITION OF test FOR VALUES FROM ('20211106') TO ('20211107');

CREATE TABLE test_20211107 PARTITION OF test FOR VALUES FROM ('20211107') TO ('20211108');

 

3. 파티션이 잘 만들어 졌는지 확인

SELECT relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_tables A, pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND A.tablename = C.relname
    AND nspname !~ '^pg_toast'
    and relname like 'test%'
  ORDER BY pg_total_relation_size(C.oid) desc 

Partition Table 확인

 

4. 데이터 밀어 넣기

700만건을 밀어 넣는 function을 만들었다

DROP FUNCTION IF EXISTS insert_fn;

CREATE OR REPLACE FUNCTION insert_fn(
INOUT poStatus BIGINT
)
LANGUAGE plpgsql    
AS $$
DECLARE lCount BIGINT;
BEGIN
-- ----------------------------------------------------------------
-- Check parameter
-- ----------------------------------------------------------------
lCount := 0;
WHILE lCount < 7000000
LOOP
INSERT INTO test(no, id, sigma, addtime) 
values(
lCount,
lCount + 1,
lCount + 2,
(CASE WHEN lCount < 1000000 THEN '2021-11-01'::TIMESTAMP
WHEN lCount < 2000000 THEN '2021-11-02'::TIMESTAMP
WHEN lCount < 3000000 THEN '2021-11-03'::TIMESTAMP
WHEN lCount < 4000000 THEN '2021-11-04'::TIMESTAMP
WHEN lCount < 5000000 THEN '2021-11-05'::TIMESTAMP
WHEN lCount < 6000000 THEN '2021-11-06'::TIMESTAMP
WHEN lCount < 7000000 THEN '2021-11-07'::TIMESTAMP
END)
);

lCount := lCount +1;

END LOOP;


SELECT lCount
INTO poStatus;

EXCEPTION
   WHEN OTHERS THEN
   ROLLBACK;
END;
$$;

 

5. Function 실행해서 700만건 Table에 반영하기

 

select insert_fn(null);

 

700만건을 날짜별로 100만건씩 밀어 넣는다.. 시간이 걸린다. 내가 Test하는 환경은 VM 가상머신에 설치된 PostgreSQL이기 때문에 다소 느린다..일단 기다리자.. 다 들어 갈 때 까지......

 

35초가 걸렸다.. 좀더 대량 데이터를 밀어 넣는다면  소스 중간에 1000 건씩 Commit 하는 구문을 넣어서 사용하시길...난 이 function을 한번 더 실행해서 1400만건을 Partition Table 부어 넣었다.

 

 

Data 1400만건, Partition Table

테스트 성능

 

그런데 테이블 이름을 변경하고나서 특이할 만한 사실이 발견되었다. Partition 릴레이션은 변경이 안되고 Table 이름만 변경되었다는 사실..위 쿼리중에 3번(Partition 이 잘 만들어 졌는지 확인 쿼리)을 다시 실행해보자..

기존에 만들어 놨던 test 테이블이 test2로 변경되어 있음

 

 

Data 3500만건, Partition Table

성능 비교

 

결론

PostgreSQL 에서 컬럼을 늘리거나 삭제하거나 어떤 행위를 했을 때

Column 형변환이 제일 오래 걸림

나머지 행위는 빠름.

 

이 글이 누군가에게는 도움이 되길 바라며.... 

 

끝~~