데이터베이스 커서와 컬럼형 데이터베이스

SQL의 CURSOR,
psycopg의 cursor 옵젝트랑 머가 다름? 😗

---

🏹 데이터베이스 커서

데에타베에스 커서: 쿼리 결과를 한 행(row)씩 처리할 수 있도록 해주는 객체.
대량의 데이터셋을 한 번에 메모리에 로드하지 않고, 필요한 만큼만 가져와서 처리 가능.
프로시저 콜로 불러온 result set 내에서 추려냄.

커서의 종류

- Forward-Only Cursor: 한 방향으로만 이동 가능

- Scrollable Cursor: 앞뒤로 이동 가능

- Holdable Cursor: 트랜잭션 종료 후에도 유지

- Non-Holdable Cursor: 트랜잭션 종료 시 자동 해제

사용 예시 (Redshift)

-- 프로시저 생성
-- result_set에 result set을 바인드 할 수 있게 하고,
-- refcursor 라는 커서 데이터 타입으로 지정
CREATE OR REPLACE PROCEDURE cursor_test_proc (result_set INOUT refcursor)
-- 프로시저 바디 $$ ... $$
AS $$
BEGIN
	-- 리저얼트셋 바인딩즁
	OPEN result_set FOR SELECT * FROM afdbt_reddit;
END;
-- PostgreSQL의 procedural language.
-- 프로시저 안에서 여러 로직이 가능하게 해줌 (FOR 같은)
$$ LANGUAGE plpgsql;

------------------------------------------------

BEGIN;
-- CALL을 할 때 리절트셋 크기에 비례해 딜레이가 좀 있지만 FETCH는 빠름
CALL cursor_test_proc ('cursortest');
-- ROW 1부터 한 행씩 FETCH
FETCH NEXT FROM cursortest;
CLOSE cursortest;
END;

빠르다잉

---

😋 그럼 어디에 씀?

- 한 번에 모든 데이터를 처리할 필요가 없을 때

- 애플리케이션에서 행 단위로 로직을 적용해야 할 때

- OLTP(Online Transaction Processing) 시스템에서 행별 트랜잭션을 수행해야 할 때

하지만,

- 반복적인 I/O 및 컨텍스트 스위칭 비용이 발생할 수 있어 성능 저하 초래.

- Redshift에서 많은 데이터 조회시 -> UNLOAD를 사용해 S3로 덤프하라고 추천하고있음.

---

🐄 컬럼형 스토리지는?

데이터를 컬럼 단위로 저장하는 데이터베이스. OLAP 환경에서 대량의 데이터 분석을 효율적으로 수행.

커서와 비교

- 컬럼 단위로 데이터 스캔. 낫 행 단위

- 디스크 I/O가 줄어듦.

- 압축 및 인덱싱 최적화를 통해 분석 성능이 향상됨.

컬럼형 DB의 용도

- 대량의 데이터 분석

- 복잡한 애구리게이션 구리다

- 빠른 검색, 필터링

- 데이터 압축, 저장 효율성

컬럼형 DB에서 커서를 대체하려면...?

- 윈도우 함수: 행 기반 연산 대체

- CTE: 통째로 갈아치운다? ... 반복 연산 간소화

- 병렬 실행과 배치 처리

- 사전 집계를 위한 materialized views

결국 윈도함수만 빼면 OLAP exploit 하는거 아닌가 ...

---

🚂 Redshift를 psycopg의 cursor 옵젝트를 사용하여 쿼리하면...?

Redshift 내부에서는 쿼리에 대해서 컬럼단위 연산을 한 후, psycopg쪽에서 해당 result set을 행 단위로 다루게 됨.

 

- Redshift 내부에서는 컬럼 기반으로 최적화된 쿼리를 실행 → 압축, 벡터 연산 활용

- 비즈니스 로직에서는 행 기반으로 처리 → Python에서 fetchone(), fetchall() 사용

- 대량 데이터 페칭 시 성능 저하 가능 → LIMIT, OFFSET 사용 지양, UNLOAD로 S3에 저장 후 처리 고려

---

🎅 Low Level 에서의 OLTP vs OLAP

	OLTP	OLAP
저장 방식	행(Row) 기반	컬럼(Column) 기반
주요 연산	개별 트랜잭션	대량 데이터 분석
인덱스 구조	B-Tree 인덱스	컬럼 단위 저장, Zone Maps, 압축
성능 초점	빠른 읽기/쓰기	빠른 조회 및 집계

OLTP의 B-Tree 인덱스

- B-Tree는 균형 트리 구조로 구성되어 있으며, 특정 값을 빠르게 찾는 데 최적화됨.

- B-Tree는 ... 다음 시간에? 😢

- 인덱스를 활용하여 개별 행 조회 성능을 향상시킴.

OLAP의 컬럼 기반 최적화 기법

- 컬럼 단위 저장 → 필요한 컬럼만 읽음.

- Zone Maps → 컬럼 블록 내 최소/최대 값을 저장하여 불필요한 블록 스캔 방지.

- Min-Max Indexes → 특정 값이 존재하는 블록만 검색.

- Compression → 컬럼 단위 압축 적용하여 I/O 감소.

- Vectorization → CPU 캐시 효율 극대화. (엔코딩되서...그것도 압축 아닌가? 🤔)

-- 예시: WHERE age > 30 실행 시 블록 필터링 과정 (pseudo code)
Block 1: [ 20, 25 ] -> Zone Map: min=20, max=25 (스킵)
Block 2: [ 40, 35 ] -> Zone Map: min=35, max=40 (읽음)
Block 3: [ 45, 30 ] -> Zone Map: min=30, max=45 (읽음)
Block 4: [ 50 ] -> Zone Map: min=50, max=50 (읽음)

Predicate Pushdown?

조건문을 쿼리엔진 레벨에서 실행하지 않고 그 전에 실행하여 I/O 부담을 줄이는 기술.
쿼리 엔진에서 스토리지 엔진레벨로 내려와 실행됨. 즉 쿼리 이전에 실행.
컬럼형 DB에서는 WHERE 조건을 최적화하여 불필요한 블록을 읽지 않음.

- HDFS나 S3같은 분산 시스템에서는 파일 read로 푸시다운 -> predicate을 각 노드에 전달

- 컬럼 기반 시스템에서는 컬럼 최적화 기법으로 푸시다운

SIMD와 AVX?

상술한 벡터라이제이션에서 사용되는, CPU 내장 하드웨어 기술

- SIMD(Single Instruction, Multiple Data): 한 번에 여러 데이터를 처리. (연산을 여러 요소에 동시에 적용 etc...)

- AVX(Advanced Vector Extensions): SIMD의 업그레이드판. 더 큰 벡터를 수용할 수 있음 (256-512 bit) -> Parallelism 향상

---

🎆 요약: OLTP vs OLAP 비교

항목	OLTP	OLAP
저장 방식	행(Row) 기반	컬럼(Column) 기반
인덱스	B-Tree	Zone Maps, Min-Max Index
I/O 효율성	낮음 (행 단위)	높음 (컬럼 단위)
CPU 활용	낮음 (단일 연산)	높음 (벡터 연산, SIMD)

물론 효율이 높다는건 throughput이 받쳐줘야하겟지? 다음 주제는 high connectivity랑 infiniband로 ?

결론

• OLTP 환경에서는 커서를 사용하여 개별 행을 처리하지만, 대량의 데이터를 다룰 경우 성능 저하 가능.
• OLAP 환경에서는 컬럼형 데이터베이스가 효율적이며, 벡터 연산 및 압축 기법을 활용해 성능을 최적화.
• Redshift 같은 컬럼형 DB에서도 커서를 사용할 수 있지만, 집합 연산을 활용하는 것이 더 적절함.
• ...그 커서랑 이 커서랑은 스코프가 조금 다르다...