DewStream

※ PostgreSQL Query Tuning: Using Indexes and LATERAL. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 인덱스의 중요성을 보여주는 튜닝 사례입니다. (실제 운영 환경에서 튜닝 후 약 110배 성능 향상되었습니다.)1. AS-IS 쿼리▸ 이 쿼리는 기준 시각 이후로 조건에 맞는 계정별 가장 최근 사용 내역을 뽑는 쿼리로 하루에 수천만 번 이상 동작합니다.-- 테이블과 컬럼명을 가칭으로 바꾼 쿼리입니다.SELECT table_a.id, max(table_b.created_at) as last_atFROM table_aINNER JOIN table_b ON table_b.a_id = table_a.id AND table_b.created_at >= $1::ti..

보호되어 있는 글입니다.

※ PostgreSQL: pg_upgrade. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 PostgreSQL 버전 업그레이드 시, 사용하는 도구인 pg_upgrade에 대한 내용입니다.1. pg_upgrade?pg_upgrade는 데이터 파일을 그대로 재사용하면서 시스템 카탈로그만 New 버전 형식으로 재생성합니다.(귀찮은 대규모 덤프/복원을 대체할 수 있습니다.) ▸ 제약사항PostgreSQL 9.2 버전 이상에서만 지원.TO-BE 버전의 PostgreSQL이 설치되어 있어야 함.pg_upgrade는 TO-BE 버전의 바이너리에서 수행해야 함.바이너리가 호환 가능해야 함. (예: 64bit ↔ 64bit)AS-IS와 TO-BE의 컴파일 옵션이 동일해야 함. (--with-blocksize, --with..

※ PostgreSQL: VIEW vs MATERIALIZED VIEW. 안녕하세요. 듀스트림입니다. 오늘은 VIEW와 MATERIALIZED VIEW에 대한 내용입니다.VIEW는 ANSI SQL 표준입니다.ANSI SQL에서 VIEW는 가상 테이블로 정의되어 있으며 대부분의 DBMS에서 동일하게 동작합니다.MATARIALIZED VIEW는 PostgreSQL, Oracle 등에서 지원하는 확장 기능이며 ANSI 표준은 아닙니다.1. VIEW vs MATERIALIZED VIEW항목VIEWMATERIALIZED VIEWANSI SQL 표준 지원✅✅물리적 데이터 저장❌✅쿼리 시 동작원본 테이블 쿼리를 매번 실행저장된 결과 반환최신성항상 최신 데이터 반영갱신 전까지는 구버전 데이터인덱스 생성 가능 여부❌✅수..

※ PostgreSQL: Transaction Isolation Level. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 트랜잭션 격리 수준에 관한 내용입니다.PostgreSQL도 MySQL과 마찬가지로ANSI SQL 표준에 따라 Transaction Isolation Level을 설정할 수 있습니다.1. ANSI SQL 표준 격리 수준격리 수준설명PostgreSQL 지원 여부MySQL 지원 여부READ UNCOMMITTED커밋되지 않은 변경사항도 다른 트랜잭션에서 읽을 수 있음 (Dirty Read 가능)❌ (자동으로 READ COMMITTED로 동작)✅READ COMMITTED커밋된 변경사항만 읽을 수 있음. 같은 쿼리라도 트랜잭션 중간에 실행하면 다른 결과 가능✅ 기본값✅ 기본값REPEATABLE R..

※ PostgreSQL: Prepared Transaction(Two-Phase Commit). 안녕하세요. 듀스트림입니다. 오늘은 분산 시스템에서 트랜잭션의 원자성을 보장하기 위해 사용하는 2PC 프로토콜의 일환인 Prepared Transaction 기능에 대한 내용입니다.1. Prepared Transaction?Prepared Transaction은 분산 시스템에서 트랜잭션의 원자성을 보장하기 위해 사용하는 기술로, PostgreSQL을 포함한 많은 데이터베이스에서 지원하는 기능입니다.Prepared Transaction은 트랜잭션을 두 단계로 나누어 처리하는 2단계 커밋(2PC, Two-Phase Commit) 프로토콜의 일환으로 동작합니다. 이 기술은 분산 트랜잭션 처리에서 중요한 역할을 하며..

※ PostgreSQL: pg_waldump. 안녕하세요. 듀스트림입니다. 오늘은 복구 관련 이슈 시 분석을 도와주는 pg_waldump에 대한 내용입니다. WAL에 대한 내용은 아래 포스팅 참고 부탁드립니다. PostgreSQL: WAL※ PostgreSQL: WAL. 안녕하세요. 듀스트림입니다. 오늘은 매우 중요한 데이터 무결성과 복구를 보장하는 핵심 메커니즘인 WAL(Write-Ahead Logging)에 대한 내용입니다.1. WAL의 기본 개념Write-Ahead Logging (WAL)dewstream.tistory.compg_waldump?pg_waldump는 PostgreSQL의 WAL(Write-Ahead Logging) 파일을 사람이 읽을 수 있는 형태로 분석/출력해주는 디버깅 및 분석 ..

※ PostgreSQL: Synchronization Batch Using FDW. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 Postgres_fdw를 활용하여 데이터를 원천(Remote)에서 로컬로 동기화하는 방법에 대한 내용입니다.이 구성은 주로 스테이징 DB를 따로 사용하거나, 프론트 영역과 OLAP 영역이 나눠져 있을 경우 나오는 아키텍처입니다.1. postgres_fdw 개념과 핵심 기능범주설명원격 테이블 매핑CREATE SERVER → CREATE USER MAPPING → CREATE FOREIGN TABLE만으로 외부 PostgreSQL 테이블을 지역 DB 객체처럼 다룸.푸시다운 최적화WHERE‧JOIN‧AGGREGATE를 가능한 한 원격 서버로 밀어 넣어 네트워크 전송량을 최소화. (필요..

※ PostgreSQL: Locale. 안녕하세요. 듀스트림입니다. 오늘 포스팅은 읽어두시면 언젠가 한번은 써먹으실 로케일에 관련된 내용입니다.1. 로케일(Locale)의 개념과 PostgreSQL의 사용 목적 로케일(Locale)은 운영체제와 애플리케이션이 문자 정렬(COLLATE), 문자 분류(CTYPE), 메시지 언어 등을 어떻게 처리할지를 정의하는 설정입니다. PostgreSQL에서는 로케일 설정이 아래 기능에 영향을 줍니다.항목설명LC_COLLATE문자열 정렬 기준LC_CTYPE대소문자 구분, 유효한 문자 분류LC_MESSAGESPostgreSQL 오류 메시지의 언어LC_NUMERIC, LC_MONETARY 등숫자, 통화, 날짜 등의 표현 방식위 설정은 initdb 시점에 결정되며 이후에는 변..

※ PostgreSQL: Performing Index Operations Online Using CONCURRENTLY. 안녕하세요. 듀스트림입니다. 또 한 주가 시작되었습니다. 오늘 포스팅은 운영 서비스 중에도 쓰기 중단 없이 인덱스를 추가·재구성·삭제할 수 있게 해주는 CONCURRENTLY 옵션에 대한 내용입니다. CREATE INDEXCREATE INDEX CREATE INDEX — define a new index Synopsis CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ …www.postgresql.org1. CONCURRENTLY 옵션?목적: DDL 실행 중에도 INSERT/UPDATE/DELETE를 차단하지 않고 인덱스를 생성·재구성·삭제할 수 있게..