> For the complete documentation index, see [llms.txt](https://leehyokyun.gitbook.io/openbook/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://leehyokyun.gitbook.io/openbook/3-2.-db-rdbms.md). # 3-2. DB / RDBMS 기초 및 설계 사상 데이터베이스의 기초 및 설계 사상에 대하여 ### 1. Database VIEW VIEW는 데이터를 보관하는 저장소가 아닌, 데이터를 추출하는 쿼리를 보관하여 사용하는 객체이다. 길고 복잡하며 여러 곳에서 사용해야 하는 쿼리라면 쿼리 자체를 저장하고 재활용할 수 있다. * 추상화와 접근 제어: 넓게 보면 추상화의 한 개념이며, 원본 데이터에 불필요하게 접근할 필요 없이 VIEW를 통한 단일 접근을 가능하게 할 수 있다. * 테이블과의 관계 및 제약: 테이블의 연장선이며 테이블처럼 간주하기에 생성 및 수정 시 DML이 아닌 DDL처럼 직관적으로 다룬다. 오직 조회 용도, 가상의 테이블(쿼리 그 자체)로만 간주해야 하며 그 이상의 수정이 필요할 경우 원본 데이터 반영 등에 대한 명확한 기준이 반드시 필요하다. ### 2. OUTER JOIN OUTER JOIN은 교집합에 속하지 않는 데이터까지 보존하여 추출하는 기법이다. * LEFT OUTER JOIN: 왼쪽 테이블(A)의 모든 데이터를 보존하며, 오른쪽 테이블(B)에서 매칭되는 조건(KEY)이 있으면 연결하고 없으면 NULL로 채운다. B의 KEY가 NULL인 조건으로 필터링하면 A에만 존재하는 차집합을 추출할 수 있다. * RIGHT OUTER JOIN: 오른쪽 테이블(B)의 모든 데이터를 보존하며, 왼쪽 테이블(A)에서 매칭되는 조건이 없으면 NULL로 채운다. A의 KEY가 NULL인 조건으로 필터링하면 B에만 존재하는 차집합을 추출한다. * FULL OUTER JOIN: 양쪽 테이블(A, B)의 모든 데이터를 보존하는 합집합 구조이다. 매칭되지 않는 영역은 서로 NULL로 채워지며, 양쪽의 KEY가 모두 NULL인 영역을 제외하는 방식으로 합집합의 차집합(대칭차집합)을 구현 가능하다. ### 3. Transaction Isolation Level 트랜잭션 격리 수준은 동시에 여러 트랜잭션이 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 결정하는 수준이다. 높은 격리 수준일수록 동시성(Concurrency)은 저하되나 정합성은 향상된다. * READ UNCOMMITTED: 커밋되지 않은 데이터도 조회가 가능한 수준이다. 다른 트랜잭션이 변경 중인 데이터를 읽었다가 해당 트랜잭션이 롤백되는 경우, 존재하지 않는 데이터를 읽게 되는 Dirty Read 문제가 발생한다. * READ COMMITTED: 커밋이 완료된 데이터만 조회가 가능한 수준이다. 오라클 등의 기본값이며 Dirty Read는 방지한다. 다만 한 트랜잭션 내에서 동일한 조회를 두 번 실행했을 때, 중간에 다른 트랜잭션이 데이터를 수정하고 커밋하면 조회 결과가 달라지는 Non-Repeatable Read 문제가 발생한다. * REPEATABLE READ: 한 트랜잭션 내에서 조회한 데이터는 트랜잭션이 종료될 때까지 동일한 결과로 보장되는 수준이다. MySQL InnoDB의 기본값이다. 다른 트랜잭션이 새로운 레코드를 삽입(Insert)하고 커밋했을 때, 이전 트랜잭션이 다시 조회하면 기존에 없던 유령 레코드가 나타나는 Phantom Read 문제가 발생할 수 있다. 다만 InnoDB는 넥스트 키 락(Next-Key Lock)과 MVCC를 통해 REPEATABLE READ에서도 Phantom Read를 효과적으로 방지한다. * SERIALIZABLE: 가장 엄격한 격리 수준으로, 읽기 작업에도 공유 잠금(Shared Lock)을 획득하여 동시성을 극도로 제한한다. 모든 격리 수준의 문제를 완벽히 해결하지만 동시 처리 성능이 대폭 저하되므로 실무에서는 성능 저하 비용을 감당할 수 있는 특수한 경우에만 사용한다. ### 4. 저장 프로시저(Stored Procedure)의 단점과 함정 저장 프로시저는 데이터베이스 내부에 컴파일되어 저장되는 SQL 프로그램이지만, 현대 애플리케이션 아키텍처에서는 여러 심각한 구조적 문제를 야기하므로 사용에 신중해야 한다. * 애플리케이션 서버와의 결합도 증가: 비즈니스 로직이 데이터베이스 계층으로 침투하여 SQL 엔진에 강하게 종속된다. 이로 인해 코드의 응집도가 떨어지고 전체 시스템의 복잡도가 극대화된다. * 버전 관리 및 배포의 어려움: 일반적인 애플리케이션 소스 코드와 달리 Git 등을 통한 버전 관리가 까다롭고, 형상 관리가 정교하게 이루어지지 않아 운영 환경 배포 시 누락이나 동기화 오류가 발생하기 쉽다. * 디버그 및 테스트의 한계: 프로그램 로직을 추적하거나 디버깅하는 도구가 매우 제한적이며, 로컬 환경에서 독립적인 단위 테스트(Unit Test)를 수행하기가 극도로 어렵다. * 데이터베이스 서버 부하 집중: 데이터 연산뿐만 아니라 복잡한 비즈니스 조건문과 루프 연산까지 DB 서버의 CPU 자원을 소모하게 만든다. 이는 스케일 아웃(Scale-out)이 상대적으로 쉬운 애플리케이션 서버와 달리, 스케일 업(Scale-up)에 의존해야 하는 데이터베이스의 비용 및 성능 병목을 가속화한다. ### 5. Undo 로그와 MVCC 기반 트랜잭션 롤백 및 읽기 메커니즘 데이터베이스는 트랜잭션의 원자성(Atomicity)과 격리성(Isolation)을 보장하기 위해 실제 디스크/버퍼의 데이터 변경 전, 변경 전의 원래 데이터를 Undo 로그 영역에 기록하는 메커니즘을 사용한다. * 트랜잭션 롤백 과정: 사용자가 트랜잭션 내에서 데이터를 수정(Update)하거나 삭제(Delete)한 후 롤백(Rollback)을 수행하면, 시스템은 버퍼 풀이나 디스크의 현재 데이터를 바라보는 것이 아니라 해당 트랜잭션 ID에 매핑되어 저장된 Undo 로그의 이전 데이터 내용을 역순으로 추적하여 원본 상태로 복원한다. * MVCC(Multi-Version Concurrency Control): 다중 버전 동시성 제어 기술은 Undo 로그를 활용하여 잠금을 사용하지 않고도 일관된 읽기를 제공하는 핵심 메커니즘이다. 특정 트랜잭션이 데이터를 수정 중이더라도, 다른 트랜잭션은 격리 수준에 따라 Undo 로그에 보존된 이전 버전의 데이터를 조회함으로써 대기 없이(Non-blocking) 읽기 작업을 수행할 수 있다. READ COMMITTED에서는 가장 최근에 커밋된 버전의 Undo 로그를 읽고, REPEATABLE READ에서는 트랜잭션이 시작된 시점의 Undo 로그 버전을 일관되게 읽어 Dirty Read를 방지하고 일관성 읽기를 제공한다. ### 6. Database Row & Page 구조 및 디스크 I/O 최적화 데이터베이스는 OS의 성능 및 파일/디스크 구조 등에 의해 결정되며, 이론적으로는 대규모 데이터 저장이 가능하다. 전체 크기는 테이블 및 이와 관련한 모든 논리적 묶음의 합(Schema = tables + Indices)으로 정의된다. * 데이터 저장 단위와 크기 제약: 보통 한 행(Record/Row)의 크기는 최대 65KB 수준이다. 영단어나 숫자는 개당 1Byte, 한글은 23Byte이므로 한 행에 56만 단어를 저장할 수 있다. 보통 1개의 테이블에는 약 1017개의 컬럼을 저장 가능하며, 테이블 수 자체는 제한이 없다. 데이터가 극도로 커진다면 데이터 자체를 테이블 기준으로 나누는 파티셔닝을 우선적으로 고려하게 된다. * 페이지(Page)와 디스크 I/O 메커니즘: 디스크에 접근하는 논리적인 I/O 단위가 바로 페이지이며, 한 번에 탐색하는 크기를 결정한다. InnoDB의 기본 페이지 크기는 16KB이다. 한 레코드의 최대 크기가 65KB인 점을 감안하면, 데이터가 조금이라도 커지거나 큰 컬럼이 채워지면 16KB 페이지 하나에 한 행도 다 담지 못하는 페이지 오프(Page Off) 현상이 즉시 발생한다. * 인라인(Inline) 저장과 오프 페이지(Off-Page) 분리: int나 VARCHAR2(100) 등 적당한 수준의 데이터는 페이지 내부에 inline하여 저장한다. 반면 BLOB, TEXT 등 대용량 데이터나 길어진 컬럼은 페이지 내부에 데이터의 위치를 가리키는 포인터만 남겨두고 빼내어, 오프 페이지(Off-Page)라는 별도의 외부 영역에 나머지 데이터를 저장하는 방식을 취한다. 이러한 오프 페이지 동작이 존재하기 때문에 한 Row의 데이터가 평시적으로 보유하는 65KB의 데이터 용량을 거의 유지하며 디스크 I/O 효율성을 극대화할 수 한다. ### 7. PostgreSQL과 MySQL InnoDB의 아키텍처 사상 비교 데이터베이스 시스템은 동시성과 저장 구조를 관리하는 설계 사상에 따라 내부 아키텍처가 완전히 상이하게 동작한다. * PostgreSQL의 MVCC 사상: PostgreSQL은 데이터가 수정(Update)될 때 기존 레코드를 직접 변경하지 않는다. 대신 기존 레코드를 삭제 표시(Invisible, Dead Tuple)로 두고, 완전히 새로운 레코드를 테이블 행으로 추가(Insert)하는 방식을 취한다. 이로 인해 Undo 로그를 별도로 관리하지 않는 단순한 구조를 가지지만, 수정이 빈번할 경우 Dead Tuple이 지속적으로 쌓여 데이터 비대화가 일어난다. 이를 정리하기 위해 VACUUM 작업이 주기적으로 필수 수행되어야 한다. 인덱스의 경우 데이터를 가리키는 포인터 형태로 저장되며 테이블은 Heap에 별도로 물리 저장되므로 인덱스가 단순 포인터 역할에 집중하고 정렬 영향도가 상대적으로 미미하다. * MySQL InnoDB의 MVCC 사상: MySQL InnoDB는 데이터가 수정될 때 버퍼 풀과 디스크의 기존 레코드를 직접 변경(In-place Update)한다. 대신 격리 수준 보장과 롤백을 위해 변경 전 데이터를 Undo 로그 영역에 차례대로 기록한다. Dead Tuple로 인한 테이블 비대화 문제는 발생하지 않지만 오래된 트랜잭션 유지 시 Undo 로그가 비대해질 수 있어 이를 삭제하는 퍼지(Purge) 스레드가 정교하게 동작한다. 인덱스의 경우 InnoDB는 읽기의 locality를 극대화하기 위해 클러스터링 인덱스 구조를 채택하여 인덱스 자체를 테이블로 보고 데이터를 물리 정렬하여 저장하므로 PK 범위 기반 조회 성능 향상이 매우 뚜렷하다. ### 8. PostgreSQL의 인덱스 종류 및 특성 PostgreSQL은 다양한 데이터 타입과 확장적인 비즈니스 요구사항을 처리하기 위해 여러 가지 고도화된 내장 인덱스 메커니즘을 제공한다. * B+ Tree 인덱스: PostgreSQL의 기본 인덱스 형식으로, 정렬된 트리 구조를 기반으로 한다. 크다, 작다, 같다와 같은 범위 검색 및 등호 연산에 최적화되어 있으며 가장 널리 사용되는 범용 인덱스이다. * Hash 인덱스: 해시 함수를 거친 해시 값 기반으로 매핑 데이터를 찾아내는 구조이다. 오직 등호(=) 비교 연산만 지원하며 범위 검색이 불가능하다는 단점이 있으나, 단순 값 매칭 조회 시에는 B+ Tree보다 빠른 성능을 보장한다. * GIN (Generalized Inverted Index) 인덱스: 일반화된 역색인 구조로, 하나의 컬럼 내에 여러 개의 값이 들어가는 다중 값 데이터에 최적화되어 있다. 배열(Array) 형태의 데이터나 JSONB 타입의 내부 Key-Value 추출, 그리고 대용량 텍스트의 전문 검색(Full Text Search) 시 성능을 극대화한다. * BRIN (Block Range Index) 인덱스: 블록 범위 인덱스 구조로, 데이터 페이지 블록 단위의 최소값과 최대값 정보만을 저장하는 경량화된 인덱스이다. 시계열 데이터나 순차적으로 대규모 정렬되어 쌓이는 로그 데이터에 적합하며, 인덱스가 차지하는 물리적 공간이 B+ Tree 대비 극도로 작아 저장 비용과 I/O를 획기적으로 줄여준다. * GiST (Generalized Search Tree) 인덱스: 일반화된 검색 트리 구조로, 사용자가 직접 정의한 데이터 형태의 트리 구조를 구현할 수 있게 돕는 템플릿 인덱스이다. 공간 데이터(지리 정보), 다차원 기하학적 데이터, 혹은 범위(Range) 타입처럼 정형화된 순서로 정렬하기 힘든 복잡한 정성적 데이터를 탐색하고 조인할 때 매우 유용하다. ### 9. MySQL InnoDB, PostgreSQL의 문자(VARCHAR) 저장 동작 #### 실제 데이터를 저장할때 data length + data로 저장하는데, 경계값 기반 혹은 유동적 판단이냐에 따라 각 DB별 문자열 저장방식이 달라진다. VARCHAR 255 / 65 / 83 .. 각 숫자는 문자의 개수, Character count이며, 이에 대한 데이터를 실제 저장할 때는 바이트 단위로 저장한다. 단, latin1의 경우 1글자 당 1byte이기에 255글자를 모두 저장할 수 있으며 utf8mb3의 경우 1글자 당 3byte이기에 83글자를 저장할 수 있다. * InnoDB의 중요한 점은 255 byte에 대한 경계값이다. * 문자셋 별로 저장가능한 최대 바이트 크기를 계산하며, 255 byte 경계값을 판단하고 넘어가면 prefix length를 1byte, 넘어가지 않으면 2byte로 구성하게 된다. * 실제 저장되는 문자열의 바이트 크기는 prefix length + data length를 합한 크기가 된다. * PostgreSQL은 varlena 기반의 유동적 byte header를 사용한다. * PostgreSQL 역시 InnoDB와 유사하게, data length + data의 형태로 저장한다. * 단, 255 byte에 대한 경계값을 따지지 않고 주어진 길이에 대한 최대 값을 판단하여, 충분하다면 1byte header를, 부족하다면 2byte header를 구성하여 사용한다. * 경계값을 보지 않는다. varlena(variable length, 유동적 길이 형태) 기반의 유동적 length header를 사용하게 된다. ### 10. DB Connection Pool #### DB Connection도 엄연한 외부 체계와의 연결 작업으로 비용이 매우 크다. * DB와의 연결도 TCP 기반의 3 Way HandShake, 인증 과정을 거치는 매우 비용이 큰 작업이며, 단순 매 요청마다 연결이 반복된다면 연결 및 연결관리에 더 많은 비용이 들 것이다. * 그럼에도 Application에서 빠른 연결 수행이 가능한 이유는 매 순간 연결을 진행하는 것이 아니라, 이미 연결된 Connection pool에서 연결을 빌려 사용하기 때문이다. 예를 들어, Hikari Pool이라는 Connection Pool의 경우 Booting 시점에 dataSource 정보를 받아 Coonection(Hikari) Pool을 생성하고 해당 시점에 TCP 연결이 끝난 연결 자원 및 상태를 미리 확보해두는 것이다. * 매 요청 시마다 연결을 새롭게 하는 것처럼 보이지만, Connection Pool에서 연결을 빌려 사용하는 것이며, 락을 제거하는 등 속도 면에서 개선하여 Application 측에서 연결관리에 비용을 소모하지 않는 설계 및 개선이 이루어졌다. #### DB Connection Pool은 처리량을 나타내는 절대적인 지표가 아닌 Saturation의 관계이다. * Connection Pool은 필요 시 Core가 DB와의 연결을 미리 완료하고, 이를 대여하는 장소일 뿐이다. * 물론 특정 처리량 수준까지는 많은 처리량을 대응하는데 효과적일 수 있으나, 그 이상부터는 오히려 CPU 자원을 점유하고 오히려 응답 latency를 늘려 처리량을 줄이는 역효과가 날 수 있는 것이다. * HikariCP는 기본값은 10개 밖에 안되며, 어느 정도 처리량을 대응하기 위해 10\~20개 수준으로 늘리면서 대응이 가능해진다(\*4\~8코어 환경에서도 10\~20개 수준의 Pool size를 권장). * PosgtreSQL : Pool size가 50개 일때 평탄화가 된다고 공식 문서에 기재 * Oracle : core 별 10개 이하의 pool size를 책정할 것을 권장 * Maximum Pool Size = Core size \* 유효 스핀들(디스크 회전축) 개수가 이론적 적정량, 이때 디스크 회전 축은 0에 수렴하여 사실상 Core size을 결정적인 영향 수치로 고려하여 정립한다. * 부하테스트도 비슷한 관점에서 접근하자, 무작정 많이 늘리는 것이 좋은 것이 아니라, 어느 수준에서 처리량이 평탄화되고 그 평탄화 지점에서의 자원 점유율 및 latency를 어떻게 최적화할 것인가의 방향으로 보는 것이 적절하겠다. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation by asking a question. Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters: ``` GET https://leehyokyun.gitbook.io/openbook/3-2.-db-rdbms.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.