PostgreSQL 개념 및 특징(with MySQL)

Overview

PostgreSQL에 대해서 공부해본다.

출처 : https://1000logos.net/postgresql-logo/

 

---

PostgreSQL이란?

Postgres라고 불리는 PostgreSQL은 고급 오픈 소스 관계형 데이터베이스 관리 시스템(RDBMS)으로 30년 넘게 개발되어 안정성, 기능 견고성, 성능 면에서 높은 평가를 받고 있다.

이는 단일 시스템부터 데이터 웨어하우스 또는 많은 동시 사용자가 있는 웹 서비스에 이르기까지 다양한 작업 부하를 처리하도록 설계되었다.

​

 

PostgreSQL 특징

• 트랜잭션 무결성 및 신뢰성을 위한 ACID 준수
• 고급 데이터 유형 및 풍부한 데이터베이스 확장 세트를 지원
• 동시 트랜잭션에 대한 포괄적인 지원(다중 버전 동시성 제어 또는 MVCC 사용)
• 표현식 인덱스 및 부분 인덱스를 포함한 고급 인덱싱 기술
• 도메인 제약 조건 및 복잡한 데이터 유형 유효성 검사와 같은 데이터 무결성 및 정확성을 위한 광범위한 기능
• 애플리케이션 유연성을 위한 전체 텍스트 검색, JSON 및 XML 지원, 기타 NoSQL과 유사한 기능
• 강력한 액세스 제어 메커니즘 및 SSL 지원을 포함한 강력한 보안 기능
• 저장 프로시저 및 트리거를 지원하여 데이터베이스 측에서 복잡한 비즈니스 논리를 처리
• 크로스 플랫폼이며 Linux, Windows, macOS를 포함한 다양한 운영 체제에서 실행

 

---

 

PostgreSQL과 MySQL

PostgreSQL과 MySQL 중 하나를 선택하는 것은 프로젝트의 특정 요구 사항에 따라 달라진다.

PostgreSQL은 고급 기능, 안정성 및 복잡한 데이터 처리가 필요한 시스템에 적합한 경우가 많은 반면, MySQL은 단순성과 속도가 우선시되는 애플리케이션에 적합하다.

 

 

특징/측면	PostgreSQL	MySQL
데이터베이스	PostgreSQL은 객체 관계형 데이터베이스 관리 시스템	MySQL은 순수 관계형 데이터베이스 관리 시스템
개발	오픈 소스, 커뮤니티 중심 개발	원래는 오픈 소스였으며 현재 Oracle이 소유
ACID 규정 준수	ACID를 완벽하게 준수	InnoDB 스토리지 엔진과 ACID를 준수
MVCC	MVCC에 대한 기본 지원	InnoDB 엔진을 통해 MVCC를 지원
복제	동기식 및 비동기식 복제가 모두 지원	주로 비동기식 복제
성능	복잡한 쿼리에 고도로 최적화	읽기가 많은 작업에 최적화
확장성	사용자 정의 데이터 유형 및 기능을 지원하여 확장성 높음	플러그인 및 UDF(사용자 정의 함수)로 확장 가능
데이터 유형	기하학적 유형과 사용자 정의 유형을 포함한 풍부한 데이터 유형 세트	JSON을 지원하는 표준 SQL 데이터 유형
인덱싱	더욱 다양한 인덱싱 방법을 제공	PostgreSQL에 비해 인덱싱 방법이 더 제한적
동시성	읽기 잠금 없이 뛰어난 동시성을 지원	적절한 튜닝 및 설정으로 동시성 높음
라이선스	PostgreSQL License (a liberal Open Source license).	GNU GPL with exceptions for proprietary products.

 

 

---

 

 

PostgreSQL Sequence 와 MySQL Auto_increment

 

PostgreSQL Sequence

• PostgreSQL의 `SEQUENCE`는 지정된 규칙에 따라 일련의 숫자 값을 생성하는 별도의 개체이다.
• 여러 테이블과 열에서 동일한 시퀀스를 사용할 수 있다.
• 더 많은 제어력과 유연성을 제공합니다. 예를 들어 증분, 최소값, 최대값, 시퀀스 순환 여부를 정의할 수 있다.
• PostgreSQL에서는 NEXTVAL 함수를 사용하여 테이블에 행을 삽입하지 않고도 시퀀스의 다음 값을 얻을 수 있다.
• `SEQUENCE`는 분산 환경에서 사용할 값을 사전 할당하는 데 사용할 수 있으며, 이는 기본 키 생성에 특히 유용할 수 있다.

 

Sequence 생성

CREATE SEQUENCE customer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 10;

• customer_id_seq라는 이름의 SEQUENCE가 생성되고, 1에서 시작해서 각 호출마다 1씩 증가하는 값을 제공한다.
• CACHE 10은 성능 최적화를 위해 10개의 값들을 미리 메모리에 캐싱하겠다는 것을 나타낸다.

 

Sequence 삽입

INSERT INTO customers (id, name)
VALUES (NEXTVAL('customer_id_seq'), 'John Doe');

• customer_id_seq SEQUENCE에서 다음 값을 가져와 id 필드에 삽입하고, name 필드에는 'John Doe'를 삽입한다.
• SEQUENCE는 SERIAL 또는 BIGSERIAL 데이터 타입과 함께 사용되기도 한다. 이들 데이터 타입은 내부적으로 SEQUENCE를 생성하여 자동 증가 식별자를 관리한다.

 

MySQL AUTO_INCREMENT

• MySQL에서 `AUTO_INCREMENT`는 새 행에 대한 고유 ID를 생성하는 데 사용되는 열 속성이다.
• 시퀀스는 테이블 내의 특정 열에 연결되어 있으며 자동 증가 값이 필요한 각 테이블에는 자체 `AUTO_INCREMENT` 값이 있다.
• 여러 테이블에서 자동 증가 시퀀스를 쉽게 공유하거나 자유롭게 사용자 정의할 수 없으므로 PostgreSQL의 시퀀스에 비해 유연성이 떨어진다.
• 행을 삽입하여 시퀀스의 다음 값을 얻는다. 새로운 `AUTO_INCREMENT` 값이 생성되고 LAST_INSERT_ID()를 사용하여 검색할 수 있다.

 

Auto_increment 생성

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
);

• id를 지정하지 않고 'users' 테이블에 새 행이 추가될 때마다 MySQL은 자동으로 마지막 가장 높은 값에서 1씩 id를 증가한다.

 

Auto_increment 열에 데이터 삽입

INSERT INTO users (username) VALUES ('John Doe');

• INSERT 명령을 실행하면 이 새 행의 id가 자동으로 생성되고 users 테이블의 마지막 가장 높은 id 값에서 증가한다.

 

MySQL에서는 AUTO_INCREMENT 속성을 사용하여 행의 기본 키 역할을 하는 고유 번호를 생성할 수 있으며 테이블에 삽입된 새 행마다 자동으로 증가된다.

새 행에 대한 고유 식별자를 제공하여 PostgreSQL 시퀀스와 유사한 목적을 제공하지만 동일한 수준의 제어 또는 교차 테이블 유틸리티를 제공하지 않는다.

​

 

---

 

 

PostgreSQL 문법

# Debain/Ubuntu
sudo apt-get install postgresql-client

# Redhat/CentOS/Rocky
sudo yum install postgresql

# Redhat/CentOS/Rocky 7 and later use dnf instead of yum
sudo dnf install postgresql

# 접속 방법
psql -h <호스트_주소> -p <포트> -U <사용자명> -d <데이터베이스명>

psql -h 10.10.10.10 -U somaz -p 5432 -d somaz-db

# db 조회
\l

# 현재 접속정보 조회
\c

# 데이터베이스 연결
\connect 데이터베이스명

# 테이블 목록 조회
\dt

# 관계목록(테이블,보기,시퀀스) 조회
\d 

#  테이블에개한 개별 정보 조회
\d 테이블이름

 

 

 

---

Reference

https://1000logos.net/postgresql-logo/

https://www.integrate.io/ko/blog/postgresql-vs-mysql-which-one-is-better-for-your-use-case-ko/

https://velog.io/@ryucherry/DBPostgreSQL-예제로-PostgreSQL-기초문법-익히기

https://aws.amazon.com/ko/compare/the-difference-between-mysql-vs-postgresql/