[데이터 아키텍트] 프로젝트 착수 시 DBMS 체크포인트

0. Introduction

 

데이터 아키텍트로 일하면서 가장 큰 조바심은 DBMS에 대한 공부가 끝이 없다는 데에서 온다.  끝없어 보이는 공부거리를 마주하면서도 위안이 되는 포인트가 있다. 평생을 DB를 다루며 일해온 직업인들에게도 낯선 DBMS를 다뤄야 하는 프로젝트는 피할 수 없는 숙명이라는 점이다. 

 

세상에는 다양한 DBMS가 있다. RDBMS만 하더라도 Oracle, MySQL, SQL Server, PostgreSQL, DB2 등 다양한 DBMS가 존재한다. 이들은 모두 SQL로 조작이 가능하다는 공통점이 있지만, 각 DBMS 별 뚜렷한 특징을 가진다. DBMS별 개성이 다른만큼 한 기업 내에서도 서비스의 특성에 맞추어 DBMS를 다양하게 선택하여 사용하고 있다. 데이터 아키텍트들도 각 개인의 커리어 여정에 따라 많이 사용해 보고 잘 알고 있는 DBMS가 다를 수밖에 없다. 모든 DBMS를 통달하는 건 불가능한 일에 가깝다.

 

그래서 오늘은, DBMS에 대해서 새로운 공부를 하기보다는 이전의 경험들을 정리해보기로 했다. 프로젝트를 경험하면서 새로운 DBMS를 다루면서 느꼈던 차이점들, 특히 의사결정과 설계 전략에 차별점이 되었던 포인트들을 상기해 보았다. 차근차근 정리하다 보니 프로젝트 투입 시 확인해보아야 할, 또는 공부해 두어야 할 체크포인트가 완성되었다.

---

 

1. 기본정보

우선적으로 확인해야 할 사항은 컨설팅 대상 시스템 DB에 대한 개괄적인 사항이다.

 

DBMS 버전 및 구성

DBMS는 무엇을 사용하고 있는지와 정확한 버전을 확인한다.

이중화 여부나 개발계와 운영계가 별도로 구축되어 있는지에 따라서 프로파일링 전략이 달라지므로 이에 대해서도 확인해야 한다. 

OS에 따라서 Default 환경설정이 조금씩 달라지는 부분도 있어서 설치되어 있는 OS도 확인해 두면 좋다.

 

데이터 규모

그다음으로는 데이터 규모를 파악하여야 한다. 대체로 RFP에 기술되어 있는 경우가 많다.

• 유효 테이블 수
• 유효 컬럼 수
• 총 데이터 Row 수
• 코멘트 작성 비율

위 사항들은 쿼리를 통해 손쉽게 확인할 수 있지만, 프로젝트 담당자가 DB 조회 권한을 가지고 있지 않은 경우 확인에 시일이 걸릴 수 있다.

 

개인정보 보호 정책

산업군에 따라 개인정보 보호에 관한 수준이 다르기도 하고, 회사별로 보호 정책이 상이할 수 있다.

• 개인정보 암호화 정책
  • 암호화 대상
  • 암호화 방법
    • DBMS 자체 암호화
    • 별도 솔루션 사용
• 개인정보 보관 정책
  • 개인정보 보관 기간
  • 파기 대상 및 방법

개인정보 보호는 데이터 거버넌스 정책을 수립하거나 데이터 모델링 시에도 고려가 필요한 부분이라 면밀한 확인이 필요하다.

---

2. 물리적 차이

DBMS에 따라서 데이터 스키마나 데이터의 저장, 처리 방식들이 상이할 수 있다. 

 

저장 방식

이 부분은 모델링 전략에 가장 큰 영향을 준다.

• 스토리지
  • Storage
  • In-Memory
• 저장 단위
  • Page
  • Row-store
  • Column-store
• 데이터 정렬
  • Heap
  • Index-organized 

저장 단위가 페이지인지, 그리고 데이터가 로우 단위로 저장되는지 혹은 컬럼 단위로 저장되는지에 따라 시스템이 어떤 식으로 활용되는지를 가늠해 볼 수 있다.

MariaDB처럼 PK의 순서에 따라 물리적으로 정렬 순서가 달라지는 경우, PK 설계 전략이 달라야 한다.

 

데이터 타입

각 DBMS는 고유한 데이터 타입을 가지고 있다.  공식문서를 통해 지원하는 데이터타입을 알아볼 필요가 있다.

• 데이터타입
• 길이 및 소수점 표시 방식
  • byte-size
  • 글자수 기준

DBMS에 따라서 데이터 길이(length)와 소수점(scale)을 선언할 때 바이트 기준으로 하는 경우가 있고, 글자수 기준으로 하는 경우가 있다. 한글을 포함하여 다국어들은 2 bytes나 3 bytes일 수 있기 때문에, 이 설정을 사전에 확인해야 한다.

 

다음으로 데이터타입별로 주로 발생하는 차이는 다음과 같다.

• 고정 길이 문자열
  • 빈 공간 패딩
  • 가변 길이형처럼 처리
• 숫자형
  • 최고 정밀도
  • 반올림/버림 처리
• 날짜 및 시간
  • 시간 정밀도
  • 타임존 관리
• 대용량 객체(LOB)
  • 저장 방식
    • 테이블 내부
    • 별도 공간 할당
  • 최대 크기

데이터타입명이 동일하게 CHAR이더라도 남는 길이에 패딩을 하는지 여부에 있어 차이가 있을 수 있다. 숫자형 데이터 타입의 경우 최대로 선언 가능한 길이와 정밀도가 DBMS별로 상이하다.

대용량 객채(LOB)는 테이블 내에 저장되느냐 별도 공간이 할당되느냐에 따라 CRUD 성능이 달라질 수 있기 때문에 사전에 확인하고 해당 데이터의 조회/업데이트 빈도에 따라 맞게 설계해주어야 한다.

 

`NULL`의 처리

각 DBMS별로 `NULL`에 대한 정의와 처리가 다르다. 주요하게 확인해보아야 하는 사항은 다음과 같다.

• `NULL`의 정의
  • 길이 0인 VARCHAR 문자열 : 오라클, 액세스
  • Unknown Value : SQL Server
• 연산
  • `NULL` 간 비교 연산
    • 모든 `NULL` 값을 동일하게 여기고 처리 : SQL Server, DB2
    • 개별 `NULL` 값이 다르다고 여기고 처리 : MySQL, PostgreSQL
  • 문자열 연산
    • `NULL` 반환
    • `''`로 처리
• 정렬 순서
  • `NULL` Last : 오라클, PostgreSQL
  • `NULL` First : 액세스, SQL Server
• 기본키(PK) 및 인덱스
  • NULL 포함 가능
  • NULL 포함 불가

각 DBMS별 `NULL`의 처리 방식에 대한 사전 이해가 부족하면, 원하는 연산 결과를 얻지 못하거나 잘못된 집합을 조회하게 될 수 있으므로 주의가 필요하다. 또한 PK와 인덱스 전략도 달라질 수 있기 때문에 상세한 파악이 필요하다.

 

더 자세한 내용은 지난 포스팅에 정리해 두었다. 

https://wvwv.tistory.com/99

DBMS별 NULL 처리 차이점에 대해 알아보자

---

3. 시스템 설정

시스템 설정에서 부터는 프로젝트 착수 이후 해당 DB에 대한 접근 권한이 주어졌을 때 확인이 가능한 부분이다.

트랜잭션 및 격리 수준

DBMS에 따라서 트랜잭션 및 격리 수준의 기본값이 다르고, 설정에 따라서도 달라질 수 있다.

• Transaction Isolation Level
• Lock Timeout 설정

 

블록 사이즈

성능 최적화 업무를 해야 한다면 블록 사이즈 확인이 필요하다. 오라클에서는 다음 쿼리로 확인할 수 있다.

SHOW PARAMETER block_size;

SELECT value 
FROM V$PARAMETER
WHERE name = 'db_block_size';

 

 

인코딩 방식

DBMS에 따라서 인코딩 방식이 다를 수 있다. EUCKR, UTF-8 등이 사용될 수 있고, 인코딩 방식에 따라서 한글 정렬 방식에도 차이가 있을 수 있다.

 

지난번 PostgreSQL을 사용하는데 한글 데이터가 글자수 기준으로 정렬되는 현상이 발생하여 확인해 보니 collate 설정이 "en_US.UTF-8"로 되어 있었다.

 PostgreSQL에서 collate 설정을 확인하는 쿼리는 아래와 같다.

SELECT datname, datcollate
FROM pg_catalog.pg_database;

 

해당 정렬 문제는 인덱스에도 동일한 현상이 발생하고 있을 수 있으므로 db설정을 바꿔주어야 한다. DB설정 수정이 어려운 경우, 조회 시 일부 구문 추가로 정렬을 바꿀 수 있다. 다음과 같이 ORDER BY절에 `COLLATE "C"`를 추가하면 글자수에 관계없이 제대로 정렬된다.

SELECT * 
FROM 테이블명
ORDER BY 컬럼명 COLLATE "C"

---

4. 테이블명/컬럼명 제약

데이터 거버넌스 정책 수립이나 모델링 시 빠질 수 없는 부분이다. 테이블명과 컬럼명 관련 제약 사항을 사전에 확인해야 한다. 특히 전사 데이터 거버넌스 정책을 수립하는 경우, 거버넌스 적용 대상 DBMS의 제약을 빠짐없이 확인하고 취합하여 수립하여야 한다.

 

테이블명/컬럼명 최대 Byte 수

• 테이블명
  • SAP
    • ASE 16.0 : 255 bytes
    • HANA : 127 bytes
    • IQ : 128 bytes
  • PostgreSQL : 63 bytes
  • MySQL : 64 bytes
  • Oracle
    • 12c 및 이전 버전 : 30 bytes
    • 12.2 이상 : 128 bytes
• 컬럼명
  • 대체로 테이블명 길이 기준과 동일함

예외적으로 테이블명/컬럼명 길이 기준이 다른 경우가 있으니 확인을 하는 것이 좋다. Naming Convention으로 검색해 보면  DBMS별로 공식 문서를 통해 오브젝트별 네이밍 제약이 기술되어 있다.

Case Sensitivity

오브젝트명의 대소문자 관련된 사항도 꼭 확인해보아야 한다.

• 대소문자 구분
  • 구분하지 않음 : 오라클, PostgreSQL, SAP HANA, MySQL(WIN), MariaDB(WIN) 
  • 구분함 : SAP ASE, SQP IQ, MySQL( Unix/Linux ), MariaDB(Unix/Linux)
• 대소문자 디폴트
  • 대문자 : 오라클
  • 소문자 : PostgreSQL

참고로, ANSI 표준은 큰따옴표를 사용하는 경우를 제외하고 대소문자를 구분하지 않도록 하고 있다.

must be case insensitive for identifiers unless they are quoted.
lower case characters may be used in identifiers and keywords, but are considered to be their upper case counterparts.

Examples:
table_name = TABLE_NAME
Table_Name = TABLE_NAME
"table_name" <> "Table_Name" <> "TABLE_NAME

 

MySQL의 경우 시스템 환경 변수 설정(`lower_case_table_names`)에 따라 case sensitivity를 변경할 수 있다.

---

5. SQL

업무를 하면서 SQL을 사용해서 데이터를 추출하거나 처리해야 하는 경우가 있기 마련이다. DBMS에 따라 지원하는 함수나 구문이 미묘하게 다를 때가 많기 때문에 이 부분에 대해서도 미리 공부할 수 있으면 좋다.

 

대표적 구문

자주 사용하고 유용한 SQL 구문 중 차이가 큰 부분은 다음과 같다.

• Top N Query
  • `Rownum` : 오라클
  • `Limit` : MySQL, PostgreSQ
  • `Top n` : SQL Server
• Join 구문
  • 표준 SQL JOIN 구문
  • `= (+)` 구문 : 오라클
  • `STRAIGHT_JOIN` : MySQL
  • `CROSS APPLY`,  `OUTER APPLY` : SQL Server
• 조건절 데이터타입이 상이할 때
  • 자동으로 형변환
  • ERROR 반환
• NULL 연산

 

DBMS 특화 함수

DBMS별로 특화된 함수들도 존재한다. 미리 알아두고 가면 유용하게 활용할 수 있다.

 

1. Oracle

• `DECODE` : `CASE`문을 간결하게 사용
• `NVL`, `NVL2`: NULL 값을 대체하는 데 사용
• `CONNECT BY`: 계층형 쿼리

2. MySQL

• `IFNULL`: NULL 값을 다른 값으로 대체
• `GROUP_CONCAT`: 여러 행 값을 하나의 문자열로 결합
• `CONCAT_WS`: 여러 문자열을 구분자를 넣어 연결

3. PostgreSQL

• `ARRAY_AGG`: 여러 행의 값을 배열로 결합하여 반환
• `TO_CHAR`: 날짜와 숫자 값을 포맷팅 하여 문자열로 반환하는 함수
• `STRING_AGG`: `GROUP_CONCAT`과 유사
• `JSONB_ 시리즈`: `JSONB_EXTRACT_PATH_TEXT`, `JSONB_SET` 등 함수

4. SQL Server

• `FORMAT`: 숫자와 날짜 데이터를 다양한 형식으로 변환
• `IIF`: 오라클 `DECODE`과 유사
• `STRING_SPLIT`: 특정 구분자를 기준으로 문자열을 분할하여 테이블 형태로 반환
• `NEWID` : 고유 식별자 생성 함수

---

 

여기까지 데이터 아키텍트가 낯선 DBMS를 사용해야 하는 프로젝트에 착수하게 되면 꼭 확인해보아야 할 체크포인트들을 정리해보았다.