테이블 이름 / 설명 pg_aggregate 사용자 정의 및 내장 집계 함수에 대한 정보 pg_am 접근 방법(access methods), 예: btree, hash 등 pg_amop 접근 방법 연산자(operator)에 대한 정보 pg_amproc 접근 방법에서 사용하는 지원 함수들 pg_attrdef 컬럼 기본값 (DEFAULT) 저장 pg_attribute 테이블/뷰의 각 컬럼 정보 pg_auth_members 역할(role) 간의 멤버십 관계 pg_authid 역할(role)에 대한 기본 정보 (암호 포함, superuser 여부 등) pg_cast 형변환 함수 정보 (예: int → text) pg_class 테이블, 인덱스, 시퀀스 등 객체 정보 pg_collation 문자 정렬 방식 정보 pg_constraint 제약조건 정보 (PK, FK, CHECK 등) pg_conversion 문자셋 간 변환 정보 pg_database 데이터베이스 목록 pg_db_role_setting 특정 DB/Role 조합에 대한 GUC 설정 pg_default_acl 기본 접근 권한 (DEFAULT PRIVILEGES) pg_depend 오브젝트 간 의존 관계 pg_description 객체에 부여된 설명(comment) pg_enum 열거형(enum) 타입 값 pg_event_trigger 이벤트 트리거 정보 pg_extension 확장(extension) 정보 pg_foreign_data_wrapper 외부 데이터 래퍼 (FDW) 정의 pg_foreign_server 외부 서버 정의 pg_foreign_table 외부 테이블 정의 pg_index 인덱스 정보 (pg_class와 연결됨) pg_inherits 테이블 상속 관계 정보 pg_init_privs 초기 권한 상태 저장용 (ALTER 권한 추적용) pg_language 함수 작성 언어 정보 (sql, plpgsql 등) pg_largeobject 대형 객체 데이터 저장 pg_largeobject_metadata 대형 객체 메타데이터 (소유자, 권한 등) pg_namespace 스키마 정보 pg_opclass 연산자 클래스 정보 pg_operator 연산자 정의 pg_opfamily 연산자 패밀리 정의 pg_parameter_acl 파라미터 접근 권한 pg_partitioned_table 파티셔닝된 테이블 정보 pg_policy Row-Level Security 정책 정보 pg_proc 함수/프로시저 정의 pg_publication logical replication 용 publication 정의 pg_publication_namespace publication과 스키마 연결 정보 pg_publication_rel publication과 테이블 연결 정보 pg_range 범위 타입 정보 pg_replication_origin 논리 복제의 origin 정보 pg_rewrite 뷰나 규칙(rule) 시스템 pg_seclabel 보안 라벨 pg_sequence 시퀀스 객체 정보 pg_shdepend 공유 객체 의존성 pg_shdescription 공유 객체 설명 pg_shseclabel 공유 객체 보안 라벨 pg_statistic planner가 사용하는 통계 정보 (ANALYZE 결과) pg_statistic_ext 확장 통계 정보 (다변량 등) pg_statistic_ext_data 확장 통계의 실제 값 pg_subscription logical replication 구독 정보 pg_subscription_rel subscription에 포함된 테이블 pg_tablespace 테이블스페이스 정의 pg_transform 사용자 정의 타입의 transform pg_trigger 트리거 정의 pg_ts_config 텍스트 검색 설정 pg_ts_config_map 텍스트 검색 설정 세부 맵핑 pg_ts_dict 텍스트 검색 사전 pg_ts_parser 텍스트 검색 파서 pg_ts_template 텍스트 검색 템플릿 pg_type 타입 정보 (기본 + 사용자 정의) pg_user_mapping 사용자-서버 매핑 (FDW용)
🧠 1. 확장 및 구성 관련 pg_available_extensions 설치 가능한 확장 목록 pg_available_extension_versions 확장의 설치 가능한 버전 목록 pg_config PostgreSQL 빌드 및 설정 정보 pg_settings 현재 GUC 설정 파라미터 값 (SHOW ALL과 동일) pg_file_settings postgresql.conf에서 읽은 설정 정보 pg_hba_file_rules pg_hba.conf에 정의된 인증 규칙 pg_ident_file_mappings pg_ident.conf의 유저 매핑
🧩 2. 역할, 사용자 관련 pg_user pg_roles와 동일, but 패스워드 없음 pg_roles 모든 역할(사용자/그룹 포함) pg_group 그룹 역할 정보 (구버전 호환용) pg_shadow 사용자 정보 + 암호 포함 (슈퍼유저만 접근)
📊 3. 통계 및 모니터링 ▶️ 세션 및 활동 pg_stat_activity 현재 실행 중인 세션 정보 pg_prepared_statements 준비된 statement 목록 pg_prepared_xacts 준비된 트랜잭션 목록 (2PC용) pg_replication_origin_status 논리 복제 origin 상태 pg_stat_replication 복제 상태 pg_stat_wal_receiver WAL 수신기 상태 pg_replication_slots replication slots 목록 pg_stat_replication_slots slots의 활동 상태 pg_stat_ssl SSL 연결 정보 pg_stat_gssapi GSSAPI 인증 정보 pg_stat_subscription 구독자 상태 pg_stat_subscription_stats 구독 통계
▶️ 성능 및 자원 pg_locks 현재 세션의 잠금 정보 pg_backend_memory_contexts backend 메모리 사용 상세 pg_shmem_allocations shared memory 사용 상태 pg_stat_wal WAL 쓰기 관련 통계 pg_stat_archiver WAL 아카이버 상태 pg_stat_bgwriter 백그라운드 writer 통계 pg_stat_checkpointer 체크포인트 정보 pg_stat_io IO 통계 pg_stat_recovery_prefetch 복구 중 prefetch 통계
🔄 4. 진행 중 작업 정보 pg_stat_progress_vacuum VACUUM 진행 정보 pg_stat_progress_analyze ANALYZE 진행 정보 pg_stat_progress_cluster CLUSTER 진행 정보 pg_stat_progress_copy COPY 진행 정보 pg_stat_progress_create_index 인덱스 생성 중 상태 pg_cursors 열린 커서 목록
📋 5. 객체 메타정보 뷰 pg_tables 사용자 테이블 목록 pg_views 사용자 뷰 목록 pg_indexes 인덱스 목록 pg_matviews materialized view 목록 pg_sequences 시퀀스 목록 pg_rules RULE 정보 pg_policies Row-Level Security 정책 pg_user_mappings FDW 사용자 매핑 pg_publication_tables publication에 포함된 테이블 pg_seclabels 보안 라벨 목록
📈 6. 통계 뷰 (Tables & Indexes) ▶️ 테이블, 인덱스 단위 통계 pg_stat_all_tables 모든 테이블의 통계 pg_stat_user_tables 사용자 테이블의 통계 pg_stat_sys_tables 시스템 테이블의 통계 pg_stat_xact_all_tables 트랜잭션당 테이블 통계 pg_stat_xact_user_tables 트랜잭션당 사용자 테이블 통계 pg_stat_xact_sys_tables 트랜잭션당 시스템 테이블 통계 pg_stat_all_indexes 모든 인덱스 통계 pg_stat_user_indexes 사용자 인덱스 통계 pg_stat_sys_indexes 시스템 인덱스 통계 pg_stat_user_functions 사용자 함수 사용 통계 pg_stat_xact_user_functions 트랜잭션 단위 함수 통계
▶️ I/O 통계 pg_statio_all_tables 테이블 I/O 통계 pg_statio_user_tables 사용자 테이블 I/O pg_statio_sys_tables 시스템 테이블 I/O pg_statio_all_indexes 인덱스 I/O pg_statio_user_indexes 사용자 인덱스 I/O pg_statio_sys_indexes 시스템 인덱스 I/O pg_statio_all_sequences 시퀀스 I/O pg_statio_user_sequences 사용자 시퀀스 I/O pg_statio_sys_sequences 시스템 시퀀스 I/O
📚 7. 통계 정보 저장소 (ANALYZE) pg_stats 컬럼 단위 통계 (ANALYZE 결과) pg_stats_ext 확장 통계 정보 pg_stats_ext_exprs 확장 통계 표현식
🌍 8. 시간대 정보 pg_timezone_abbrevs 시간대 약어 목록 pg_timezone_names 시간대 전체 이름 목록
CREATE OR REPLACE FUNCTION generate_sample_data(p_table text, p_count int)
RETURNS void AS $$
DECLARE
col_rec record;
col_list text;
val_list text;
sql text;
i int;
BEGIN
-- Step 1: 컬럼 리스트 확인
SELECT string_agg(quote_ident(column_name), ', ')
INTO col_list
FROM information_schema.columns
WHERE table_name = p_table
AND table_schema = 'public';
IF col_list IS NULL THEN
RAISE EXCEPTION '테이블 "%"의 컬럼 정보를 찾을 수 없습니다.', p_table;
END IF;
-- Step 2: 반복 삽입
FOR i IN 1..p_count LOOP
val_list := (
SELECT string_agg(
CASE
WHEN data_type LIKE '%int%' THEN (trunc(random()*100))::text
WHEN data_type LIKE '%char%' OR data_type = 'text' THEN
quote_literal(
substr(
md5(random()::text),
1,
COALESCE(character_maximum_length, 10)
)
)
WHEN data_type = 'date' THEN
quote_literal(current_date - (random()*365)::int)
WHEN data_type = 'timestamp without time zone' THEN
quote_literal(now() - (random()*1000000)::int * interval '1 second')
ELSE 'NULL'
END, ', '
)
FROM information_schema.columns
WHERE table_name = p_table
AND table_schema = 'public'
);
sql := format('INSERT INTO public.%I (%s) VALUES (%s);', p_table, col_list, val_list);
EXECUTE sql;
END LOOP;
END;
$$ LANGUAGE plpgsql;
OS환경 : Rocky Linux 8.10 (64bit) 3대 DB 환경 : PostgreSQL 17.4 3대 Sharding : Citus 12.x
📌 개요
실시간 데이터 처리를 위해 PostgreSQL 17 + Citus를 활용한 샤딩 기반 분산 데이터베이스 구조를 구축하고, 1억 건 성능 테스트까지 진행한 과정을 정리했습니다.
⚙️ pgpass설정(postgres 패스워드 변경 필요)
--사전에 postgreSQL postgres계정 패스워드 변경 필요
cd ~
vi .pgpass
192.168.67.113:5432:*:postgres:postgres12!
192.168.67.171:5432:*:postgres:postgres12!
192.168.67.244:5432:*:postgres:postgres12!
-- citus 17 다운로드
cd ~
curl -O https://download.postgresql.org/pub/repos/yum/17/redhat/rhel-8-x86_64/citus_17-13.0.3-1PGDG.rhel8.x86_64.rpm
-- citus_17 설치
rpm -ivh citus_17-13.0.3-1PGDG.rhel8.x86_64.rpm
🛠️ postgresql.conf 설정
-- default parameter 생략
-- 아래 내용 추가
wal_level = logical
shared_preload_libraries = 'citus,$libdir/pg_stat_statements'
listen_addresses = '*'
max_replication_slots = 10
max_wal_senders = 10
🛠️ pg_hba.conf 설정
-- hba.conf 파일 수정
host all all 192.168.67.0/24 md5
pg_aggregate 사용자 정의 및 내장 집계 함수에 대한 정보 pg_am 접근 방법(access methods), 예: btree, hash 등 pg_amop 접근 방법 연산자(operator)에 대한 정보 pg_amproc 접근 방법에서 사용하는 지원 함수들 pg_attrdef 컬럼 기본값 (DEFAULT) 저장 pg_attribute 테이블/뷰의 각 컬럼 정보 pg_auth_members 역할(role) 간의 멤버십 관계 pg_authid 역할(role)에 대한 기본 정보 (암호 포함, superuser 여부 등) pg_cast 형변환 함수 정보 (예: int → text) pg_class 테이블, 인덱스, 시퀀스 등 객체 정보 pg_collation 문자 정렬 방식 정보 pg_constraint 제약조건 정보 (PK, FK, CHECK 등) pg_conversion 문자셋 간 변환 정보 pg_database 데이터베이스 목록 pg_db_role_setting 특정 DB/Role 조합에 대한 GUC 설정 pg_default_acl 기본 접근 권한 (DEFAULT PRIVILEGES) pg_depend 오브젝트 간 의존 관계 pg_description 객체에 부여된 설명(comment) pg_enum 열거형(enum) 타입 값 pg_event_trigger 이벤트 트리거 정보 pg_extension 확장(extension) 정보 pg_foreign_data_wrapper 외부 데이터 래퍼 (FDW) 정의 pg_foreign_server 외부 서버 정의 pg_foreign_table 외부 테이블 정의 pg_index 인덱스 정보 (pg_class와 연결됨) pg_inherits 테이블 상속 관계 정보 pg_init_privs 초기 권한 상태 저장용 (ALTER 권한 추적용) pg_language 함수 작성 언어 정보 (sql, plpgsql 등) pg_largeobject 대형 객체 데이터 저장 pg_largeobject_metadata 대형 객체 메타데이터 (소유자, 권한 등) pg_namespace 스키마 정보 pg_opclass 연산자 클래스 정보 pg_operator 연산자 정의 pg_opfamily 연산자 패밀리 정의 pg_parameter_acl 파라미터 접근 권한 pg_partitioned_table 파티셔닝된 테이블 정보 pg_policy Row-Level Security 정책 정보 pg_proc 함수/프로시저 정의 pg_publication logical replication 용 publication 정의 pg_publication_namespace publication과 스키마 연결 정보 pg_publication_rel publication과 테이블 연결 정보 pg_range 범위 타입 정보 pg_replication_origin 논리 복제의 origin 정보 pg_rewrite 뷰나 규칙(rule) 시스템 pg_seclabel 보안 라벨 pg_sequence 시퀀스 객체 정보 pg_shdepend 공유 객체 의존성 pg_shdescription 공유 객체 설명 pg_shseclabel 공유 객체 보안 라벨 pg_statistic planner가 사용하는 통계 정보 (ANALYZE 결과) pg_statistic_ext 확장 통계 정보 (다변량 등) pg_statistic_ext_data 확장 통계의 실제 값 pg_subscription logical replication 구독 정보 pg_subscription_rel subscription에 포함된 테이블 pg_tablespace 테이블스페이스 정의 pg_transform 사용자 정의 타입의 transform pg_trigger 트리거 정의 pg_ts_config 텍스트 검색 설정 pg_ts_config_map 텍스트 검색 설정 세부 맵핑 pg_ts_dict 텍스트 검색 사전 pg_ts_parser 텍스트 검색 파서 pg_ts_template 텍스트 검색 템플릿 pg_type 타입 정보 (기본 + 사용자 정의) pg_user_mapping 사용자-서버 매핑 (FDW용)
🧠 1. 확장 및 구성 관련 pg_available_extensions 설치 가능한 확장 목록 pg_available_extension_versions 확장의 설치 가능한 버전 목록 pg_config PostgreSQL 빌드 및 설정 정보 pg_settings 현재 GUC 설정 파라미터 값 (SHOW ALL과 동일) pg_file_settings postgresql.conf에서 읽은 설정 정보 pg_hba_file_rules pg_hba.conf에 정의된 인증 규칙 pg_ident_file_mappings pg_ident.conf의 유저 매핑
🧩 2. 역할, 사용자 관련 pg_user pg_roles와 동일, but 패스워드 없음 pg_roles 모든 역할(사용자/그룹 포함) pg_group 그룹 역할 정보 (구버전 호환용) pg_shadow 사용자 정보 + 암호 포함 (슈퍼유저만 접근)
📊 3. 통계 및 모니터링 ▶️ 세션 및 활동 pg_stat_activity 현재 실행 중인 세션 정보 pg_prepared_statements 준비된 statement 목록 pg_prepared_xacts 준비된 트랜잭션 목록 (2PC용) pg_replication_origin_status 논리 복제 origin 상태 pg_stat_replication 복제 상태 pg_stat_wal_receiver WAL 수신기 상태 pg_replication_slots replication slots 목록 pg_stat_replication_slots slots의 활동 상태 pg_stat_ssl SSL 연결 정보 pg_stat_gssapi GSSAPI 인증 정보 pg_stat_subscription 구독자 상태 pg_stat_subscription_stats 구독 통계
▶️ 성능 및 자원 pg_locks 현재 세션의 잠금 정보 pg_backend_memory_contexts backend 메모리 사용 상세 pg_shmem_allocations shared memory 사용 상태 pg_stat_wal WAL 쓰기 관련 통계 pg_stat_archiver WAL 아카이버 상태 pg_stat_bgwriter 백그라운드 writer 통계 pg_stat_checkpointer 체크포인트 정보 pg_stat_io IO 통계 pg_stat_recovery_prefetch 복구 중 prefetch 통계
🔄 4. 진행중 작업 정보 pg_stat_progress_vacuum VACUUM 진행 정보 pg_stat_progress_analyze ANALYZE 진행 정보 pg_stat_progress_cluster CLUSTER 진행 정보 pg_stat_progress_copy COPY 진행 정보 pg_stat_progress_create_index 인덱스 생성 중 상태 pg_cursors 열린 커서 목록
📋 5. 객체 메타정보 뷰 (편의용) pg_tables 사용자 테이블 목록 pg_views 사용자 뷰 목록 pg_indexes 인덱스 목록 pg_matviews materialized view 목록 pg_sequences 시퀀스 목록 pg_rules RULE 정보 pg_policies Row-Level Security 정책 pg_user_mappings FDW 사용자 매핑 pg_publication_tables publication에 포함된 테이블 pg_seclabels 보안 라벨 목록
📈 6. 통계 뷰 (Tables & Indexes) ▶️ 테이블, 인덱스 단위 통계 pg_stat_all_tables 모든 테이블의 통계 pg_stat_user_tables 사용자 테이블의 통계 pg_stat_sys_tables 시스템 테이블의 통계 pg_stat_xact_all_tables 트랜잭션당 테이블 통계 pg_stat_xact_user_tables 트랜잭션당 사용자 테이블 통계 pg_stat_xact_sys_tables 트랜잭션당 시스템 테이블 통계 pg_stat_all_indexes 모든 인덱스 통계 pg_stat_user_indexes 사용자 인덱스 통계 pg_stat_sys_indexes 시스템 인덱스 통계 pg_stat_user_functions 사용자 함수 사용 통계 pg_stat_xact_user_functions 트랜잭션 단위 함수 통계
▶️ I/O 통계 pg_statio_all_tables 테이블 I/O 통계 pg_statio_user_tables 사용자 테이블 I/O pg_statio_sys_tables 시스템 테이블 I/O pg_statio_all_indexes 인덱스 I/O pg_statio_user_indexes 사용자 인덱스 I/O pg_statio_sys_indexes 시스템 인덱스 I/O pg_statio_all_sequences 시퀀스 I/O pg_statio_user_sequences 사용자 시퀀스 I/O pg_statio_sys_sequences 시스템 시퀀스 I/O
📚 7. 통계 정보 저장소 (ANALYZE) pg_stats 컬럼 단위 통계 (ANALYZE 결과) pg_stats_ext 확장 통계 정보 pg_stats_ext_exprs 확장 통계 표현식
🌍 8. 시간대 정보 pg_timezone_abbrevs 시간대 약어 목록 pg_timezone_names 시간대 전체 이름 목록
