Skip to content

PostgreSQL

PostgreSQL은 객체-관계형 데이터베이스 관리 시스템(ORDBMS)의 일종이다. BSD 허가권으로 배포되며 오픈소스 개발자 및 관련 회사들이 개발에 참여하고 있다.

최초설치시 기본 포트로 5432를 사용한다.

Categories

Commandline

  • psql - command line client
  • pg_dump - 백업용 클라이언트

Extensions

  • pg_cron
  • hstore
  • Apache AGE - A Graph Extension for PostgreSQL.
  • pg_graphql - PostgreSQL용 GraphQL 확장
  • pg_crdt - 실험적인 Postgrest용 CRDT 확장 오픈소스
  • PgBouncer - lightweight connection pooler for PostgreSQL
  • pg_flo - PostgreSQL 데이터를 실시간으로 스트리밍, 변환 및 재라우팅
  • pgrx - Rust로 PostgreSQL 확장을 개발하는 프레임워크
  • pglite-fusion - PostgreSQL 테이블에 SQLite 임베딩하기
  • VectorChord - 40만개 벡터를 PostgreSQL에 $1로 저장하기

PostgreSQL을 사용한 프로젝트들

Client

Tools

ETC

Extension

PostgreSQL은 SQL 객체를 패키지로 만들어 하나의 단위로 사용함으로써 데이터베이스 기능을 확장하는 방법을 제공합니다.

PostGIS

PostGIS 2.3 확장 프로그램 그룹 및 PostGIS 3.0 확장 프로그램은 JSON-C 지원을 포함하여 Cloud SQL에서 지원됩니다.

  • postgis
  • postgis_tiger_geocoder
  • postgis_topology

데이터 유형 확장 프로그램

  • btree_gin - B-tree와 동등한 동작을 구현하는 샘플 GIN 연산자 클래스를 제공합니다.
  • btree_gist - B-tree와 동등한 동작을 구현하는 GiST 색인 연산자 클래스를 제공합니다.
  • chkpass - 암호화된 비밀번호를 저장하기 위해 설계된 데이터 유형 chkpass를 구현합니다. Cloud SQL용 PostgreSQL 12에서는 지원되지 않습니다.
  • citext - 대소문자를 구분하지 않는 문자열 유형 citext를 제공합니다.
  • cube - 다차원 큐브를 나타내는 데이터 유형 cube를 구현합니다.
  • hstore - 단일 PostgreSQL 값 내에서 키-값 쌍 조합을 저장하기 위한 hstore 데이터 유형을 구현합니다.
  • isn - 일부 국제 제품 번호 지정 표준에 대한 데이터 유형을 제공합니다.
  • ltree - 계층 트리 구조에 저장된 데이터 라벨을 나타내는 데이터 유형 ltree를 구현합니다.
  • lo - 대형 객체(LO 또는 BLOB) 관리를 지원합니다.
  • postgresql-hll - 새로운 데이터 유형인 hll(HyperLogLog 데이터 구조)을 도입합니다. 아래의 postgresql-hll 섹션도 참조하세요.

언어 확장 프로그램

  • plpgsql - 함수, 절차, 트리거를 만들기 위한 로드 가능한 절차적 언어입니다. DO 블록에서 코드를 직접 실행하는 데 이 언어를 사용할 수도 있습니다.

기타 확장 프로그램

  • dict_int - 전체 텍스트 검색을 위한 부가기능 사전 템플릿으로 정수 색인 생성을 제어합니다.
  • earthdistance - 지구 표면에서 대원 거리를 계산하는 두 가지 접근법을 제공합니다.
  • fuzzystrmatch - 문자열 간의 유사점과 거리를 확인하는 여러 기능을 제공합니다.
  • intagg - 정수 애그리게이터와 열거자를 제공합니다.
  • intarray - null이 없는 정수 배열을 조작하고 이에 대한 색인이 생성된 검색을 수행하는 데 필요한 함수 및 연산자 세트를 제공합니다.
  • pageinspect - 하위 수준에서 데이터베이스 페이지의 콘텐츠를 검사합니다. 아래의 pageinspect 섹션도 참조하세요.
  • pgaudit - 감사 데이터를 수집합니다. Cloud SQL에서 이 확장 프로그램을 사용하는 방법은 pgAudit를 사용하여 PostgreSQL 감사를 참조하세요.
  • pg_buffercache - 공유 버퍼 캐시의 변경사항을 실시간으로 검사하는 수단을 제공합니다.
  • pgfincore - PostgreSQL에서 운영체제 디스크 캐시 메모리의 페이지를 관리하는 함수 집합입니다. 아래의 pgfincore 섹션도 참조하세요.
  • pg_freespacemap - 여유 공간 맵(FSM)을 검사합니다. 아래의 pg_freespacemap 섹션도 참조하세요.
  • pgcrypto - PostgreSQL의 암호화 기능을 제공합니다.
  • pgstattuple - 튜플 수준 통계를 얻기 위한 다양한 함수를 제공합니다.
  • pg_repack - 테이블 및 색인에서 블로트를 제거할 수 있습니다. 원하는 경우 온라인 CLUSTER(클러스터 색인별로 테이블 정렬)를 수행할 수 있습니다. 아래의 pg_repack 섹션도 참조하세요.
  • pgrowlocks - 지정된 테이블에 대한 행 잠금 정보를 제공합니다.
  • pg_prewarm - 관계 데이터를 운영체제 버퍼 캐시나 PostgreSQL 버퍼 캐시로 편리하게 로드하는 방법을 제공합니다.
  • pg_stat_statements - 실행된 모든 SQL 구문 실행 통계를 추적하는 수단을 제공합니다.
  • pg_trgm - 빠른 유사 문자열 검색을 지원하는 인덱스 연산자 클래스뿐 아니라 트라이그램 매칭을 기반으로 하는 영숫자 텍스트의 유사성을 결정하는 데 필요한 함수와 연산자를 제공합니다.
  • pg_visibility - 테이블의 가시성 지도(VM) 및 페이지 수준의 가시성 정보를 검사할 수 있는 방법을 제공합니다. 아래의 pg_visibility 섹션도 참조하세요.
  • PL/Proxy - PostgreSQL 데이터베이스 간에 리모트 프로시져 콜을 허용하는 프로시져 언어 핸들러이며, 선택 사항인 샤딩이 있습니다. 아래의 PL/Proxy 섹션도 참조하세요.
  • postgres_fdw - 인스턴스 내에서 또는 인스턴스 간에 외부 데이터 래퍼를 만들 수 있습니다. 아래의 postgres_fdw 섹션도 참조하세요.
  • sslinfo - 현재 클라이언트가 인스턴스에 연결할 때 제공한 SSL 인증서에 대한 정보를 제공합니다.
  • tablefunc - 테이블(여러 행)을 반환하는 다양한 함수를 포함합니다.
  • tsm_system_rows - SELECT 명령어의 TABLESAMPLE 절에서 사용할 수 있는 테이블 샘플링 메서드인 SYSTEM_ROWS를 제공합니다.
  • tsm_system_time - SELECT 명령어의 TABLESAMPLE 절에서 사용할 수 있는 테이블 샘플링 메서드인 SYSTEM_TIME을 제공합니다.
  • unaccent - 어휘소에서 악센트(발음 구별 기호)를 제거하는 텍스트 검색 사전입니다.
  • uuid-ossp - 여러 표준 알고리즘 중 하나를 사용하여 범용 고유 식별자(UUID)를 생성하는 기능을 제공합니다.

ETC

ERD Tools

Show all parameters

show the value of a run-time parameter. 

SHOW ALL

데이터 디렉토리 확인 방법 

show data_directory;

Timestamp 

SELECT TO_TIMESTAMP('2017-03-31 9:30:20','YYYY-MM-DD HH:MI:SS');

MIN/MAX

최대/최소 추출. 레코드 집계 함수.

GREATEST/LEAST

레코드 집계 함수가 아닌 경우 사용할 수 있다. Array 같은 자료형, 즉 1객 데이터 또는 plpgsql의 함수 안에서 변수 단위로 사용 가능.

ARRAY

EMPTY ARRAY 

select * from add_daemon_and_port(ARRAY[]::INTEGER[]);

또는: 

select * from add_daemon_and_port('{}');

Tips

잘 알려지지 않은 PostgreSQL 기능들:

  • Insert on Conflict(Upsert) 시 수정/추가된 Row 수 알기 : xmax
  • 특정 컬럼에 권한 부여하기
  • 멀티 패턴 매칭: SIMILAR TO 또는 ~ 정규식
  • 현재 시퀀스값 증가시키지 않고 알아내기: pg_sequence_last_value()
  • 멀티라인 SQL과 \COPY 같이 사용하려면 : TEMPORARY VIEW 또는 COPY와 \g 이용
  • 자동 생성 키에 값 설정 막기: 테이블 생성시 GENERATED ALWAYS 지정 (GENERATED BY DEFAULT 대신)
  • Pivot 테이블 만들기: pandas.pivot_table, \crosstabview 또는 tablefunc 확장 사용
  • Dollar Quoting
    • $$ 와 $$ 사이의 모든 글자는 문자열로 인식
    • $JSON$ / $function$ 처럼 안에 Tag이용 가능
    • ::jsonb 를 붙이면 빠르게 jsonb객체 생성
  • DB객체에 코멘트 달기: COMMENT ON TABLE/COLUMN, Dollar Quoting 이용해서 긴 문자열 설명 추가도 가능
    • 작성: COMMENT ON TABLE sale IS 'Sales made in the system';
    • 보기: \d+ sale
  • DB 별 History 별도로 기록하기
    • \set HISTFILE ~/.psql_history- :DBNAME
  • 자동완성을 대문자로 하기: \set COMP_KEYWORD_CASE upper
  • 슬립 주기: pg_sleep(초), pg_sleep_for('4 minutes 14 seconds')
  • 서브 쿼리 없이 그룹의 첫/마지막 줄 가져오기: DISTINCT ON (그룹 컬럼)
  • uuid-ossp 확장없이 UUID 생성하기: gen_random_uuid() - version 4 UUID를 생성
  • 재현 가능한 랜덤 데이터 생성: setseed() 로 시드를 같게
  • 기존 데이터를 즉시 검증하지 않고 조건 추가하기: ALTER 할때 NOT VALID 로 조건만 먼저 추가하고, ALTER VALIDATE로 기존 데이터 검증은 따로 실행
  • 오라클의 Synonym 같은 기능을 이용하기: search_path 변경 (Zero Downtime Migration시 유용)
  • 겹치는 Range 찾기 : OVERLAPS 연산자

Postgres를 모든 곳에 사용하세요

Postgres는 (수백만명의 사용자까지) 수많은 백엔드 기술을 대체 가능

  • Kafka, RabbitMQ, Mongo, Redis,..
  • 캐시에 Redis 대신 UNLOGGED 테이블에 TEXT 를 JSON 형으로 사용
    • 스토어드 프로시저로 데이터에 대한 만료기간을 설정
  • 메시지큐(Kafka) : SKIP LOCKED
  • 데이터 웨어하우스는 Postgres+TimescaleDB
  • Mongo 대신 JSONB를 저장하고 검색 및 인덱싱
  • pg_cron 으로 메일 전송 같은 CRON 데몬으로 사용
  • Geospacial 쿼리에 사용
  • Elastic 대신 Fulltext 검색에 사용
  • DB내에서 JSON을 생성해서 서버사이드 코드 없이 API에 바로 전달하기
  • GraphQL 어댑터로 GraphQL도 지원

Query History

Partial Index (부분 인덱스)

조건을 만족하는 것만 indexing 하기 위해 쓰입니다.

아래의 DDL 예시 참조: 

CREATE UNIQUE INDEX email_idx ON public.myapp_email USING btree (email) WHERE (email IS NOT NULL and email <> '');

where 절을 보면 email이 not NULL이면서, email이 빈 문자열이 아닌 경우에만 indexing을 한다는 것을 의미합니다.

with Node.js

LISTEN & NOTIFY

테이블의 컬럼 출력 순서

"[wiki.postgresql.org/wiki/Alter_column_position Alter column position]" in the PostgreSQL Wiki says:

PostgreSQL currently defines column order based on the ``attnum`` column of the ``pg_attribute`` table. The only way to change column order is either by recreating the table, or by adding columns and rotating data until you reach the desired layout.

That's pretty weak, but in their defense, in standard SQL, there is no solution for repositioning a column either. Database brands that support changing the ordinal position of a column are defining an extension to SQL syntax.

One other idea occurs to me: you can define a VIEW that specifies the order of columns how you like it, without changing the physical position of the column in the base table.

varchar(n)를 기본적으로 사용하지 말아야 하는 이유

해당 행목 참조.

varchar(n) 대신 글자수 제한 방법

PostgreSQL:Constraints#글자 수 제한 항목 참조. 간단히: 

ALTER TABLE names ADD CONSTRAINT namechk CHECK (length(name) <= 255);

문자열 계산 방법 

select length('abcd');  -- 4 -- 문자열의 길이를 "바이트 단위"로 반환.
select char_length('abcd');  -- 4 -- length() 와 비슷하지만. "문자 단위"로 반환한다.

select length('Dž') -- 2
select char_length('Dž'); -- 1

select character_length('abcd');  -- 4 -- char_length() 와 동일
select bit_length('1234');   -- 32 -- 문자열의 비트 수
select octet_length('1234'); -- 4 -- 문자열의 바이트 수

데이터 웨어하우스로 사용하기

데이터분석을 통한 의사결정 지원시스템 Narrative가 경험을 통해 정리한 주요 트윅들

  • 프로덕션 시스템과 같은 서버 사용하지 말 것
  • PG 12+로 업그레이드(CTE 사용하지 말 것)
  • 인덱스는 가능한 작게
  • 테이블 파티셔닝(range/list)
  • 디스크 I/O 최소화
  • 벌크 인서트 후 Vacuum
  • 병렬 쿼리 사용하기
  • 통계 샘플링을 증가시키기 (컬럼별 또는 DB 전체. 기본값 100 이지만 500)
  • 더 적은 컬럼 사용하기 (row 기반 저장이기 때문)
  • 5천만~1억로우 테이블에서는 Postgres가 훌륭하게 동작
  • 10억단위로 스케일하게 될때는 Citus 를 추천

Function pipelines - PostgreSQL에 함수형 프로그래밍 추가하기 

SELECT device_id,
ㅤㅤtimevector(ts, val) -> sort() -> delta() -> abs() -> sum()
ㅤㅤㅤㅤas volatility
FROM measurements
WHERE ts >= now()-'1 day'::interval
GROUP BY device_id;

데이터 백업 및 복구 하는 방법 

## 데이터베이스 전체 백업
pg_dumpall -f all-backup.sql -U postgres -W

## 특정 데이터베이스 선택 백업
pg_dump -h 127.0.0.1 -p 5432 -U postgres -W --compress=5 -f sample_db-20201219.sql.gz sample

## 특정 데이터베이스의 테이블 백업
pg_dump -h 127.0.0.1 -p 5432 -U postgres -W -d sample -t sample_table -f sample_table-20201219.sql

쿼리파일로 복구하는 방법: 

psql -h 127.0.0.1 -p 5432 -U postgres -W -d sample -f sample_db-20201219.sql

다른 데이터베이스 동기화 방법

PostgreSQL에서 다른 데이터베이스(DB), 예를 들어 MariaDB의 특정 테이블을 동기화하여 PostgreSQL의 테이블로 복제하는 방법은 다양한 도구와 기술을 통해 가능합니다.

일반적으로 이를 위해 사용되는 옵션은 다음과 같습니다:

Foreign Data Wrapper (FDW)
PostgreSQL에서 외부 데이터베이스의 테이블에 접근하고 이를 복제할 수 있는 방법으로, mysql_fdw라는 외부 데이터 래퍼를 사용해 MariaDB에 연결할 수 있습니다.
Bucardo, SymmetricDS
이들 도구는 이기종 데이터베이스 간의 동기화를 지원하며, MariaDB에서 PostgreSQL로 데이터 복제를 설정할 수 있습니다.
  • Bucardo - PostgreSQL 환경에서 사용하는 마스터-슬레이브 또는 마스터-마스터 동기화 도구입니다.
  • SymmetricDS - MariaDB와 PostgreSQL 간의 양방향 및 일방향 동기화를 지원하며, 테이블 단위로 동기화할 수 있습니다.
이들 도구는 데이터 변경 사항을 트리거 방식 또는 로그 기반으로 감지하여 다른 데이터베이스에 실시간 또는 주기적으로 동기화합니다.
ETL (Extract, Transform, Load) 솔루션 사용
Apache NiFi나 Talend와 같은 ETL 도구를 사용하여 MariaDB의 데이터를 추출하고 이를 PostgreSQL로 로드하는 작업을 주기적으로 수행할 수 있습니다.

bigint vs uuid 

CREATE UNLOGGED TABLE test_bigint (
   id bigint GENERATED ALWAYS AS IDENTITY (CACHE 200) PRIMARY KEY
);

uuid-ossp 확장 필요. 

CREATE UNLOGGED TABLE test_uuid (
   id uuid DEFAULT gen_random_uuid() PRIMARY KEY
);

serial vs slug

Uniform Resource Identifier#Slug vs ID 항목 참조.

Docker-Compose Example 

version: '2.1'

services:
  root_db:
    image: postgres
    restart: always
    volumes:
      - db_data:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    environment:
      POSTGRES_PASSWORD: password
      POSTGRES_USER: postgres
      POSTGRES_DB: root_db
    healthcheck:
      test: pg_isready -U "$$POSTGRES_USER" -d "$$POSTGRES_DB"
      interval: 10s
      timeout: 2s
      retries: 10
volumes:
  db_data: {}

Database URL: pg://root_db:5432?u=postgres&p=password&d=root_db

2025년을 위한 7개의 데이터베이스 중 하나

  • Database#2025년을 위한 7개의 데이터베이스 (GeekNews) 참조
  • PostgreSQL은 기본적으로 사용되는 안정적인 기술
    • "Just use Postgres"라는 문구는 널리 알려진 밈이자 신뢰성을 상징하는 표현임
    • ACID를 준수하며 물리적 및 논리적 복제를 포함한 강력한 기능을 제공함
    • 주요 벤더들 간에 광범위한 지원을 받고 있는 안정적 데이터베이스임
  • PostgreSQL의 가장 큰 매력: 확장성
    • 확장 기능(Extensions)을 통해 독창적인 기능을 추가 가능함
    • 주요 확장 기능 예시:
      • AGE: 그래프 데이터 구조와 Cypher 쿼리 언어 지원
      • TimescaleDB: 시계열 데이터 작업 지원
      • Hydra Columnar: 열 기반 스토리지 엔진 제공
    • 확장 기능은 PostgreSQL을 타 데이터베이스와 차별화시키는 핵심 요소임
  • PostgreSQL의 유용성과 확장성
    • 다양한 생태계를 갖추고 있으며, 기본 설정이 합리적이고 사용자 친화적임
    • 비 PostgreSQL 서비스에서도 Postgres 와이어 프로토콜을 사용해 클라이언트 호환성을 제공함
    • WebAssembly(Wasm) 환경에서도 설치 가능할 정도로 가벼움
  • PostgreSQL 학습 권장
    • PostgreSQL의 가능성과 한계를 이해하기 위해 시간을 투자할 가치가 있음
    • 예: MVCC(Multi-Version Concurrency Control)의 복잡성 이해
    • 간단한 CRUD 애플리케이션 개발, PostgreSQL 확장 기능 작성 등을 추천함

See also

Favorite site

Article

Guide

Tip & Trick

Project

References

  1. Lesser_Known_PostgreSQL_Features_-_Haki_Benita.pdf ↩

  2. Hello_world_»_Head_first_PostgreSQL.pdf ↩