Oracle 사용자가 PostgreSQL 보고 배 아파하는 기능 6가지 (PostgreSQL 킬러 기능 총정리)

Oracle 사용자가 PostgreSQL 보고 배 아파하는 기능 6가지 (PostgreSQL 킬러 기능 총정리)

테스트 환경: PostgreSQL 17, Rocky Linux 9 / Oracle 19c

지금까지 이 시리즈는 "Oracle과 뭐가 다른가"를 주로 다뤘습니다. 대부분 "Oracle에선 이랬는데 PostgreSQL에선 저렇다" 식이었죠.

이번 편은 다릅니다. "이건 Oracle에도 있었으면 좋겠다" 싶은, PostgreSQL만의 킬러 기능들입니다. 솔직히 저는 이 기능들을 보면서 Oracle 시절이 살짝 억울했습니다. "이걸 그동안 복잡하게 돌아갔던 거야?"

이번 편은 실무 시리즈의 하이라이트입니다. 실습으로 직접 확인하며 진행합니다.

• ①JSONB - MongoDB를 대체하는 문서 저장소
• ②배열 타입 - 정규화 없이 여러 값
• ③CTE + 재귀 쿼리 - 계층 구조를 우아하게
• ④윈도우 함수 - (사실 Oracle에도 있지만 PostgreSQL이 더 편함)
• ⑤UPSERT - MERGE보다 간결한 ON CONFLICT
• ⑥범위 타입 - 기간·구간을 타입으로

바쁘신 분은 관심 기능으로 바로 이동하세요. 하나씩 보면 왜 배가 아픈지 아실 겁니다.

 

① JSONB - PostgreSQL 안의 MongoDB

가장 강력한 기능부터. JSONB는 JSON을 네이티브로 저장하고 인덱싱하고 검색하는 기능입니다. Oracle도 JSON을 지원하지만, PostgreSQL의 JSONB는 성숙도가 다릅니다.

기본 사용

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES 
('{"name": "홍길동", "age": 30, "skills": ["Java", "SQL"]}');

-- JSON 필드 조회
SELECT profile->>'name' AS name,      -- 텍스트로 추출
       profile->'age' AS age_json,    -- JSON으로 추출
       profile->'skills'->>0 AS first_skill  -- 배열 첫 요소
FROM users;

연산자 정리:

->   : JSON 반환 (키 탐색)
->>  : 텍스트 반환 (값 추출)
#>   : 경로 탐색 (JSON 반환)
#>>  : 경로 탐색 (텍스트 반환)
@>   : 포함 검사 (GIN 인덱스로 가속!)

검색과 인덱싱

-- GIN 인덱스 (6편 참고)
CREATE INDEX idx_profile ON users USING GIN (profile);

-- 특정 조건 검색 (인덱스 탐)
SELECT * FROM users WHERE profile @> '{"age": 30}';

-- 배열 안에 특정 스킬 있는 사람
SELECT * FROM users WHERE profile->'skills' ? 'SQL';

★ PostgreSQL 17 신기능: JSON_TABLE

PostgreSQL 17에서 추가된 JSON_TABLE은 JSON을 관계형 테이블로 변환합니다. 이건 정말 강력합니다.

-- JSON을 테이블처럼 펼치기!
SELECT jt.*
FROM users,
     JSON_TABLE(profile, '$' COLUMNS (
         name VARCHAR(50) PATH '$.name',
         age  INT         PATH '$.age'
     )) AS jt;

JSON 데이터를 일반 SQL 테이블처럼 다룰 수 있어서, 집계·조인·윈도우 함수를 그대로 적용할 수 있습니다. PostgreSQL 17은 JSON_EXISTS, JSON_QUERY, JSON_VALUE도 추가했습니다.

배 아픈 포인트: JSONB + GIN 인덱스 + JSON_TABLE 조합이면, 1TB 이하 워크로드는 MongoDB를 따로 쓸 필요가 없습니다. PostgreSQL 하나로 관계형 + 문서형을 다 처리합니다.

 

② 배열 타입 - 정규화 없이 여러 값

2편에서 맛봤던 배열입니다. 한 컬럼에 여러 값을 담습니다.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[],           -- 문자열 배열
    prices NUMERIC[]       -- 숫자 배열
);

INSERT INTO products (name, tags, prices) VALUES 
('노트북', ARRAY['전자', '컴퓨터'], ARRAY[1200000, 1100000]);

-- 배열 조회
SELECT name, 
       tags[1] AS first_tag,        -- 1부터 시작! (Oracle 개발자 주의)
       array_length(tags, 1) AS tag_count
FROM products;

-- 배열 검색
SELECT * FROM products WHERE '전자' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['컴퓨터'];

-- 배열 펼치기 (행으로)
SELECT name, unnest(tags) AS tag FROM products;

배 아픈 포인트: Oracle이라면 product_tags 별도 테이블을 만들고 조인해야 할 것을, 배열 한 컬럼으로 끝냅니다. 물론 정규화가 필요한 경우도 있지만, 태그처럼 단순한 다중 값은 배열이 훨씬 편합니다.

주의: 배열 인덱스는 1부터 시작합니다 (tags[1]). 프로그래밍에 익숙하면 헷갈리니 주의하세요.

 

③ CTE와 재귀 쿼리 - 계층 구조를 우아하게

CTE(Common Table Expression, WITH 절) 는 Oracle에도 있지만, PostgreSQL의 재귀 CTE가 특히 깔끔합니다. Oracle의 CONNECT BY를 대체합니다.

기본 CTE

-- 쿼리를 이름 붙여 단계별로 (가독성 향상)
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 5000
),
dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_sal 
    FROM employees GROUP BY dept_id
)
SELECT h.emp_name, d.avg_sal
FROM high_earners h
JOIN dept_avg d ON h.dept_id = d.dept_id;

★ 재귀 CTE - CONNECT BY의 대안

-- 조직도 계층 구조 (Oracle의 CONNECT BY PRIOR 대체)
WITH RECURSIVE org_chart AS (
    -- 시작점 (최상위)
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀 부분 (하위로 내려감)
    SELECT e.emp_id, e.emp_name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT LPAD(' ', (level-1)*2) || emp_name AS org_tree, level
FROM org_chart;

Oracle: START WITH ... CONNECT BY PRIOR
PostgreSQL: WITH RECURSIVE ... UNION ALL

배 아픈 포인트: WITH RECURSIVE는 SQL 표준이라 다른 DB에서도 통합니다. Oracle의 CONNECT BY는 Oracle 전용이었죠. 표준을 배우면 어디서든 씁니다.

 

④ 윈도우 함수 - 더 편하게

윈도우 함수는 Oracle에도 있습니다. 하지만 PostgreSQL에서 특히 자주, 편하게 쓰게 됩니다.

-- 부서별 급여 순위
SELECT emp_name, dept_id, salary,
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY dept_id) AS diff_from_avg
FROM employees;

-- 누적 합계
SELECT emp_name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

-- 이전/다음 행 값 (LAG/LEAD)
SELECT emp_name, salary,
       LAG(salary) OVER (ORDER BY hire_date) AS prev_salary,
       LEAD(salary) OVER (ORDER BY hire_date) AS next_salary
FROM employees;

RANK, ROW_NUMBER, LAG, LEAD, SUM OVER 등 모두 Oracle과 문법이 거의 동일합니다. 이 부분은 Oracle 지식이 그대로 통합니다.

 

⑤ UPSERT - MERGE보다 간결한 ON CONFLICT

"있으면 UPDATE, 없으면 INSERT"를 처리하는 기능입니다. Oracle은 MERGE를 썼는데, PostgreSQL의 ON CONFLICT가 더 간결합니다.

-- ON CONFLICT (PostgreSQL의 UPSERT)
INSERT INTO products (id, name, tags) 
VALUES (1, '노트북', ARRAY['전자'])
ON CONFLICT (id) 
DO UPDATE SET 
    name = EXCLUDED.name,
    tags = EXCLUDED.tags;

-- 충돌 시 무시하기
INSERT INTO products (id, name) VALUES (1, '노트북')
ON CONFLICT (id) DO NOTHING;

EXCLUDED는 "INSERT하려던 값"을 가리킵니다. 간결하죠.

MERGE도 지원 (PostgreSQL 15+)

복잡한 조건이면 MERGE도 씁니다. PostgreSQL 15에서 도입되고, 17에서 RETURNING과 MERGE_ACTION()이 추가되었습니다.

MERGE INTO ledger l
USING sales s ON l.item_id = s.item_id
WHEN MATCHED THEN
    UPDATE SET amount = l.amount + s.amount
WHEN NOT MATCHED THEN
    INSERT (item_id, amount) VALUES (s.item_id, s.amount)
RETURNING merge_action(), l.*;   -- PG17: 어떤 작업했는지 반환

배 아픈 포인트: 간단한 UPSERT는 ON CONFLICT로 한 줄, 복잡하면 MERGE로. Oracle은 항상 MERGE의 장황한 문법을 써야 했죠.

 

⑥ 범위 타입 - 기간·구간을 타입으로

Oracle엔 없는 개념입니다. 범위(range)를 하나의 값으로 다룹니다.

CREATE TABLE reservations (
    room_id INT,
    during TSRANGE   -- 시간 범위 타입
);

INSERT INTO reservations VALUES 
(101, '[2026-07-01 14:00, 2026-07-01 16:00)');

-- 겹침 검사 (&&)
SELECT * FROM reservations 
WHERE during && '[2026-07-01 15:00, 2026-07-01 17:00)'::tsrange;

-- 특정 시각 포함 검사 (@>)
SELECT * FROM reservations 
WHERE during @> '2026-07-01 15:00'::timestamp;

범위 타입 종류:

int4range  : 정수 범위
numrange   : 숫자 범위
tsrange    : 타임스탬프 범위
daterange  : 날짜 범위

배타 제약으로 겹침 방지

-- 같은 방의 예약 시간이 겹치지 않게 강제!
ALTER TABLE reservations 
ADD CONSTRAINT no_overlap 
EXCLUDE USING GIST (room_id WITH =, during WITH &&);

배 아픈 포인트: 예약 시스템에서 "시간 겹침 방지"를 DB 레벨에서 강제할 수 있습니다. Oracle이라면 애플리케이션 로직이나 복잡한 트리거로 처리해야 했죠. 이건 정말 우아합니다.

 

★ 킬러 기능 한눈에 보기

기능 무엇 Oracle이라면

JSONB	문서 저장·검색	JSON 지원 있으나 성숙도 차이
배열	다중 값 컬럼	별도 테이블 정규화
재귀 CTE	계층 구조	CONNECT BY (전용 문법)
윈도우 함수	분석 함수	있음 (거의 동일)
ON CONFLICT	UPSERT	MERGE (장황)
범위 타입	구간을 타입으로	없음 (수동 처리)

 

★ AI로 킬러 기능 학습하기

이 기능들을 본인 상황에 적용하는 법을 Claude에게 물어보세요.

프롬프트:
"우리 시스템에 이런 데이터가 있어: [설명]
Oracle에서는 [현재 방식]으로 처리했는데,
PostgreSQL 17의 JSONB/배열/범위타입 중 
뭘 쓰면 더 좋을지 추천하고 예제를 만들어줘."

특히 "Oracle에서 여러 테이블로 정규화했던 것을 PostgreSQL 기능으로 단순화할 수 있는지" 물어보면 좋은 아이디어를 얻습니다.

⚠️ 주의: 이 기능들은 강력하지만 남용은 금물입니다. 특히 JSONB와 배열은 정규화가 필요한 데이터에까지 쓰면 나중에 관리가 어려워집니다. "이 데이터가 정말 비정형인가?"를 먼저 판단하세요.

 

마무리

PostgreSQL 킬러 기능들을 정리하면:

1. JSONB — MongoDB 없이 문서 저장 (JSON_TABLE로 더 강력)
2. 배열 — 단순 다중 값은 정규화 없이
3. 재귀 CTE — CONNECT BY의 표준 대안
4. 윈도우 함수 — Oracle 지식 그대로 활용
5. ON CONFLICT — MERGE보다 간결한 UPSERT
6. 범위 타입 — 기간·구간을 우아하게

Oracle DBA로서 이 기능들을 보면 살짝 배가 아프지만, 반대로 생각하면 이제 이걸 쓸 수 있다는 게 PostgreSQL로 넘어온 보상입니다. 특히 JSONB와 범위 타입은 한 번 맛보면 "이걸 왜 이제 알았지" 싶습니다.

다음 편은 실무 시리즈의 마지막, 권한과 스키마 관리입니다. Oracle의 권한 관리 경험이 PostgreSQL에서 어떻게 통하고 어떻게 다른지, "유저와 롤이 같다"는 개념 전환을 다룹니다.

여러분이 애용하는 PostgreSQL 킬러 기능이나, Oracle에서 복잡하던 걸 PostgreSQL로 단순화한 경험이 있다면 댓글로 공유해주세요.

 

공식 링크

• PostgreSQL JSON 함수 문서
• PostgreSQL 17 릴리스 노트

이 글은 PostgreSQL 17 기준입니다. JSON_TABLE 등 일부 기능은 PostgreSQL 17 이상에서만 지원됩니다.