PostgreSQL의 특징 및 주요기능에 대해 알아보기

date

Jun 1, 2023

slug

db-postgresql

author

status

Public

tags

Blog

Database

summary

PostgreSQL의 주요기능에 대해 한걸음씩 알아가기

type

Post

thumbnail

category

📗 Docs

updatedAt

Jun 6, 2023 09:33 PM

PostgreSQL

• 고급 기능과 다양한 설정 옵션을 제공하기 때문에 초기 설정과 관리가 다소 복잡할 수 있습니다. 상대적으로 더 많은 관리 작업과 경험이 필요할 수 있습니다.

관계형 데이터베이스 시스템

ANSI SQL

병렬처리 지원

다양한 클라이언트 도구와 활발한 오픈 소스 커뮤니티

PostgreSQL의 데이터 타입

1. Numeric 데이터 타입
• integer: 정수를 저장합니다. 예: integer (4 바이트 크기), smallint (2 바이트 크기), bigint (8 바이트 크기)
• numeric 또는 decimal: 고정소수점 숫자를 저장합니다. 예: numeric(5,2) 는 최대 세 자리의 정수와 두 자리의 소수를 표현합니다.
• real and double precision: 부동소수점 숫자를 저장합니다. 예: real (4 바이트 크기), double precision (8 바이트 크기)

2. Character 데이터 타입
• character varying(n) 또는 varchar(n): 최대 길이가 n인 가변 길이 문자열을 저장합니다. 예: varchar(50)
• character(n) 또는 char(n): 길이가 n인 고정 길이 문자열을 저장합니다. 예: char(5)
• text: 가변 길이 문자열을 저장합니다. 이 타입은 길이 제한이 없습니다.

3. Binary 데이터 타입
• bytea: 바이너리 데이터를 저장합니다. 예: E'\\xDEADBEEF'

4. Temporal 데이터 타입
• date: 날짜를 저장합니다. 예: date '2004-10-19'
• time: 시간을 저장합니다. 예: time '04:05:06'
• timestamp: 날짜와 시간을 저장합니다. 예: timestamp '2004-10-19 10:23:54'
• interval: 시간 간격을 저장합니다. 예: interval '1 day 10 hours'

5. Boolean 데이터 타입
• boolean: 참 또는 거짓 값을 저장합니다. 예: boolean true

6. Geometric 데이터 타입
• 이들은 점, 선, 경로, 다각형 등 다양한 기하학적 형태를 나타냅니다. 예: point '(1,2)'

7. UUID 데이터 타입
• uuid: 유니버설 고유 식별자(Universal Unique Identifier)를 저장합니다. 예: uuid 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'

8. Array 데이터 타입
• 이 데이터 타입은 동일한 데이터 타입의 요소를 가진 배열을 저장합니다. 예: integer[] '{1,2,3,4}'

9. JSON 데이터 타입
• json과 jsonb: JSON 데이터를 저장합니다. jsonb는 이진 형식으로 저장되며, json보다 검색과 인덱싱이 더 효율적입니다. 예: json '{"name": "John", "age": 30}'
• JSON 데이터 타입을 사용하면 복잡한 JSON 문서를 저장하고 쿼리할 수 있으며, JSONB 데이터 타입은 이진 형식으로 저장되어 검색과 인덱싱 성능을 향상시킵니다.

10. 사용자 정의 데이터 타입(User-Defined Types)
• PostgreSQL은 사용자가 새로운 데이터 타입을 정의할 수 있는 기능을 제공합니다. 이를 통해 데이터베이스 스키마를 정확하게 사용자의 요구 사항에 맞게 설정할 수 있습니다.
예:
-- 사용자 정의 Point 타입 생성
CREATE TYPE point AS (
  x float,
  y float
);

-- 테이블 생성
CREATE TABLE shapes (
  id serial primary key,
  shape_name varchar(50),
  position point
);

-- 데이터 삽입
INSERT INTO shapes (shape_name, position)
VALUES ('Square', ROW(10.0, 20.0)), ('Circle', ROW(30.0, 40.0));

-- 데이터 조회
SELECT shape_name, position.x, position.y
FROM shapes;
위의 예시에서는 point라는 사용자 정의 데이터 타입을 생성하고, 이를 shapes 테이블에서 사용합니다. point 데이터 타입은 x와 y 두 개의 속성으로 구성된 좌표를 나타냅니다. shapes 테이블에서는 도형의 이름과 해당 도형의 위치를 저장합니다.
데이터를 삽입할 때는 ROW 함수를 사용하여 point 데이터를 생성하고 저장합니다. 데이터를 조회할 때는 position.x와 position.y와 같은 방식으로 사용자 정의 데이터 타입 내의 속성에 접근할 수 있습니다.

11. 범위 타입(Range Types)
• PostgreSQL의 범위 타입은 값의 범위를 나타내는 데이터 타입입니다. 예를 들어, int4range는 정수의 범위를, daterange는 날짜의 범위를 나타냅니다. 이를 통해 특정 범위 내에 값이 있는지를 효율적으로 확인할 수 있습니다.

12. 열거형 타입(Enum Types)
• 열거형 타입은 특정 값들의 목록을 나타내는 데이터 타입입니다. 예를 들어, 'red', 'green', 'blue'와 같은 색상을 나타내는 열거형 타입을 생성할 수 있습니다.

13. 비트 문자열 타입(Bit String Types)
• 비트 문자열 타입은 이진 데이터를 나타내는 데이터 타입입니다. 이는 주로 'bit'와 'bit varying' 두 가지 형태로 사용됩니다. 'bit(n)'는 고정 길이 n의 비트 문자열을, 'bit varying(n)'는 최대 길이 n의 비트 문자열을 나타냅니다.

14. NoSQL 스타일의 데이터 타입(hstore, 키-값 쌍을 저장하는 데이터 타입)
예:
-- 테이블 생성
CREATE TABLE user_data (
  id SERIAL PRIMARY KEY,
  attributes HSTORE
);

-- 데이터 삽입
INSERT INTO user_data (attributes)
VALUES ('"name" => "John", "age" => "30", "city" => "New York"');

-- 데이터 조회
SELECT attributes->'name' AS name, attributes->'age' AS age
FROM user_data;
위의 예시에서 user_data 테이블은 attributes라는 hstore 컬럼을 가지고 있습니다. attributes 컬럼은 키-값 쌍을 저장하며, 각 키와 값은 문자열 형태로 저장됩니다. 이를 통해 유연한 데이터 모델을 구성할 수 있습니다.
위 예시에서는 'name', 'age', 'city'와 같은 키에 대한 값을 저장하고, 쿼리하여 해당 값을 가져오는 것을 확인할 수 있습니다. 이렇게 hstore를 사용하면 스키마가 고정되어 있지 않고 동적인 데이터 모델을 적용할 수 있습니다.

PostgreSQL의 인덱싱

B-Tree 인덱스

• PostgreSQL에서 가장 일반적으로 사용되는 인덱스 타입입니다. 이 인덱스는 키를 정렬된 순서로 저장하므로, 대등(equal) 및 범위(range) 비교에 효과적입니다.

Hash 인덱스

• 키의 해시 값을 사용하여 데이터를 저장합니다. 이 인덱스는 대등 비교에만 효과적이며, 키의 순서에 대해서는 알지 못합니다.

GiST (Generalized Search Tree) 인덱스

• 다양한 데이터 타입에 대해 여러 종류의 쿼리를 효율적으로 지원할 수 있는 "일반화된" 인덱스입니다. 공간 및 텍스트 검색 같은 복잡한 데이터 구조에 유용합니다.

SP-GiST (Space-Partitioned GiST) 인덱스

• "공간 분할" 데이터에 최적화된 GiST의 변형입니다. 이 인덱스는 트리, IP 네트워크, 다각형 등과 같이 비-중첩(non-overlapping), 공간 분할 데이터 구조에 사용됩니다.

GIN (Generalized Inverted Index) 인덱스

• 이 인덱스는 다중 값 컨테이너에 효과적입니다. 배열, 텍스트 문서 등에 사용됩니다.

BRIN (Block Range INdex) 인덱스

• 매우 큰 테이블에 효과적입니다. BRIN은 특정 연속 블록 범위에 대한 요약 정보를 저장합니다.

Partial 인덱스

• Partial 인덱스는 특정 조건을 만족하는 행만 인덱스화하는 인덱스 유형입니다. 이를 통해 특정 데이터 부분집합에 대한 검색 성능을 향상시킬 수 있습니다. WHERE 절을 사용하여 인덱스가 적용되는 행을 제한할 수 있습니다.

-- Partial 인덱스 생성
CREATE INDEX idx_completed_orders ON orders (order_id) WHERE status = 'completed';

-- 상태가 "completed"인 주문 조회
SELECT order_id, status FROM orders WHERE status = 'completed';

PostgreSQL의 트랜잭션 처리

1. Atomicity (원자성)
트랜잭션 내의 모든 작업이 성공하거나, 아무것도 수행되지 않아야 합니다. 이는 트랜잭션이 '모두 또는 없음'의 원칙을 따른다는 것을 의미합니다.

2. Consistency (일관성)
트랜잭션이 데이터베이스를 한 일관된 상태에서 다른 일관된 상태로 이동시키는 것을 보장합니다. 이는 데이터베이스의 무결성 제약 조건이 항상 충족되는 것을 의미합니다.

3. Isolation (고립성)
각 트랜잭션은 독립적으로 실행되며, 다른 트랜잭션의 작업에 영향을 받지 않습니다.

4. Durability (영속성)
트랜잭션이 성공적으로 커밋된 후에는, 그 결과는 영구적이어야 합니다. 시스템 장애가 발생하더라도 커밋된 트랜잭션의 변경사항은 유지되어야 합니다.

• Write-ahead logging (WAL)
모든 변경사항이 디스크에 안전하게 기록되기 전에는 트랜잭션이 커밋되지 않도록 보장하는 기술입니다. 이는 Durability를 보장합니다.

• Multi-Version Concurrency Control (MVCC)
각 트랜잭션이 데이터베이스의 '스냅샷'에 대해 작동하도록 하여, 다른 트랜잭션의 변경사항에 영향을 받지 않도록 하는 메커니즘입니다. 이는 Isolation을 보장합니다.

• Locking and Concurrency Control (락과 동시성 제어)
PostgreSQL은 트랜잭션 간의 충돌을 방지하고, 데이터 무결성을 보장하기 위해 다양한 잠금과 동시성 제어 메커니즘을 사용합니다.

PostgreSQL의 기능

저장 프로시저와 사용자 정의 함수(User-Defined Functions)

-- 저장 프로시저 생성
CREATE OR REPLACE PROCEDURE calculate_total_price(order_id INT)
AS $$
DECLARE
    total DECIMAL;
BEGIN
    SELECT SUM(price * quantity) INTO total
    FROM order_items
    WHERE order_id = calculate_total_price.order_id;

    UPDATE orders
    SET total_price = total
    WHERE id = calculate_total_price.order_id;
END;
$$ LANGUAGE plpgsql;

-- 저장 프로시저 실행
CALL calculate_total_price(123);

CREATE FUNCTION cancel_order(order_id INT) RETURNS VOID AS
$$
BEGIN
    UPDATE orders
    SET status = 'Cancelled'
    WHERE order_id = order_id;
END;
$$
LANGUAGE plpgsql;

1. 호출 방식: 사용자 정의 함수는 단일 SQL 문 내에서 직접 호출될 수 있습니다. 함수는 SELECT 문이나 WHERE 절과 같은 다른 SQL 문 내에서 호출될 수 있습니다. 반면에, 저장 프로시저는 CALL 문을 사용하여 명시적으로 호출되어야 합니다.

2. 결과 반환: 사용자 정의 함수는 일반적으로 결과를 반환합니다. SELECT 문에서 함수를 호출하면 함수의 결과를 사용할 수 있습니다. 반면에, 저장 프로시저는 일반적으로 결과를 직접 반환하지 않습니다. 저장 프로시저는 주로 데이터를 수정하거나 처리하는 데 사용됩니다.

3. 트랜잭션 제어: 사용자 정의 함수는 트랜잭션 내에서 실행되는 경우 트랜잭션의 일부로 처리됩니다. 함수의 실행이 롤백되는 경우, 이전 상태로 돌아갑니다. 저장 프로시저는 별도의 트랜잭션을 사용하거나 트랜잭션 외부에서 실행될 수 있습니다.

4. 파라미터 전달: 사용자 정의 함수와 저장 프로시저는 모두 파라미터를 전달할 수 있습니다. 그러나 사용자 정의 함수는 다른 SQL 문 내에서 호출되는 경우에도 파라미터를 전달할 수 있지만, 저장 프로시저는 명시적인 호출을 통해만 파라미터를 전달할 수 있습니다.

5. 코드 구조: 사용자 정의 함수는 일반적으로 단일 SQL 문을 실행하는 작은 코드 조각으로 작성됩니다. 반면에, 저장 프로시저는 더 큰 규모의 로직을 포함하는 복잡한 코드 블록으로 작성됩니다. 저장 프로시저는 변수 선언, 제어 구문 (if-else, 반복문 등), 예외 처리 등을 포함할 수 있습니다.

윈도우 함수(Window Functions)

<함수>(<표현식>) OVER ([PARTITION BY <파티션 기준>] [ORDER BY <정렬 기준>] [ROWS <범위/행 기준>])

SELECT product_category, product_price, SUM(product_price) OVER (PARTITION BY product_category) AS category_total
FROM products;

SELECT product_name, product_price, RANK() OVER (ORDER BY product_price DESC) AS rank
FROM products;

SELECT date, revenue, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM sales;

SELECT order_id, customer_id, order_date, 
       ROW_NUMBER() OVER (ORDER BY order_date) AS row_num
FROM orders;

SELECT order_id, customer_id, order_date,
       SUM(order_total) OVER (ORDER BY order_date) AS cumulative_total
FROM orders;

• 윈도우 프레임(SPECIFYING THE WINDOW FRAME) 정의
PostgreSQL은 기본적으로 RANGE나 ROWS를 사용하여 윈도우 프레임을 정의할 수 있습니다.
윈도우 프레임은 윈도우 명세 내에서 계산에 포함되는 행들의 범위를 지정합니다. ROWS BETWEEN 또는 RANGE BETWEEN 절로 정의됩니다.
ROWS BETWEEN 절은 행의 개수를 기준으로 윈도우 프레임을 지정합니다. 예를 들어, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING은 현재 행을 포함하여 이전 행과 다음 행까지의 범위를 지정합니다.
RANGE BETWEEN 절은 값의 범위를 기준으로 윈도우 프레임을 지정합니다. 예를 들어, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW은 현재 행을 포함하여 이전 값과의 범위를 지정합니다.

• Null 처리 정렬
• 윈도우 함수에서 NULL 값을 처리하는 방식에 차이가 있습니다. PostgreSQL은 기본적으로 NULLS FIRST 또는 NULLS LAST와 같은 NULL 정렬 옵션을 사용하여 NULL 값을 처리합니다. MySQL은 NULL 값을 기본적으로 가장 작은 값으로 처리합니다.

일정(Schedule)

-- 스케줄 작업 생성
INSERT INTO pgagent.pga_jobagent (jobagentid, jobagentname, jobhostagent, jobdesc)
VALUES (1, 'My Job Agent', '', 'My Job Agent');

INSERT INTO pgagent.pga_jobstep (jstid, jstjobid, jstname, jstdesc, jstenabled, jstkind, jstcode)
VALUES (1, 1, 'My Job Step', '', TRUE, 's', 'SELECT my_function();');

INSERT INTO pgagent.pga_schedule (jscid, jscjobid, jscname, jscdesc, jscenabled, jscstart, jscend, jscminutes, jschours, jscweekdays, jscmonthdays, jscmonths)
VALUES (1, 1, 'My Schedule', '', TRUE, '2000-01-01 01:00:00', NULL, '{0}', '{1}', '{0,1,2,3,4,5,6}', '{-1}', '{0}');

-- pg_cron 확장 모듈 설치
CREATE EXTENSION pg_cron;

-- 스케줄 작업 생성
SELECT cron.schedule('0 1 * * *', 'SELECT my_function()');

암호화와 XML 처리

-- pgcrypto 확장 모듈 활성화
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 텍스트 암호화
SELECT pgp_sym_encrypt('mytext', 'mysecretpassword');

-- 암호화된 텍스트 복호화
SELECT pgp_sym_decrypt('\xc36cf9...', 'mysecretpassword');

-- XML 데이터 저장
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    data XML
);

INSERT INTO books (data)
VALUES ('<book><title>Book 1</title><author>Author 1</author></book>');

-- XML 데이터 쿼리
SELECT data
FROM books
WHERE xpath_exists('/book/author[text()="Author 1"]', data);

-- XML 데이터 업데이트
UPDATE books
SET data = xml_modify(data, '/book/author[text()="Author 1"]/text()', 'New Author')
WHERE xpath_exists('/book/author[text()="Author 1"]', data);

외부 데이터 연결(Foreign Data Wrappers, FDW)

트리거(Trigger), 외래 키(Foreign Key), 뷰(View)

CREATE FUNCTION update_inventory() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.quantity < OLD.quantity THEN
        -- 재고 감소에 따른 추가 작업 수행
        -- ...
    END IF;
    
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER inventory_trigger
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_inventory();

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT,
    -- ...
    FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

복제(Replication)

1. 물리적 복제(Physical Replication)
이 방식은 바이트 단위로 데이터를 복제합니다. PostgreSQL은 Write-Ahead Logging (WAL) 방식을 사용하여 Master의 모든 변경 사항을 Slave에 복제합니다. 이 방식은 거의 실시간으로 일어나며, 주로 재해 복구와 가용성 향상을 목적으로 사용됩니다.

2. 논리적 복제(Logical Replication)
PostgreSQL 10 이후 버전부터는 Logical Replication이라는 기능이 도입되었습니다. 이 방식은 특정 데이터베이스 또는 테이블을 복제하는 데 사용됩니다. Logical Replication은 데이터의 일부만 복제하거나, 다른 PostgreSQL 버전 또는 다른 시스템으로의 복제를 지원합니다.

샤딩(Sharding)

파티셔닝(Partitioning)

클러스터링(Clustering)

1. PostgreSQL Automatic Failover (PAF)
PostgreSQL 클러스터에서 자동 실패 복구를 제공하는 Pacemaker 리소스 에이전트입니다.

2. Patroni
PostgreSQL에 대한 템플릿화된 클러스터링 솔루션을 제공하는 오픈 소스 프로젝트입니다.

3. Pgpool-II
여러 PostgreSQL 서버 사이에서 연결 및 쿼리를 분산하며, 고가용성, 로드 밸런싱, 자동 실패 복구 등을 제공하는 중간 계층입니다.

참고링크

• PostgreSQL Documents