green plum 설계

• MPP(Massively Parallel Processing) 구조로 여러 Segment 노드가 병렬로 작업
• 각 Segment의 효율적인 메모리 캐시 활용이 성능을 좌우하는 핵심 요소
• Greenplum에서 캐시 효율성을 높이는 주요 요소는 다음과 같습니다.
  • 데이터 Locality (데이터 지역성) :: Greenplum은 Segment마다 개별적인 캐시가 존재하며, 분산키(Distribution Key)를 통해 데이터를 Segment에 균일하게 분산. 동일한 Segment 내에서 많은 작업을 처리하면 데이터가 캐시에 머물 확률이 높아짐
  • 적절한 데이터 분산 (Distribution) :: 자주 JOIN이나 GROUP BY에 쓰이는 컬럼을 Distribution Key로 설정하여, 연산 시 데이터가 한 Segment에 모이도록 설계 추천하며, 일반적으로 PK 또는 FK 컬럼, 혹은 조인에 자주 등장하는 컬럼이 좋음
  • 파티셔닝(Partitioning) :: 시간이나 지역 등으로 파티셔닝하면, 불필요한 데이터를 메모리에 적재하지 않아 메모리 효율이 증가
  • 적절한 컬럼 데이터타입(Data type) 선정 :: INT, SMALLINT 등 가능한 작은 데이터 타입 사용 시, 더 많은 데이터가 캐시에 유지
  • 최적화된 압축(Compression) :: Zstandard(zstd)와 같은 압축 방식을 활용하면 캐시 히트율이 올라감
• Postgresql.conf 설정

shared_buffers = 8GB. # 데이터 캐싱 용도로 사용되는 메모리 (전체 RAM의 25%)
effective_cache_size = 24GB # OS + DB가 활용할 수 있는 최대 캐시량 설정 (OS 캐시까지 포함하여 DB가 활용 가능한 캐시량을 옵티마이저에게 알려주는 역할)
work_mem = 512MB  # 작업별 정렬, 해시 연산 메모리 (병렬성 많다면 작게)
maintenance_work_mem = 2GB. # 인덱스 생성/삭제 및 파티션 관리 시 사용하는 메모리
wal_buffers = 16MB  # Write-Ahead Logging 버퍼 설정 (쓰기 성능 향상)

greenplumDB(GPDB)

• postgresql 기반 MPP(Massively Parallel Processing) (postgreSQL은 SMP)
• 여러개의 disk기반 db인스턴스들이 하나의 dbms처럼 작동(분산처리)
• postgresql planner뿐만 아니라 gporca도 쿼리 플래닝에 사용
• 시스템 카탈로그, 옵티마이저, 쿼리 샐행기, 트랜젝션 매니저 수정/강화
• 외부 테이블 병렬 로딩, 자원관리, 쿼리 최적화, 스토리지 강화 (append-optimized storage, column storage) (for bi workload)
• MPP
  • 각 CPU가 독립된 memory와 disk를 할당받아 분산처리가 가능(shared noting architecture)
• SMP(Symmetric Multi Processing) :: cpu들이 memory와 disk를 공유

greenplum 속도개선

• 분산키 설정 :: 유니크하면서 3개 이상을 넘지 않는 컬럼수 (분산도 90%이상되도록 확인)
• 파티셔닝 (빅테이블 조건이면서 빈번하게 조회가 일어나는 올랩용 마트) :: 일자 컬럼을 ragnge 파티션/ 브랜드 혹은 구분자를 가지고 조회조건을 자주 조회하면 리스트 파티션을 통해서 구분
• 인덱스 :: dw 사상의 특징상 인덱스는 잘 안검/ 빈번한 del, insert가 일어나면 drop생성이 나음/ 비트맵, 비트리인덱스 선택

command

\list # 데이터베이스 확인
\c airflow # 데이터베이스 사용
\dt # 테이블 확인
\ds # Sequence 목록
\df # Function 목록
\dv # View 목록
\du # User 목록
show data_directory; # 데이터 디렉터리 확인

install - mac

brew install postgresql
brew services start postgresql 또는 /opt/homebrew/opt/postgresql@14/bin/postgres -D /opt/homebrew/var/postgresql@14
psql -v
export PATH="/usr/local/opt/postgresql/bin:$PATH" >> ~/.zshrc
source ~/.zshrc
createdb 'testdb'
psql <DB_NAME> 
\du

• vector plugin

brew install pgvector 또는
git clone --branch v0.8.0 https://github.com/pgvector/pgvector.git
make
make install

(psql 접속)
CREATE EXTENSION vector;
\dx
create table tb_js_test (id SERIAL PRIMARY KEY, name TEXT, embed VECTOR(3));
insert into db_js_test (name, embed) values ('김준석', '[0.1, 0.2, 0.3]);
(유사도 검색 예제) select * from tb_js_test where id < 5 order by embed <=> '[0.2, 0.3, 0.4]' limit 1

• postgresql vector plugin 연산

<-> : L2 거리 (l2, 유클리드 거리) 
<#> : negative inner product (내적) 
<=> : cosine 거리 (코사인 유사도) 
<+> L1 거리 (added in 0.7.0) (L1 거리, 맨하탄 거리)
<~> : hamming 거리 (binary vectors, added in 0.7.0) (해밍 거리) 
<%> : jaccard 거리 (binary vectors, added in 0.7.0) (자카드 거리)

기본문법

PostgreSQL 기본 문법

\l  # 데이터베이스 목록 및 소유자 보기
SELECT current_user; # 현재 접속 계정
\du  # 사용자 목록 및 권한 보기
\c <database_name> # 데이터 베이스 변경
\q # 나가기
\dt # 테이블 목록 보기

데이터베이스 생성 및 접속

createdb mydb           # 데이터베이스 생성
psql mydb               # 생성한 DB에 접속
psql -U username -d mydb  # 사용자명과 함께 DB 접속

스키마 생성 및 사용

# 스키마 생성
CREATE SCHEMA my_schema;

# 테이블 생성 시 스키마 지정
CREATE TABLE my_schema.users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    email TEXT
);

# 스키마를 디폴트로 설정 (세션 내에서만 유효)
SET search_path TO my_schema;

# 이제 테이블 생성 시 스키마 생략 가능
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    title TEXT
);

테이블 생성 및 확인

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

\dt  -- 테이블 목록 보기

데이터 삽입

INSERT INTO users (name, age) VALUES ('홍길동', 30);

데이터 조회

SELECT * FROM users;
SELECT name FROM users WHERE age > 20;

데이터 수정

UPDATE users SET age = 31 WHERE name = '홍길동';

데이터 삭제

DELETE FROM users WHERE id = 1;

인덱스 생성

CREATE INDEX idx_users_name ON users(name);

테이블 삭제

DROP TABLE users;

사용자 및 권한

CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

postgresql db 성능 통계

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

# 테이블별 읽기/쓰기 통계
SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;


# 인덱스 사용 현황
SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
ORDER BY idx_scan DESC;

# 캐시 적중률
SELECT
  ROUNDnumeric / NULLIF(SUM(blks_hit) + SUM(blks_read), 0))::numeric, 4 AS cache_hit_ratio
FROM pg_stat_database;

# 현재 실행 중인 쿼리 확인
SELECT pid, now() - query_start AS runtime, query
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY runtime DESC;

# 디스크 I/O 부하 분석 (OS 수준)
SELECT * FROM pg_stat_io WHERE backend_type = 'client backend';

# Auto Vacuum 동작 확인 (테이블에 쌓이는 불필요한 튜플(dead tuples) 을 자동으로 정리)
SELECT relname, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;