PostgreSQL에서 그래프 데이터베이스로 pgRouting 활용하기

 

🔎 PostgreSQL에서 pgRouting을 그래프 데이터베이스로 활용하는 방법

PostgreSQL은 강력한 관계형 데이터베이스이지만, 일부 확장을 활용하면 그래프 데이터베이스로도 활용할 수 있습니다. 그중 하나가 바로 pgRouting입니다.

pgRouting은 일반적으로 최단 경로 탐색에 사용되지만, 사실상 기본적인 그래프 기능을 제공하여 다양한 응용이 가능합니다. 특히 전용 그래프 데이터베이스(Neo4j, Apache AGE) 없이도 간단한 그래프 구조를 구현하고 활용할 수 있습니다.

이 블로그에서는 pgRouting의 개념과 활용법을 살펴보고, GIS(지리정보 시스템) 외에도 태스크 스케줄링, 서버 리소스 할당, 추천 시스템 등 다양한 비(非)GIS 활용 사례를 알아보겠습니다.

반응형

---

📌 pgRouting이란?

pgRouting은 PostGIS 확장 기능 중 하나로, 그래프 기반 네트워크 분석을 수행할 수 있는 PostgreSQL 확장입니다.

✔ 주요 기능:

• 최단 경로 탐색 (Dijkstra, A* 알고리즘)
• 네트워크 분석 (경로 최적화, 이동 비용 계산)
• 그래프 데이터 처리 (노드/엣지 구조)

✔ 활용 분야:

• GIS: 지도 기반 네비게이션, 물류 최적화
• 태스크 스케줄링: 작업 간 종속 관계를 고려한 일정 최적화
• 리소스 할당: 네트워크 내 최적의 경로 찾기
• 추천 시스템: 사용자 행동 기반 콘텐츠 추천

---

1️⃣ pgRouting을 이용한 그래프 데이터 모델링

✅ 그래프의 기본 개념

pgRouting은 데이터를 **그래프 구조(노드와 엣지)**로 모델링하여 사용합니다.

• 노드(Node): 개별 엔터티(장소, 작업, 서버, 사용자 등)
• 엣지(Edge): 노드 간의 연결 관계(도로, 종속성, 데이터 흐름 등)

예를 들어, 다음과 같은 그래프를 생각해볼 수 있습니다.

(A) ---3--- (B) ---2--- (C)  

여기서 A, B, C는 노드이며, 3과 2는 **엣지의 가중치(비용)**입니다.

---

2️⃣ 비(非) GIS 환경에서의 pgRouting 활용 사례

🛠 1. 태스크 스케줄링(Task Scheduling)

프로젝트에서 작업 간 종속 관계를 그래프로 표현할 수 있습니다.

🔹 예제 시나리오:
작업(Task) 간 선후 관계가 있으며, 특정 작업이 끝나야 다음 작업이 시작될 수 있음.

(Start) → (Task A) → (Task B) → (Task C) → (End)

🔹 pgRouting을 활용한 최적 일정 계산

-- 작업 테이블 생성
CREATE TABLE tasks (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

-- 작업 간 종속 관계(엣지) 테이블 생성
CREATE TABLE dependencies (
    id SERIAL PRIMARY KEY,
    source INTEGER NOT NULL,
    target INTEGER NOT NULL,
    duration INTEGER NOT NULL,
    CONSTRAINT fk_source FOREIGN KEY (source) REFERENCES tasks(id),
    CONSTRAINT fk_target FOREIGN KEY (target) REFERENCES tasks(id)
);

-- 예제 데이터 삽입
INSERT INTO dependencies (source, target, duration) VALUES
    (1, 2, 3),  -- Task A (3일)
    (2, 3, 4),  -- Task B (4일)
    (3, 4, 5),  -- Task C (5일)
    (4, 5, 2);  -- Task D (2일)

-- 최장 경로(전체 프로젝트 일정) 계산
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, duration AS cost FROM dependencies',
    1,  -- 시작 작업 ID
    5   -- 종료 작업 ID
);

📌 결과: 프로젝트의 총 소요 시간(최장 경로)이 도출됨 → 일정 최적화 가능

---

🖥 2. 서버 리소스 할당 & 네트워크 최적화

클라우드 서버 간의 데이터 전송 경로 최적화를 위해 pgRouting을 활용할 수 있습니다.

🔹 예제 시나리오:

• 여러 개의 서버가 있고, 각 서버 간 데이터 전송 비용(레이턴시)이 다름
• 최적의 경로를 찾아 데이터 이동 속도를 최적화해야 함

🔹 pgRouting을 활용한 네트워크 최적 경로 찾기

-- 서버 노드 테이블 생성
CREATE TABLE servers (
    id SERIAL PRIMARY KEY,
    name TEXT,
    x DOUBLE PRECISION,
    y DOUBLE PRECISION
);

-- 서버 간 연결 테이블 생성
CREATE TABLE server_latency (
    id SERIAL PRIMARY KEY,
    source INTEGER,
    target INTEGER,
    cost DOUBLE PRECISION,
    CONSTRAINT fk_source FOREIGN KEY (source) REFERENCES servers(id),
    CONSTRAINT fk_target FOREIGN KEY (target) REFERENCES servers(id)
);

-- 예제 데이터 삽입
INSERT INTO server_latency (source, target, cost) VALUES
    (1, 2, 1.5), -- Server A → Server B (1.5ms)
    (2, 3, 2.0), -- Server B → Server C (2.0ms)
    (2, 4, 1.8), -- Server B → Server D (1.8ms)
    (4, 3, 1.0); -- Server D → Server C (1.0ms)

-- A* 알고리즘을 활용한 최적 경로 탐색
SELECT * FROM pgr_astar(
    'SELECT id, source, target, cost FROM server_latency',
    1,  -- Server A (출발지)
    3   -- Server C (목적지)
);

📌 결과: 서버 간 **최적 경로(레이턴시 최소화 경로)**가 도출됨 → 리소스 효율성 극대화

---

📺 3. 추천 시스템(Recommendation System)

YouTube 같은 추천 시스템에서도 pgRouting을 활용할 수 있습니다.

🔹 예제 시나리오:

• 사용자들이 특정 영상을 시청하거나 좋아요를 누르면 관계(엣지)가 형성됨
• 이를 바탕으로 비슷한 영상을 추천

🔹 pgRouting을 활용한 영상 추천

-- 추천을 위한 영상 간 관계 데이터 삽입
INSERT INTO video_relationships (source_video_id, target_video_id, relationship_type, weight) VALUES
    (1, 2, 'same_category', 5), -- 같은 카테고리
    (2, 3, 'watched_by_same_users', 3), -- 같은 사용자가 시청
    (4, 5, 'same_category', 5);

-- 특정 사용자가 좋아한 영상과 관련된 추천 영상 찾기
SELECT * FROM pgr_dijkstra(
    'SELECT id, source_video_id AS source, target_video_id AS target, weight AS cost FROM video_relationships',
    1,  -- 사용자가 본 영상 ID
    3   -- 추천할 영상 ID
);

📌 결과: 사용자의 시청 패턴을 기반으로 최적의 추천 경로 도출

---

728x90

🔎 PostgreSQL에서 pgRouting을 그래프 DB처럼 활용하기

pgRouting은 단순한 최단 경로 탐색을 넘어 다양한 그래프 활용이 가능합니다.

• 태스크 스케줄링: 프로젝트 일정 최적화
• 네트워크 최적화: 서버 리소스 효율적 분배
• 추천 시스템: 사용자 행동 기반 추천

📌 결론: PostgreSQL만으로도 그래프 기반 분석이 가능하며, 전용 그래프 DB 없이도 효과적인 데이터 분석을 수행할 수 있습니다. 🚀

https://supabase.com/blog/pgrouting-postgres-graph-database

Postgres as a Graph Database: (Ab)using pgRouting