서론

가리사니에 가입해보신 적이 있다면 처음 가입시 현재 국가를 자동으로 가져오는 것을 볼 수 있습니다. 비정기적으로 https://www.maxmind.com 에서 국가별 IPv4 / IPv6 정보를 가져옵니다.

상당히 오랫동안 IP정보를 갱신하지 않고 있다가, 오늘 다시 갱신하게 되었습니다. 오랜만에 가서 그런지 파일 포멧이 바뀌었습니다. 그전 포멧은 unsgined int 형태의 범위 시작 ~ 종료 국가 이런식이였는데 현재 포멧은 IP대역 형태로 바뀌었습니다.

생각해보면 ISP(인터넷 서비스 제공업체)가 IP 대역 단위로 얻어올 것 같긴 합니다.

참고 IP 대역 계산 /2015/07/16/%EB%B0%B1%EC%97%85-%EA%B0%80%EB%A6%AC%EC%82%AC%EB%8B%88-IP-%EB%8C%80%EC%97%AD-%EA%B3%84%EC%82%B0.html

그래서 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)

속도도 빠르고 인덱스도 타는 것을 확인 할 수 있습니다.

다음글: [백업][가리사니] 함수형 프로그래밍
이전글: [백업][가리사니] javaee mvc 패턴