close
프로필 배경
프로필 로고

XX아~ 열심히 하지마~. 잘하란 말야~

Blog/DB · 2024. 7. 15. fullscreen 넓게보기

[PostgreSQL] 우편번호 Data를 사용하기 위한 Partition Table 만들기

   PostgreSQL을 사용하여 간단하게 Java + Spring Boots + Maven + myBatis Framework을 갖고 우편번호 검색 Web Page 및 RESTFul-API를 제공하는 Project를 생성하는 과정을 포스팅하려고 준비하는데 가장 먼저 Data를 준비하기 위하여 한국 우편번호 Data를 Sample Data로 했다.   그 과정에서 대표적으로 많이 사용되는 PostgreSQL 과 MariaDB를 같은 장비에 Default 설정으로 설치하고 같은 내용의 Data를 저장한 후 조회 했을 때 성능이 어떻게 차이 나는지 궁금해서 비교해보고자 한다.

   우편번호 고시파일은 우체국 사이트에 가면 최신으로 다운받을 수 있다.
  https://www.epost.go.kr/search/zipcode/areacdAddressDown.jsp

 

우편번호검색 - 우편번호 내려받기

 

www.epost.go.kr

   지역별 주소 DB를 다운로드 받아서 사용하면 된다.

[    컬럼에 대한 안내문이 HWP 파일로 포함되어 있다.   (한국은 언제까지 hwp 파일을 쓰려고 하는지... 정말 끈질기다.) ]

 

[    지역별로 구분되어 우편번호 데이터가 txt 파일로 구성되어 있다.]

 

[ CSV 파일 형태로 구분자는 ❘ 로 구분되어 있댜. ]

 

   일단 파티셔닝을 하기 위해서 기준을 선정했다.   먼저 파일명에서 보는 것 처럼 지역을 기준으로 파티셔닝을 할 예정이다.   내가 받아서 입력한 Data - 2024.07.15 - 기준 전체 Record 수는 6,422,465 건이다.   요즘 DB의 하드웨어를 생각하면 파티셔닝을 해야할 이유가 없는 수준의 Record 양이다. 하지만 학습차원에서 파티셔닝을 진행하고자 하는 것이고 나의 경우엔 VMM에 설치된 Linux 위에 PostgreSQL을 설치해서 사용하고 있기 때문에 매우 열악한 스펙에서 DB가 개발DB가 운영되고 있다.   그래서 일반테이블과 파티션 테이블의 사용이 더 눈에 띄게 성능 차이를 보일 것이라고 예상한다.
   일단 지역별로 파티셔닝을 해서 테이블을 구분하고자 한다.   파티셔닝 기준은 위의 CSV파일에서 보이는 "시도"를 기준으로 구분할 것이다.   파일 자체도 "시도"를 기준으로 나뉘어져 있다.   그러면 친절하게 시도를 기준으로 나뉘어져 있는 파일을 파티션테이블 이름을 지정해야 하니 메인 테이블명 뒤에 해당 "시도"의 DDD 번호를 붙여서 지정하는 것으로 방향을 잡고 시작하겠다.

   Table Create Script 
   테이블 생성 스크립트를 아래에서 확인하자.   나의 경우 Schemas를 "Study"로 했고 테이블 명을 tb0000_zipcode 로 하기로 했다.   나머지 컬럼명은 한글을 그대로 영문으로 옮겼다.

PostgreSQL DDL

SET search_path TO "Study";

SHOW search_path;

DROP TABLE IF EXISTS tb0000_zipcode;

DROP Sequence IF EXISTS tb0000_zipcode_seq;

CREATE SEQUENCE tb0000_zipcode_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

CREATE TABLE tb0000_zipcode (
	seq int4 DEFAULT nextval('tb0000_zipcode_seq'::regclass) NOT NULL,
    zipcode bpchar(5) COLLATE "ko_KR.utf8" NOT NULL,
	sido varchar(20) COLLATE "ko_KR.utf8" NOT NULL,
	sido_eng varchar(40) COLLATE "ko_KR.utf8" NOT NULL,
	sigugun varchar(20) COLLATE "ko_KR.utf8" NOT NULL,
	sigugun_eng varchar(40) COLLATE "ko_KR.utf8" NOT NULL,
	eupmyun varchar(20) COLLATE "ko_KR.utf8" NOT NULL,
	eupmyun_eng varchar(40) COLLATE "ko_KR.utf8" NOT NULL,
	roadname_cd varchar(12) COLLATE "ko_KR.utf8" NOT NULL,
	roadname varchar(80) COLLATE "ko_KR.utf8" NOT NULL,
	roadname_eng varchar(80) COLLATE "ko_KR.utf8" NOT NULL,
	approval_yn bpchar(1) COLLATE "ko_KR.utf8" NOT NULL,
	building_main_no varchar(5) COLLATE "ko_KR.utf8" NOT NULL,
	building_sub_no varchar(5) COLLATE "ko_KR.utf8" NOT NULL,
	building_mng_no varchar(25) COLLATE "ko_KR.utf8" NOT NULL,
	bulk_dev_name varchar(40) COLLATE "ko_KR.utf8" NULL,
	district_use_name varchar(200) COLLATE "ko_KR.utf8" NOT NULL,
	admin_district_cd varchar(10) COLLATE "ko_KR.utf8" NOT NULL,
	admin_district_name varchar(20) COLLATE "ko_KR.utf8" NOT NULL,
	ri varchar(20) COLLATE "ko_KR.utf8" NOT NULL,
	admin_dong_name varchar(40) COLLATE "ko_KR.utf8" NOT NULL,
	mountain_yn bpchar(1) COLLATE "ko_KR.utf8" NOT NULL,
	land_lot_main_no bpchar(4) COLLATE "ko_KR.utf8" NOT NULL,
	eupmyundong_serial bpchar(2) COLLATE "ko_KR.utf8" NOT NULL,
	land_lot_sub_no bpchar(4) COLLATE "ko_KR.utf8" NOT NULL,
	old_zipcode bpchar(6) COLLATE "ko_KR.utf8" NULL,
	zipcode_serial bpchar(3) COLLATE "ko_KR.utf8" NULL,
    
	CONSTRAINT pk_tb0000_zipcode_seq_sido PRIMARY KEY (seq, sido)
)
PARTITION BY LIST (sido);

COMMENT ON TABLE tb0000_zipcode IS '우편번호';

-- Column comments
COMMENT ON COLUMN tb0000_zipcode.seq 					IS 'Sequence';
COMMENT ON COLUMN tb0000_zipcode.zipcode 				IS '구역번호';
COMMENT ON COLUMN tb0000_zipcode.sido 					IS '시도';
COMMENT ON COLUMN tb0000_zipcode.sido_eng 				IS '시도영문';
COMMENT ON COLUMN tb0000_zipcode.sigugun 				IS '시군구';
COMMENT ON COLUMN tb0000_zipcode.sigugun_eng 			IS '시군구영문';
COMMENT ON COLUMN tb0000_zipcode.eupmyun 				IS '읍면';
COMMENT ON COLUMN tb0000_zipcode.eupmyun_eng 			IS '읍면영문';
COMMENT ON COLUMN tb0000_zipcode.roadname_cd 			IS '도로명코드';
COMMENT ON COLUMN tb0000_zipcode.roadname 				IS '도로명';
COMMENT ON COLUMN tb0000_zipcode.roadname_eng 			IS '도로명영문';
COMMENT ON COLUMN tb0000_zipcode.approval_yn			IS '지하여부';
COMMENT ON COLUMN tb0000_zipcode.building_main_no		IS '건물번호본번';
COMMENT ON COLUMN tb0000_zipcode.building_sub_no		IS '건물번호부번';
COMMENT ON COLUMN tb0000_zipcode.building_mng_no		IS '건물관리번호';
COMMENT ON COLUMN tb0000_zipcode.bulk_dev_name			IS '다량배달처명';
COMMENT ON COLUMN tb0000_zipcode.district_use_name		IS '시군구용건물명';
COMMENT ON COLUMN tb0000_zipcode.admin_district_cd		IS '법정동코드';
COMMENT ON COLUMN tb0000_zipcode.admin_district_name	IS '법정동명';
COMMENT ON COLUMN tb0000_zipcode.ri						IS '리명';
COMMENT ON COLUMN tb0000_zipcode.admin_dong_name		IS '행정동명';
COMMENT ON COLUMN tb0000_zipcode.mountain_yn			IS '산여부';
COMMENT ON COLUMN tb0000_zipcode.land_lot_main_no		IS '지번본번';
COMMENT ON COLUMN tb0000_zipcode.eupmyundong_serial		IS '읍면동일련번호';
COMMENT ON COLUMN tb0000_zipcode.land_lot_sub_no		IS '지번부번';
COMMENT ON COLUMN tb0000_zipcode.old_zipcode			IS '구 우편번호';
COMMENT ON COLUMN tb0000_zipcode.zipcode_serial			IS '우편번호일련번호';


CREATE INDEX idx_tb0000_zipcode_eupmyun		ON ONLY tb0000_zipcode USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_roadname	ON ONLY tb0000_zipcode USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_sido		ON ONLY tb0000_zipcode USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_sigugun		ON ONLY tb0000_zipcode USING btree (sigugun);


-- tb0000_zipcode_02 definition
CREATE TABLE tb0000_zipcode_02 PARTITION OF tb0000_zipcode  FOR VALUES IN ('서울특별시');
CREATE INDEX idx_tb0000_zipcode_02_eupmyun		ON tb0000_zipcode_02 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_02_roadname		ON tb0000_zipcode_02 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_02_sido			ON tb0000_zipcode_02 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_02_sigugun		ON tb0000_zipcode_02 USING btree (sigugun);


-- tb0000_zipcode_031 definition
CREATE TABLE tb0000_zipcode_031 PARTITION OF tb0000_zipcode  FOR VALUES IN ('경기도');
CREATE INDEX idx_tb0000_zipcode_031_eupmyun		ON tb0000_zipcode_031 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_031_roadname	ON tb0000_zipcode_031 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_031_sido		ON tb0000_zipcode_031 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_031_sigugun		ON tb0000_zipcode_031 USING btree (sigugun);


-- tb0000_zipcode_032 definition
CREATE TABLE tb0000_zipcode_032 PARTITION OF tb0000_zipcode  FOR VALUES IN ('인천광역시');
CREATE INDEX idx_tb0000_zipcode_032_eupmyun		ON tb0000_zipcode_032 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_032_roadname	ON tb0000_zipcode_032 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_032_sido		ON tb0000_zipcode_032 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_032_sigugun		ON tb0000_zipcode_032 USING btree (sigugun);


-- tb0000_zipcode_033 definition
CREATE TABLE tb0000_zipcode_033 PARTITION OF tb0000_zipcode  FOR VALUES IN ('강원특별자치도');
CREATE INDEX idx_tb0000_zipcode_033_eupmyun		ON tb0000_zipcode_033 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_033_roadname	ON tb0000_zipcode_033 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_033_sido		ON tb0000_zipcode_033 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_033_sigugun		ON tb0000_zipcode_033 USING btree (sigugun);


-- tb0000_zipcode_041 definition
CREATE TABLE tb0000_zipcode_041 PARTITION OF tb0000_zipcode  FOR VALUES IN ('충청남도');
CREATE INDEX idx_tb0000_zipcode_041_eupmyun		ON tb0000_zipcode_041 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_041_roadname	ON tb0000_zipcode_041 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_041_sido		ON tb0000_zipcode_041 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_041_sigugun		ON tb0000_zipcode_041 USING btree (sigugun);


-- tb0000_zipcode_042 definition
CREATE TABLE tb0000_zipcode_042 PARTITION OF tb0000_zipcode  FOR VALUES IN ('대전광역시');
CREATE INDEX idx_tb0000_zipcode_042_eupmyun		ON tb0000_zipcode_042 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_042_roadname	ON tb0000_zipcode_042 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_042_sido		ON tb0000_zipcode_042 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_042_sigugun		ON tb0000_zipcode_042 USING btree (sigugun);


-- tb0000_zipcode_043 definition
CREATE TABLE tb0000_zipcode_043 PARTITION OF tb0000_zipcode  FOR VALUES IN ('충청북도');
CREATE INDEX idx_tb0000_zipcode_043_eupmyun		ON tb0000_zipcode_043 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_043_roadname	ON tb0000_zipcode_043 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_043_sido		ON tb0000_zipcode_043 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_043_sigugun		ON tb0000_zipcode_043 USING btree (sigugun);


-- tb0000_zipcode_044 definition
CREATE TABLE tb0000_zipcode_044 PARTITION OF tb0000_zipcode  FOR VALUES IN ('세종특별자치시');
CREATE INDEX idx_tb0000_zipcode_044_eupmyun		ON tb0000_zipcode_044 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_044_roadname	ON tb0000_zipcode_044 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_044_sido		ON tb0000_zipcode_044 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_044_sigugun		ON tb0000_zipcode_044 USING btree (sigugun);


-- tb0000_zipcode_051 definition
CREATE TABLE tb0000_zipcode_051 PARTITION OF tb0000_zipcode  FOR VALUES IN ('부산광역시');
CREATE INDEX idx_tb0000_zipcode_051_eupmyun		ON tb0000_zipcode_051 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_051_roadname	ON tb0000_zipcode_051 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_051_sido		ON tb0000_zipcode_051 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_051_sigugun		ON tb0000_zipcode_051 USING btree (sigugun);


-- tb0000_zipcode_052 definition
CREATE TABLE tb0000_zipcode_052 PARTITION OF tb0000_zipcode  FOR VALUES IN ('울산광역시');
CREATE INDEX idx_tb0000_zipcode_052_eupmyun		ON tb0000_zipcode_052 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_052_roadname	ON tb0000_zipcode_052 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_052_sido		ON tb0000_zipcode_052 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_052_sigugun		ON tb0000_zipcode_052 USING btree (sigugun);


-- tb0000_zipcode_053 definition
CREATE TABLE tb0000_zipcode_053 PARTITION OF tb0000_zipcode  FOR VALUES IN ('대구광역시');
CREATE INDEX idx_tb0000_zipcode_053_eupmyun		ON tb0000_zipcode_053 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_053_roadname	ON tb0000_zipcode_053 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_053_sido		ON tb0000_zipcode_053 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_053_sigugun		ON tb0000_zipcode_053 USING btree (sigugun);


-- tb0000_zipcode_054 definition
CREATE TABLE tb0000_zipcode_054 PARTITION OF tb0000_zipcode  FOR VALUES IN ('경상북도');
CREATE INDEX idx_tb0000_zipcode_054_eupmyun		ON tb0000_zipcode_054 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_054_roadname	ON tb0000_zipcode_054 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_054_sido		ON tb0000_zipcode_054 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_054_sigugun		ON tb0000_zipcode_054 USING btree (sigugun);


-- tb0000_zipcode_054 definition
CREATE TABLE tb0000_zipcode_055 PARTITION OF tb0000_zipcode  FOR VALUES IN ('경상남도');
CREATE INDEX idx_tb0000_zipcode_055_eupmyun		ON tb0000_zipcode_055 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_055_roadname	ON tb0000_zipcode_055 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_055_sido		ON tb0000_zipcode_055 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_055_sigugun		ON tb0000_zipcode_055 USING btree (sigugun);


-- tb0000_zipcode_061 definition
CREATE TABLE tb0000_zipcode_061 PARTITION OF tb0000_zipcode  FOR VALUES IN ('전라남도');
CREATE INDEX idx_tb0000_zipcode_061_eupmyun		ON tb0000_zipcode_061 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_061_roadname	ON tb0000_zipcode_061 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_061_sido		ON tb0000_zipcode_061 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_061_sigugun		ON tb0000_zipcode_061 USING btree (sigugun);


-- tb0000_zipcode_062 definition
CREATE TABLE tb0000_zipcode_062 PARTITION OF tb0000_zipcode  FOR VALUES IN ('광주광역시');
CREATE INDEX idx_tb0000_zipcode_062_eupmyun		ON tb0000_zipcode_062 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_062_roadname	ON tb0000_zipcode_062 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_062_sido		ON tb0000_zipcode_062 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_062_sigugun		ON tb0000_zipcode_062 USING btree (sigugun);



-- tb0000_zipcode_062 definition
CREATE TABLE tb0000_zipcode_063 PARTITION OF tb0000_zipcode  FOR VALUES IN ('전북특별자치도');
CREATE INDEX idx_tb0000_zipcode_063_eupmyun		ON tb0000_zipcode_063 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_063_roadname	ON tb0000_zipcode_063 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_063_sido		ON tb0000_zipcode_063 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_063_sigugun		ON tb0000_zipcode_062 USING btree (sigugun);


-- tb0000_zipcode_062 definition
CREATE TABLE tb0000_zipcode_064 PARTITION OF tb0000_zipcode  FOR VALUES IN ('제주특별자치도');
CREATE INDEX idx_tb0000_zipcode_064_eupmyun		ON tb0000_zipcode_064 USING btree (eupmyun);
CREATE INDEX idx_tb0000_zipcode_064_roadname	ON tb0000_zipcode_064 USING btree (roadname);
CREATE INDEX idx_tb0000_zipcode_064_sido		ON tb0000_zipcode_064 USING btree (sido);
CREATE INDEX idx_tb0000_zipcode_064_sigugun		ON tb0000_zipcode_064 USING btree (sigugun);

TB0000_ZIPCODE.sql
0.01MB

   테이블 생성을 하고 우체국 사이트에서 받은 파일을 Import 해서 우편번호 테이블을 생성한다.

 

MariaDB DDL

-- Set the default schema
USE db_iamwhatiam;

-- Drop table if exists
DROP TABLE IF EXISTS tb0000_zipcode;

-- Create the main table
CREATE TABLE tb0000_zipcode (
    seq                     INT          AUTO_INCREMENT                        COMMENT 'Sequence',
    zipcode                 CHAR(5)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '구역번호',
    sido                    VARCHAR(20)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '시도',
    sido_eng                VARCHAR(40)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '시도영문',
    sigugun                 VARCHAR(20)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '시군구',
    sigugun_eng             VARCHAR(40)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '시군구영문',
    eupmyun                 VARCHAR(20)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '읍면',
    eupmyun_eng             VARCHAR(40)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '읍면영문',
    roadname_cd             VARCHAR(12)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '도로명코드',
    roadname                VARCHAR(80)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '도로명',
    roadname_eng            VARCHAR(80)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '도로명영문',
    approval_yn             CHAR(1)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '지하여부',
    building_main_no        VARCHAR(5)   COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '건물번호본번',
    building_sub_no         VARCHAR(5)   COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '건물번호부번',
    building_mng_no         VARCHAR(25)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '건물관리번호',
    bulk_dev_name           VARCHAR(40)  COLLATE "utf8mb4_unicode_ci"     NULL COMMENT '다량배달처명',
    district_use_name       VARCHAR(200) COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '시군구용건물명',
    admin_district_cd       VARCHAR(10)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '법정동코드',
    admin_district_name     VARCHAR(20)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '법정동명',
    ri                      VARCHAR(20)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '리명',
    admin_dong_name         VARCHAR(40)  COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '행정동명',
    mountain_yn             CHAR(1)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '산여부',
    land_lot_main_no        CHAR(4)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '지번본번',
    eupmyundong_serial      CHAR(2)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '읍면동일련번호',
    land_lot_sub_no         CHAR(4)      COLLATE "utf8mb4_unicode_ci" NOT NULL COMMENT '지번부번',
    old_zipcode             CHAR(6)      COLLATE "utf8mb4_unicode_ci"     NULL COMMENT '구 우편번호',
    zipcode_serial          CHAR(3)      COLLATE "utf8mb4_unicode_ci"     NULL COMMENT '우편번호일련번호',
    PRIMARY KEY (seq, sido)  -- 주요 키를 sido와 seq로 설정
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
PARTITION BY LIST COLUMNS(sido) (
    PARTITION tb0000_zipcode_02     VALUES IN ('서울특별시'),
    PARTITION tb0000_zipcode_031    VALUES IN ('경기도'),
    PARTITION tb0000_zipcode_032    VALUES IN ('인천광역시'),
    PARTITION tb0000_zipcode_033    VALUES IN ('강원특별자치도'),
    PARTITION tb0000_zipcode_041    VALUES IN ('충청남도'),
    PARTITION tb0000_zipcode_042    VALUES IN ('대전광역시'),
    PARTITION tb0000_zipcode_043    VALUES IN ('충청북도'),
    PARTITION tb0000_zipcode_044    VALUES IN ('세종특별자치시'),
    PARTITION tb0000_zipcode_051    VALUES IN ('부산광역시'),
    PARTITION tb0000_zipcode_052    VALUES IN ('울산광역시'),
    PARTITION tb0000_zipcode_053    VALUES IN ('대구광역시'),
    PARTITION tb0000_zipcode_054    VALUES IN ('경상북도'),
    PARTITION tb0000_zipcode_055    VALUES IN ('경상남도'),
    PARTITION tb0000_zipcode_061    VALUES IN ('전라남도'),
    PARTITION tb0000_zipcode_062    VALUES IN ('광주광역시'),
    PARTITION tb0000_zipcode_063    VALUES IN ('전북특별자치도'),
    PARTITION tb0000_zipcode_064    VALUES IN ('제주특별자치도')
);

ALTER TABLE tb0000_zipcode COMMENT '우편번호';

-- Add indexes
CREATE INDEX idx_tb0000_zipcode_eupmyun     ON tb0000_zipcode (eupmyun)     USING BTREE;
CREATE INDEX idx_tb0000_zipcode_roadname    ON tb0000_zipcode (roadname)    USING BTREE;
CREATE INDEX idx_tb0000_zipcode_sido        ON tb0000_zipcode (sido)        USING BTREE;
CREATE INDEX idx_tb0000_zipcode_sigugun     ON tb0000_zipcode (sigugun)     USING BTREE;

tb0000_zipcode.sql
0.00MB

   나의 경우엔 테스트를 위해서 파티션을 나눈 테이블과 하나에 모두 저장한 테이블 2개를 준비해서 비교하고 대표적으로 많이 사용되는 무료DB를 기준으로 서로 크로스 체크를 해보려고 한다.   물론 설치 환경에 따라 다를 수 있지만 같은 VMM에 Default 설정으로 설치한 MariaDB 와 PostgreSQL의 성능차이를 비교해 볼 수 있을 것이라고 생각해서 테스트해본다.
   지금 설정한 SynologyNAS(DS920+)의 VMM 은 CPU Core 3, Memory 8GB 를 셋팅했다.   성능의 차이를 보여주기에는 충분할 것 같다.   일단 PostgreSQL에서 단일 테이블의 조회와 파티션 테이블의 조회를 비교해 보자.

 단일 테이블을 대상으로 아래의 Query를 실행했을 때의 결과이다.

1. SELECT count(*) FROM tb0000_zipcode;   (6,422,465 건 / Partition Table)
   - postgreSQL : 0.904s
   - MariaDB : 8m 30s

2. SELECT * FROM "Study".tb0000_zipcode WHERE roadname  LIKE '%가덕시동길%';   (53건)
   - postgreSQL  : 5s
   - MariaDB : 1m 58s

 

파티션된 테이블(충청북도)을 대상으로 조회

1. SELECT * FROM "Study".tb0000_zipcode_043 WHERE roadname  LIKE '%가덕시동길%';
   - postgreSQL : 0.244s

2. SELECT * FROM tb0000_zipcode PARTITION (tb0000_zipcode_043) WHERE roadname LIKE '%가덕시동길%';
   - MariaDB : 3s

 

Group By 로 Sido별 Count

[ postgreSQL 3 초 ]
[ MariaDB 52 초 ]


   결론적으로 같은 하드웨어 스펙에서 기본 설정으로 설치한 MariaDB 와 postgreSQL 을 같은 데이터 양과 같은 조건의 Partition을 기준으로 조회 결과를 보면 확연하게 차이가 나는 것을 볼 수 있다.   앞으로는 특별한 이유가 없다면 MariaDB 보다는 postgreSQL을 사용하는게 낫겠다는 결론이 나왔다.   이 테스트는 Full Scan을 하게끔 하는 Query에서 결과를 보여준 것이다.   Server라고 할 만큼의 하드웨어 스펙에서 테스트 한다면 아마도 미미한 차이로 그 차이를 느끼지 못할 수 도 있다.   하지만 저사양의 상황에서 같은 조건에서 저정도의 속도 차이는 특별한 이유가 있지 않는 이상 MariaDB를 고집할 이유는 전혀 없어 보인다.   물론 이건 지극히 개인적인 견해고 극단적인 조건에서의 테스트이기 때문에 참고 자료로 봐주길 바란다.

'Blog > DB' 카테고리의 다른 글

Rocky Linux 에 MariaDB 설치하기  (0) 2024.07.05
Rocky Linux 에 PostgreSQL 설치 및 외부연결 설정  (0) 2024.07.02
ORACLE에서 Long Type의 Column에 대한 Like 검색 Ver.2  (0) 2023.12.08
SqlDeveloper에서 XMLTYPE 값 보기  (0) 2019.01.08
ORACLE에서 LONG TYPE의 COLUMN에 LIKE 조건을 걸기 위해서.. LONG TO CHAR  (0) 2019.01.08
Blog/DB 관련 글
더 보기

티스토리툴바