MySQL? PostgerSQL 뭐가 다른거지?

MySQL / PostgerSQL은 관계형 데이터베이스!

 

 

MySQL과 PostgeSQL은 현재 많이 사용하고 있는 관계형 데이터베이스라고 하는데 정작 차이점과 어떤 기능인지 정확히 몰라 직접 공부를 하면서 작성을 하는 포스트입니다! 

---

1. MySQL과 PostgreSQL 개요

• MySQL은 왜 만들어졌을까?
  • MySQL 1995년에 마이클 비데니우스(Michael Widenius)와 데이빗 액스마크(David Axmark)가 개발한 오픈 데이터베이스 시스템으로 그 당시에는 상용 관계형 데이터베이스(RDBMS)가 많았지만, 가볍고 빠르게 작동하면서도 무료로 사용할 수 있는 데이터베이스가 필요했었다.
    
    
  • 시대적 흐름으로 인터넷이 발전하면서 웹 애플리케이션의 트래픽 처리 능력이 중요한 요소였는데, MySQL은 대규모 웹사이트에서 읽기 작업을 빠르게 처리하는 데 적합한 데이터베이스가 필요했기 때문에 만들어졌다!
    
    
• MySQL
  
  • 특징
    • 관계형 데이터베이스 : 데이터를 테이블의 행(row)과 열(Column)로 구성해 저장하며, 테이블 간의 관계를 설정할 수 있다
    • 빠른 읽기 성능 : 대규모 트래픽을 처리하는 웹 애플리케이션에 적합하며, 읽기 작업에 강점을 보인다
      
      
    • 오픈 소스 : 누구나 무료로 사용할 수 있으며, 전 세계적으로 활발한 커뮤니티 지원들 통해 빠르게 발전.
      
      
    • Auto_INCREMENT : 기본 키(primal Key)가 자동으로 증가하도록 설정할 수 있고, 데이터를 삽입할 때 중복없이 고유한 ID를 할당할 수 있다
      
      
    • 다양한 스토리지 엔진 : MySQL은 InnoDB, MyISAM 등 다양한 스토리지 엔진을 지원한다. 
      
      
    • 복제(Replication)기능 : MySQL은 데이터를 여러 서버에 복제해 성능을 향상시키고, 고가요성(HA)을 지원한다.
      
      
    • 확장성 : 수평적 확장을 통해 여러 서버에 데이터를 분산시켜 대규모 데이터를 처리할 수 있다!

InnoDB와 MyISAM에 대한 가벼운 정보들

InnoDB : 트랜잭션, 외래 키 지원, 안전하고 안정적
MyISAM : 빠른 읽기 성능, 트랜잭션 X, 외래키 지원 X

 

• PostgreSQL은 왜 만들어졌을까?
  • PostgreSQL의 기원은 1986년 캘리포니아 대학 버클리에서 Michael Stonebraker가 주도한 연구 프로젝트인 POSTGRES 프로젝트로 시작.
    
    
  • 당시 데이터베이스는 이미 많이 사용되고 있었지만, 데이터 처리의 복잡성과 확장성을 더 높일 수 있는 데이터베이스가 필요하다 생각하여 만들어졌다.

• PostgreSQL
  • 특징
    • 관계형 및 비정형 데이터 처리 : PostgreSQL은 관계형 데이터와 함께 JSON, XML 등 비정형 데이터도 처리할 수 있다. 이를 통해 전통적인 RDBMS 기능과 NoSQL 기능을 결합한 혼합형 데이터베이스로 사용
      
      
    • SERIAL : 기본 키(primal key) 자동 증가를 지원하며, 데이터 삽입 시 고유한 ID를 자동으로 할당
      
      
    • ACID 트랜잭션 : PostgreSQL은 ACID 원칙을 철저히 준수하여 데이터의 일관성과 무결성을 보장. 복잡ㅎㄴ 트랜잭션 처리가 필요한 금융 시스템이나 대규모 데이터 처리에 적합
      
      
    • 확장성 : 사용자 정의 자료형, 함수, 연산자 등을 추가할 수 있어 매우 유연한 확장성을 제공, 이를 통해 맞춤형 데이터 처리 시스템을 구축할 수 있다.
      
      
    • 다중 버전 동시성 제어(MVCC) : MVCC를 통해 여러 트랜잭션이 동시에 처리될 수 있도록 한다. 성능을 유지하면서 데이터 일관성을 보장한다.
      
      
    • 복잡한 쿼리 처리 : PostgreSQL은 복잡한 쿼리, 서브 쿼리, 윈도우 함수, 집계 연산 등을 효율적으로 처리한다.
      
      
    • 트리거와 규칙 : 고급 데이터베이스 작업을 위한 트리거와 규칙을 정의할 수 있다. 자동화된 데이터 처리 로직을 구축할 수 있다.

ACID 원칙

1. Atimicty(원자성)
- 트랜잭션의 모든 작업이 하나의 단위로 취급되어, 전부 커밋되거나 전부 롤백되어야 한다는 원칙
2. Consistency(일관성)
- 트랜잭션이 실행되기 전과 후에 데이터베이스가 일관된 상태를 유지해야 한다는 원칙
3.Isolation(고립성)
- 동시에 실행되는 여러 트랜잭션이 서로의 중간 상태를 볼 수 없어야 한다는 원칙
4. Durability(지속성)
- 트랜잭션이 커밋된 이후의 변경 사항은 시스템 장애나 다운타임 이후에도 영구적으로 보존되어야 한다는 원칙

 

---

2. MySQL과 PostgreSQL의 차이점

• 트랜잭션 및 ACID 지원
  • PostgreSQL: 모든 트랜잭션에서 완벽한 ACID 지원, 복잡한 트랜잭션에 강함.
  • MySQL: 저장 엔진에 따라 트랜잭션 지원 여부가 다름 (InnoDB만 ACID 지원).
    
    
• 데이터 타입 및 유연성
  • PostgreSQL: JSONB, 배열 등 다양한 데이터 타입 지원, 복잡한 데이터 처리 유연함.
  • MySQL: 기본적인 데이터 타입 위주, JSON 지원은 있지만 기능이 제한적.
    
    
• 쿼리 최적화 및 성능
  • PostgreSQL: 복잡한 쿼리 처리 성능 우수, 고급 쿼리 최적화에 강점.
  • MySQL: 단순 쿼리에 특화, 읽기 성능이 우수하지만 복잡한 연산에서는 성능 저하.
    
    
• 확장성 및 커스터마이징
  • PostgreSQL: 확장성과 커스터마이징 가능, 다양한 확장(Extensions)과 내장 언어 지원.
  • MySQL: 커스터마이징 기능 제한적, 기본 기능 중심으로 사용.
    
    
• 커뮤니티 및 라이선스
  • PostgreSQL: 오픈소스 커뮤니티 중심, BSD 라이선스로 자유로운 사용 가능.
  • MySQL: Oracle 소유, GPL 라이선스로 상업적 사용 시 제약이 있을 수 있음.

MySQL vs PostgreSQL 비교 표

항목	MySQL	PostgreSQL
트랜잭션 및 ACID 지원	저장 엔진에 따라 다름(InnoDB만 ACID 지원)	완벽한 ACID 트랜잭션 지원
데이터 타입 및 유연성	기본적인 데이터 타입만 지원, JSON 기능 제한적	JSONB, 배열, HSTORE 등 다양한 타입 및 복잡한 데이터 구조 지원
쿼리 최적화 및 성능	단순 쿼리에 특화, 복잡한 쿼리는 성능 저하 발생	복잡한 쿼리 최적화에 강점, 큰 데이터셋에서도 효율적 쿼리 처리
확장성 및 커스터마이징	기능 확장과 커스터마이징 제한적	유저 정의 함수, 확장, 내장 언어로 커스터마이징 가능
커뮤니티 및 라이선스	GPL 라이선스, Oracle 소유, 상업적 지원 강함	BSD 라이선스, 오픈소스 중심의 커뮤니티로 다양한 확장 기능 제공

---

3. MySQL과 PostgreSQL 공통 기초 SQL 문법

• 데이터베이스 생성 및 선택
  
  • MySQL

CREATE DATABASE mydb;
USE mydb;

 

•  데이터베이스 생성 및 선택
  • PostgreSQL

CREATE DATABASE mydb;
\c mydb;

 

• 데이터 삽입 (INSERT)
  • MySQL과 PostgreSQL 동일

INSERT INTO users (name, email) VALUES ('HyeonLo', 'HyeonLo123@example.com');

 

• 데이터 조회 (SELECT)
  • MySQL과 PostgreSQL 동일

SELECT * FROM users;

 

• 데이터 수정 (UPDATE)
  • MySQL과 PostgreSQL 동일

UPDATE users SET email = 'LoHyeon@example.com' WHERE name = 'HyeonLo';

 

• 데이터 삭제 (DELETE)
  • MySQL과 PostgreSQL 동일

DELETE FROM users WHERE name = 'HyeonLo';

---

3. MySQL과 PostgreSQL 주요 SQL 문법 차이!!!

 

• AUTO_INCREMENT vs SERIALMySQL

MYSQL : AUTO_INCREMENT

MySQL에서 기본 키(primal key)를 자동 증가

<예시 코드>
CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMAL KEY,
    	name VARCHAR(255)
);

 

PostgreSQL : SERIAL

PostgreSQL에서는 SERIAL 키워드를 사용해 자동 증가 열을 생성

<예시 코드>
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

MySQL은 AUTO_INCREMENT를 PostgreSQL은 SERIAL를 사용하여 둘 다 자동으로 증가하는 값을 부여하지만, PostgreSQL의 SERIAL은 내부적으로 시퀀스를 사용한다는 차이

 

• 문자열 처리 : CONCAT vs || (operator)

MySQL : CONCAT

MySQL에서는 문자열을 연결할 때 CONCAT 함수를 사용하여 여러 문자열을 하나로 결합할 때 사용

<예시 코드>
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

 

PostgreSQL : || (operator)

PostgreSQL에서는 문자열 결합 시 || 연산자를 사용해 함수 대신 간단한 연산자를 사용해 문자열을 연결할 수 있다.

<예시 코드>
SELECT first_name || ' ' || last_name AS full_name FROM users;

 

MySQL은 CONCAT을  PostgreSQL은 || 연산자를 사용하는 것이 특징이다 (PostgreSQL에서도 CONCAT() 함수 사용 가능)

 

 

• INSERT ON DUPLICATE KEY UPDATE vs. INSERT INTO ON CONFLICT

MySQL : INSERT ON DUPLICATE KEY UPDATE

MySQL에서는 중복된 키(예 : 기본 키)가 있을 경우 기존 데이터를 업데이트 하는 기능으로 사용

<예시 코드>
INSERT INTO users (id, name)
VALUES (1, 'HyeonLo')
ON DUPLICATE KEY UPDATE name = 'MySQL HyeonLo';

 

PostgreSQL : INSERT INTO ... ON CONFLICT

PostgreSQL에서는 비슷한 기능을 INSERT INTO ... ON CONFLICT 사용하여 구현
중복된 키나 고유 제약 조건이 충돌할 경우, DO UPDATE로 데이터를 수정할 수 있다

<예시 코드>
INSERT INTO users (id, name)
VALUES (1, 'HyeonLo')
ON CONFLICT (id) DO UPDATE SET name = 'PostgreSQL HyeonLo';

 

MySQL은 INSERT ON DUPLICATE KEY UPDATE 를 사용하고, PostgreSQL은 ON CONFLICT 구문을 사용해 중복된 데이터를 처리할 수 있다.
PostgreSQL의 방식이 좀 더 유연하게 다양한 충돌 상황을 다룰 수 있다.

 

• 정렬 NULL 처리: IS NULL vs. NULLS FIRST/LAST

MySQL : IS NULL

MySQL에서는 기본적으로 NULL 값이 가장 먼저 정렬된다. IS NULL을 사용해 따로 조건을 설정 가능

<예시 코드>
SELECT * FROM users ORDER BY name IS NULL, name ASC;

 

PostgreSQL : NULLS FIRST / LAST

PostgreSQL에서는 NULLS FIRST / LAST 를 사용해 명시적으로 NULL값을 맨 앞으로 또는 맨 뒤로 정렬 가능

<예시 코드>
SELECT * FROM users ORDER BY name NULLS LAST;

 

MySQL에서는 NULL 정렬을 기본적으로 가장 먼저 처리하지만, PostgreSQL에서는 명시적으로 NULLS FIRST 또는 NULLS LAST 를 지정해서 정렬 방식을 결정할 수 있다.

 

 

---

• 마무리

오늘은 MySQL과 PostgreSQL의 개요와 주요 특징, 그리고 차이점에 대해서 배웠다. 두 데이터베이스는 모두 관계형 데이터베이스(RDBMS)이지만, 각자 다른 목표를 가지고 발전해왔다. MySQL은 빠른 읽기 성능과 경량화된 구조 덕분에 웹 애플리케이션에서 자주 사용되며, PostgreSQL은 ACID 준수와 복잡한 트랜잭션 처리에 강점이 있어 데이터 무결성이 중요한 시스템에서 많이 활용된다.
이번 TIL을 통해 두 데이터베이스의 특성을 명확히 이해할 수 있었으며, 프로젝트 환경에 맞게 올바른 DBMS를 선택할 수 있는 기준을 세울 수 있었다. 앞으로는 이 지식을 바탕으로 실제 개발 환경에서 MySQL과 PostgreSQL을 더 깊이 있게 사용해보는 연습을 해야겠다!