As I've always been

Postgres 메모리 설정을 최대한 활용하는 방법Postgres 데이터베이스는 많은 양의 RAM을 사용함. 결과 집합을 만들 때 인덱스 매칭, 테이블에서 관련 행 검색, 튜플 병합/필터링/집계/정렬 등의 단계를 거치며, 이 모든 단계에서 메모리에 의존Postgres의 메모리 사용량을 최적화하기 위해서는 사용 가능한 RAM을 최대한 활용하면서도, 다양한 유형의 메모리 할당을 효율적으로 조정하고, OS가 과도한 메모리 사용으로 프로세스를 종료시키는 것을 방지해야 함Sharing is CaringPostgres와 관련된 RAM의 가장 큰 부분은 shared_buffers라고 불리며, 가장 자주 검색되는 모든 테이블과 인덱스의 행을 나타냄. 사용 빈도에 따라 점수를 매기는 휴리스틱에 의해 지원됨shared_b..

1. hstore 타입의 내부 저장 구조PostgreSQL의 hstore 타입은 Key-Value 쌍을 저장하는 자료형이다.내부적으로는 바이너리 형태로 저장되게 된다.초기 입력은 문자열 형태로 입력되지만, 실제 데이터는 바이너리로 압축하여 저장됨각 Key-Value 쌍은 내부적으로 바이너리로 인코딩되며, 데이터 접근 시 이를 파싱하여 처리한다.따라서 일반적으로 저장공간 효율성이 뛰어난 편이며, JSON 타입과 비교할 때 좀 더 간결하게 저장될 수 있다. 2. hstore 타입의 인덱스 사용법PostgreSQL의 hstore는 다음과 같은 인덱스들을 지원한다.(1) GIN 인덱스가장 자주 쓰이며 권장되는 방법이다hstore 필드에서 특정 key의 존재 여부나 특정 key-value 쌍이 존재하는지(@> 연..

1. PostgreSQL에서 Bloom Filter란?PostgreSQL에서 제공하는 bloom 확장(extension)은 여러 컬럼을 조합한 검색에서 효율적인 검색을 돕기 위해 블룸 필터 방식을 이용하는 비트맵 기반의 확률적 인덱스이다.기본적으로 Index Access Method를 통해 bloom filter를 인덱스로서 add-on하는 것이다.장점: 여러 컬럼을 조합한 쿼리에 대해 공간을 절약하면서 빠른 검색 속도를 지원한다.단점: False Positive(거짓 긍정, 존재하지 않는 데이터가 있다고 판단하는 오류)가 발생할 수 있다.일반적으로 정확한 결과를 얻으려면 블룸필터 인덱스를 통해 후보를 좁힌 후, 최종적으로 일반적인 방법으로 필터링을 수행한다. 2. Bloom Extension 설치하기기..

1. 조인 전략 목록중첩 루프 조인(Nested Loop Join)머지 조인(Merge Join)해시 조인(Hash Join)참고: 이 외에도 “인덱스 중첩 루프(Index Nested Loop)”나 “병렬 조인(Parallel Join)” 같은 변형이 있지만, 기본 원리는 위 세 가지에 포함된다.2. 각 조인 전략의 특징2.1 중첩 루프 조인 (Nested Loop Join)원리외부 테이블(outer)의 각 튜플을 순회하면서, 내부 테이블(inner)에서 조건에 맞는 튜플을 매칭(탐색)장점작은 테이블끼리의 조인이나, 외부 튜플이 극히 적을 때 효율적임인덱스가 잘 구축되어 있으면 인덱스 중첩 루프(Index Nested Loop)로 빠르게 탐색 가능단점튜플 수가 많아지면 O(Nouter × Cost(in..

PostgreSQL가 최초 initdb()를 수행하게 되면 Template0, Template1, Postgres 3개의 데이터베이스가 생성된다. Template 데이터베이스는 이름에서 나타나듯이 데이터베이스 생성 시 템플릿을 제공하기 위해 만들어진 데이터베이스이다.사용자가 Template1에 객체를 추가한 후에 새로운 데이터베이스를 생성하여 사용하게 되면 Template1에서 생성한 내용들이 복제된다.따라서, 추가적인 작업을 하지 않아도 Template1에 적용된 템플릿이나 오브젝트들을 새롭게 생성한 데이터베이스에서 동일하게 사용할 수 있게 된다. Template0 데이터베이스는 Template1의 초기 상태와 동일하다.PostgreSQL 에서 제공하는 표준 객체만 포함되어 있으며 수정이나 변경이 불가..

PostgreSQL에서는 WAL의 쓰기 동작을 조정하는 몇 가지 매개변수를 통해 유사한 효과를 낼 수 있다.이 값들을 통해 쓰기 성능을 개선할 수 있다. 단 이러한 설정 시, 재해 등으로 인해 데이터 손실 가능성이 생기게 된다. 다음은 관련 설정들이다:1. wal_writer_delayWAL Writer 프로세스가 디스크로 WAL을 기록하기 전에 대기하는 시간(밀리초)을 설정기본값: 200ms더 큰 값을 설정하면 쓰기 빈도가 줄어들어 디스크 I/O 부하를 줄일 수 있다.설정:SET wal_writer_delay = '500ms';2. wal_writer_flush_afterWAL Writer가 데이터를 디스크로 flush하기 전에 누적할 수 있는 최대 데이터 양(바이트)을 지정기본값: 1MB더 큰 값을 ..

네트워크 라우터의 TCP 연결 타임아웃과 Postgres keep-alive 설정에 대한 흥미로운 사례를 공유하고자 한다.부제는 "Cisco와 Windows의 조합으로 만들어진 혼돈" 정도로 하고자 한다. 문제 상황Windows 환경에서 Postgres 클라이언트와 DB 서버를 사용하던 중, 90분이 지나면 연결이 끊어지는 현상이 발생했다. 이 문제를 해결하기 위해 Cisco 공식 문서를 찾아보면서 원인을 파악할 수 있었다.라우터의 연결 상태 관리 방식라우터는 로컬 상태 테이블에서 연결들을 추적하며, 이를 통해 라우팅 결정을 내리고 유효하지 않은 패킷을 필터링한다. 이 상태 정보는 다음과 같은 경우에 제거된다:양쪽 중 한 쪽이 연결을 종료할 때연결이 너무 오랫동안 유휴 상태일 때 (클라이언트와 서버가 모..

PostgreSQL를 사용하는 프로젝트에서 DevOps 업무를 맡았을 때 자주 사용했던 쿼리들을 간단히 정리해보았다. -- t_product 테이블의 Auto VACUUM 설정을 비활성화 ALTER TABLE public.t_product SET (autovacuum_enabled = false); -- 가장 오래된 20개의 테이블들의 목록 조회 SELECT c.oid::regclass AS table_name, greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age, pg_size_pretty(pg_table_size(c.oid)) AS table_size FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid..