불곰 불곰

DB

• [Bigquery] 시간, 숫자, 날짜 생성하기

  DB/Bigquery 2022. 2. 20. 17:17

  PostgreSQL의 시간, 숫자, 날짜 생성하기와 마찬가지로 bigquery에서도 동일한 함수가 존재합니다. postgresql과 차이점은 오버로딩 개념으로 동일한 함수명으로 여러 타입을 받아서 처리했다면 bigquery는 각 타입마다 함수명이 다릅니다. generate_array(start, end [, step]) int타입의 숫자를 입력하여 생성할 때 사용하는 함수입니다. SELECT GENERATE_ARRAY(1, 5) AS example_array; +-----------------+ | example_array | +-----------------+ | [1, 2, 3, 4, 5] | +-----------------+ SELECT GENERATE_ARRAY(0, 10, 3) AS examp..

• [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)..

• SQL 스타일 가이드

  DB 2022. 1. 16. 17:27

  개요 SQL 스타일을 통일시켜 직관적인 SQL 코드를 짜고 읽을 수 있도록 합니다. mordern sql style guide를 기반으로 깃랩, 모질라 문서를 참고하여 정리했습니다. ‼️가 붙어 있는 부분은 lint가 적용되지 않아 개개인이 주의해야 되는 부분입니다. Case 네이밍에는 문자, 숫자, _ 만 사용합니다. 네이밍의 시작은 문자로 시작하고 _로 끝나지 않습니다. 테이블 명의 최대 길이는 20자입니다. 한 줄에 표현할 수 있는 쿼리의 길이는 100자입니다. 탭은 사용하지 않고 공백만 사용합니다. jetbrains 제품을 사용한다면 탭을 공백으로 변환하도록 합니다. 쿼리 내 들여쓰기는 4칸 들여쓰기로 합니다. 필드 네이밍 필드 이름은 모두 소문자로 제공하며 스네이크 케이스를 지향합니다. (카멜케..

• [Bigquery] Resources exceeded during query execution: The query could not be executed in the allotted memory 해결 방법

  DB/Bigquery 2022. 1. 16. 17:06

  원인 bigquery에서 쿼리를 실행하면 아래와 같은 오류가 발생하는 경우가 종종 있습니다. Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 140% of limit. Top memory consumer(s): ORDER BY operations: 98% other/unattributed: 2% bigquery는 분산 데이터베이스 기반으로 쿼리 대부분이 여러 노드에서 실행되지만 계산이 필요한 특정 명령어의 경우에는 단일 노드에서 실행이 되어야 합니다. 이 때, 단일 노드에서 너무 많은 데이터를 처리하려 하면 위와 같이 오류가 발생합니다. bigquery는 ..

• [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나 다른 키로 정렬을 준다면 수행시간이 더 늘어나는 ..

• [DB] 함수를 사용하지 않고 만 나이 쉽게 계산하기

  DB 2021. 8. 15. 20:41

  만 나이를 계산하려면 생일이 지났는지, 지나지 않았는지에 따라 계산이 달라집니다. 하지만 아래에서 설명하는 수식을 사용하면 아주 쉽게 만 나이를 구할 수 있습니다. floor((현재 연월일 - 생년월일) / 10000) 현재 날짜: 20210815 생년월일1: 20001225 select floor((20210815 - 20001225) / 10000); -- 20 현재 날짜: 20211225 생년월일1: 20001225 select floor((20211225 - 20001225) / 10000); -- 21 위 식이 성립하는 이유는 구하고자 하는 연도 - 생일 연도 를 하게 되면 한국 나이 - 1 의 값이 나오게 됩니다. 여기서 구하고자 하는 월, 일이 생일 월, 일보다 적다면 앞에서 구한 값에서 1..

• [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] DB Lock

  DB 2021. 3. 13. 23:21

  명시적 lock (Explicit Lock) PostgreSQL은 여러 lock 모드들을 사용하여 테이블의 데이터를 동시 접근을 제어합니다. 이와 같은 lock 모드는 다중 버전 동시성 제어(MVCC)를 제공하지 않는 상황에서 어플리케이션을 제어하는 lock을 쓸 수 있고 대부분의 PostgreSQL 명령들은 해당 명령이 실행되는 동안 명령 실행의 대상이 되는 테이블이 삭제되거나 수정되지 않도록 적절한 모드의 lock을 자동으로 얻습니다. 예를 들어, 특정 테이블에서 truncate가 실행되는 동안 다른 작업이 실행되지 않도록 해당 테이블에 배타적 lock(exclusive lock)을 얻습니다. 동시성 제어 (Concurrency Control) 동시성 제어란 DBMS가 다수의 사용자 사이에서 동시에 ..

• [DB] 데이터베이스 정규화 (Database Normalization) - 1NF, 2NF, 3NF, BCNF

  DB 2021. 3. 6. 21:06

  이상현상의 종류와 함수적 종속성이 무엇인지 알고 있어야 이해가 쉽기 때문에 이 개념들을 먼저 설명을 먼저 한 다음, 데이터베이스 정규화를 설명합니다. 이상 현상 (Anomaly) 데이터베이스 설계를 잘못했을 때, 불필요하게 데이터가 중복으로 저장될 수 있습니다. 이럴 경우, 리소스가 낭비되고 운영 상 의도치 않은 부작용이 발생할 수도 있습니다. 부작용으로는 삽입 이상(Insertion Anomaly), 갱신 이상(Update Anomaly), 삭제 이상(Deletion Anomaly)이 있습니다. 아래는 학사 시스템 예제로 1명의 학생은 1개의 학과에 속할 수 있으며 학번과 과목명의 조합으로 기본키를 가지는 테이블 입니다. 삽입 이상 (Insertion Anomaly) 수강을 1개도 하지 않는 편입생을 ..