PostgreSQL

1. PostreSQL의 물리적 한계치 물론 사용 가능한 disk 용량, 성능 이슈 등 실직적인 제한이 먼저 적용되겠지만, 모든 자원이 충분하다고 가정할 때 물리적인 limit이다. 항목 최대치 데이터베이스 사이즈 무제한 데이터베이스 수량 4,294,950,911 데이터베이스 당 Relations(테이블, 뷰, 인덱스 등의 테이블 객체)수량 1,431,650,303 Relations 사이즈 32TB 테이블 당 ROW 수량 4,294,967,295 pages영역의 크기에 해당하는 ROW 테이블 당 COL 수량 1,600 결과셋의 COL 수량 1,664 COL 사이즈 1GB 테이블 당 인덱스 수량 무제한 인덱스 당 컬럼 32 파티션 키 32 식별자 키 (ex, 테이블, ROW, COL 등의 명칭) 63 bytes function의 매개변수 100 쿼리파라미터 65,535 테이블당 ROW 수량은 4,294,967,295개의 pages 영역에 저장 가능한 ROWS로 제한되어 있는데, 4,294,967,295는 2^32 - 1로 32비트 시스템에서 사용가능한 최대 정수이다. 데이터베이스에서 최대로 관리할 수 있는 pages의 수며, 각 페이지에는 여러 튜플이 저장될 수 있다. 테이블 당 인덱스의 수량은 이론상은 “무제한"이제만, 실제로는 데이터베이스가 관리할 수 있는 최대 Relations (테이블, 뷰, 인덱스 등의 테이블 객체)에 의해 제한된다. 위 표의 인덱스 당 칼럼 수, 파티션 키 수량, 식별자 키, 함수 매개변수 최대 수량은 기본값이며 설정값을 변경하여 증가시킬 수 있다. 테이블 당 최대 칼럼 수는 1600개이지만, 저장되는 튜플이 8192바이트의 힙 페이지에 fit 해야 한다는 조건 때문에 더 줄어들 수 있다. 예를 들어 튜플 헤더를 제외하고, 1600개의 int칼럼 투플 - 6400 bytes로 힙페이지에 정상 저장 가능 (6400 < 8192) 1600의 bigint칼럼 투플 - 12800 bytes로 heap page를 초과 (12800 < 8192) text, varchar, char같이 길이 변경이 가능한 필드의 경우 값이 크면 TOAST 테이블 영역이라 불리는 주 저장공간 외부영역에 값을 저장하고, 본래 테이블에는 해당 데이터를 가리키는 포인터만 남게 된다. 테이블에서 삭제된 칼럼들도 최대 칼럼 개수에 포함된다. 삭제된 칼럼에 대해 새로 생성된 ROW도 내부적으로는 null 표시되지만, 추적을 위해 여전히 공간을 차지하여 최대 개수에 영향을 준다. 2. 결론 운영 단계에서 1억 개 이상의 테이블을 생성하거나 1000개가 넘는 칼럼의 테이블을 생성하는 일은 없을 것이고, 이러한 물리적 제약보다 자원의 한계 (용량 및 성능이슈)를 먼저 만날 것이기에 정확한 수치를 정확히 외울 필요는 없겠지만, 삭제된 칼럼들과 그 이후 생성된 ROW들이 내부적으로는 추적을 위해 해당 컬럼을 NULL로 저장하며, 이 과정에서 사용되는 NULL비트맵이 공간을 차지하기에 최대 카운트에 영향을 준다는 것 운영 시에 유의해야 할 내용이다.

1. Index-Only Scans PostgreSQL의 모든 인덱스는 "보조(Secondary)" 인덱스이다. 각 인덱스는 테이블의 메인 데이터 영역(테이블의 heap 영역)과 분리되어서 저장된다. 그렇기 때문에 일반적인 인덱스 스캔에서 각 ROW를 찾기 위해서는, index와 heap 영역 모두에 접근하여 데이터를 탐색해야 한다. 보통 WHERE 절 조건에 부합하는 데이터들은 인덱스 영역 - 서로 가까이 존재하여 정렬된 순서로 빠르게 접근할 수 있다. (인덱스 테이블은 정렬된 상태로 생성) heap 영역 - 특별한 규칙 없이 어디에서든 분포할 수 있기에 heap 영역을 스캔할 때는 무작위로 접근하게 되어 속도가 느리다. 이 퍼포먼스 문제를 해결하기 위해 PostgreSQL은 힙 영역에 대한 접근 없이 인덱스 내에서만 데이터를 조회하는 Index-only 스캔을 지원한다. 기본 개념은 말 그대로 heap 영역의 참조 없이 index 항목에서 바로 값을 반환하는 것으로 매우 효율적으로 보이지만 몇 가지 제한사항이 있다. ...

1. 인덱스(INDEX)와 오더바이(ORDER BY) 인덱스는 쿼리의 결과로 특정 row를 찾는 것뿐만 아니라, 특정 순서로 데이터를 정렬하는데도 효율적일 수 있다. ORDER BY와 인덱스를 효율적으로 사용하면 별도의 정렬 과정 없이 ORDER BY를 수행할 수 있다. PostgreSQL에서 현재 지원하는 인덱스 타입 중에서는 B-tree 인덱스만이 정렬 결과로 인덱스를 생성할 수 있다. 다른 인덱스 유형은 특정되지 않은 순서로, 실행 때마다 다른 순서로 열을 반환한다. * 상세한 B-tree 인덱스의 개념은 다음 글을 참고 - [Postgresql] - [PostgreSQL] B-tree 인덱스의 원리 및 특징 ...

1. 2단계 커밋 프로토콜([two-phase commit (2PC)) PostgreSQL은 two-phase commit (2PC) 프로토콜을 지원한다. 다수의 분산 시스템 환경에서 모든 데이터베이스가 정상적으로 수정되었음을 보장하는 두 단계 커밋 프로토콜로 분산 트랜잭션에 참여한 모든 데이터베이스가 모두 함께 커밋되거나 롤백되는 것을 보장한다. PostgreSQL의 2단계 트랜잭션은 외부 트랜잭션 관리 시스템에서 사용하기 위해 존재하며 X/Open XA 표준에서 제안된 특징과 모델을 따른다. (사용빈도가 낮은 일부 기능은 구현되지 않았다.) 2단계 커밋은 다음 스탭에 따라 작동된다. Coordinator Cohort QUERY TO COMMIT --------------------------------> VOTE YES/NO prepare*/abort* <------------------------------- commit*/abort* COMMIT/ROLLBACK --------------------------------> ACKNOWLEDGMENT commit*/abort* <-------------------------------- end ...

1. 기본 트랜잭션의 개념 및 원리 트랜잭션의 기본 개념과 사용 방법은 다음 포스트에서 확인이 가능하다. [Postgresql] - [PostgreSQL] 트랜잭션(Transaction)의 개념 및 사용 기본적으로 트랜잭션이 어떤 것인지, COMMIT, ROLLBACK도 익숙하게 사용하고 있다면, PostgreSQL 내부의 트랜잭션이 어떤 구조로 작동하며 세부 단계를 어떻게 확인 가능한지 자세히 알아보자. 2. 트랜잭션과 식별자 (Transactions and Identifiers) 기본 개념에서 확인했듯이 트랜잭션은 명시적으로 실행(BEGIN, START TRANSACTION), 종료 (COMMIT, ROLLBACK) 할 수 있다. 명시적 트랜잭션 외의 SQL 구문들은 단일 트랜잭션이 자동으로 적용된다. 그렇다면 각각의 트랜잭션이 어떻게 구분되는지 먼저 살펴보자. ...