회사에서 주로 PostgreSQL을 쓰다 보니, 관련 Docs를 보는 일들이 많았다.

링크들을 클릭하다 우연히 FAQ를 보게 됐는데 생각보다 유용해서 필요해 보이는 것만 추려서 번역했다. (고마워요 gpt, 구글!)

번역할 때 삼은 기준이다.

원문 그대로 쓰는 경우

영어로 많이 부르는 경우 ex) prepared, query plan, lock
한글로 번역할 때 생소한 부분 ex) locale
row, column
gaps ⇒ 간격이란 뜻이 있지만, 갭이라 하는 경우도 많아 원문 그대로 씀
기타 예약어, 명령어 및 볼드 처리 된 부분

한국어 해석의 경우

statistics ⇒ 통계
optimizer ⇒ 최적화기
double quote ⇒ 쌍따옴표
space ⇒ 공백
punctutation ⇒ 구두점(.)
numbering ⇒ 번호 지정

기타

문장 마지막은 ~입니다 or 세요
요약, 의문, 정리 등 주석을 추가한 부분이 있고, 이 부분은 굵게 처리했다.
중요하다고 생각되는 부분도 굵게 처리했다.

3. Generic Questions

3.18 PostgreSQL은 다른 DBMS들과 어떻게 비교되는지

기능

PostgreSQL은 트랜잭션, 하위 select(서브 쿼리), 트리거, 뷰 테이블, 외래 키 참조 무결성, 정교한 lock 등 대규모 독점 DBMS에 존재하는 대부분의 기능을 갖추고 있습니다. 우리는 사용자 정의 유형같이 그들이 가지고 있지 않은 기능들도 있습니다.

상속, 규칙, 다중 버전 동시성 제어를 통해 lock contention을 줄일 수 있습니다. 다른 특징으로는 지리 공간 기능, 시간적 기능. GUI 인터페이스 등을 타사 또는 PostgreSQL 확장 프로그램을 통해 사용할 수 있습니다.

성능

PostgreSQL의 성능은 다름 독점 및 오픈 소스 데이터베이스와 비슷합니다. 어떤 것은 빠르거나, 느리다. 우리의 성능은 다른 데이터베이스와 비교할 때, +/-10% 정도 차이가 있습니다.

신뢰성

우리는 DBMS가 신뢰성이 있어야 하며, 그렇지 않으면 가치가 없단 것을 압니다. 우리는 최소한의 버그를 갖도록 안정적인 코드를 릴리스하도록 노력하고 있습니다.

각 배포판들은 최소 한 달의 베타 테스트를 거치고, 우리의 릴리스 기록을 보면 우리가 바로 프로덕션 환경에 사용할 수 있는 안정적이고 견고한 릴리스를 제공할 수 있음을 알 수 있습니다.

기술 지원

우리의 메일링 리스트는 발생한 문제를 해결하는 데 도움을 주기 위해 대규모 개발자 및 사용자 그룹과의 연락을 제공합니다.

수정을 보장할 수는 없지만, 독점 DBMS가 항상 수정 사항을 제공하는 것은 아닙니다.

개발자, 유저 커뮤니티, 매뉴얼 그리고 소스 코드에 직접 접근할 수 있는 것은 PostgreSQL이 다른 DBMS보다 뛰어난 지원을 제공하는 경우가 많다. 필요에 따라서 상업적인 사고 지원이 가능합니다.

가격

우리의 독점 소스와 오픈 소스 모두 무료로 사용할 수 있습니다. BSD 라이선스에 설명된 내용을 제외하고 제한 없이 우리의 코드를 당신의 제품에 포함할 수 있습니다.

3.19 PostgreSQL을 내장할 수 있나요?

postgreSQL은 클라이언트/서버 아키텍처로 설계돼, 클라이언트와 서버별로 별도의 프로세스와 다양한 헬퍼 프로세스가 필요합니다.
많은 임베디드 아키텍처들은 이러한 요구 사항들을 지원할 수 있습니다.

하지만, 임베디드 된 아키텍처에 데이터베이스 서버가 실행하다 애플리케이션 프로세스 내에서 실행하는 경우, PostgreSQL을 사용할 수 없으며, 경량화된 데이터베이스 솔루션을 써야 합니다.

인기 있는 내장 가능한 선택지로는 SQLite나 Firebird SQL이 있습니다.

5. Administrative Questions

5.1 소스 코드로 설치할 때, /usr/local/pgsql 이 아닌 곳에 어떻게 PostgreSQL을 설치하는지?

Configure를 실행할 때 —prefix 옵션을 지정합니다.

5.3 다른 호스트의 연결을 어떻게 제어하나요?

일반적으로 PostgreSQL은 유닉스 도메인 소켓 또는 TCP/IP 사용하는 로컬 시스템의 연결만 허용합니다.

당신의 postgresql.conf 파일에 있는 listen_addresses 내용을 수정하고. $PGDATA/pg_hba.conf 파일을 수정해 호스트 기반 인증을 활성화하고, 데이터베이스 서버를 재시작하지 않으면 다른 머신에 연결할 수 없습니다.

5.4 어떻게 데이터베이스 엔진 성능을 올릴 수 있을까요?

잠재적인 성능 향상으로 크게 3가지가 있습니다.

쿼리 변경
1. 표현식 및 부분 인덱스를 포함한 인덱스 생성
2. 테이블 복사나, 파일 내용을 복사해 테이블로 옮길 때, 여러 번의 INSERT 대신 COPY 사용
3. 커밋 오버헤드를 줄이기 위해 하나의 트랜잭션에 여러 개의 구문(statement)을 그룹화
4. 인덱스에서 많은 행을 검색할 때 CLUSTER 명령어 사용
5. 쿼리 결과에 LIMIT 사용
6. Prepared Statement 사용하기
7. 정확한 최적화 통계를 유지하기 위해 ANALYZE 명령어 사용
8. 주기적으로 VACUUM이나 pg_autovacuum을 사용
9. 많은 데이터를 변경할 때 인덱스를 제거하기(DROP)
서버 설정
1. postgresql.conf의 여러 설정들은 성능에 영향을 줍니다. 자세한 내용을 Administration Guide/Server Run-time Environment/Run-time Configuration. 항목을 보세요
하드웨어 선택
1. 하드웨어가 성능에 미치는 영향은 다음 링크에 자세히 나와 있습니다. (없는 링크임)

(추가 설명)

1.a에서 말하는 표현식 express란 결과를 기반으로 인덱스를 생성한단 뜻

즉, 하나 이상의 열을 변형하거나 계산한 결과에 대한 인덱스를 만든다.
ex) CREATE INDEX idx_lower_name ON users (LOWER(name)); ⇒ name 열의 값을 소문자로 치환한 값에 대한 인덱스

5.5 어떤 디버깅 기능을 사용할 수 있나요?

디버깅 및 성능 측정에 유용한 정보를 줄 수 있는 쿼리 및 프로세스 통계를 출력할 수 있는 많은 log_* 서버 설정 변수가 이 링크에 있습니다.

5.6 왜 연결을 시도 하면 "Sorry, too many clients" 메시지를 받나요?

데이터베이스 세션의 기본 제한인 100개에 도달했기 때문입니다. 연결 제한을 늘려야 하는지, 커넥션 pooler를 추가해야 할지는 데이터베이스 연결 수 문서를 보세요.

5.8 PostgreSQL은 다양한 국가의 일광 절약 시간제(서머 타임) 변경 사항을 처리할 수 있나요?

PostgreSQL 8.0 릴리스 이후부터 일광 절약 정보를 위해 널리 사용되는 tzdata 데이터베이스(zoneinfo 데이터베이스 또는 Olson 시간대 데이터베이스)에 의존합니다.

사용자에게 미치는 DST 법 변경 사항을 처리하려면, 새 tzdata 세트를 설치하고, 서버를 재시작하세요.

모든 PostgreSQL 업데이트 릴리스에는 사용 가능한 최신 tzdata 파일들이 있으므로, 일반적으로 메이저 버전의 마이너 버전을 최신 상태로 유지하는 것으로 충분합니다.

새로운 tzdata 파일들을 포함해 정기적인 업데이트를 받는 플랫폼에서는, 시스템의 tzdata 파일 복사본을 사용하는 게 더 편리할 수 있습니다.

이는 컴파일 타임에 옵션으로 가능합니다. 대부분 리눅스 배포판은 사전 구축된 PostgreSQL 버전을 통해 이 방식을 선택합니다.

5.9 어떤 컴퓨터 하드웨어를 사용해야 하나요?

PC 하드웨어들 대부분이 호환되기 때문에, 대부분의 사람들은 모든 PC 하드웨어들이 같은 성능이라 믿는 경향이 있습니다.

하지만, 그렇지 않습니다. ECC RAM, 좋은 성능의 하드 드라이브/SSD, 신뢰할 수 있는 파워 서플라이 그리고 고품질 메인보드는 저렴한 하드웨어보다 더 안정적이고, 나은 성능을 제공합니다.

PostgreSQL은 대부분의 하드웨어에서 동작하지만, 안정성과 성능이 중요하다면, 하드웨어 옵션을 철저히 조사하는 게 좋습니다.

다른 많은 애플리케이션과 다르게 데이터베이스 서버는 I/O와 메모리 제한돼있습니다. 먼저 하위 I/O 서브시스템부터 초점을 맞추고 메모리 제한, 마지막으로 cpu 이슈를 고려하는 것이 좋습니다.

고품질, 좋은 성능의 SSD는 보통 데이터베이스 성능을 올릴 수 있는 저렴한 수단입니다. 우리의 이메일 목록은 하드웨어 옵션과 장단점을 논의하는 데 사용될 수 있습니다.

요약: 고품질의 하드웨어는 언제나 좋다.

5.10 PostgreSQL은 CPU 자원을 어떻게 사용하나요

PostgreSQL 서버는 프로세스 기반입니다.(스레드를 사용하지 않습니다.) 각 데이터베이스 세션은 하나의 PostgreSQL OS 프로세스로 연결됩니다. 여러 개의 세션은 OS에 의해 사용 가능한 CPU로 자동 분산됩니다.

또한 OS는 CPU를 사용해 디스크 I/O를 처리하고, 데이터베이스가 아닌 다른 작업을 실행합니다.
클라이언트 애플리케이션은 스레드로 사용할 수 있고, 각 스레드는 별도의 데이터베이스 프로세스에 연결됩니다.

버전 9.6 이후로 일부 쿼리는 별도의 OS 프로세스에서 병렬로 실행될 수 있으므로 여러 cpu 코어를 사용할 수 있습니다.
병렬 쿼리는 버전 10에서 기본적으로 활성화돼있고, 향후 릴리스에서 추가 병렬 처리가 예상됩니다.

5.11 왜 postgreSQL은 많은 프로세스를 갖고, 언제 유휴 상태가 되나요

위에서 말했듯이, PostgreSQL은 프로세스 기반이며, 연결당 하나의 postgres(윈도우에선 postgres.exe) 인스턴스를 시작합니다.
postmaster(커넥션을 받고, 그곳에서 새 postgres 인스턴스를 시작해 줌)는 항상 running 상태이며, PostgreSQL은 일반적으로 성능 수집기, 백그라운드 작성기, autovaccum 데몬, walsender 등과 같은 하나 이상의 “helper” 프로세스를 갖고 있으며, 대부분 시스템 모니터링 툴에선 “postgres”란 인스턴스로 나타납니다.

프로세스 수에도 불구하고, 실제로 매우 적은 자원을 사용합니다. 다음 답변을 참고하세요.

5.12 왜 PostgreSQL은 많은 메모리를 사용하나요

보이는 것에도 불구하고, 이것은 명백히 정상이며, 실제로 Top과 같은 도구나 윈도우 프로세스 모니터에서 PostgreSQL이 사용하고 있는 것처럼 사용되는 메모리가 거의 없습니다.

top이나 윈도우 프로세스 모니터 같은 도구들은 많은 postgres 인스턴스를 보여주고, 각 인스턴스들은 많은 메모리를 사용하는 것으로 보입니다.

종종 합산하면, postgres 인스턴스들이 사용하는 총량은 실제 컴퓨터에서 가동하는 메모리 양의 몇 배입니다!

이는 이러한 도구들이 메모리 사용을 보고하는 방식에 따른 결과입니다. 이 도구들은 공유 메모리를 잘 이해하지 못하며, 각 postgres 인스턴스들이 개별적으로, 베타적으로 메모리를 사용하고 있다 보여줍니다.

PostgreSQL은 뒷단과 캐시 데이터 간 커뮤니케이션을 위해 하나의 거대한 공유 메모리를 사용합니다.
성능 측정 도구들은 공유 메모리 블록을 모든 postgres 인스턴스에 대해 한 번만 세는 대신 postgres 인스턴스마다 한 번씩 세기 때문에, 그들은 PostgreSQL이 아주 많은 메모리를 사용하고 있다고 과대평가합니다.

요약: 공유 메모리 안에 postgres 인스턴스들이 있어, 인스턴스 하나만 보면 되는데, 다 봐버려서 합산하니까 과다 사용으로 간주함

더욱이, 이 툴들은 개별 인스턴스가 시작될 때 즉시 전체 공유 메모리가 사용된다는 것으로 보고하지 않고, 시작 이후 접촉한 공유 페이지 수를 계산합니다.

인스턴스가 가동되는 동안 모든 페이지를 다룰 때까지 필연적으로 더 많은 공유 메모리를 사용합니다. 인스턴스의 보고된 사용량이 점점 증가하면서 전체 공유 메모리 블록을 포함하게 됩니다.

이런 현상은 빈번히 메모리 누수로 오해받지만, 실제로 그런 것이 아니라 보고 상의 결과물일 뿐입니다.

요약: 인스턴스들은 공유 메모리 영역 내에서 활동하지만, 성능 측정 도구들은 이거를 고려하지 않고, 각 인스턴스들이 공유 메모리만큼의 자원을 사용하고 있다고 오해함

6. Operational Questions

6.4 row, 테이블, 데이터베이스의 최대 크기는 몇인가요?

제한은 다음과 같습니다.

데이터베이스 최대 크기: 제한 없음(32TB의 데이터베이스가 존재하고 있음)
테이블의 최대 크기: 32TB
row의 최대 크기: 400GB
필드의 최대 크기: 1GB
테이블이 가질 수 있는 row의 수: 제한 없음
테이블이 가질 수 있는 column의 수: column 타입에 따라 250~1600개
테이블이 가질 수 있는 인덱스 수: 제한 없음

물론, 실제로 제한이 없는 건 아니고, 사용 가능한 디스크나 메모리/스왑 공간에 제한됩니다.
이 값들이 불필요하게 크다면 성능이 떨어집니다.

테이블의 최대 크기는 32TB여서 OS에서 대용량 파일 지원이 필요하지 않습니다. 큰 테이블은 여러 개의 1GB 파일들로 저장돼 파일 시스템 크기 제한은 중요하지 않습니다.

기본 block의 크기는 32KB로 늘리면, 최대 테이블, row 크기 및 column 개수를 4배로 늘릴 수 있습니다.
최대 테이블 크기는 테이블 파티션으로 증가시킬 수 있습니다.

한 가지 제약사항은 2000자가 넘는 column엔 인덱스를 생성할 수 없습니다.
다행히도, 이런 인덱스 수요는 매우 희귀합니다 긴 column의 MD5 같은 해시 함수 인덱스를 통해 고유성을 보장할 수 있으며, full text 인덱싱을 통해 column 내의 단어를 찾을 수 있습니다.

만약 2KB가 넘는 row를 테이블에 저장한다면 테이블의 최대 row 개수 제한은 약 40억 이하로 제한될 수 있습니다. 자세한 건 TOAST 문서를 보세요.

6.5 일반적인 텍스트 파일의 데이터를 저장하는데 디스크 공간이 얼마나 필요한가요?

PostgreSQL 데이터베이스는 텍스트 파일의 데이터를 저장하는데 최대 5배의 디스크 공간이 요구될 수 있습니다.
예를 들어, 숫자 + 문자가 100,000 라인으로 구성된 파일을 고려해 보면 문자열 평균 길이는 20bytes 정도 됩니다.

flat file(데이터베이스의 파일)은 2.8MB 정도 됩니다.

이 데이터가 포함된 PostgreSQL 데이터베이스 파일의 크기는 5.2MB로 예측됩니다.

 24 bytes: 각 row 헤더 (대략적임)
 24 bytes: 하나의 int 및 텍스트 필드
+ 4 bytes: 튜플로 구성된 페이지 포인터
----------------------------------------
 52 bytes per row

PostgreSQL의 최대 페이지 크기는 8KB입니다.

8192 bytes per page
-------------------  =  158 rows per database page (소수점 내림)
  52 bytes per row

 100000 data rows
------------------  =  633 database pages (소수점 올림)
 158 rows per page

633 database pages * 8192 bytes per page  =  5,185,536 bytes (5.2 MB)

인덱스엔 많은 overhead가 필요하지 않지만, 인덱스 중인 데이터가 포함돼있어, 크기도 커집니다.
NULL은 비트맵으로 저장되며, 매우 적은 공간을 차지합니다.
긴 값은 투명하게 압축될 수 있으며, 이 토픽에 대해선 다음 파일을 보세요

6.6 왜 내 쿼리는 느린가요? 왜 내 인덱스는 사용되지 않나요?

인덱스는 데이터베이스 성능을 상승하는데 필수적입니다. 하지만 인덱스는 데이터베이스 시스템의 추가 부하를 의미하므로, 합리적으로 적용해야 합니다.

이 유용한 튜토리얼을 통해 특성 상황에 가장 적합한 인덱스를 생성할 수 있도록 이들의 특성을 이해하면 됩니다.

인덱스는 모든 쿼리에서 사용되지 않습니다. 인덱스는 최소 크기보다 큰 테이블, 테이블 내의 row 중 적은 비율로 select query를 수행할 때 사용됩니다.

왜냐하면, 인덱스 스캔으로 인한 랜덤 디스크 접근이 순차 탐색(sequential scan)이나 테이블을 통한 직접 읽기(조건 없이 테이블을 읽는다는 뜻인 듯)보다 느릴 수 있기 때문입니다.

인덱스를 사용할지 결정하기 위해, PostgreSQL은 테이블에 대한 통계를 있어야만 합니다. 이런 통계들은 VACCUM ANALYZE 또는 간단한 ANALYZE로 수집됩니다.

통계를 사용해, 최적화 프로그램은 테이블의 row 개수와 인덱스를 언제 사용해야 좋을지를 알 수 있습니다.
통계는 join 순서와 방법을 최적화하는 데에도 유용합니다. 테이블이 변경되는 것을 고려해 주기적으로 통계를 수집해야 합니다.

인덱스는 일반적으로 ORDER BY 또는 join 수행에 사용되지 않습니다.
명시된 정렬이 뒤따르는 순차 탐색은 대부분 큰 테이블에서 인덱스 스캔보다 빠릅니다.
그렇지만, ORDER BY와 LIMIT가 결합되면, 테이블의 소수만 반환되므로 인덱스 스캔을 사용하는 경우가 많습니다.

만약 최적화 프로그램의 순차 스캔 선택이 잘못됐다 생각하면, SET enable_seqscan을 ‘off’를 사용하고, 쿼리를 다시 수행해 인덱스 스캔이 정말로 빠른지 확인하세요.

LIKE 또는 ~ 같은 wild-card 연산자를 사용하면, 인덱스 스캔은 다음 상황에서 사용됩니다.

검색 문자열의 시작은 문자열의 시작 부분에 고정돼야 합니다. 예를 들어
- LIKE 패턴이 % 또는 _로 시작하면 안 됩니다.
- ~ (정규 표현식) 패턴이 ^로 시작해야만 합니다.(must)
검색 문자열(string)은 문자 클래스(character)로 시작할 수 없습니다.
ILIKE 및 ~*와 같은 대소문자를 구분하지 않는 검색은 인덱스를 지원하지 않습니다. 대신에, section 4.8에 명시된 정규표현식을 사용하세요.
non-C locale 정렬은 LIKE 동작과 일치하지 않는 경우가 많아 initdb 중에 C locale을 사용해야만 합니다.
이러한 경우에 특수한 text_pattern_ops 인덱스를 만들 수 있지만, 이는 LIKE 인덱싱에만 유용합니다.

또한 단어 검색에 full text 인덱싱을 사용하는 것도 가능합니다.
가끔씩 인덱스는 “손상됨” 상태로 생성돼서 사용되지 않을 수 있습니다. 여기를 보세요.
SlowQueryQuestions 기사는 더 많은 팁과 가이드라인을 포함하고 있습니다.

6.7 쿼리 최적화기가 어떻게 내 쿼리를 평가했는지 알 수 있나요?

EXPLAIN 명령어를 통해 알 수 있습니다. 이 문서를 보세요.
PostgreSQL 실행 plan과 쿼리 실행 전략을 시각적 형식으로 가져올 수 있는 쿼리 프로파일링 도구를 사용할 수도 있습니다.

6.8 텍스트 정렬 방식을 어떻게 변경하나요?

PostgreSQL은 initdb 중에 선택된 현재 locale이 정의된 순서에 따라 텍스트 데이터를 정렬합니다.
(8.4 이후로 새로운 데이터베이스를 생성할 때 다른 locale을 선택 가능합니다.)

만약, 정렬이 마음에 들지 않으면, 다른 locale을 사용해야 합니다.

특히, “C”를 제외한 대부분 Locale들은 사전 순서에 따라 정렬되므로 구두점(punctutation)과 공백(spacing)이 거의 무시됩니다.

만약 이걸 원하지 않는다면 “C” locale을 써야 합니다.

6.9 정규표현식과 대소문자 구분 없는 정규표현식 검색을 어떻게 하나요? 대소문자 구분 없는 정규표현식 검색에 인덱스를 어떻게 사용하나요?

정규 표현식에서 ~ 연산자는 패턴이 일치하는지, ~*는 대소문자 구분 없이 패턴이 일치하는지 봅니다.
LIKE의 대소문자 구분 없는 변형문을 ILIKE라고 합니다.

대소문자 구분 없는 동등 비교는 다음과 같이 구성됩니다

SELECT *
FROM tab
WHERE lower(col) = 'abc';

“col”에는 표준 인덱스가 적용되지 않습니다. 그렇지만, “lower(col)”에 대한 정규 표현식 인덱스를 만든다면 사용할 수 있습니다.

CREATE INDEX tabindex ON tab (lower(col));

만약, 위의 인덱스가 UNIQUE로 생성되면, 해당 column은 대문자와 소문자 저장될 수 있지만, 대소문자만 다른 동일한 값을 포함할 수 없습니다.

column에 특정한 경우를 강제로 저장하기 위해선, CHECK 제약 조건이나 트리거를 사용해야 합니다.

PostgreSQL 8.4 이후로는 내부적으로 “lower()” 호출이 구현된 기여 받은 CITEXT 데이터 유형을 사용할수 있습니다. 이를 통해 완전히 대소문자 구분이 없는 데이터 유형을 효과적으로 다룰 수 있습니다.

CITEXT는 8.3에서도 사용할 수 있으며, 8.2 이하에서는 ASCII 문자만 대소문자를 구분하지 않고 처리하는 이전 버전을 pgFoundry에서 사용할 수 있습니다.

6.11 문자열 유형들의 차이점은 무엇인가요

Type	Internal Name	Notes
VARCHAR(n)	varchar	size specifies maximum length, no padding
CHAR(n)	bpchar	blank-padded to the specified fixed length
TEXT	text	no specific upper limit on length
BYTEA	bytea	variable-length byte array (null-byte safe)
"char" (with the quotes)	char	one byte

시스템 카탈로그를 검사하거나 몇몇 에러 메시지를 통해 내부 이름을 볼 수 있을 것입니다.

위의 처음 4가지 유형들은 “varlena” 유형입니다.(필드 길이가 명시적으로 디스크에 저장되고, 그 뒤에 데이터가 저장됨)
따라서 실제 공간은 예상보다 약간 큽니다.

그렇지만, 긴 값들도 압축되므로, 디스크 공간이 예상보다 적을 수 있습니다.

VARCHAR(n)은 애플리케이션에서 문자열 길이에 상한선이 요구되고, 가변 길이 문자열을 저장하는데 효과적입니다.

TEXT는 “unlimited” 길이의 문자열을 위한 자료형입니다. (비록 PostgreSQL의 모든 최대 길이는 1GB 지만)

CHAR(n)은 모든 문자열을 같은 길이로 저장할 때 쓰입니다.

VARCHAR(n)이 제공된 문자만 저장한다면, CHAR(n)은 특정 길이에서 공백으로 채워집니다.

BYTEA는 이진 데이터, 특히 0 bytes인 값을 저장할 때 쓰입니다.

CHAR(n)에 공백 채우기에 추가 저장 공간과 실행 시간이 필요한 점을 제외하면, 이러한 모든 유형들은 비슷한 성능 특성을 가지고 있습니다.

“char” 유형(CHAR(n)과 구별하려면 “가 필요)은 정확히 한 바이트를 저장하는데 특화됐습니다.
시스템 카탈로그에서 찾을 수 있지만, 유저 테이블에서 사용하는 것은 일반적으로 권장되지 않습니다.

TMI: CHAR(n)의 경우, 테이블을 정의할 때, 처음부터 memset 같은 기능으로 다 0으로 채우고 가는 줄 알았는데 런타임에 약간의 자원을 필요로 하는 것 같다. 사소한 차이지만 알아두면 좋을 것 같다.

6.12 SERIAL/자동화 필드를 어떻게 만들 수 있나요?

PostgreSQL은 SERIAL 데이터 타입을 지원합니다. 사실, SERIAL 타입은 실제 자료형이 아닙니다. 시퀀스에서 생성하는 정수 열의 약어입니다.

예를 들자면 다음 쿼리는

CREATE TABLE person (
    id SERIAL,
    name TEXT
);

자동적으로 다음 쿼리로 변환됩니다.

CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
    id INTEGER NOT NULL DEFAULT nextval('person_id_seq'),
    name TEXT
);

자동적으로 생성되는 시퀀스는 table_serialcolumn_seq이라고 불리고, table과 serialcolumn은 각각 table과 SERIAL 컬럼의 이름입니다.

시퀀스에 대한 자세한 내용은 CREATE SEQUENCE 매뉴얼 페이지를 보세요.

SERIAL에선, 결과 컬럼이 INTEGER 대신 BIGINT 유형이란 것을 제외하면 BIGSERIAL 유형도 있습니다.

테이블에서 20억이 넘는 serial 값을 넘는다면 이것이 필요할 수 있습니다.

일반적인 운영 부분에서 시퀀스는 “holes” 또는 “gaps”를 가질 수 있단 것을 주의하세요.

”holes”나 “gap”로 인해 키들이 1, 4, 5, 6, 8, …로 생성되는 것은 지극히 정상입니다. sequence gaps에 대한 FAQ 항목을 보세요.

6.14 currval()은 다른 유저로부터 경쟁 조건을 일으키지 않나요?

아니오. currval()은 세션에 할당된 가장 최신의 시퀀스 값을 반환하며, 다른 세션과는 독립적입니다.

6.15 왜 내 시퀀스/SERIAL 컬럼 번호 지정에 gaps가 생기나요? 왜 트랜잭션 중단시, 내 시퀀스 넘버를 재사용하지 않나요?

동시성을 향상시키기 위해, 시퀀스 값은 트랜잭션의 요청에 따라 주어집니다.
시퀀스 객체는 잠긴 상태로 유지되지 않지만, 다른 트랜잭션이 다른 시퀀스 값을 얻기 위해 즉시 사용할 수 있습니다.

이로 인해, nextval() 함수 참고 섹션 문서에서 중단된 트랜잭션으로부터 번호 지정에 gaps가 생깁니다.

추가적으로, 서버를 비정상적으로 종료하면, 복구를 위해 시퀀스가 증가하게 됩니다.

PostgreSQL은 할당할 시퀀스 넘버 캐시를 유지하고 있기 때문에, 비정상적인 종료 시, 어떤 캐싱 된 숫자를 사용하고 있는지 확신할 수 없습니다.

어쨌든, 시퀀스에 공백이 생길 수 있으므로, 안전한 옵션을 선택하고 시퀀스를 증가시킵니다.

시퀀스에서 gaps의 다른 원인은 CREATE SEQUENCE 에서 CACHE를 사용하는 것입니다.

일반적으로, SERIAL 키 또는 SEQUENCE를 gap 없이 의존할 필요는 없으며, 순서대로 동작할 것이라 가정해도 안 됩니다.
이것은 같은 트랜잭션 내에서 양쪽 다 생성될 때, n + 1 id가 n 이후에 삽입될 것이란 보장이 없는 것과 같습니다.

합성 키가 동일한지 보고, 동일한 경우에만 비교합니다.

gap 없는 시퀀스는 가능하지만, 성능에 매우 나쁩니다. 한 번에 최대 하나의 트랜잭션만 gap 없는 시퀀스에서 row 삽입이 가능합니다.

gap 없는 시퀀스가 내장된 SERIAL 또는 SEQUENCE 예약어는 없지만, 구현하는 것은 간단합니다.

gap 없는 시퀀스 구현체들은 저장된 메일링 리스트, 스택 오버플로우 등에서 찾을 수 있습니다.

필수적인 비즈니스 요구 사항이 아니라면, gap 없는 시퀀스 사용하지 마세요.
동적 생성으로 gap 없는 번호 지정을 화면에 보여줘야 하는 요구가 있다면, row_number()란 window function을 사용하거나, 배치 프로세스 추가를 고려하세요.

또 다른 볼것: FAQ: Using sequences in PostgreSQL.

TMI: 최근 글 생성하면 id값이 32나 그 이상으로 뛰는 경우들이 있었는데, 캐시절을 1로 해줘도 해결되지 않았었다.

해결 방안이 안 보여서 불편했었는데, 이 글을 번역하고 마음이 편해졌다.

6.16 OID가 뭔가요?

한줄 정리: WITH OIDS 절은 12이후로 지원 안 함, WITHOUT OIDS 절은 있음

테이블이 WITH OIDS 절로 생성된 경우 각 row는 INSERT 동안 자동적으로 채워지는 OID column이 포함됩니다.

처음에는 전체 설치에서 고유하지만, OID 카운터가 40억을 넘어가면, 그 이후론 OID에 중복이 발생할 수 있습니다.

OID column에 유니크 인덱스를 생성하는 것을 통해 싱글 테이블 내에서 OID 중복을 예방하는 게 가능합니다.(그러나 WITH OIDS 절 자체는 이러한 인덱스를 생성하지 않습니다.)

시스템은 인덱스를 검사해 새로 생성된 OID가 존재하는지 확인하고, 존재하면 다시 생성하는 작업을 반복합니다.

이 작업은 OID가 포함된 테이블에 40억 개보다 작은 row가 있는 한, 잘 작동합니다.

PostgreSQL은 크기 제한이 문제가 되지 않는 시스템 카탈로그의 객체 식별자에 OID를 사용합니다.

사용자 테이블에 고유한 row 숫자가 필요하다면, OID column보단, SERIAL이 더 적합하고, 테이블에 20억 이상의 항목들이 있을 거라 예측되면 BIGSERIAL이 좋습니다.

6.17 CTID가 뭔가요?

CTID는 테이블의 오프셋 위치와 테이블 블록으로 특정 물리적 row를 식별하는데 쓰입니다.

CTID는 인덱스 항목에서 물리적인 row에 대한 포인터로 사용됩니다.

논리적 row의 CTID는 업데이트되면 변경되므로 CTID를 장기적으로 row 식별자로 사용할 수 없습니다.

하지만, 경쟁 상태가 예상되지 않는 트랜잭션에서 수정 작업 시, row를 식별하는데 유용합니다.

6.23 다중 데이터베이스간 쿼리 수행을 어떻게 하나요?

현재 데이터베이스 외에는 직접적인 쿼리를 할 방법은 없지만, 몇몇 접근 방법들이 있습니다.

PostgreSQL의 SQL/MED 지원을 통해 “foreign data wraper(fdw)”가 출시되고, 원격 데이터베이스의 테이블을 로컬 데이터베이스로 연결할 수 있습니다.

원격 데이터베이스는 같은 PostgreSQL 인스턴스 내에 있거나, 세계 반대편에 있든 문제는 없습니다.

postgres_fdw는 9.3 버전에 사용 가능하고, 읽기/쓰기 지원이 추가됐습니다. 9.2의 읽기 전용 버전은 contrib 모듈로 컴파일하고 설치할 수 있습니다.

contrib/dblink는 함수 호출을 사용하여 데이터베이스 간 쿼리를 허용하며 이전 버전에서 사용할 수 있습니다.

postgres_fdw와 달리 원격 서버에 조건을 "push down"할 수 없으므로 종종 필요한 것보다 더 많은 데이터를 가져오게 됩니다.
물론, 클라이언트는 다른 데이터베이스에 동시에 연결하고 결과물을 자기 쪽에서 병합할 수도 있습니다.

6.26 어떤 복제 솔루션을 사용할 수 있나요?

“복제”는 단일 용어지만, 복제 방법엔 여러 기술들이 있으며, 장단점이 있습니다.

우리 문서에는 이 주제에 대한 좋은 지침서가 있고 복제 소프트웨어의 특징을 분류한 문서도 있습니다.

마스터/슬레이브 복제를 통해 단일 마스터는 읽기/쓰기 쿼리를 받을 수 있지만, 슬레이브 DB가 읽기/SELECT 쿼리만 허용합니다.

무료에, 가장 유명한 마스터-슬레이브 구조의 PostgreSQL 복제 솔루션은 Slony-I입니다.

다중 마스터 복제를 통해 읽기/쓰기 쿼리를 복제된 여러 컴퓨터에 보낼 수 있습니다.
이 기능은 서버 간 수정 사항을 동기화해야 하므로 성능에 영향을 미칩니다.

PGCluster는 이런 상황에 가장 적합하고. 널리 사용되고 있는 무료 솔루션입니다.

다양한 복제 모델을 지원하는 독점 하드웨어 기반 복제 솔루션도 있습니다.

6.27 공유 스토리지 postgresql 서버 클러스터를 만들 수 있나요?

PostgreSQL은 SAN, SCSI backplane, iSCSI volume 및 다른 공유 미디어의 공유 스토리지를 사용한 클러스터링 지원을 하지 않습니다.

“RAC-style”과 같은 클러스터링은 지원하지 않습니다. 현재는 복제 기반 클러스터링만 지원합니다.

자세한 내용은 다음 Replication, Clustering, and Connection Pooling를 보세요.

공유 스토리지 “failover”는 가능하지만, 동시간에 하나 이상의 postmaster를 실행하고 데이터 공간에 접근하는 것은 안전하지 않습니다.
Heartbeat 및 STONITH 또는 기타 하드 연결 해제 옵션이 권장됩니다.

6.28 왜 내 테이블과 컬럼명이 쿼리에서 인식되지 않나요? 대문자가 유지되지 않는 이유는 무엇인가요?

가장 흔한 원인은 테이블 생성 단계에서 테이블 또는 column 명에 쌍따옴표 사용을 인식하지 못하는 것입니다.

쌍따옴표가 사용되면, 테이블과 column 명(식별자로 불림) 대소문자를 구분하여 저장되며, 쿼리에서 이름을 참조할 때 반드시 쌍따옴표를 써야 하는 것을 의미합니다.

pgAdmin 같은 몇몇 인터페이스는 테이블 생성에 식별자를 자동으로 쌍따옴표로 묶습니다.

따라서 식별자를 인지하려면 다음 중 하나를 수행해야 합니다.

테이블 생성에 쌍따옴표 식별자를 피하기
식별자로 소문자만 사용하기
쿼리에서 참조할 때 쌍따옴표 식별자를 쓰기

6.31 왜 BEGIN, ROLLBACK, COMMIT이 stored 프로시저/함수에서 작동하지 않나요?

PostgreSQL은 stored 함수에서 자율 트랜잭션을 지원하지 않습니다. 모든 PostgreSQL 쿼리에서 그렇듯, stored 함수는 항상 트랜잭션 내에서 실행되고 트랜잭션 외부에선 실행될 수 없습니다.

트랜잭션을 관리하기 위해 stored 프로시저가 필요하다면, dblink 또는 클라이언트 측 스크립트에서 작업을 수행할 수 있습니다.

일부 케이스에서 각 BEGIN/EXCEPTION/END 블록이 하위 트랜잭션을 생성하기 때문에, PL/PgSQL에서 예외 블록을 사용하는데 필요한 작업을 할 수 있습니다.

6.33 왜 prepared 쿼리 실행할 때, 느려지나요?

PostgreSQL 9.2 이상부터 이 이슈는 매우 드물어졌습니다. 왜냐하면 실행 별 generic이나 값 최적화 plan을 사용하기로 결정할 수 있습니다.

플래너는 다음은 내용들을 통해 잘못된 선택을 할 수 있습니다.

PostgreSQL이 플래닝 타임에 전체 쿼리와 매개변수들을 알게 될 때, 테이블의 통계를 사용해 쿼리에 사용된 column이 일반적인지, 그렇지 않은지 알 수 있습니다.

이를 통해 쿼리의 특정 부분에서 많거나 적은 결과를 예상할 수 있으므로 데이터를 더 효율적으로 가져오는 방식으로 변경할 수 있습니다.

예를 들어, 쿼리 플래너는 인덱스 스캔 대신 순차 탐색을 선택할 수 있습니다.

‘active=y’인 결과를 찾고. 테이블의 레코드 99%가 ‘active=y’를 가진다는 것을 알면 순차 탐색이 더 빠를 것이라 판단합니다.
Prepared 쿼리에서, PostgreSQL은 plan이 만들어질 때, 모든 매개변수의 값을 갖지 않습니다.

prepared 쿼리를 실행할 때 전달한 매개변수의 값에 관계없이 상당히 잘 작동하는 “안전한” plan을 선택합니다.

불행히도, 제공되는 값이 테이블에서 무작위로 선택된 일부 값의 평균보다 흔하거나 그렇지 않으면, 이 plan은 적합하지 않을 수 있습니다.

이 문제가 발생했다 의심되면, EXPLAIN 명령어를 통해 느린 쿼리와 빠른 쿼리를 비교하세요. “EXPLAIN SELECT query…” 와 “PREPARE query... ; EXPLAIN EXECUTE query…”결과를 비교해 plan 차이가 나는지 확인하세요.

EXPLAIN ANALYZ는 row 개수 예측 및 실제 개수와 같은 더 정확한 정보를 줄 수 있습니다.

일반적으로 이 문제가 있는 사람들은 다양한 매개변수로 자주 실행되는 plan 비용이 많이 드는 쿼리에 대한 튜닝 옵션이 아니라 SQL 인젝션을 예방하기 위해 보안 목적으로 prepared 쿼리를 사용합니다.

이런 사람들은 클라이언트 인터페이스가 지원하는(ex PgJDBC) 클라이언트 측 prepared statement를 사용을 고려해야 합니다.

(의문: prepared 써서 느려졌단 말을 들어본 적은 없고, 실제 있어도 클라이언트 측에서 prepared를 쓰는 게 맞는지 의문)

PostgreSQL 프로토콜은 서버 측에서 매개변수화된 쿼리를 지원하며, 대부분의 클라이언트 드라이버는 클라이언트 측에 prepared statement 인터페이스를 통해 이를 사용하는 것을 지원합니다.

현재 PostgreSQL은 특정 매개변수 값 세트를 사용해 prepared statement을 다시 plan 하도록 요청하는 방법을 제공하지 않습니다. 그렇지만, 9.2 이상부터 통계적으로 바람직하다 평가되는 경우 자동으로 이를 수행할 수 있습니다.

6.34 function이 standalone 보다 느린 이유는 무엇입니까?

다음 FAQ 항목을 보세요. PL/PgSQL 함수의 쿼리는 prepared 되고 캐시 되므로, 쿼리를 직접 “prepared” 하고 “execute” 하는 것과 동일하게 실행됩니다.

테이블 통계를 개선하거나 쿼리를 개선하는 게 도움이 되지 않는 매우 심각한 오류가 있는 경우, 실행할 때마다 PL/PgSQL이 이 쿼리를 다시 prepare 하도록 문제를 해결할 수 있습니다.

이렇게 하려면 PL/PgSQL에서 “EXECUTE … USING” 절을 사용해 쿼리를 텍스트 문자열로 제공합니다. 대안으로, quote_literal or quote_nullable 함수는 쿼리 텍스트로 대체된 파라미터를 이스케이프(특수문자나 예약어를 일반 문자로 대체) 할 수 있습니다.

6.35 왜 문자열 정렬이 부정확한가요?

먼저, 사용하고자 하는 locale을 확실하게 하세요. 데이터베이스 전체 locale을 보려면 “SHOW lc_collate” 명령어를 사용하세요.

만약 column 별 데이터 정렬을 사용하는 경우 다음 사항을 확인하세요. 만약 모든 것이 원하는 대로 됐으면, 계속 읽으세요.

PostgreSSQL은 문자열 정렬에 C 라이브러리의 locale 기능을 사용합니다. 그래서 예상한 대로 문자열 정렬이 안 될 수 있으며, C 라이브러리 이슈일 가능성이 높습니다.

텍스트 파일에서 “sort”유틸 기능을 활용해 C 라이브러리의 정렬 정책을 알 수 있습니다.

LC_COLLATE=xx_YY.utf8 sort testfile.txt

만약 PostgreSQL이 준 정렬 결과가 일치하면, 문제는 PostgreSQL 외부에 있습니다.

PostgreSQL은 libc 동작과 달리 문자열을 바이트 순서로 정렬하여 순위가 같은 항목을 구분합니다. 이는 실제로 거의 차이가 없고, 일반적으로 사용자가 정렬 순서로 문제를 제기할 때 문제의 원인은 아니지만, 예를 들어, 조합형, 비조합형 유니코드가 섞인 경우 문제가 될 수 있습니다.

만약, 문제가 C 라이브러리에 있으면, 운영체제 담당자에게 문의해야 합니다.
그러나 C 라이브러리에 locale 정의에 버그가 있다고 알려져 있지만, 대부분은 C 라이브러리가 정확할 것입니다

여기서 “정확하다”의 의미는 인정된 일부 국제 및 국내 표준을 따른다는 의미입니다.

일반적인 제기된 문제 항목입니다.

공백과 특수문자: 보통 정렬 알고리즘은 여러 단계를 거칩니다.

1. 모든 문자를 비교하고, 공백과 구두점을 무시합니다.

2. 공백과 구두점을 비교하여 순서를 결정합니다.(실제 일어난 일을 단순화 한 것입니다.)

locale 정의 자체를 변경하지 않고는 이 단계를 변경할 수 없습니다.(심지어 어렵습니다.)

데이터를 조금 바꿔서 이 문제를 피하고 싶을 수 있습니다.
예를 들어, 이름 필드를 정렬할 때, 이름과 성 필드로 분할하여 그 사이 공백을 피할 수 있습니다.

대/소문자: C locale 외 locale들은 일반적으로 정렬을 대소문자를 같이 진행합니다. 그래서 아스키 기반으로 정렬해 A B C … a b c… 가 아닌 a A b B c C … 가 나올 수 있습니다. 이는 정확합니다.

독일어 locale: ä의 순서를 a 또는 ae로 정렬합니다. 둘 다 유효하지만(참고) 대부분 C 라이브러리는 하나만 제공합니다.

이를 고치는 것은 커스텀 locale을 만들어야 합니다. 가능한 일이지만, 약간의 작업이 필요합니다.

ASCII/byte 순서가 아님: 아니요. 그렇게 하면 안 되고, 그래서도 안 돼요. 아스키는 정렬 순서가 아닌 인코딩입니다. 그래도 하고 싶다면, C locale을 사용하세요. 하지만, 아스키가 아닌 문자에 대한 기능을 사용합니다.

즉, Mac OS X 또는 BSD 기반 운영체제를 사용하고 있고, UTF-8을 사용한다면, 포기하세요. 해당 운영체제의 locale 정의는 잘못되어 있습니다.

Reference

https://wiki.postgresql.org/wiki/FAQ

FAQ - PostgreSQL wiki

Additional FAQ Entries on this Wiki Contents 1 Translations of this Document 2 Platform-specific questions 3 General Questions 4 User Client Questions 5 Administrative Questions 6 Operational Questions 7 Data Modelling Questions Translations of this Docume

wiki.postgresql.org

'개발' 카테고리의 다른 글

[JPA] FindAll이 같은 값만 나와요 (2)	2025.02.15
[PostgreSQL] FDW로 Cross Database 해결하기 (0)	2025.01.19
파일 옮길 땐 tar를 쓰자 (0)	2024.11.19
ArrayBuffer, Blob (0)	2024.11.09
단위 테스트 적용하기 (0)	2024.10.26

[PostgreSQL] FAQ를 번역해 보았다. (스압)

목차

3. Generic Questions

5. Administrative Questions

6. Operational Questions

Reference

'개발' 카테고리의 다른 글

'개발' Related Articles

티스토리툴바

3. Generic Questions