PostgreSQL #

PostgreSQL은 북미와 일본에서는 높은 인지도와 많은 인기를 얻고 있는 RDBMS다. 국내에서는 아직 잘 사용하지 않고 있지만, 초기 개발 단계부터 완벽한 ACID와 MVCC를 지원하는 아키텍처로 설계된 PostgreSQL은 대용량의 복잡한 트랜잭션 처리를 위한 RDBMS이다. PostgreSQL은 객체-관계형 데이터베이스 시스템(ORDBMS)으로, 과거 IBM에서 작성된 RDBMS의 화이트페이퍼를 기반으로 Oracle, DB2 그리고 PostgreSQL이 구현되었기 때문에 MySQL이나 mariaDB와 같은 오픈소스 제품과 달리 PostgreSQL의 경우 상용 RDBMS 급의 기능을 제공하는 오픈소스 DBMS이다 PostgreSQL은 가장 진보된 오픈소스 데이터베이스 시스템인 범용·객체 관계형 데이터베이스 관리 시스템이다. PostgreSQL은 캘리포니아에 있는 버클리 대학교에서 postgres4.2를 기반으로 개발되었다. PostgreSQL은 Unix와 유사한 플랫폼에서 실행되도록 설계되었다. 그러나 PostgreSQL은 Mac OS X, Solaris 및 Windows와 같은 다양한 플랫폼에서 실행될 수 있도록 설계되었다. PostgreSQL은 무료이며 오픈소스소프트웨어이다. 그렇기 때문에 어떤 형태로든 자유롭게 사용, 수정 및 배포할 수 있다. PostgreSQL은 Oracle 이전에도 MVCC(Multi-version Concurrency Control) 기능을 구현하는 최초의 데이터베이스 관리 시스템이다. C나 C++, Java등과 같은 다른 프로그래밍 언어를 사용하여 개발된 사용자 정의 함수도 추가할 수 있다.

PostgreSQL 주요 특징 #

1. 관계형 모델

   Postgres 프로젝트 연구의 최초의 목적 중의 하나는 복합객체(complex object), 규칙(rule)들을 다룰 수 있으며, 고수준으로 확장 가능한 관계형 DBMS를 제작하려던 것이었다. 따라서 PostgreSQL 는 관계형 DBMS가 가지고 있는 거의 모든 기능이 있다. 예를 들면 SQL에서 서술적인 질의어의 사용과 질의 최적화, 동시성 제어, 트랜잭션 처리, 멀티 유저 기능 들을 제공하고 있다.

2. 고수준 확장성

   PostgreSQL는 사용자 정의 오퍼레이터와 타입, 함수, 엑세스 메소드를 지원한다.

3. 객체지향

   PostgreSQL 는 상속, 객체와 같은 객체지향개념에서 볼 수 있는 여러 특징을 초보적이나마 구현하고 있다. 이러한 특징 때문에 어떤 사람들은 PostgreSQL 을 설명할 때 ORDBMS라고 말하기도 한다.

4. 안정성, 재해 복구

   • 미리 쓰기 로깅(WAL)
   • 복제: 비동기식, 동기식, 논리적
   • 시점 복구(PITR), 활성 대기
   • 테이블 스페이스

5. 보안

   • 인증: GSSAPI, SSPI, LDAP, SCRAM-SHA-256, 인증서 등
   • 강력한 액세스 제어 시스템
   • 열 및 행 수준 보안
   • 인증서 및 추가 방법을 사용한 다단계 인증

6. 확장성

   • 저장 함수 및 프로시저
   • 절차적 언어: PL/PGSQL, Perl, Python (및 기타)
   • SQL/JSON 경로 표현식
   • 외부 데이터 래퍼: 표준 SQL 인터페이스를 사용하여 다른 데이터베이스 또는 스트림에 연결
   • 테이블에 대한 맞춤형 스토리지 인터페이스
   • PostGIS를 포함하여 추가 기능을 제공하는 많은 확장

7. 국제화, 텍스트 검색

   • ICU 대조를 통한 국제 문자 세트 지원
   • 대소문자를 구분하지 않고 악센트를 구분하지 않는 데이터 정렬
   • 전체 텍스트 검색

공식 홈페이지 #

http://www.postgresql.org/

PostgreSQL Limits #

튜닝 - DB 설치 후 하면 좋은 작업 #

PostgreSQL을 처음 설치한 후 생각보다 속도가 나오지 않아서 당황한 적이 있다. 이는 환경설정에 메모리 크기가 작게 잡혀있어서 생기는 문제로 설정을 변경하여 주면 빠른 속도를 얻을 수 있다.

shared_buffers #

데이터베이스에 접근할 시 데이터를 읽는 데 필요한 메모리의 크기를 늘려서 느린 디스크 속도를 보완한다. 이 메모리의 크기를 정해주는 옵션이 shared_buffers 이다.

PostgreSQL을 설치하면 기본 설정이 32M로 되어있다. 이 값을 늘려주면 좀 더 나은 속도를 얻을 수 있다. 보통 시스템에 설치된 메모리의 1/4 ~ 1/2 정도로 설정하는데 이는 시스템에 따라 사용자가 적당히 설정해주면 된다.

예) shared_buffers = 2GB

그룹 함수 #

그룹 함수란? #

하나 이상의 행을 그룹으로 묶어 연산하여 하나의 결과로 나타내는 것이다.

그룹 함수 사용 시 고려사항 #

• NULL 값은 무시된다. (값이 NULL인 행은 제외하고 수행)
• 반드시 단 하나의 값만을 반환한다.
• GROUP BY 열을 SELECT 절에 포함하지 않아도 된다.
• 하나 이상의 GROUP BY 열을 나열하여 그룹에 대한 요약 결과를 조회할 수 있다.
• GROUP BY 설정 없이 일반 컬럼과 기술될 수 없다.
• SELECT 절의 일반 컬럼과 GROUP BY 절의 일반 컬럼은 개수가 같아야 한다.
• GROUP BY 절의 일반컬럼 개수가 더 적으면 오류가 발생한다.
• COUNT, MAX와 MIN은 문자, 숫자, 날짜 데이터 모두에게서 사용할 수 있다. 그러나, AVG, SUM, VARIANCE, STDDEV는 NUMBER만 사용할 수 있다.

그룹 함수의 종류 #

집단함수 #

• SUM : 해당 컬럼 값들에 대한 총합을 구하는 함수

  SELECT SUM(SAL) FROM EMP;
  (EMP 테이블의 SAL 컬럼의 모든 값에 대한 총합을 구한다.)
  

• AVG : 해당 컬럼 값들에 대한 평균을 구하는 함수

  SELECT AVG(SAL) FROM EMP;
  (EMP 테이블의 SAL 컬럼의 모든 값에 대한 평균을 구한다.)
  

• COUNT : 테이블에서 조건을 만족하는 행(ROW)의 개수를 반환하는 함수

  SELECT COUNT(*), COUNT(COMM) FROM EMP;
  (COUNT(*)은 EMP 테이블의 모든 행의 수를 반환하고, COUNT(COMM)은 COMM 컬럼에 값이 있는 행의 수를 반환한다.)
  

• MAX : 해당 컬럼 값들 중 최댓값을 반환하는 함수

  SELECT MAX(SAL) FROM EMP; 
  (EMP 테이블의 SAL 컬럼의 모든 값 중 최댓값을 반환한다.)
  

• MIN : 해당 컬럼 값들 중 최솟값을 반환하는 함수

  SELECT MIN(SAL) FROM EMP;
  (EMP 테이블의 SAL 컬럼의 모든 값 중 최솟값을 반환한다.)
  

• STDDEV : 해당 컬럼 값들에 대한 표준편차를 구하는 함수

  SELECT STDDEV(SAL) FROM EMP;  
  (EMP 테이블의 SAL 컬럼의 모든 값의 표준편차를 구한다.)
  

GROUP BY, HAVING #

• GROUP BY : 어떤 컬럼 값을 기준으로 그룹 함수를 적용하는 경우 GROUP BY 절 뒤에 기준이 되는 컬럼명을 기술하여 사용한다.

• HAVING : SELECT 문에서 조건을 구할 때 WHERE 절을 사용하는 것처럼, 그룹 함수를 이용할 때의 조건문으로는 HAVING 절을 사용한다. (그룹 함수에 대한 조건이므로 GROUP BY와 함께 사용한다.

  SELECT 상품ID, 월, SUM(매출액) AS 매출액
  FROM 월별매출
  GROUP BY 상품ID, 월
  HAVING 월 = '2023.06';
  

ROLLUP #

• GROUP BY의 컬럼에 대해 소계(Subtotal)를 생성한다.

• GROUP BY에 두 개 이상의 컬럼이 오면 순서에 따라 결과가 달라진다.

  SELECT 상품ID, 월, SUM(매출액) AS 매출액
  FROM 월별매출
  GROUP BY ROLLUP(상품 ID, 월);
  

GROUPING SETS #

• GROUP BY에 나오는 컬럼의 순서와 관계 없이 다양한 소계가 생성할 수 있다.

  SELECT 상품ID, 월, SUM(매출액) AS 매출액
  FROM 월별매출
  GROUP BY GROUPING SETS(상품ID, 월);
  

CUBE #

• CUBE 함수에 제시한 컬럼에 대해 결합할 수 있는 모든 집계를 계산한다.

• 예시로 부서, 직업을 CUBE에 제시하면 부서별 합계, 직업별 합계, 부서별 직업별 합계, 전체 합계를 조회한다.

  SELECT 상품ID, 월, SUM(매출액) AS 매출액
  FROM 월별매출
  GROUP BY CUBE(상품ID, 월);
  

GROUPING #

• ROLLUP, GROUPING SET, CUBE로 생성되는 합계 값을 구분하기 위해 사용한다.

• 계산된 곳은 1, 아닌 곳은 0으로 반환하여 표시한다.

  SELECT 
      CASE GROUPING(상품ID) WHEN 1 THEN '모든 상품 ID' ELSE 상품ID END AS 상품ID,
      CASE GROUPING(월) WHEN 1 THEN '모든 월' ELSE 월 END AS 월,
      SUM(매출액) AS 매출액
  FROM 월별매출
  GROUP BY ROLLUP(상품ID, 월);
  

참고 사이트 #

• https://d2.naver.com/helloworld/227936
• http://www.linuxlab.co.kr/docs/97-11-4.htm
• http://www.gurubee.net/lecture/2889
• https://learning-e.tistory.com/25

Incoming Links #

Related Data Sciences (DataScience 0) #

• PostgreSQL/JSON Functions and Operators
• PostgreSQL/JSON Path Language
• PostgreSQL/JSON Types
• PostgreSQL/dblink
• PostgreSQL/pg dump
• 데이터 백업

Suggested Pages #

• 0.729 Hyungyong Kim
• 0.052 삭제요청
• 0.017 DataBase Management System
• 0.014 Jupyter
• 0.012 pcr
• 0.011 Graph
• 0.011 데이터베이스
• 0.011 데이터 구조
• 0.011 GO
• 0.011 Gene_ontology_database
• More suggestions...