1. 인덱스를 활용한 조회 속도 극대화

-- artist_id로 작품을 자주 검색하므로, artworks 테이블에 인덱스를 생성합니다.
CREATE INDEX idx_artworks_artist_id ON artworks (artist_id);

-- 커뮤니티 게시글을 제목으로 검색할 때 속도를 높입니다.
-- (B-tree가 아닌 GIN/GiST 인덱스를 Full-Text Search에 사용할 수 있지만, 기본 B-tree를 먼저 사용합니다.)
CREATE INDEX idx_posts_title ON posts (title);

---

2. PostGIS를 이용한 지도 검색 기능 구현

단계 1: PostGIS 확장 활성화

CREATE EXTENSION postgis;

단계 2: 지리 타입 컬럼 추가 및 인덱스 생성

ALTER TABLE artworks ADD COLUMN geom GEOMETRY(Point, 4326);

UPDATE artworks 
SET geom = ST_SetSRID(ST_MakePoint(location_lon, location_lat), 4326);

CREATE INDEX idx_artworks_geom ON artworks USING GIST (geom);

단계 3: 반경 검색 쿼리 실행

SELECT title,
       ST_Distance(geom, ST_SetSRID(ST_MakePoint(126.978, 37.566), 4326)) AS distance_m
FROM artworks
WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(126.978, 37.566), 4326), 5000)
ORDER BY distance_m;

---

3. 트리거를 이용한 데이터 동기화

ALTER TABLE users ADD COLUMN comment_count INT DEFAULT 0;

CREATE OR REPLACE FUNCTION increase_comment_count()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    UPDATE users SET comment_count = comment_count + 1
    WHERE id = NEW.user_id; 
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_increase_comment_count
AFTER INSERT ON comments
FOR EACH ROW
EXECUTE FUNCTION increase_comment_count();

---

📌 프로시저와 함수 사용법

PostgreSQL은 반복적이거나 복잡한 작업을 데이터베이스 내부에 저장하고 필요할 때 호출하는 함수(Function)와 프로시저(Procedure)를 제공합니다.

---

함수 (Function)

1. 함수 생성 (예시: 게시글 수 세기)

CREATE OR REPLACE FUNCTION get_post_count_by_author(
    p_author_id INT
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    post_count INT;
BEGIN
    SELECT COUNT(*) INTO post_count
    FROM posts
    WHERE author_id = p_author_id;

    RETURN post_count;
END;
$$;

2. 함수 실행

SELECT get_post_count_by_author(5);

---

프로시저 (Procedure)

프로시저는 트랜잭션을 제어하거나, 데이터를 반환하지 않는 복잡한 작업을 수행할 때 사용됩니다. (PostgreSQL 11부터 정식 지원)

1. 프로시저 생성 (예시: 작성자 게시글 일괄 삭제)

CREATE OR REPLACE PROCEDURE delete_author_posts(
    p_author_id INT
)
LANGUAGE plpgsql
AS $$
BEGIN
    DELETE FROM posts WHERE author_id = p_author_id;
    -- COMMIT; -- 필요 시 명시적으로 작성
END;
$$;

2. 프로시저 실행

CALL delete_author_posts(5);

---

고급 CRUD: 트랜잭션 (Transaction)

BEGIN;

INSERT INTO posts (title, content, author_id) VALUES ('새 글', '내용', 1);

UPDATE users SET points = points + 10 WHERE id = 1;

COMMIT;

-- 오류 발생 시
-- ROLLBACK;

---

🚀 필수적인 SQL 기능 및 최적화

A. 지리 정보 시스템 (GIS)

• PostGIS 설치 및 활용으로 위치 기반 검색 최적화
• geometry / geography 타입 활용 → 효율적인 거리 계산 및 지도 쿼리

SELECT title
FROM artworks
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_MakePoint(126.978, 37.566), 4326),
    5000
);

---

B. 인덱스 (Indexing)

• FK: artist_id, user_id → 반드시 인덱스 생성
• WHERE 조건 자주 쓰이는 컬럼 (posts.category, users.email) → 인덱스 생성

---

C. 함수와 프로시저를 이용한 비즈니스 로직

• 사용자 랭킹 자동 업데이트 함수 (update_user_rank()) 작성 가능
• 트리거와 조합해 데이터 무결성 유지 및 자동화

CREATE OR REPLACE FUNCTION update_user_rank()
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
    UPDATE users
    SET rank = CASE
        WHEN post_count > 100 THEN 'Gold'
        WHEN post_count > 50 THEN 'Silver'
        ELSE 'Bronze'
    END;
END;
$$;

---

✅ 결론

• PostgreSQL은 단순 CRUD를 넘어서 인덱스, GIS, 함수/프로시저, 트리거 등 강력한 기능을 제공
• 실무에서는 속도 최적화 + 데이터 무결성 + 공간 데이터 처리가 핵심
• 점진적으로 확장 기능(PostGIS, 파티셔닝, JSONB 등)을 익히면 훨씬 더 강력한 DB 아키텍처 설계 가능