Database

[PostgreSQL] 중복데이터 삭제 방법(중복데이터 하나만 남기고 제거)

테이블에 중복된 데이터가 있을 때 중복된 데이터에서 한개만 남기고 나머지를 제거해야 하는 이슈가 있었다. 여기서 중복이라는 것은 유일할 것이라고 생각하는 컬럼의 데이터가 여러개 있을 경우를 말한다. (애초에 pk 또는 unique key를 잡았다면 문제가 없었을 것이다.) DELETE FROM table_name where ctid in ( SELECT A.ctid FROM ( SELECT ctid, ROW_NUMBER() over (PARTITION BY column_name ORDER BY column_name desc) AS num FROM table_name ) A WHERE A.num > 1 ) 중복을 제거할 테이블에서 중복 체크할 컬럼을 PARTITION으로 묶어 ORDER BY 순으로 번호를..

[PostgreSQL] GIN INDEX로 LIKE 검색 성능 개선

Problem 백만건이 넘는 서비스 테이블에 LIKE 연산자를 사용하여 검색하면, 너무나도 늦게 결과값이 나옵니다. 아무리 인내심이 많은 사용자라도 기다릴 수 없는 시간이었습니다. INDEX(인덱스)는 데이터 저장, 수정, 삭제에 대한 성능을 희생시켜 탐색에 대한 성능을 대폭 상승시키는 효과가 있습니다. LIKE 연산자를 사용하는 컬럼에 INDEX를 적용하면, 검색 속도가 빨라 질 것을 기대했지만 전혀 성능이 좋아지지 않았습니다. INDEX에 조사하던 중 아무 타입 지정 없이 INDEX를 적용하면 B-Tree 타입으로 색인을 하는 것을 알았습니다. B-Tree 타입은 인덱스를 적용하는 컬럼의 값을 변형하지 않고 원래의 값을 이용합니다. 따라서 = 연산과 같은 값 자체에 대한 탐색 (single value..

[PostgreSQL] VIEW vs MVIEW(Materialized View)

Meterialized View(MVIEW) 란? MVIEW는 말 그대로 VIEW입니다. 하지만 일반 VIEW는 가상으로 존재하는 논리적 테이블이고, MVIEW는 실제 존재하는 TABLE처럼 물리적으로 존재하는 테이블입니다. 데이터가 물리적으로 존재하기 때문에 쿼리 실행 시간의 수행속도를 향상 시킬 수 있습니다. 그리고 SUM, MIN, MAX, AVG, COUNT등 그룹 함수를 미리 계산해 놓을 때 많이 사용하고, INDEX를 사용할 수 있다는 것도 장점이 됩니다. VIEW와 Materialized View의 차이점 VIEW Materialized View disk에 저장되지 않는 가상 테이블 기초 테이블을 물리적으로 복사를 하며 디스크에 저장 기초 테이블이 업데이트 될 때마다 VIEW도 업데이트 됨 ..

[PostgreSQL] JSON 컬럼에 특정 값만 추출해서 Index를 걸어보자

JSON 데이터를 PostgreSql에 저장할 때 data type을 json으로 하여 저장을 합니다. 프로젝트를 진행 하던 중 json 데이터의 특정 값만 찾아서 Index를 걸어야 하는 경우가 발생하여 처리 방법을 작성하고 성능까지 비교해보겠습니다. JSON 연산자 PostgreSql에서 data type이 json인 컬럼에 where절을 사용할 때 아래와 같이 쿼리를 사용합니다. -- info column(json data) : { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}} -- json data에서 items의 product가 Diaper인 데이터 조회 쿼리 SELECT info ->> 'customer' AS custo..