PostgreSQL

김기훈·2025년 10월 23일

DataBase

목록 보기

2/6

PostgreSQL

가장 널리 사용되는 오픈소스 관계형 데이터베이스 관리 시스템(RDBMS) 중 하나
- MySQL보다 더 정교하고 표준 SQL에 충실하며, 확장성이 뛰어난 DB

-- 데이터베이스 생성
CREATE DATABASE mydb;

-- 테이블 생성
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 데이터 삽입
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- 조회
SELECT * FROM users;

-- 수정
UPDATE users SET name = 'Bob' WHERE id = 1;

-- 삭제
DELETE FROM users WHERE id = 1;

PostgreSQL 전용 문법

CTE (Common Table Expression)

쿼리 안의 임시 테이블
- 복잡한 서브쿼리를 깔끔하게 분리해서 재사용하거나, 재귀 쿼리를 작성할 수 있음
  - WITH 절로 선언

WITH cte_name AS (
    SELECT * FROM users WHERE age >= 20
)
SELECT name, email FROM cte_name WHERE email LIKE '%gmail.com';

내부 쿼리 결과를 cte_name이라는 이름으로 저장하고, 아래 메인 쿼리에서 재사용

재귀 CTE 예시

조직도나 트리 구조를 탐색할 때 사용 가능:

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 1  -- CEO부터 시작
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

JSON / JSONB

JSON: 입력된 JSON 문자열 그대로 저장 (공백, 순서 유지)
JSONB: 바이너리 형태로 저장, 검색/인덱싱 가능

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

INSERT INTO users (data) VALUES
('{"name": "Alice", "age": 25, "skills": ["Python", "SQL"]}'),
('{"name": "Bob", "age": 30, "skills": ["Go", "Rust"]}');

-- 조회
SELECT
  data->>'name' AS name,
  data->>'age' AS age
FROM users
WHERE data->>'age' = '25';

-- 내부조건 조회
SELECT * FROM users
WHERE data->'skills' ? 'Python';

-- 인덱싱
CREATE INDEX idx_users_data ON users USING gin (data);

-> : JSON 객체 반환 / ->> : 텍스트 값 반환

Window Function (윈도우 함수)

윈도우 함수는 “집계함수(AVG, SUM, RANK 등)” 를 쓰지만
- GROUP BY 없이도 개별 행별로 계산할 수 있게 해줌

SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

OVER : 윈도우 정의 / PARTITION BY : 그룹 나누기 / ORDER BY : 그룹 내 정렬

name	department	salary	dept_rank
Alice	IT	8000	1
Bob	IT	6000	2
Carol	HR	7000	1
Dave	HR	6500	2

트랜잭션 & MVCC (동시성 제어)

트랜잭션 기본

ACID를 완벽하게 지킴
- Atomicity: 모두 성공 or 모두 실패
- Consistency: 일관된 상태 유지
- Isolation: 동시에 작업해도 간섭 없음
- Durability: 커밋된 데이터는 절대 유실되지 않음

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;  -- 혹은 ROLLBACK;

MVCC (Multi-Version Concurrency Control)

“읽기 작업(SELECT)이 쓰기(UPDATE/DELETE)를 블로킹하지 않는다”
- 즉, 동시에 여러 유저가 접근해도 락(Lock) 없이 읽기가 가능
내부 원리
- 각 행(Row)에는 xmin과 xmax라는 숨은 버전 정보가 있음.
- 새 트랜잭션이 UPDATE하면 기존 데이터를 덮어쓰지 않고 새 버전(Row Version) 을 만듦.
- 다른 트랜잭션은 자신이 시작할 때 기준으로 유효한 버전만 조회함.

인덱스 최적화

CREATE INDEX idx_users_name ON users(name);

인덱스 타입	설명	예시
BTREE	기본 인덱스 (범위 검색 가능)	`CREATE INDEX ... USING btree`
HASH	동등 비교용 (정확히 일치할 때)	`USING hash`
GIN	JSONB, 배열 검색용	`USING gin`
GiST	공간, 유사도 검색	`USING gist`
BRIN	대용량, 순차적 데이터에 효율	`USING brin`

FastAPI + PostgreSQL을 연결

예시 구조

fastapi_postgres_project/
├── app/
│   ├── main.py
│   ├── models.py
│   ├── database.py
│   └── routers/
│       └── users.py
├── .env
├── pyproject.toml / requirements.txt
└── Dockerfile (선택)

FastAPI에서 DB 연결 준비

FastAPI는 SQLAlchemy + psycopg2 조합으로 PostgreSQL을 다루는 게 일반적
- (Pydantic 모델 ↔ SQLAlchemy 모델 ↔ DB 구조)
설치
- poetry add fastapi uvicorn sqlalchemy psycopg2-binary python-dotenv
- pip install fastapi uvicorn sqlalchemy psycopg2-binary python-dotenv

기본

Tortoise-ORM

fastapi_tortoise_project/
├── app/
│   ├── main.py
│   ├── models.py
│   ├── schemas.py
│   ├── routers/
│   │   └── users.py
│   └── core/
│       └── config.py
├── .env
└── pyproject.toml / requirements.txt

패키지 설치

poetry add fastapi uvicorn tortoise-orm aerich python-dotenv

.env 파일

DB_URL=postgres://admin:1234@localhost:5432/fastapi_db

설정 파일 (core/config.py)

# app/core/config.py
from dotenv import load_dotenv
import os

load_dotenv()

DB_URL = os.getenv("DB_URL")

모델 정의 (models.py)

# app/models.py
from tortoise import fields
from tortoise.models import Model

class User(Model):
    id = fields.IntField(pk=True)
    name = fields.CharField(max_length=50)
    email = fields.CharField(max_length=100, unique=True)
    created_at = fields.DatetimeField(auto_now_add=True)

    def __str__(self):
        return self.name

스키마 정의 (schemas.py)

# app/schemas.py
from pydantic import BaseModel

class UserIn(BaseModel):
    name: str
    email: str

class UserOut(BaseModel):
    id: int
    name: str
    email: str

    class Config:
        orm_mode = True

FastAPI + Tortoise 연동 (main.py)

# app/main.py
from fastapi import FastAPI
from tortoise.contrib.fastapi import register_tortoise
from .core.config import DB_URL
from .routers import users

app = FastAPI(title="FastAPI + PostgreSQL + Tortoise ORM")

# 라우터 등록
app.include_router(users.router)

# Tortoise ORM 초기화
register_tortoise(
    app,
    db_url=DB_URL,
    modules={"models": ["app.models"]},
    generate_schemas=True,   # 앱 시작 시 테이블 자동 생성
    add_exception_handlers=True,
)

@app.get("/")
async def root():
    return {"message": "Tortoise ORM 연결 성공!"}

라우터 예시 (routers/users.py)

# app/routers/users.py
from fastapi import APIRouter, HTTPException
from app import models, schemas

router = APIRouter(prefix="/users", tags=["users"])

@router.post("/", response_model=schemas.UserOut)
async def create_user(user: schemas.UserIn):
    existing = await models.User.filter(email=user.email).first()
    if existing:
        raise HTTPException(status_code=400, detail="이미 존재하는 이메일입니다.")
    new_user = await models.User.create(**user.dict())
    return new_user

@router.get("/", response_model=list[schemas.UserOut])
async def get_users():
    return await models.User.all()

마이그레이션 관리 (Aerich)

Tortoise ORM은 aerich로 DB 스키마 버전 관리도 가능 (나중에 모델 수정 → 마이그레이션)

초기 설정: aerich init -t app.main.TORTOISE_ORM

마이그레이션 파일 생성: aerich init-db

모델 변경 하려면: aerich migrate / aerich upgrade

docker을 이용하여 설치 및 실행

PostgreSQL 설치
- docker run --name postgres-container -e POSTGRES_USER=admin -e POSTGRES_PASSWORD=1234 -e POSTGRES_DB=mydb -p 5432:5432 -d postgres:16

옵션	의미
`--name postgres-container`	컨테이너 이름
`-e POSTGRES_USER=admin`	DB 사용자 이름
`-e POSTGRES_PASSWORD=1234`	비밀번호
`-e POSTGRES_DB=mydb`	생성할 기본 DB 이름
`-p 5432:5432`	포트 매핑 (로컬:도커)
`-d postgres:16`	백그라운드 실행, PostgreSQL 16버전 사용

docker 내부로 들어가서 psql 실행
- docker exec -it postgres-container psql -U admin -d mydb
- 접속되면: mydb=#
- 여기서 SQL 명령어 입력 가능: \dt = 테이블 목록 보기 \q = 종료

psql

PostgreSQL에 접근하기 위해 CLI 도구(psql) 가 로컬에 설치 필요
- ocker 안에서 psql 실행하기
- docker exec -it postgres psql -U postgres
  - postgres는 컨테이너 이름
  - 명령 성공하면 postgres=# 이거 나옴

명령어

명령어	기능
`\l`	DB 목록 보기
`\c DB명`	DB 접속
`\dt`	테이블 목록
`\d 테이블명`	테이블 구조 보기
`\q`	psql 종료
`\t`	출력 포맷 토글 (on/off)

김기훈

안녕하세요.

이전 포스트

DB - SQL

다음 포스트

PostgreSQL

DataBase

PostgreSQL

PostgreSQL 전용 문법

CTE (Common Table Expression)

재귀 CTE 예시

JSON / JSONB

Window Function (윈도우 함수)

트랜잭션 & MVCC (동시성 제어)

트랜잭션 기본

MVCC (Multi-Version Concurrency Control)

인덱스 최적화

FastAPI + PostgreSQL을 연결

예시 구조

FastAPI에서 DB 연결 준비

기본

Tortoise-ORM

패키지 설치

.env 파일

설정 파일 (core/config.py)

모델 정의 (models.py)

스키마 정의 (schemas.py)

FastAPI + Tortoise 연동 (main.py)

라우터 예시 (routers/users.py)

마이그레이션 관리 (Aerich)

docker을 이용하여 설치 및 실행

psql

명령어

DB - SQL

Tortoise-ORM

0개의 댓글