[Medium] Postgres vs MySQL

간단히 말해, 두 데이터베이스의 주요한 차이점은 기본 인덱스와 보조 인덱스를 구현하고, 데이터를 저장하고 업데이트하는 방법으로 요약됩니다. 이에 대해 더 살펴보겠습니다. 

기초

---

인덱스는 데이터베이스가 페이지로 구현한 노드 계층을 통해 키를 검색할 수 있게 해주는 데이터 구조(주로 B+ 트리)입니다. 트리 순회는 결과가 없는 페이지를 제거하고 결과가 있는 페이지를 좁혀나가는 과정을 거칩니다. 이 과정은 키가 존재하는 리프 페이지가 발견될 때까지 계속됩니다.

 

리프 노드 또는 페이지에는 정렬된 키와 그 값의 목록이 포함됩니다. 키가 발견되면 해당 값이 반환되고, 페이지는 데이터베이스 공유 버퍼에 캐시되어 이후 쿼리가 동일한 페이지의 키를 요청할 때 유용하게 사용될 수 있습니다.

B+ 트리 인덱스의 키는 인덱스가 생성된 테이블의 열(들)이고, 값은 데이터베이스가 다르게 구현하는 부분입니다. PostgreSQL과 MySQL에서 값이 무엇인지 탐구해봅시다.

MySQL

---

기본 인덱스에서 값은 모든 속성을 포함한 전체 행 객체입니다. 그래서 기본 인덱스를 클러스터형 인덱스(clustered indexes) 또는 인덱스-조직형 테이블(index-organized table)이라고 부르는 경우가 많습니다. 즉, 기본 인덱스는 테이블입니다.

 

기본 인덱스에서 키를 조회하면 키가 존재하는 페이지와 해당 키의 값(즉, 전체 행)을 찾을 수 있어 추가 열을 가져오기 위한 추가 I/O가 필요하지 않습니다.

 

보조 인덱스에서는 키가 인덱싱된 열(들)이고 값은 실제 행이 위치한 곳을 가리키는 포인터입니다. 보조 인덱스 리프 페이지의 값은 주로 기본 키입니다.

 

MySQL에서는 모든 테이블에 기본 인덱스가 있어야 하며, 모든 추가 보조 인덱스는 기본 키를 가리킵니다. MySQL 테이블에 기본 키를 생성하지 않으면 시스템이 이를 생성합니다

PostgreSQL

---

PostgreSQL에서는 기본 인덱스가 없으며, 모든 인덱스는 보조 인덱스이고 시스템이 관리하는 데이터 페이지의 튜플 ID를 가리킵니다. 힙에 로드된 테이블 데이터는 기본 인덱스 리프 페이지와 달리 정렬되지 않습니다. 따라서 행을 1-100까지 삽입하고 모든 행이 동일한 페이지에 있을 때, 나중에 120번 행을 업데이트하면 이 20개의 행이 다른 페이지로 이동해 순서가 뒤바뀔 수 있습니다. 반면 클러스터형 기본 인덱스에서는 키의 순서를 만족하는 페이지로 삽입해야 합니다. 그래서 PostgreSQL 테이블은 "힙 조직형 테이블(heap organized tables)"로 자주 불립니다.

 

PostgreSQL에서는 업데이트와 삭제가 실제로 삽입입니다. 모든 업데이트나 삭제는 새로운 튜플 ID를 생성하고, MVCC(Multi-Version Concurrency Control) 이유로 기존 튜플 ID는 유지됩니다. 이 부분은 나중에 더 자세히 다루겠습니다.

Queries Cost

---

SELECT * FROM T WHERE C2 = 'x2';

 

이 쿼리를 MySQL에서 실행할 때는 두 번의 B+ 트리 조회가 필요합니다. 먼저 보조 인덱스를 사용하여 x2의 기본 키를 찾고, 그런 다음 기본 인덱스를 사용하여 1을 조회하여 전체 행을 반환합니다(모든 속성을 반환하므로).

 

PostgreSQL에서는 보조 인덱스를 조회하는 것이 단 한 번의 인덱스 조회만 필요하고, 이어서 힙에서 전체 행이 있는 페이지를 가져오기 위해 단일 I/O가 필요합니다. B+ 트리 조회 한 번이 두 번의 조회보다 당연히 더 효율적입니다.

SELECT * FROM T WHERE PK BETWEEN 1 AND 3;

 

기본 키 인덱스에서 범위 쿼리를 수행할 때는 MySQL이 더 효율적입니다. 단일 조회로 첫 번째 키를 찾고, B+ 트리의 연결된 리프 페이지를 따라 이동하면서 인접한 키와 전체 행을 찾습니다.

 

PostgreSQL은 여기서 고전합니다. 보조 인덱스 조회가 동일한 B+ 트리 리프 페이지를 따라 이동하면서 키를 찾을 수는 있지만, 이는 튜플 ID와 페이지만 수집합니다. PostgreSQL은 여전히 힙에서 전체 행을 가져오기 위해 무작위 읽기를 수행해야 하며, 이 행들이 힙 전역에 분산되어 있을 수 있습니다. 

UPDATE T SET C1 = ‘XX1’ WHERE PK = 1;

 

MySQL에서 인덱싱되지 않은 열을 업데이트할 때는 해당 행이 있는 리프 페이지만 새 값으로 업데이트됩니다. 다른 보조 인덱스는 기본 키가 변경되지 않았기 때문에 업데이트할 필요가 없습니다.

 

PostgreSQL에서는 인덱싱되지 않은 열을 업데이트할 때 새로운 튜플이 생성되며, 모든 보조 인덱스가 새 튜플 ID로 업데이트되어야 할 수도 있습니다. 이는 많은 쓰기 I/O를 초래합니다. 2016년에 Uber가 PostgreSQL에서 MySQL로 전환한 주요 이유 중 하나가 이것이었습니다.

 

이 비교를 통해 MySQL과 PostgreSQL의 인덱스 동작 방식 및 성능 차이를 이해할 수 있습니다. 각 데이터베이스는 특정 쿼리와 워크로드에 대해 고유한 강점과 약점을 가지고 있습니다.

Processes vs Threads

---

MySQL은 스레드를 사용합니다. 이는 여러 이유로 효율적일 수 있습니다:

• 경량성: 스레드는 프로세스보다 가벼워서 시스템 자원을 덜 사용합니다.
• 메모리 공유: 스레드는 부모 프로세스의 가상 메모리 주소를 공유하므로 메모리 사용 효율성이 높습니다.
• 컨텍스트 스위칭: 스레드 간의 컨텍스트 스위칭은 TLB(Translation Look-aside Buffer)를 무효화하지 않기 때문에 상대적으로 빠릅니다. 이는 메모리 매핑이 동일하기 때문에 메모리 접근이 더 효율적입니다.

PostgreSQL은 프로세스를 사용합니다. 이는 스레드 기반 접근 방식과 비교하여 다음과 같은 장단점이 있습니다:

• 독립성: 각 프로세스는 독립된 가상 메모리를 가지므로 하나의 프로세스가 크래시하더라도 다른 프로세스에 영향을 미치지 않습니다.
• PCB vs. TCB: 프로세스 제어 블록(PCB)이 스레드 제어 블록(TCB)보다 크지만, 이는 프로세스가 독립된 자원과 환경을 가지고 있기 때문입니다.

Summary

---

각 데이터베이스 시스템의 장단점을 이해하고, 여러분의 사용 사례와 쿼리에 맞는 시스템을 선택하는 것이 중요합니다. MySQL의 스레드 기반 접근 방식은 가벼운 메모리 사용과 빠른 컨텍스트 스위칭을 제공하는 반면, PostgreSQL의 프로세스 기반 접근 방식은 더 높은 독립성과 안정성을 제공합니다. 따라서 어떤 데이터베이스가 더 적합한지는 사용자의 특정 요구 사항과 환경에 따라 달라질 수 있습니다.

 

결론적으로, 어떤 데이터베이스 시스템이 더 좋다고 단정 지을 수는 없습니다. 각각의 데이터베이스 시스템이 제공하는 기능과 성능을 이해하고, 여러분의 필요에 가장 적합한 것을 선택하는 것이 중요합니다.

원문

• https://medium.com/@hnasr/postgres-vs-mysql-5fa3c588a94e