PostgreSQL에 대해서

이번 포스팅에서는 PostgreSQL에 대해 알아보도록 하겠다.

많은 DB들 중 PostgreSQL에 대해서 알아보는 이유는 이력서 넣던 도중 많이 보이는 RDBMS는 MSSQL, PostgreSQL, MYSQL이 가장 많이 보이고 NOSQL은 Redis, MongoDB이 많이 보인다.

 

PostgreSQL란

오픈 소스 객체 관계형 데이터베이스 시스템으로 ORDBMS이다.

다른 관계형 데이터베이스 시스템과 달리 연산자, 복잡 자료형, 집계 함수, 자료형 변환자, 확장기능 등이 제공된다.

다양한 DB 객체를 사용자가 임의로 만들 수 있는 기능을 제공함으로써 마치 새로운 하나의 프로그래밍 언어처럼 기능을 구현할 수 있다.

 

특징 및 기능

 

데이터 유형

기본 요소 - 정수, 문자열, boolean 등

구조화 - 날짜/시간, 배열, 범위, UUID

문서 - JSON, XML, 키-값

기하학 - 점, 선, 원, 다각형

사용자 정의

 

데이터 무결성

고유, Nullable, 기본 키, 외래 키, 제외 제약, 명시적 잠금, 권고 잠금

 

동시성, 성능

인덱싱 - B-tree, 다중 열, 표현식

고급 인덱싱 - GiST, SP-Gist, KNN Gist, GIN, BRIN, 커버링 인덱스, 블룸 필터

정교한 쿼리 플래너/ 옵티마이저, 인덱스 전용 스캔, 다중 열 통계

트랜잭션, 저장점을 통해 중첩 트랜잭션

다중 버전 동시성 제어(MVCC)

읽기 쿼리 병렬화 및 B-트리 인덱스 구축

테이블 파티셔닝

직렬화 기능을 포함해 SQL 표준에 정의된 모든 트랜잭션 격리 수준

JIT 표현식 컴파일

 

안정성, 재해 복구

미리 쓰기 로깅(WAL)

복제 - 비동기식, 동기식, 논리적

시점 복구(PITR), 활성 대기

테이블스페이스

 

보안

인증 - GSSAPI, SSPI, SCRAM-SHA-256, 인증서 등

강력한 액세스 제어 시스템

열 및 행 수준 보안

인증서 및 추가 방법을 사용한 다단계 인증

 

확장성

저장 함수 및 프로시저

절차적 언어 - PL/PGSQL, Python 등

SQL/JSON 경로 표현식

외부 데이터 래퍼 - 표준 SQL 인터페이스를 사용해 다른 데이터베이스 또는 스트림에 연결

테이블에 대한 맞춤형 스토리지 인터페이스

 

국제화, 텍스트 검색

ICU 대조를 통해 국제 문자 세트 지원

대소문자를 구분하지 않고 악센트를 구분하지 않는 데이터 정렬

전체 텍스트 검색

 

다음으로 PostgreSQL를 설치하고 사용해 보도록 하자.

필자는 MacOS를 사용중이므로 MacOS 설치 기준으로 하겠다.

 

아래 명령어를 통해 설치할 수 있다.

brew install postgresql

 

아래 명령어로 설치된 버전을 확인할 수 있다.

postgres -V
postgres (PostgreSQL) 14.12 (Homebrew)

 

 

서비스 목록을 확인하자.

brew services list
Name          Status User File
nginx         none
postgresql@14 none

 

status는 none으로 나온다.

 

이번엔 실행을 하도록 하자.

brew services restart postgresql@14
==> Successfully started `postgresql@14` (label: homebrew.mxcl.postgresql@14)

brew services list
Name          Status  User      File
nginx         none
postgresql@14 started hanjihoon ~/Library/LaunchAgents/homebrew.mxcl.postgresql@14.plist

 

위와 같이 실행 되는 것을 확인할 수 있다.

PostgreSQL은 Default 접속 계정으로 자동으로 계정을 생성해 준다.

 

접속해서 확인해 보자.

psql postgres

psql (14.12 (Homebrew))
Type "help" for help.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 hanjihoon | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

 

psql postgres

\du

위 두 명령어로 인해 접속과 role 리스트를 확인할 수 있고 필자 mac 이름으로 Superuser가 생성되었다.

 

다음은 Client Tool을 확인해 보자. TablePlus, Psequel, SQLPro, Postico 등이 있다.

필자는 DBeaver를 사용해서 접속해 보도록 하겠다.

 

PostgreSQL을 선택한다.

 

필요한 드라이버를 다운로드한다.

 

Username에 설정된 role을 입력하고 Test Connection을 진행 시 정상적으로 연결된다.

 

다음은 DB를 생성해 보자.

Test라는 이름의 데이터베이스를 만들어 보도록 하겠다.

postgres=# create database test;
CREATE DATABASE
postgres=# create user testuser with encrypted password 'testpass';
CREATE ROLE
postgres=#

 

데이터 베이스를 만든 후 testuser라는 user를 생성했다.

postgres=# alter user testuser createdb;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 hanjihoon | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 testuser  | Create DB

 

testuser에게 데이터베이스 생성 권한도 부여했다.

postgres=# grant all privileges on database test to testuser;
GRANT

생성한 유저에게 test 데이터베이스에 대한 모든 권한을 주도록 하자.

자세한 권한 부여에 대한 부분은 다음 페이지에서 확인하자.

https://www.postgresql.org/docs/13/sql-grant.html

GRANT

 

데이터 베이스 리스트도 확인할 수 있다.

postgres=# \dt
Did not find any relations.

위와 같이 테이블 리스트도 확인할 수 있으며 아무 테이블도 없을 경우 Did not find any relations.라는 문구를 확인할 수 있다.

postgres=# \connect test
You are now connected to database "test" as user "hanjihoon".

데이터 베이스 연결은 위와 같이 하고 이미 연결되어 있을 시 위처럼 문구가 뜬다.

다음은 추가한 사용자로 접속해 보자.

test=# \q
hanjihoon@hanjihun-ui-MacBookAir postgresql@14 % psql postgres -U testuser
psql (14.12 (Homebrew))
Type "help" for help.

postgres=>

접속을 종료하고 -U 옵션과 username을 추가해 추가한 사용자로 접속할 수 있다.

터미널을 확인해 보면

postgres=#
postgres=>

바뀐 것을 확인할 수 있다. #은 super user를 뜻한다.

새로 생성한 test database에 새로 생성한 testuser로 DBeaver에 접속해 보자.

 

 

postgres=> \dt
Did not find any relations.
postgres=> create table dept (
        deptno    NUMERIC CONSTRAINT PK_DEPT PRIMARY KEY,
    dname    VARCHAR(14) ,
    loc        VARCHAR(13)
);
CREATE TABLE
postgres=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | dept | table | testuser
(1 row)

postgres=> CREATE TABLE emp (
    empno    NUMERIC CONSTRAINT PK_EMP PRIMARY KEY,
    ename    VARCHAR(10),
    job        VARCHAR(9),
    mgr        NUMERIC,
    hiredate    DATE,
    sal        NUMERIC(7,2),
    comm    NUMERIC(7,2),
    deptno    NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
);
CREATE TABLE
postgres=> \dt
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | dept | table | testuser
 public | emp  | table | testuser
(2 rows)

postgres=>

 

postgres=> \q
hanjihoon@hanjihun-ui-MacBookAir postgresql@14 % psql test -U testuser
psql (14.12 (Homebrew))
Type "help" for help.

test 데이터베이스로 바꾸고 

테스트용 테이블과 데이터를 넣어주도록 하겠다.

CREATE TABLE dept (
    deptno    NUMERIC CONSTRAINT PK_DEPT PRIMARY KEY,
    dname    VARCHAR(14) ,
    loc        VARCHAR(13) 
) ;
CREATE TABLE emp (
    empno    NUMERIC CONSTRAINT PK_EMP PRIMARY KEY,
    ename    VARCHAR(10),
    job        VARCHAR(9),
    mgr        NUMERIC,
    hiredate    DATE,
    sal        NUMERIC(7,2),
    comm    NUMERIC(7,2),
    deptno    NUMERIC(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT 
);

-- Insert test data - dept
INSERT INTO dept VALUES    (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES    (30,'SALES','CHICAGO');
INSERT INTO dept VALUES    (40,'OPERATIONS','BOSTON');
-- Insert test data - emp
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-07-87','dd-mm-yyyy')-85,3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,to_date('13-07-87','dd-mm-yyyy')-51,1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

 

데이터가 잘 들어가는 것을 확인할 수 있다.

 

SQL은 ANSI SQL을 사용하면 된다.

select dept.deptno, dname, count(empno)
from dept
    left outer join emp on (emp.deptno = dept.deptno)
group by dept.deptno, dname
order by deptno;

 

select deptno, ename, sal
from(
select deptno, ename, sal, rank() over(partition by deptno order by sal, empno) as rank
from emp) e
where rank = 1;

 

여기까지 PostgreSQL을 설치하고 툴을 이용해 접속하여 SQL도 작성해 보았다.

간단한 쿼리를 해보고 실제 DB를 운용하지 않았기 때문에 기존에 사용하던 MYSQL과 비교했을 때 차이점을 딱히 느끼지 못해서 추가로 Mysql과 PostgreSQL에 대한 차이를 알아보고 끝내도록 하자.

 

Mysql과 PostgreSQL의 차이

Mysql은 오라클에 의해 관리되는 오픈 소스 관계형 데이터베이스 관리 시스템으로 주로 웹 애플리케이션에 사용되며 속도와 용이성으로 인해 사용된다. 반면 PostgreSQL은 JSON, XML 등 다양한 데이터 타입을 지원하며 고급 인덱싱 기능과 복잡한 쿼리를 최적화할 수 있고 대용량 데이터 처리에서도 뛰어난 성능을 지녔다.

mysql은 읽기 성능에 강점을 가지고 있으며 웹 사이트와 같은 읽기 작업이 많은 애플리케이션에 맞춰져 있다 하지만 Postgresql은 쓰기 작업과 복잡한 트랜잭션 처리에 강점을 가지고 있다.

 

이로써 두 데이터베이스 기술에 대해서도 차이를 알아보았다.

간단하게 사용해서는 이 PostgreSQL의 기능을 다 사용하지 못하고 장점도 이끌어내지 못했다. 공식 페이지에서 사용법을 개인적으로 공부를 더 해보고 차후 시스템 설계 시에 관계형 DB 선택에 폭을 늘려줄 수 있도록 조금 더 공부하도록 하겠다.