PostgreSQL vs MySQL: 파티셔닝, 복제, 쿼리 최적화, 그리고 그 이상
EDB 팀
2024년 9월 23일
오픈 소스 데이터베이스 세계에서 PostgreSQL과 MySQL은 가장 인기 있고 널리 사용되는 두 가지 시스템으로 손꼽힙니다. 두 시스템은 많은 유사점을 공유하지만, 초보자와 노련한 DBA 모두를 혼란스럽게 할 수 있는 주목할 만한 차이점도 가지고 있습니다.
이 글에서는 두 시스템을 심도 있게 비교하면서, 두 시스템의 유사점과 차이점을 살펴봅니다. 특히, 오픈 소스 데이터베이스에 대한 이해를 높이고자 하는 분들에게 조직이나 응용 프로그램에 적합한 시스템을 결정하는 데 도움이 될 것입니다. SQL 구문과 준수, 사용 용이성, 사용 가능한 기능, 사용자 정의 가능성, 성능, 확장성 등의 차이점을 살펴봅니다.
포괄적인 분석 결과, PostgreSQL이 광범위한 기능 세트와 강력한 커뮤니티 지원을 바탕으로 더 나은 선택이라는 결론을 내렸습니다. MySQL의 직관적인 인터페이스는 간단한 응용 프로그램에 효율적이지만, PostgreSQL은 복잡한 응용 프로그램이나 대량의 데이터를 처리하는 응용 프로그램에 적극 권장됩니다.
PostgreSQL vs. MySQL: 어떤 것을 선택해야 할까요?
PostgreSQL과 MySQL은 모두 다양한 실시간 응용 프로그램을 지원하는 널리 사용되는 오픈 소스 데이터베이스입니다. MySQL이 세계에서 가장 인기 있는 데이터베이스로 인정받는 반면, PostgreSQL은 세계에서 가장 진보된 관계형 데이터베이스 관리 시스템(RDBMS)으로 자주 묘사됩니다. PostgreSQL과 달리 MySQL은 SQL 표준을 완전히 준수하지 않으며 PostgreSQL에서 사용할 수 있는 많은 기능이 부족하기 때문에 PostgreSQL이 점점 더 인기를 얻고 개발자들 사이에서 선호되는 선택이 되고 있습니다.
오라클이 MySQL을 인수한 이후, 이 데이터베이스는 엔터프라이즈 버전과 오픈 소스 버전의 두 가지 버전으로 나뉘게 되었습니다. 후자의 경우, 오라클이 MySQL의 개발을 통제하고 있다는 이유로 사용자들로부터 비판을 받고 있습니다. 반대로, PostgreSQL은 포괄적인 엔터프라이즈급 기능 목록으로 인해 전 세계적으로 선호되고 있습니다. 다양한 회사들의 상당한 기여를 통해 PostgreSQL의 기능을 향상시키는 데 전념하는 글로벌 커뮤니티에 의해 개발되어, 다른 오픈 소스 및 상업용 데이터베이스와 비교해도 기능 면에서 경쟁력이 있고 경쟁력이 매우 높습니다.
왜 PostgreSQL인가?
PostgreSQL은 오픈 소스이며, 기능이 풍부한 객체 관계형 데이터베이스 관리 시스템(ORDBMS)으로, Oracle과 같은 실시간 최고급 데이터베이스와 경쟁합니다. 개발자들은 또한 온프레미스 및 클라우드 환경에서 데이터베이스의 설정과 사용을 단순화할 수 있기 때문에 PostgreSQL을 NoSQL 데이터베이스로 선택합니다. 프라이빗 또는 퍼블릭 클라우드에 수많은 데이터베이스가 있는 환경에서 PostgreSQL 인스턴스 구축을 자동화하면 상당한 시간을 절약할 수 있습니다. 또한 Docker 컨테이너를 포함한 모든 플랫폼에서 널리 채택되고 있습니다.
어떤 종류의 응용 프로그램?
ACID를 완벽하게 준수하고 엔터프라이즈급인 PostgreSQL은 개발자와 DBA 모두에게 친화적입니다. 모든 영역에서 높은 트랜잭션과 복잡한 응용 프로그램에 가장 적합한 선택이며, 다양한 웹과 모바일 기반 응용 프로그램 서비스를 지원할 수 있습니다. 또한, PostgreSQL은 대량의 데이터에 대한 복잡한 보고 쿼리와 절차를 실행하는 데 탁월한 데이터 웨어하우스로 사용될 수 있습니다.
왜 MySQL인가?
MySQL은 오픈 소스와 상용 버전으로 제공되며, 상용 버전은 오라클에서 관리합니다. RDBMS 데이터베이스로서 설정과 사용이 간단하지만, 완전한 SQL 준수가 필요한 응용 프로그램에는 적합하지 않을 수 있습니다. MySQL은 SQL 표준과 관련하여 상당한 제한이 있으므로, 내결함성 데이터베이스에서 소량의 데이터를 처리하는 간단한 웹 응용 프로그램에 더 적합합니다. 또한, MySQL의 통합 기능은 제한되어 있어 이기종 데이터베이스 환경에서 사용하기가 복잡합니다.
어떤 종류의 응용 프로그램?
MySQL은 부분적으로 SQL 규격에 맞는 데이터베이스로, 간단한 웹 응용 프로그램이나 간단한 스키마 설계와 간단한 SQL 쿼리 작업이 필요한 응용 프로그램에 적합합니다. 대량의 데이터를 다루는 복잡한 응용 프로그램에는 적합하지 않습니다.
사용의 용이성
PostgreSQL은 구조화된 데이터와 비구조화된 데이터를 모두 처리할 수 있는 RDBMS 기능과 성능을 포괄적으로 제공하는 사용자 친화적인 데이터베이스입니다. yum을 사용하거나 PostgreSQL 웹사이트의 소스 코드를 사용하여 Linux 기반 환경에 쉽게 설치할 수 있습니다. 소스 코드를 사용하여 설치하면 설치 과정을 보다 세밀하게 제어할 수 있습니다.
MySQL은 사용의 용이성으로 잘 알려져 있습니다. MySQL 환경의 설치와 설정은 다양한 운영 체제에서 간단하게 이루어집니다. 그러나 다른 데이터베이스에 비해 SQL과 데이터베이스 기능의 제한으로 인해 효율적인 RDBMS 애플리케이션을 구축하는 데 어려움이 있을 수 있습니다.
구문
SQL 구문은 두 데이터베이스에서 모두 비슷합니다. 그러나 MySQL의 경우 주목할 만한 차이점은 모든 SQL 구문이 지원되는 것은 아니라는 점입니다. 지원되는 구문은 두 데이터베이스에서 모두 비슷합니다. 이에 대해서는 아래의 “쿼리” 섹션에서 자세히 살펴보겠습니다.
PostgreSQL 쿼리:
SELECT * FROM employees;MySQL 쿼리:
SELECT * FROM employees;데이터 유형
MySQL과 PostgreSQL은 모두 정수, 날짜, 타임스탬프와 같은 전통적인 유형부터 JSON, XML, TEXT와 같은 좀 더 복잡한 유형에 이르기까지 광범위한 지원 데이터 유형을 제공합니다. 그러나 복잡한 실시간 데이터 검색 요구 사항을 처리하는 기능에 차이가 있습니다. PostgreSQL은 네트워크 데이터 유형 및 비트 문자열과 함께 전통적인 SQL 데이터 유형(예: 숫자, 문자열, 날짜, 십진수 등)과 비정형 데이터 유형(예: JSON, XML, HSTORE)을 모두 지원합니다.
PostgreSQL은 ARRAY, NETWORK 유형, 공간 데이터를 저장하고 처리하기 위한 고급 공간 데이터 기능을 포함하는 기하학적 데이터 유형 등 더 광범위한 데이터 유형을 지원한다는 점에서 차별화됩니다. 지원되는 데이터 유형에 대한 자세한 정보는 여기에서 확인할 수 있습니다. 공간 데이터 유형과 기능을 처리하는 기능은 오픈 소스 확장 프로그램인 PostGIS를 통해 향상됩니다.
MySQL은 애플리케이션에서 다양한 데이터 형식을 처리하기 위해 다양한 데이터 유형을 지원합니다. 여기에는 정수, 문자 또는 문자열, 타임스탬프 및 시간대가 포함된 날짜를 저장하기 위한 전통적인 데이터 유형뿐만 아니라 이미지 같은 이진 데이터 저장을 위한 부울, 부동 소수점, 십진수, 큰 텍스트 및 BLOB도 포함됩니다. 특히, MySQL은 기하학적 데이터 유형을 지원하지 않습니다.
JSON: PostgreSQL vs.
PostgreSQL은 버전 9.2부터 JSON 데이터 유형을 지원하기 시작하면서 MySQL보다 더 발전된 JSON 데이터 기능을 제공하게 되었습니다. 여기에는 JSON 문서 내에서 효율적인 데이터 검색을 가능하게 하는 다양한 JSON 전용 연산자와 함수가 포함되어 있습니다. JSON을 이진 형식으로 저장하는 PostgreSQL 버전 9.4의 JSONB 기능은 GIN 인덱싱이라고도 알려진 전체 텍스트 인덱싱도 지원합니다. 이 개선 사항으로 인해 JSON 문서에 대한 전체 텍스트 검색 속도가 크게 향상되었습니다.
이와는 대조적으로, MySQL은 훨씬 늦은 버전인 5.7부터 JSON 데이터 유형에 대한 지원을 도입했습니다. JSON 데이터 열은 SQL을 사용하여 쿼리할 수 있고, JSON 속성은 색인할 수 있지만, JSON 전용 함수의 범위는 PostgreSQL에 비해 제한적입니다. MySQL의 중요한 제약은 JSON 열에 대한 전체 텍스트 인덱싱을 지원하지 않는다는 것입니다. MySQL은 SQL을 완전히 준수하지 않기 때문에, JSON 데이터를 저장하고 처리하는 데 가장 적합한 선택이 아닐 수 있습니다.
복제 및 클러스터링
MySQL과 PostgreSQL은 모두 복제 및 클러스터링 기능을 제공하여 데이터 작업을 수평적으로 분산할 수 있도록 합니다.
MySQL은 기본 복제본과 기본 복제본-다중 복제본 메커니즘을 지원하여 데이터 변경 사항이 SQL을 통해 기본 데이터베이스에서 복제본 데이터베이스로 복제되도록 합니다. 복제는 비동기적이기 때문에 성능과 확장성 측면에서 문제가 될 수 있습니다.
MySQL 복제의 주요 장점은 복제본이 읽기 전용이 아니라는 것입니다. 기본 데이터베이스가 충돌할 때 응용 프로그램이 복제본으로 전환되면 복제본은 읽기와 쓰기를 모두 수행할 수 있으므로 원활한 응용 프로그램 운영이 가능합니다. 그러나 DBA는 복제본이 복제 모드를 종료하고 모든 변경 사항이 기본 데이터베이스로 역복제되도록 해야 합니다. 이 작업은 장시간 실행되는 SQL을 처리할 때 느려질 수 있습니다.
또한 MySQL은 네트워크 데이터베이스(NDB) 클러스터도 지원합니다. 이 클러스터는 수평적 확장성을 필요로 하는 높은 트랜잭션 환경에 유용한 다중 주 복제 메커니즘이지만, 성능 및 지연 문제 발생을 피하기 위해 신중한 구현이 필요합니다.
PostgreSQL 복제는 그 신뢰성으로 인해 높은 평가를 받고 있습니다. MySQL과 달리, PostgreSQL의 복제는 WAL 파일을 기반으로 하기 때문에 더 빠르고, 더 신뢰할 수 있으며, 관리하기가 더 쉽습니다. PostgreSQL은 캐스케이딩 복제를 포함하여, 기본 복제본과 기본 복제본-다중 복제본 구성을 지원합니다. 스트리밍 또는 물리적 복제라고 불리는 이 복제는 동기식 또는 비동기식일 수 있습니다.
기본적으로 복제는 비동기적이며, 복제본은 읽기 요청을 처리합니다. 복제본의 데이터 스냅샷을 기본 복제본에 반영해야 하는 응용 프로그램의 경우, 동기식 복제가 유용합니다. 그러나 트랜잭션이 복제본에 커밋되지 않으면 기본 복제본이 중단될 수 있습니다.
테이블 수준의 복제는 Slony, Bucardo, Londiste, RubyRep와 같은 외부 오픈 소스 도구를 사용하여 수행할 수 있으며, 이 도구들은 모두 트리거 기반 복제를 활용합니다. 또한, PostgreSQL은 WAL 레코드를 사용하여 테이블 수준의 복제를 수행하고 트리거 기반 복제의 복잡성을 완화하는 논리적 복제를 지원합니다. 처음에는 pglogical이라는 확장 프로그램에 의해 지원되었던 논리적 복제는 버전 10부터 PostgreSQL 코어의 일부가 되었습니다.
뷰
MySQL은 뷰를 지원하지만, 뷰 내의 SQL이 사용하는 테이블의 수가 61개로 제한된다는 제한이 있습니다. 뷰는 물리적으로 데이터를 저장하지 않는 가상 테이블의 기능을 수행하며, MySQL은 구체화된 뷰를 지원하지 않습니다. 간단한 SQL로 만든 뷰는 업데이트할 수 있지만, 복잡한 SQL로 만든 뷰는 업데이트할 수 없습니다.
PostgreSQL은 MySQL과 비슷한 방식으로 작동하는 뷰를 지원합니다. 간단한 SQL로 구성된 뷰는 업데이트할 수 있지만, 복잡한 SQL로 구성된 뷰는 업데이트할 수 없습니다. 그러나 규칙을 사용하여 복잡한 뷰를 업데이트할 수 있는 해결 방법이 있습니다. 또한, PostgreSQL은 물리적으로 데이터를 저장해야 하는 경우 새로 고침 및 색인 생성이 가능한 구체화된 뷰를 지원합니다.
트리거
MySQL은 ‘INSERT’, ‘UPDATE’, ‘DELETE’ 구문에 대한 ‘AFTER’ 및 ‘BEFORE’ 이벤트에 대한 트리거를 지원합니다. 그러나 MySQL의 트리거는 동적 SQL 문이나 저장된 프로시저를 실행할 수 없습니다. 이러한 제한은 보다 복잡한 데이터베이스 작업을 처리하는 데 필요한 유연성에 영향을 미칠 수 있습니다.
PostgreSQL은 ‘INSERT’, ‘UPDATE’, ‘DELETE’ 이벤트에 대해 ‘AFTER’, ‘BEFORE’, ‘INSTEAD OF’ 트리거를 지원하는 보다 고급 트리거 기능을 제공합니다. 이러한 동적 실행 기능은 PostgreSQL 트리거를 보다 다양하게 만들어, 함수를 사용하여 복잡한 SQL 작업을 효율적으로 처리할 수 있도록 합니다.
CREATE TRIGGER 감사AFTER INSERT OR UPDATE OR DELETE ON employeeFOR EACH ROW EXECUTE FUNCTION employee_audit_func();저장 프로시저
저장 프로시저는 복잡한 데이터 추출 요구 사항을 해결하는 데 중요한 요소이며, 개발자들은 종종 저장 프로시저를 데이터베이스 개발 프로세스에 통합합니다. MySQL과 PostgreSQL 모두 저장 프로시저를 지원하지만, MySQL은 표준 SQL 구문만 지원하고, PostgreSQL은 보다 정교한 프로시저를 제공합니다.
PostgreSQL은 저장된 프로시저를 함수로 구현하는데, 이 함수에는 ‘RETURN VOID’ 절이 있습니다. 이 기능은 Ruby, Perl(PlPerl), Python(PlPython), TCL, Pl/PgSQL, SQL, JavaScript 등 MySQL에서는 사용할 수 없는 여러 프로그래밍 언어를 지원하기 때문에 개발자들이 선호하는 기능입니다.
쿼리
앞서 언급했듯이, MySQL은 완전한 SQL 호환 데이터베이스가 아니며 모든 SQL 기능을 지원하지 않습니다. 이러한 한계 때문에 특히 고급적이고 복잡한 SQL이 필요한 데이터 웨어하우징 애플리케이션의 경우 개발자들에게 어려운 선택이 될 수 있습니다.
MySQL을 선택할 때 다음과 같은 제한 사항을 고려하십시오.
- 특정 ‘UPDATE’ SQL 결과는 예상치 못한 결과가 나올 수 있으며 아래와 같이 SQL 표준과 일치하지 않을 수 있습니다.
설명
mysql> select * from test;+------+------+| c | c1 |+------+------+| 10 | 100 |+------+------+1 row in set (0.01 sec)mysql> update test set c=c+1, c1=c;쿼리 OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from test;+------+------+| c | c1 |+------+------+| 11 | 11 |+------+------+1 row in set (0.00 sec)
SQL 표준에 따른 예상 결과는 다음과 같습니다.Explain
mysql> * from ;
+——+——+
| c | c1 | +——+——+ | 11 | 10 |
+------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 11 | +------+------+ | 11 | 1+------+------+- UPDATE 또는 DELETE 문을 실행할 수 없으며, 동일한 테이블에서 SELECT를 실행할 수 없습니다. 다음은 DELETE 작업의 예입니다.
mysql> delete from test where c in (select t1.c from test t1, test t2 where t1.c=t2.c);ERROR 1093 (HY000): - ‘LIMIT’ 절은 하위 쿼리에서 허용되지 않습니다.
mysql> select * from test where c in (select c from test2 where c<3 limit 1);ERROR 1235 (42000):MySQL은 여전히 ‘LIMIT’ 및 ‘IN/ALL/ANY/SOME’ 서브쿼리를 지원하지 않습니다.
또한, MySQL은 자주 사용되는 ‘FULL OUTER JOINS’, ‘INTERSECT’, ‘EXCEPT’와 같은 몇 가지 표준 SQL 절에 대한 지원이 부족합니다. 게다가, 부분 인덱스, 비트맵 인덱스, 표현식 인덱스와 같은 중요한 인덱스 유형이 지원되지 않아 쿼리 성능의 속도와 효율성에 영향을 미칩니다.
PostgreSQL은 반대로, SQL 표준 기능의 포괄적인 배열을 지원하는 완전한 SQL 호환 데이터베이스입니다. 이러한 유연성 덕분에 어떤 영역의 어떤 응용 프로그램이든 PostgreSQL을 데이터베이스로 사용할 수 있게 되어, 온라인 트랜잭션 처리(OLTP), 온라인 분석 처리(OLAP), 데이터 웨어하우스(DWH) 환경에서 널리 사용되고 있습니다. 이 때문에 PostgreSQL은 복잡한 SQL을 작성해야 하는 개발자들에게 최적의 선택으로 각광받고 있습니다.
파티셔닝
MySQL과 PostgreSQL은 모두 큰 테이블의 쿼리 성능을 향상시키기 위해 테이블 파티셔닝을 지원하지만, 각 데이터베이스마다 고려해야 할 특정 제한 사항이 있습니다.
MySQL은 RANGE, LIST, HASH, KEY, COLUMNS(RANGE와 LIST) 파티션 유형을 통해 선언적 테이블 파티셔닝을 제공합니다. 또한 서브 파티셔닝도 지원합니다. 그러나 이 기능은 DBA에게도 특정 제한 사항을 제시할 수 있습니다.
- MySQL 버전 8.0부터 테이블 파티셔닝은 MyISAM과 같은 다른 엔진을 제외한 InnoDB와 NDB 스토리지 엔진으로 제한됩니다.
- 파티셔닝이 가능하려면 파티션 키 열이 테이블의 모든 PRIMARY 및 UNIQUE KEY 제약 조건의 일부여야 합니다. 또는 PRIMARY 또는 UNIQUE KEY가 없는 테이블을 파티셔닝할 수 있는데, 이는 RDBMS 세계에서는 흔하지 않습니다.
- 테이블 파티션을 테이블 스페이스에 배치하는 기능은 MySQL 버전 5.7.24부터 제거되어, 테이블 파티션을 통한 디스크 I/O 밸런싱의 잠재적 이점이 감소했습니다.
mysql> create table emp (id int not null, fname varchar (30), lname varchar(30), store_id int not null ) partition by range (store_id) ( partition p0 values less than (6) tablespace tbs, partition p1 values less than(20) tablespace tbs1, partition p2 values less than (40) tablespace tbs2);ERROR 1478 (HY000): InnoDB : 공유 테이블 스페이스에 파티션된 테이블을 사용할 수 없습니다.mysql>PostgreSQL 파티셔닝에는 상속을 통한 테이블 파티셔닝과 선언적 파티셔닝이라는 두 가지 방법이 있습니다. 선언적 파티셔닝은 버전 10에 도입되었으며, MySQL과 유사한 기능을 수행하는 반면, 상속을 통한 파티셔닝에는 트리거 또는 규칙이 필요합니다. 대규모 데이터 세트에 대한 적절한 파티셔닝 전략은 성능을 크게 향상시키며, 지원되는 유형에는 RANGE, LIST, HASH가 포함됩니다. 선언적 파티셔닝은 상속 기반 파티셔닝에서 이전에 제기된 성능 문제를 개선합니다.
PostgreSQL의 두 가지 파티셔닝 전략 모두 고유한 장점과 한계가 있습니다.
- MySQL과 마찬가지로 PostgreSQL의 선언적 파티셔닝은 파티션 키 열이 모든 PRIMARY 및 UNIQUE KEY 제약 조건의 일부가 되어야 합니다.
- 상속 분할에서는 자식 테이블이 기본 테이블로부터 PRIMARY 또는 UNIQUE KEY 제약 조건을 상속받을 수 없습니다.
- 기본 테이블에 대한 ‘INSERT’와 ‘UPDATE’ 작업은 자동으로 자식 테이블로 리디렉션되지 않습니다. 이러한 리디렉션과 새로운 분할의 자동 생성을 위해서는 트리거나 규칙을 구현해야 합니다.
테이블 확장성
테이블이 커질수록 성능 문제가 발생할 수 있는데, 테이블에 대한 쿼리를 실행하는 데 더 많은 리소스와 시간이 필요하기 때문입니다. 따라서 성능을 유지하려면 테이블을 효율적으로 설계하는 것이 중요합니다. MySQL과 PostgreSQL은 이 문제에 대한 다양한 솔루션을 제공합니다.
MySQL은 B-트리 인덱싱과 파티셔닝을 지원하여 큰 테이블에 대한 쿼리 성능을 향상시킵니다. 그러나 MySQL은 비트맵, 부분, 기능적 인덱스를 지원하지 않으므로 DBA가 조정할 수 있는 옵션이 제한적입니다. 큰 테이블을 파티셔닝하면 성능이 향상될 수 있지만, MySQL은 파티셔닝된 테이블을 일반 테이블 스페이스에 배치할 수 없도록 제한하므로 효과적인 I/O 밸런싱이 어렵습니다.
PostgreSQL은 확장 가능한 테이블에서 데이터 작업을 향상시키기 위해 여러 가지 인덱싱 옵션과 두 가지 유형의 파티셔닝을 제공합니다. 표현식 인덱싱, 부분 인덱싱, 비트맵 인덱싱, 그리고 전체 텍스트 인덱싱은 큰 테이블에서 쿼리 성능을 크게 향상시킬 수 있습니다. 게다가, PostgreSQL에서는 테이블 파티션과 인덱스를 서로 다른 디스크 파일 시스템에 있는 별도의 테이블 스페이스에 배치할 수 있기 때문에 테이블 확장성이 크게 향상됩니다.
PostgreSQL에서 수평적 테이블 수준의 확장성을 달성하기 위해서는 CitusDB, Greenplum, IBM Netezza와 같은 Postgres 기반의 상용 제품이 필요할 수 있습니다. 오픈 소스 PostgreSQL 자체는 수평적 테이블 파티셔닝을 지원하지 않습니다. PostgresXC는 사용 가능한 옵션이지만, 성능과 유지 관리 오버헤드로 인해 선호도가 낮습니다.
저장
데이터 저장은 모든 데이터베이스 시스템의 중요한 측면입니다. PostgreSQL과 MySQL은 데이터 저장을 위한 몇 가지 옵션을 제공합니다. 이 옵션은 테이블과 인덱스와 같은 물리적 데이터베이스 객체를 디스크에 저장하는 것을 포함합니다. 이 섹션에서는 일반적인 저장소와 플러그인 저장소라는 두 가지 유형의 저장소 옵션을 살펴봅니다.
PostgreSQL은 테이블스페이스라고 알려진 일반적인 저장소 메커니즘을 사용합니다. 이 메커니즘은 테이블, 인덱스, 구체화된 뷰와 같은 물리적 객체를 수용합니다. 테이블스페이스는 여러 물리적 위치에 걸쳐 물리적 객체를 그룹화하고 저장함으로써 I/O의 효율적인 분배를 가능하게 합니다. 그러나, PostgreSQL은 현재 플러그형 스토리지 엔진을 지원하지 않지만, 향후 릴리스에서 이 기능이 지원될 것으로 예상됩니다.
MySQL은 PostgreSQL과 유사하게 InnoDB 엔진 내에 테이블스페이스 옵션을 제공하여, DBA가 물리적 객체를 그룹화하고 저장함으로써 I/O 분배를 향상시킬 수 있도록 합니다. 또한, MySQL은 플러그형 스토리지 엔진을 지원하여 OLTP 및 데이터 웨어하우징과 같은 다양한 애플리케이션의 특정 스토리지 요구를 충족시킵니다. 이 기능은 플러그형 스토리지 기능이 플러그인 설치를 통해 활성화되기 때문에 MySQL의 가장 중요한 장점 중 하나입니다. 플러그형 스토리지를 구성하는 것은 복잡할 수 있지만, 애플리케이션은 이러한 복잡성에 영향을 받지 않습니다.
지원되는 데이터 모델
RDBMS 내의 NoSQL 기능은 JSON, XML, 기타 TEXT 데이터 유형과 같은 비정형 데이터를 효과적으로 처리할 수 있습니다.
MySQL은 제한적인 NoSQL 기능을 제공합니다. MySQL 버전 5.7부터 JSON 데이터 유형이 도입되었지만, 이 기능이 완전히 성숙하기 위해서는 추가적인 개발이 필요합니다.
반면, PostgreSQL은 지난 3년 동안 NoSQL 기능을 원하는 개발자들에게 널리 사랑받아 왔습니다. JSON과 JSONB 데이터 유형을 통해 PostgreSQL은 훨씬 빠르고 효율적인 JSON 기반 데이터 작업을 가능하게 합니다. JSON 데이터는 B-Tree와 GIN을 사용하여 색인할 수 있어 검색 성능이 향상됩니다. 또한, PostgreSQL은 XML 형식과 기타 복잡한 텍스트 데이터를 관리하기 위해 XML과 HSTORE 데이터 유형을 지원합니다. 공간 데이터 유형에 대한 지원으로 PostgreSQL은 종합적인 멀티 모델 데이터베이스로 자리매김하고 있습니다.
보안
기업에서 데이터베이스 보안은 무단 액세스로부터 데이터를 보호하는 데 중요한 역할을 합니다. 데이터베이스 내의 다양한 수준에서 보안 액세스가 구현됩니다. 여기에는 객체 수준과 연결 수준이 포함됩니다.
MySQL은 ROLES와 PRIVILEGES를 통해 데이터베이스, 객체, 연결 액세스를 관리합니다. 각 사용자는 연결하는 모든 개별 IP 주소에 대해 SQL 명령을 사용하여 연결 권한을 부여받아야 합니다. 그렇지 않으면 서브넷 내의 여러 IP 주소에 대해 한 번에 권한을 부여할 수 있습니다.
IP 주소 “192.168.1.1”의 사용자 “testuser”에게 데이터베이스 “testdb”에 대한 모든 권한을 부여하는 명령의 예는 다음과 같습니다.
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser@'192.168.1.1' IDENTIFIED BY 'newpassword' ;사용자가 192.168.1 서브넷 내의 모든 IP에서 접속하는 경우, 명령은 다음과 같습니다:
GRANT ALL PRIVILEGES ON testdb.* TO 'testuser@'192.168.1.*' IDENTIFIED BY 'newpassword' ;권한을 부여할 때는 반드시 암호를 지정해야 합니다. 그렇지 않으면 사용자가 연결할 수 없습니다.
또한, MySQL은 네트워크를 통한 SSL 기반 연결을 지원하고 SE-Linux 모듈을 통해 보안을 제공합니다. MySQL 엔터프라이즈 버전에서는 LDAP(Lightweight Directory Access Protocol) 및 PAM(Privileged Access Management)과 같은 외부 인증 시스템과의 통합이 가능합니다.
PostgreSQL은 ‘GRANT’ 명령을 통해 정의된 ROLES와 PRIVILEGES를 사용하여 데이터베이스 객체와 데이터에 접근할 수 있도록 해줍니다. 연결 인증은 IP 주소, 사용자 이름, 접근 유형을 나열하는 ‘pg_hba.conf’ 인증 파일을 통해 보다 간단하게 관리할 수 있으며, 보다 직관적이고 신뢰할 수 있는 접근 방식을 제공합니다. ‘pg_hba.conf’ 파일의 샘플 항목은 다음과 같습니다.
host database user address auth-method [md5 or trust or reject]PostgreSQL의 오픈 소스 버전은 SSL 기반 연결을 지원하고 LDAP, Kerberos, PAM을 포함한 외부 인증 시스템과 통합할 수 있어 효율성과 신뢰성을 모두 제공합니다.
분석 기능은 행 집합에 대해 집계를 수행합니다. 분석 기능에는 창 함수와 집계 함수의 두 가지 주요 유형이 있습니다. 집계 함수는 행 집합당 단일 값(예: SUM, AVG, MIN, MAX)을 제공하는 반면, 분석 함수는 각 행에 대한 값을 반환합니다. MySQL과 PostgreSQL 모두 다양한 분석 함수를 지원합니다. MySQL은 버전 8.0부터 일부 윈도우 함수를 도입했으며, PostgreSQL은 다음과 같은 다양한 윈도우 함수를 오랫동안 지원해 왔습니다.
CUME_DIST 현재 행의 상대적 순위를 반환합니다.
DENSE_RANK 갭 없이 파티션 내에서 현재 행의 순위를 매깁니다.
FIRST_VALUE 파티션 내에서 첫 번째 행에 대해 평가된 값을 반환합니다.
LAG 파티션 내에서 현재 행의 앞에 있는 지정된 물리적 오프셋 행에 있는 행에서 평가된 값을 반환합니다.
LAST_VALUE 파티션 내에서 마지막 행에 대해 평가된 값을 반환합니다.
LEAD 파티션 내의 현재 행 다음에 있는 행에서 평가된 값을 반환합니다.
NTILE 파티션 내의 행을 가능한 한 균등하게 나누고 각 행에 1부터 인수 값까지 정수를 할당합니다.
NTH_VALUE 정렬된 파티션 내의 n번째 행에 대해 평가된 값을 반환합니다.
PERCENT_RANK 현재 행의 상대 순위(rank-1)/(총 행 수-1)를 반환합니다.
RANK 갭을 사용하여 파티션 내의 현재 행의 순위를 매깁니다.
ROW_NUMBER 1부터 시작하여 파티션 내의 현재 행에 번호를 매깁니다.
MySQL은 PostgreSQL과 거의 동일한 윈도우 함수를 지원하지만, 다음과 같은 제한 사항이 있습니다.
- 윈도우 함수는 ‘UPDATE’ 또는 ‘DELETE’ 문에서 사용할 수 없습니다.
- 윈도우 함수에는 ‘DISTINCT’가 지원되지 않습니다.
- ‘NESTED’ 창 함수는 지원되지 않습니다.
관리(GUI 도구)
MySQL 데이터베이스는 Oracle의 SQL Developer, MySQL Workbench, DBeaver, OmniDB 등 다양한 GUI 도구를 사용하여 원격으로 액세스할 수 있습니다. MySQL 데이터베이스의 성능과 상태를 모니터링하기 위해 널리 사용되는 도구로는 Nagios, Cacti, Zabbix 등이 있습니다.
PostgreSQL도 마찬가지로 Oracle의 SQL Developer, pgAdmin, OmniDB, DBeaver 등을 사용하여 GUI로 관리할 수 있습니다. PostgreSQL의 성능과 상태를 모니터링하기 위해 Nagios, Zabbix, Cacti와 같은 도구도 널리 사용됩니다.
성능
MySQL 데이터베이스 성능 최적화는 제한된 옵션과 많은 인덱스 유형에 대한 지원 부족으로 인해 어려울 수 있습니다. 완전한 SQL 준수가 이루어지지 않으면 효율적이고 성능이 우수한 SQL 쿼리를 작성하기가 어렵습니다. MySQL은 또한 대량의 데이터를 처리하는 데 적합하지 않습니다. 테이블 스페이스는 여러 디스크에 데이터를 분산하기 위해 존재하지만, InnoDB 엔진으로 제한되어 테이블 파티션을 수용할 수 없습니다. 테이블에 액세스하는 간단한 쿼리를 가속화하기 위해 B-트리 인덱스를 생성하는 것이 도움이 될 수 있습니다.
PostgreSQL은 OLTP, OLAP, 데이터 DWH 등 다양한 작업 부하에 매우 적합합니다. SQL 표준을 완벽하게 준수하므로 효율적인 쿼리와 pl/pgsql 프로그램을 작성할 수 있습니다. B-트리, 비트맵, 부분, 전체 텍스트 등 다양한 인덱스를 지원하므로 전반적인 성능이 향상됩니다. 온라인 재색인 및 테이블 재구성을 통해 데이터 부하를 효과적으로 제거할 수 있습니다. 또한, PostgreSQL은 메모리 할당을 위한 다양한 구성 옵션을 제공하며, 분할된 테이블을 여러 테이블 스페이스에 분산시켜 디스크 I/O의 효율적인 균형을 유지할 수 있습니다.
채택
PostgreSQL은 세계에서 가장 진보된 오픈 소스 데이터베이스로 인정받고 있으며, 업무상 중요한 작업 부하를 처리하는 데 널리 사용되고 있습니다. PostgreSQL 커뮤니티는 EDB, 2ndQuadrant 등의 기업과 함께 PostgreSQL 채택이 전 세계적으로 계속 확대될 수 있도록 하는 데 중요한 역할을 하고 있습니다.
반면에, MySQL은 RDBMS 또는 ORDBMS 애플리케이션에 적합한 선택이 아닙니다. 오라클이 MySQL을 인수한 이후로, MySQL의 채택이 크게 감소했고, 오픈 소스 공간에서의 개발 진행이 지연되어 사용자 기반으로부터 비판을 받고 있습니다.
스택
스택은 웹 애플리케이션 개발을 용이하게 하도록 설계된 다양한 애플리케이션, 운영 체제, 데이터베이스 기술의 통합된 모음입니다.
PostgreSQL과 MySQL은 여러 조직과 서비스 제공업체에서 사용하는 다양한 스택의 필수적인 부분입니다. MySQL은 특히 Linux, Apache, MySQL/MongoDB, PHP/Python을 의미하는 LAMP 스택에서 널리 사용됩니다. 반대로 PostgreSQL은 Linux, Apache, Postgres, PHP/Python을 포함하는 LAPP 스택에서 두드러지게 사용됩니다.
PostgreSQL을 사용하려는 개발자들에게는 LAPP 스택이 매력적인 옵션입니다. LAPP 스택은 NoSQL과 RDBMS의 기능을 결합한 것입니다. Amazon과 VMware와 같은 주요 플랫폼 서비스 제공업체들은 LAPP 스택 모듈이 사전 설치된 서비스를 제공하기 시작하면서 LAPP 스택의 채택을 더욱 촉진하고 있습니다.
PostgreSQL로 전환하기
PostgreSQL은 다양한 기능과 PostgreSQL 개발자들의 강력한 개발 노력 덕분에 최고의 데이터베이스 선택입니다. 오늘날 대부분의 조직이 PostgreSQL을 사용하고 있으며, 많은 도메인이 애플리케이션에 이를 채택하고 있으며, 많은 사람들이 레거시 애플리케이션을 이 플랫폼으로 마이그레이션하려고 합니다. 레거시 Oracle 데이터베이스에서 전환하고 있으며, 몇 달이 아닌 며칠 안에 마이그레이션을 완료하려는 사람들에게는 EDB Postgres Advanced Server가 훌륭한 옵션입니다. 향상된 Postgres 데이터베이스가 Oracle 호환성과 엔터프라이즈급 보안 기능을 제공하기 때문입니다.
본문: PostgreSQL vs MySQL: Partitioning, Replication, Query Optimization, and More
EDB 영업 기술 문의: 02-501-5113
이메일: salesinquiry@enterprisedb.com
홈페이지 문의하기
