불곰 불곰

DB/PostgreSQL

• [PostgreSQL] 시간, 숫자, 날짜 생성하기 (generate_series)

  DB/PostgreSQL 2022. 2. 20. 16:40

  파이썬과 같은 프로그래밍 언어에서 1~10까지의 수를 생성하거나 2022-01-01 ~ 2022-01-10 까지의 날짜를 생성하는 것은 반복문을 통해 쉽게 만들 수 있습니다. postgresql에서도 generate_series 함수를 사용하면 이러한 문제를 쉽게 해결할 수 있습니다. generate_series(start, stop, [step 또는 interval)) 3번째 인자는 입력된 타입이 int인지 timestamp인지에 따라서 step 이거나 inteval을 줄 수 있습니다. SELECT * FROM generate_series(2,4); generate_series ----------------- 2 3 4 (3 rows) SELECT * FROM generate_series(5,1,-2)..

• [PostgreSQL] limit과 offset의 성능 저하 줄이기

  DB/PostgreSQL 2021. 10. 30. 18:30

  보통 pagination을 구현할 때, SQL의 limit과 offset을 많이 사용합니다. 테이블의 레코드 수가 크지 않다면 문제가 되진 않지만 몇십만건 이상일 경우, 성능 저하가 올 수 있습니다. 아래는 1200만건 정도의 레코드가 있는 테이블에서 limit, offset으로 특정 구간을 pagination한 쿼리입니다. select * from orders limit 10000 offset 10000000; offset으로 10,000,000을 주었기 때문에,1000만건을 full scan 후 limit으로 주어진 10,000건을 조회하여 보여주고 있습니다. 위의 쿼리는 정렬순서가 없어서 테이블에 저장되어 있는 순서대로 추출했지만 아래와 같이 pk나 다른 키로 정렬을 준다면 수행시간이 더 늘어나는 ..

• [PostgreSQL] Vacuum

  DB/PostgreSQL 2021. 4. 4. 18:41

  pg에서는 주기적인 Vacuum이 필요합니다. Vacuum은 진공 청소기라는 뜻 그대로 더이상 사용하지 않는 데이터를 정리해주는 역할을 합니다. 즉, 디스크 조각 모음과 같습니다. pg는 다중 버전 동시성 제어(MVCC)를 지원하기 때문에 데이터의 삭제, 수정이 발생하면 더이상 사용하지 않는 여러 버전의 데이터가 존재합니다. 만약 Vacuum을 진행하지 않으면 이러한 데이터가 지속적으로 쌓여서 실제 테이블 데이터 자체는 적은데 테이블의 사이즈는 어마어마하게 커지는 것을 볼 수 있습니다. 이런 테이블은 당연히 조회 속도가 느려집니다. 또한 데이터베이스의 나이가 줄지 않아 트랜잭션 ID 겹침 현상이 발생해 auto vacuum이 freeze 상태에서 멈출 수 있습니다. 이러한 현상이 지속되면 트랜잭션 ID를..

• [Postgresql] 데이터 해시화, 암호화, 복호화

  DB/PostgreSQL 2021. 4. 3. 22:41

  해시화 MD5 md5 는 해시화로 한번 변형을 하면 복원을 할 수 없습니다. 예전에는 비밀번호와 같은 값을 많이 변환했지만 보안적인 측면에서 이미 취약하다고 하기 때문에 탈취가 되어도 크게 중요하지 않은 데이터에서만 사용하기를 권장합니다. 형식 md5(문자열) return hex예시 SELECT md5('abcd'); -- e2fc714c4727ee9395f324cd2e7f331fdigest digest를 사용하면 md5 뿐만 아니라 sha1, sha256, sha512와 같이 평문을 해시화 할 수 있습니다. 반환 타입은 bytea 이므로 encode()함수를 사용해 16진수로 변환하여 사용하면 됩니다. 형식 해시 타입 md5, sha1, sha224, sha256, sha384, sha5..

• [PostgreSQL] COALESCE, NULLIF, SIGN, IS DISTINCT FROM

  DB/PostgreSQL 2021. 2. 28. 19:41

  COALESCE와 NULLIF 두 함수는 유사한 동작을 하는 것 같지만 실제로는 다른 결과를 유발합니다. COALESCE COALESCE(X, Y)는 첫 파라미터인 X가 NULL이라면 Y를 반환하고 그렇지 않으면 X를 반환합니다. select coalesce(null, '123'); -- '123' select coalesce('1', '123'); -- '1' NULLIF NULLIF(X, Y)는 X와 Y가 같다면 NULL을 반환하고 그렇지 않으면 X를 반환합니다. select nullif('1', '1'); -- null select nullif('1', '2'); -- 1 select nullif(null, null); -- null SIGN 입력된 값이 양수면 1, 0이면 0, 음수면 -1을 반환..

• [PostgreSQL] CUBE문을 사용해 사용자 액션 집계하기

  DB/PostgreSQL 2021. 2. 28. 18:51

  먼저 사용자들의 액션 플래그를 집계하는 쿼리를 다음과 같다고 가정합니다. WITH action_flag AS ( SELECT user_id, SIGN(SUM(CASE WHEN action = 'purchase' THEN 1 ELSE 0 END)) AS has_purchase, SIGN(SUM(CASE WHEN action = 'review' THEN 1 ELSE 0 END)) AS has_review, SIGN(SUM(CASE WHEN action = 'favorite' THEN 1 ELSE 0 END)) AS has_favorite FROM action_log GROUP BY user_id ) SELECT * from action_flag 이러한 결과는 다음과 같습니다. user_id has_purch..

• [PostgreSQL] ROLLUP을 사용해 소계 구하기

  DB/PostgreSQL 2021. 2. 28. 17:15

  먼저 아래와 같은 데이터가 있다고 가정합니다. dt order_id user_id item_id price category sub_category 2017-01-01 1 1 1 10000 men jacket 2017-01-01 1 1 2 5000 food fish 2017-01-01 1 1 3 2500 book business 2017-01-01 2 2 1 10000 men jacket 2017-01-01 2 2 5 50000 women bag 2017-01-01 3 2 2 5000 food fish 2017-01-01 4 3 1 10000 men jacket 2017-01-01 5 4 4 40000 cd classic 이러한 데이터에서 카테고리별 총 매출과 소계를 계산하고자 합니다. 소계란 전체가 아닌..

• [PostgreSQL] window function의 window frame 알아보기

  DB/PostgreSQL 2021. 2. 27. 21:59

  window function에 관해서는 https://brownbears.tistory.com/310 에서 설명이 되어 있습니다. window function의 사용예제를 보다 보면 BETWEEN 구문이 들어가 있는 것을 확인할 수 있습니다. 이를 window frame 즉, 프레임 지정 구문이라 합니다. 프레임 지정이란 현재 레코드 위치를 기반으로 상대적인 window를 지정하는 구문입니다. 프레임 지정 구문은 {범위} BETWEEN {시작} AND {종료} 또는 {범위} {시작} 형태입니다. 시작, 종료 시작과 종료에는 다음과 같은 키워드를 조합하여 사용할 수 있습니다. CURRENT ROW: 현재 행 n PRECEDING: n행 앞 n FOLLOWING: n행 뒤 UNBOUNDED PRECEDING..

• [PostgreSQL] with문으로 공통 테이블 식 (CTE) 사용하기

  DB/PostgreSQL 2021. 2. 27. 21:09

  CTE는 Common Table Expression으로 간단하게 쿼리 결과를 일시적으로 저장하여 테이블처럼 사용하는 것입니다. PostgreSQL에서는 with구문을 사용해 구현할 수 있습니다. WITH temp_query_result AS ( SELECT category_name, product_id, sales, row_number() over (PARTITION BY category_name ORDER BY sales DESC) AS rank FROM product_sales ) SELECT * FROM temp_query_result; with문은 조회 결과 뿐만 아니라 values 구문을 사용해 유사 테이블을 만들 수 있습니다. WITH device(id, device) AS ( VALUES (..

• [PostgreSQL] JSON type 사용하기

  DB/PostgreSQL 2020. 6. 7. 16:40

  json과 jsonb 타입PostgreSQL에서는 JSON 형식을 필드로 선언해 저장할 수 있습니다. 관련 타입은 json, json[], jsonb, jsonb[] 으로 4가지가 있습니다. 여기서 jsonb 라는 타입을 볼 수 있는데 json과 jsonb의 차이점은 입력된 값 그대로를 저장할 것인지 최적화된 값을 저장할 것인지 입니다.json 타입은 입력된 공백, 키 순서, 중복 등과 같은 모든 것을 그대로 저장합니다. 저장 이후 질의를 한다고 가정하면 json blob을 질의할 때마다 로드하고 구문을 분석하기 때문에 속도가 느린 단점이 있습니다.그에 반해 jsonb는 json blob의 줄임말로 입력된 값을 질의에 최적화된 형태로 저장합니다. 따라서 키의 순서, 중복 제거, 공백 제거 등이 발생해 초..