원본 본문으로 이동하기

PostgreSQL 자료형 inet cidr

박용서 - 서론 가리사니에 가입해보신 적이 있다면 처음 가입시 현재 국가를 자동으로 가져오는 것을 볼 수 있습니다. 비정기적으로 https://www.maxmind.com 에서 국가별 IPv4 / IPv6 정보를 가져옵니다. 상당히 오랫동안 IP정보를 갱신하지 않고 있다가, 오늘 다시 갱신하게 되었습니다. 오랜만에 가서 그런지 파일 포멧이 바뀌었습니다. 그전 포멧은 unsgined int 형태의 범위 시작 ~ 종료 국가 이런식이였는데 현재 포멧은 IP대역 형태로 바뀌었습니다. 생각해보면 ISP(인터넷 서비스 제공업체)가 IP 대역 단위로 얻어올 것 같긴 합니다. - 참고 IP 대역 계산 https://gs.saro.me/#!m=elec&jn=219 그래서 IP대역 -> IP범위(시작, 종료) 로 치환하는 것을 만드려다가 PostgreSQL 에 이미 있을 것 같아 조사하던중.. cidr를 알게되었습니다. inet 과 cidr - 참고 : http://www.postgresql.org/docs/9.5/static/datatype-net-types.html inet 는 ip 자료형 입니다 - IPv4, IPv6를 가리지 않음 - 때문에 처음 IP 테이블을 도입했을때, 호환성을 생각하지 않아도 되는 엄청난 메리트가 있었습니다. cidr 는 ip 대역 자료형입니다. - 위 IP 대역계산 참고 - 예를들어 192.168.100.128/25 식으로 넣어 둘 수 있습니다. cidr 내 inet 검색방법 - 참고 : http://www.postgresql.org/docs/9.5/static/functions-net.html 예를들어 대역 >> 요소로 계산할 수 있습니다. SELECT '192.168.1.19'::inet << '192.168.1.0/24'::cidr; SELECT '192.168.1.0/24'::cidr >> '192.168.1.19'::inet; 이 두 값의 결과는 true 입니다. 마이그레이션 - 다행이도 필자는 IP 테이블을 참조하는 함수가 2개밖에 없었기 때문에 순식간에 고칠 수 있었습니다. - 필자는 개인사이트를 만들때... 프로시저/함수의 영향을 많이받았습니다. - 거의 모든 쿼리를 함수(pg-sql에서는 프로시저가 없음으로 함수를 프로시저처럼사용)로 만들어 놓기에 고칠것이 3개 밖에 없었습니다. - 다만... 저 사이트에서 제공해주는 포멧이 바뀌어서 그 리더를 만드는 시간이 대부분이였습니다...;;; 업로드 국가정보 업데이트 처리 완료 [248 rows] 처리시간 : 0.053205199 초 국가미할당 IP : 80. ~~ 비공개 국가미할당 IP : 86. ~~ 비공개 국가미할당 IP : 86. ~~ 비공개 국가미할당 IP : 91. ~~ 비공개 국가미할당 IP : 185. ~~ 비공개 국가미할당 IP : 193. ~~ 비공개 국가미할당 IP : 217. ~~ 비공개 IP-국가 업데이트 처리 완료 [234962 rows] 처리시간 : 53.348162211 초 - 임시 테이블 삽입시간 / 실제 옴겨지면서 원테이블이 비워지고 다시 입력되는 시간은 약 3초정도 - 3초간 IP 조회 결과가 달라질 수 있는데.. 실무에선 어떻게 처리하는지 모르겠네요 음..;; - 드는생각은 컬럼에 버전 넣고.. 넣은다음에 완료되면 이전버전 삭제?;;; SQL - 참조 : http://www.postgresql.org/docs/9.5/static/gist-builtin-opclasses.html - GiST : https://en.wikipedia.org/wiki/GiST -- 위 참조를 참고 : 범위값이기 때문에 GiST 인덱스를 사용하였습니다. CREATE INDEX "인덱스이름" ON public."테이블" USING GIST (net inet_ops); -- 어차피 조회는 많지만 수정은 일어나지 않아 (엄밀히 말하면 통체로 갈아엎는 것 제외) 클러스터로 하였습니다. ALTER TABLE public."테이블" CLUSTER ON "인덱스이름"; 실행코드도 예전같은경우에는 아래와 같이 처리하였습니다. - 플랜 좀 줄여보겠다고.. 이상한 order by 넣고.. 여러분.. 그러지마세요..;; 하하;;; - 아래코드를 만들던 때에 인덱스를 타지않아서 저렇게 처리했던걸로 기억합니다....;;; - 다시 강조하지만.. 일반적으로 저렇게 쓰지마세요..;; 유지보수적으로 잘못된 방법..;; -- 함수내 코드일부 SELECT 결과 FROM "테이블" WHERE 시작IP <= 비교IP -- IP 국가 값이 완벽하게 맞아 떨어지는게 반드시 주요사항이 아니기에 종료를 비교하지 않음. -- 이 주석의 문제로 빈공간의 IP는 그보다 전 공간의 IP 국가가 출력될 수 있음! -- AND -- 종료IP >= 비교IP ORDER BY "시작IP" DESC -- 인덱스 태우려고 이러는거임.!! -- 플랜에서 인덱스를 타지않아 변경 사실, 인덱스를 안타도 속도가 빠름... 아주 약간 더 빨라짐.. LIMIT 1 이번엔 좀더 간결하게 바뀌었습니다. -- 함수내 코드일부 SELECT 결과 FROM "테이블" WHERE IP대역컬럼 >> 비교IP LIMIT 1 실행 SELECT 결과 FROM "테이블" WHERE IP대역컬럼 >> '비교IP'::inet 결과 플랜 Index Scan using "인덱스" on "테이블" (cost=0.28..8.30 rows=1 width=3) Index Cond: ((net)::inet >> '비교IP'::inet) - 속도도 빠르고 인덱스도 타는 것을 확인 할 수 있습니다. - PostgreSQL