PostgreSQL vs MySQL - 2. DDL Operation (+DCL)

글을 작성하기 전에

PostgreSQL DB 운영을 먼저 시작하고 MySQL DB 운영을 처음 접했을 때 놀랐던건 DDL Operation에 대한 수행시간 및 리스크였습니다. DBA 입장에서 운영서버의 스키마를 변경/관리하는 DDL은 Lock을 발생 시킬 수 있으므로 서비스에 대한 영향도를 검토 이후에 직접 수행 해야하는 것은 당연하지만, PostgreSQL DDL 작업 시 작업간 모니터링을 포함한다면 비교적 짧은시간 내 작업이 완료되었으나 MySQL의 경우에는 Online DDL이 5.6버전 이후에서야 지원되기 시작하였고 수행시 리스크가 많이 있어 다양한 3rd Party 툴을 사용하는 경우가 많이 있었습니다. 이 글을 통해 Operation 시 검토 사항에 대해 정리하고 각각의 DBMS를 비교해보려고 합니다.

MySQL과 PostgreSQL은 새로운 버전이 릴리즈됨에 따라 포스팅할 내용들의 개선점이 적용되었을 수 있으므로, 댓글을 통해 정보 공유해주시면 감사하겠습니다.

 

What is DDL??

DDL 이란 data definition language의 약자로, 관계형 데이터베이스의 구조를 정의하는 언어를 의미합니다
테이블, 인덱스 및 사용자와 같은 데이터베이스의 객체를 만들고 수정하기 위한 목적으로 사용되며 DDL 구문은 CREATE, DROP, ALTER, RENAME, TRUNCATE 등입니다.

PostgreSQL vs MySQL - 2. DDL Operation

MySQL과 PostgreSQL 모두 DDL 수행 시 Lock을 유발할 수 있습니다. MySQL에서는 MySQL 엔진의 잠금 중 Metadata Lock이 발생 할 수 있으며, PostgreSQL에서는 엔진/스토리지 잠금이 분리되어 있지 않기 때문에 AccessExclusiveLock이 발생 할 수 있습니다. 

PostgreSQL

PostgreSQL은 MySQL의 Instant DDL 알고리즘처럼 카탈로그만 변경하는 방식으로 구현되어 수행되는 시간이 짧음. 그럼에도 불구하고 수행를 위해서는 순간적으로 AccessExclusiveLock이 발생할 수 있기때문에 DBA 모니터링 필요.

 

PostgreSQL DDL 시 고려사항

- 특히 운영서버에서 자주 활용되는 ALTER TABLE ~ ADD COLUMN의 경우 Default 값이 없다면 매우 짧은 시간내에 완료

- 11버전 이후에 대해서는 Default 값이 있는 컬럼에 대해서도 이후의 추가되는 로우에 적용 되고, 조회 시 Default 값을 불러오는 로직이 포함

- CREATE INDEX의 경우 CONCURRENTLY 옵션을 추가한다면 온라인으로 수행 가능 

- 테이블을 다시 쓰는 경우는 컬럼의 자료형을 변경하거나, 실시간으로 변경가능한 Default 값(ex) clock_timestamp()) 을 추가 하는 경우임

MySQL

MySQL은 5.6버전부터 online DDL을 지원했기 때문에 DDL 작업의 리스크를 줄이기 위해 다양한 3rd Party tool(pt-osc, gh-ost)을 이용하거나 다양한 방식으로 업데이트.

 

MySQL DDL 시 고려사항

- DDL 마다 수행 가능한 알고리즘이 분리되어 있음 (https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html)

- 운영서버에서 자주 활용되는 ALTER TABLE ~ ADD COLUMN의 경우 MySQL 8.0부터 Instant 알고리즘 적용

 ※  ADD Column시 Instant 알고리즘 불가한 경우

- MySQL 8.0.12버전 이하

- 컬럼 추가시 AFTER, BEFORE 등의 구문으로 위치를 지정할 때 (PostgreSQL에서는 해당 구문 미지원)

- 대상 테이블이 FULLTEXT 인덱스를 가질 때

- ALTER TABLE 구문 내 다른 오퍼레이션이 포함될 때  

- instant 알고리즘이 수행이 불가할 경우(버전이나 다른 오퍼레이션) inplace/Copy 수행방식으로 수행

- Inpace 알고리즘 수행 시, 원본테이블에서 작업이 수행되지만 DML 변동사항이 기록되어야하며 innodb_online_alter_log_max_size 값을 초과할 경우 작업이 롤백된다.

- copy 알고리즘 수행 시, 원본테이블의 복사본 테이블을 생성하여 데이터를 복제하는 방식, 수행간 DML이 불가하며 작업중 리소스 제한 기능이 없음

- 3rd party 툴인  pt-osc, gh-ost 를 사용하는 경우가 많이 있으며 chunk 단위로 리소스 설정이 가능하고 DML 수행에 제한이 없어 대용량 테이블의 경우 해당 툴을 사용( 추후 포스팅에 상세하게 정리 예정)

PostgreSQL vs MySQL -  (번외) DCL Operation

What is DCL??

DCL 이란 Data Control Language의 약자로, 관계형 데이터베이스에 접근하고 객체들을 사용하도록 권한을 주고 회수하는 명령어를 의미합니다.

DCL 구문은 GRANT, REVOKE 등입니다.

 

MySQL을 먼저 접한 사람들에게 가이드 하는 PostgreSQL DCL의 차이점

- PostgreSQL은 MySQL과 다르게 권한을 데이터베이스 → 스키마 → 테이블 순으로 부여해야한다.

- PostgreSQL은 식별자로 "(double quote)을 사용합니다. (MySQL - `(backticks)) "를 붙히지 않으면 모든 문자는 소문자로 변환된다

- PostgreSQL은 User Privileges 와 Access Privileges가 구분되며, 실제 사용자는 Access Privileges만 신경쓰면 된다.
(User Privileges란 계정생성시 부여하는 권한 ref.https://www.postgresql.org/docs/current/sql-createuser.html)
- Aurora PostgreSQL에서는 pg_hba.conf를 통해 ACL(MySQL 의host) 를 관리하지만 Aurora PostgreSQL에서는 pg_hba.conf를 제어할수 없으므로 NACL/SG를 통한 관리가 선행되어야 한다.
- MySQL에서는 스키마에 CRUD 권한을 부여해놓으면 신규 오브젝트 생성시에도 동일한 정책이 적용되나 PostgreSQL에서는 적용되지 않는다.  (default privileges 적용 필요)
- Default 스키마인 Public에는 누구나 접속이 가능하도록 권한부여가 되어 있으므로 되도록 Public 스키마를 사용하지 않아야한다.
- PostgreSQL에서는 계정마다 스키마를 검색하는 순서인 search_path가 설정되어 있는데, Default는 {$user}, public 이므로 Search_path의 변경이 필요할 수 있다.
- MySQL에서는 show grants for 계정명을 통해 권한을 확인 할 수 있지만, PostgreSQL은 데이터베이스, 스키마, 테이블 별 적용 되는 권한이 다르므로 \l,\dn,\dp+ 등의 명령어로 각각의 오브젝트를 확인해야 한다.

- 오브젝트 단위가 아닌 계정별 부여되어 있는 권한을 확인하기 위해서는 information_schema 내에 있는 뷰들을 활용해야한다. (table_privilieges)