Skip to main content
Tilnote
T
tilnote
책에 포함된 노트PostgreSQL 입문부터 실전 운영까지

애플리케이션과 PostgreSQL 연결하기

Views 16약 8분생성형 AI 활용
Summary

콘솔에서 맞던 SQL도 코드에서는 다르게 실패한다

psql에서 직접 실행한 SQL이 빠르고 정확해도, 애플리케이션에 들어가면 문제가 생길 수 있다. 요청마다 새 연결을 만들고 닫으면 연결 비용이 커진다. 사용자가 입력한 값을 문자열로 붙이면 SQL 인젝션이 생긴다. ORM이 자동으로 만든 쿼리가 테이블 전체를 읽거나, 반복문 안에서 수백 번의 SELECT를 실행할 수도 있다.

그래서 애플리케이션에서 PostgreSQL을 잘 쓴다는 말은 세 가지를 함께 지킨다는 뜻이다.

  1. 연결을 재사용하고, 데이터베이스가 감당할 수 있는 수만 유지한다.

  2. SQL은 파라미터 바인딩으로 실행한다.

  3. 트랜잭션, 조회 모델, 배치 작업의 경계를 업무 단위에 맞춘다.

이 장에서는 Python, Node.js, Java를 예로 들지만 핵심은 언어가 아니라 패턴이다.

애플리케이션과 PostgreSQL 연결하기AI 생성 이미지

연결 문자열과 커넥션 풀

애플리케이션은 보통 연결 문자열로 PostgreSQL에 접속한다.

postgresql://app_user:[email protected]:5432/shop?sslmode=require

여기에는 사용자, 비밀번호, 호스트, 포트, 데이터베이스 이름, SSL 같은 접속 옵션이 들어간다. 운영 환경에서는 이 값을 코드에 직접 쓰지 않는다. 환경 변수, 시크릿 매니저, 컨테이너 오케스트레이션의 비밀 값으로 주입한다. 로그에도 전체 연결 문자열이 찍히지 않게 해야 한다. 비밀번호가 포함되어 있기 때문이다.

PostgreSQL 연결은 가벼운 문자열 핸들이 아니다. 서버 쪽 자원을 사용한다. 웹 요청마다 새 연결을 만들면 짧은 요청이 많을수록 서버가 연결 생성과 해제에 시간을 쓴다. 그래서 백엔드 애플리케이션은 커넥션 풀을 둔다. 풀은 미리 만든 연결을 빌려 주고, 작업이 끝나면 닫지 않고 다시 보관한다.

Python에서는 다음처럼 풀에서 연결을 빌려 쓸 수 있다.

from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    conninfo='postgresql://app_user:secret@db:5432/shop?sslmode=require',
    min_size=2,
    max_size=10,
)

with pool.connection() as conn:
    row = conn.execute(
        'select id, email from customers where email = %s',
        ('[email protected]',)
    ).fetchone()

Node.js의 pg도 비슷하다.

const { Pool } = require('pg')

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
})

const { rows } = await pool.query(
  'select id, email from customers where email = $1',
  [email],
)

Java에서는 HikariCP 같은 풀을 자주 사용한다.

spring.datasource.url=jdbc:postgresql://db:5432/shop
spring.datasource.username=app_user
spring.datasource.password=secret
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=5000
spring.datasource.hikari.idle-timeout=300000

풀 크기는 크게 잡는다고 항상 좋아지지 않는다. 예를 들어 애플리케이션 서버가 8대이고 각 서버의 풀이 20개라면 최대 160개의 데이터베이스 연결이 생긴다. 배치 서버, 관리자 도구, 모니터링 연결까지 더하면 max_connections를 쉽게 소진한다. 먼저 전체 연결 예산을 정하고, 인스턴스 수와 풀 크기를 곱해 계산해야 한다.

트래픽이 많고 짧은 요청이 많은 서비스에서는 PgBouncer 같은 외부 풀러도 사용한다. 특히 transaction pooling은 트랜잭션이 끝날 때 서버 연결을 반환하므로 많은 클라이언트 연결을 적은 PostgreSQL 연결로 처리할 수 있다. 다만 세션 상태가 유지된다는 가정은 깨질 수 있다. 세션 단위 SET, 장시간 유지되는 임시 테이블, LISTEN/NOTIFY, 서버 측 prepared statement 같은 기능은 transaction pooling과 충돌할 수 있으므로 직접 연결이나 session pooling을 검토해야 한다.1

SQL 인젝션은 문자열 결합에서 시작된다

사용자 입력을 SQL 문자열에 붙이는 방식은 피해야 한다.

# 위험한 방식
sql = "select id, email from customers where email = '" + email + "'"
cur.execute(sql)

사용자가 a@example.com을 입력한다고 가정하면 문제가 없어 보인다. 하지만 입력값에 SQL 조각이 섞이면 조건이 변조될 수 있다. 공격 여부와 별개로 작은따옴표, 줄바꿈, 인코딩 문제도 직접 처리해야 한다.

올바른 방식은 파라미터 바인딩이다. SQL 문장과 값을 분리해서 드라이버에 넘긴다.

cur.execute(
    'select id, email from customers where email = %s',
    (email,)
)

Node.js에서는 위치 파라미터를 쓴다.

await pool.query(
  'select id, email from customers where email = $1',
  [email],
)

JDBC에서는 PreparedStatement의 ? 자리에 값을 바인딩한다.

PreparedStatement ps = conn.prepareStatement(
    "select id, email from customers where email = ?"
);
ps.setString(1, email);

파라미터 바인딩은 값에 적용된다. 테이블 이름, 열 이름, 정렬 방향 같은 SQL 식별자는 값으로 바인딩할 수 없다. 검색 화면에서 사용자가 정렬 기준을 고르게 할 때는 입력값을 그대로 ORDER BY에 붙이지 말고 허용 목록을 둔다.

name      -> order by name asc
createdAt -> order by created_at desc
price     -> order by price asc

허용 목록에 없는 값은 기본 정렬로 돌리거나 요청을 거절한다. 이 원칙은 ORM을 쓰더라도 같다. ORM이 모든 문자열 조합을 안전하게 만들어 주지는 않는다.

ORM은 SQL을 없애지 않는다

ORM은 객체와 테이블을 연결해 반복 코드를 줄인다. 하지만 ORM을 쓰면 SQL을 몰라도 된다는 뜻은 아니다. 오히려 ORM이 만든 SQL을 읽을 수 있어야 장애를 줄인다.

가장 흔한 문제는 N+1 쿼리다. 주문 목록 50개를 조회한 뒤 반복문 안에서 주문마다 고객을 한 번씩 조회하면 전체 쿼리는 51번 실행된다.

1번: select * from orders limit 50
50번: select * from customers where id = ?

해결은 조인, eager loading, batch loading 중 하나다. 화면에 주문과 고객 이메일이 함께 필요하다면 처음부터 필요한 열만 조인해서 가져오는 편이 명확하다.

select
    o.id,
    o.ordered_at,
    o.total_amount,
    c.email
from orders o
join customers c on c.id = o.customer_id
where o.status = 'paid'
order by o.ordered_at desc
limit 50;

두 번째 문제는 불필요한 전체 조회다. ORM의 findAll이나 컬렉션 접근이 실제로는 select * from large_table이 될 수 있다. API 응답에 필요한 열이 5개라면 5개만 조회한다. 특히 JSONB, 긴 본문, 이미지 메타데이터처럼 큰 열은 목록 화면에서 빼는 것이 좋다.

세 번째 문제는 마이그레이션 충돌이다. 여러 개발자가 동시에 열을 추가하고, 운영 DB에는 수동 변경이 섞이면 마이그레이션 순서가 꼬인다. 원칙은 단순하다. 운영 스키마 변경은 마이그레이션 파일을 단일 경로로 적용한다. 생성된 SQL을 리뷰한다. 큰 테이블에 열을 추가하면서 기본값과 NOT NULL 제약을 함께 넣거나, 인덱스를 만드는 작업은 잠금과 실행 시간을 미리 확인한다. PostgreSQL 버전과 명령 형태에 따라 실제 영향은 달라지지만, 운영 테이블에서는 짧은 잠금도 장애로 이어질 수 있다. 운영에서는 가능하면 CREATE INDEX CONCURRENTLY처럼 서비스 영향을 줄이는 방식을 검토한다. 다만 이 명령은 일반 CREATE INDEX보다 오래 걸릴 수 있고, 명시적인 트랜잭션 블록 안에서는 실행할 수 없다는 점도 함께 기억해야 한다.

트랜잭션 경계는 서비스 로직 경계다

트랜잭션은 코드의 함수 하나가 아니라 업무의 원자적 단위에 맞춘다. 주문 생성이라면 보통 주문 생성, 주문 상세 입력, 재고 차감, 결제 대기 상태 기록이 한 트랜잭션에 들어간다. 중간에 실패하면 모두 되돌아가야 하기 때문이다.

반대로 외부 결제 API 호출, 이메일 발송, 대용량 파일 처리처럼 오래 걸리거나 되돌리기 어려운 작업을 데이터베이스 트랜잭션 안에 오래 넣으면 안 된다. 트랜잭션이 길어질수록 잠금, MVCC 정리 지연, 충돌 가능성이 커진다.

Python의 psycopg 계열은 기본적으로 명령 실행 시 암시적 트랜잭션을 시작한다. 단순 SELECT도 트랜잭션을 열 수 있고, 오류가 난 뒤에는 rollback()을 호출하기 전까지 같은 연결에서 다음 명령이 거부될 수 있다.2 따라서 연결을 풀에 돌려주기 전에 반드시 커밋 또는 롤백 상태가 정리되어야 한다.

with pool.connection() as conn:
    try:
        with conn.transaction():
            cur = conn.execute(
                '''
                update products
                set stock = stock - %s
                where id = %s and stock >= %s
                returning id
                ''',
                (qty, product_id, qty)
            )
            if cur.fetchone() is None:
                raise ValueError('insufficient stock')

            conn.execute(
                'insert into orders(customer_id, status) values (%s, %s)',
                (customer_id, 'pending')
            )
    except Exception:
        # transaction 블록이 롤백한다.
        raise

여기서 중요한 부분은 재고 차감 UPDATE가 실제로 성공했는지 확인하는 것이다. 조건을 만족하는 행이 없는데도 다음 INSERT를 실행하면, 재고는 부족한데 주문은 생성되는 모순이 생길 수 있다.

재시도도 구분해야 한다. PostgreSQL은 REPEATABLE READ나 SERIALIZABLE 격리 수준에서 직렬화 실패가 발생할 수 있으며, 이때 SQLSTATE 40001은 전체 트랜잭션 재시도 대상이다. 교착 상태인 40P01도 재시도할 수 있는 경우가 많다. 반면 잘못된 외래 키, 필수 값 누락, 형 변환 실패는 다시 실행해도 성공하지 않는다. 사용자에게 오류를 반환하거나 코드를 고쳐야 한다.

재시도 가능 후보:
- 40001 serialization_failure
- 40P01 deadlock_detected

상황을 보고 판단:
- 23505 unique_violation

즉시 중단할 가능성이 큰 오류:
- 23502 not_null_violation
- 23503 foreign_key_violation
- 22P02 invalid_text_representation

중요한 점은 실패한 SQL 한 줄만 다시 실행하는 것이 아니라, 의사결정 로직을 포함한 전체 트랜잭션을 다시 실행해야 한다는 것이다. PostgreSQL 공식 문서도 직렬화 실패 처리에서 전체 트랜잭션 재시도를 요구한다.3

조회 모델과 페이지네이션

API는 화면이 필요한 모양으로 데이터를 반환한다. 하지만 화면 모델을 그대로 정규화 테이블에 강요할 필요는 없다. 쓰기 모델은 정규화된 테이블로 유지하고, 읽기 모델은 조인 쿼리, 뷰, materialized view, 요약 테이블로 별도 구성할 수 있다.

주문 목록 API라면 모든 주문 상세를 한 번에 내려주기보다 목록에 필요한 값만 조회한다.

select
    o.id,
    o.ordered_at,
    o.status,
    o.total_amount,
    c.email
from orders o
join customers c on c.id = o.customer_id
where o.ordered_at >= $1
order by o.ordered_at desc, o.id desc
limit 50;

페이지네이션은 처음에는 limit과 offset이 간단하다.

select id, title, created_at
from posts
order by created_at desc
limit 20 offset 10000;

하지만 뒤쪽 페이지로 갈수록 PostgreSQL은 앞의 10000개를 건너뛰기 위해 더 많은 행을 읽어야 한다. 무한 스크롤이나 최신순 목록에는 keyset pagination이 더 적합하다.

select id, title, created_at
from posts
where (created_at, id) < ($1, $2)
order by created_at desc, id desc
limit 20;

이 방식은 마지막으로 본 (created_at, id)를 다음 요청의 커서로 사용한다. 같은 시각에 생성된 글이 있을 수 있으므로 id를 함께 정렬 기준에 넣어 순서를 안정적으로 만든다. 이 쿼리에는 (created_at desc, id desc) 방향에 맞는 인덱스를 검토한다.

배치와 대량 삽입은 요청 처리와 다르게 설계한다

관리자 업로드, 로그 적재, 정산 계산은 일반 API 요청과 성격이 다르다. 한 건씩 INSERT하면 네트워크 왕복과 WAL 기록 비용이 커진다. 대량 입력은 가능한 한 묶어서 처리한다.

선택지는 세 가지다.

  1. 수백~수천 건 단위의 multi-row INSERT

  2. 드라이버의 batch 실행

  3. 가장 큰 적재 작업에는 COPY

대량 적재 후에는 바로 운영 테이블에 넣기보다 staging 테이블에 먼저 넣고 검증한 뒤 병합하는 방식이 안전하다.

create temporary table import_order_items (
    order_no text,
    sku text,
    quantity integer
);

-- COPY로 적재한 뒤 검증
select sku, count(*)
from import_order_items
group by sku
having count(*) > 1;

그 다음 상품 코드 존재 여부, 수량 범위, 중복 주문 번호를 확인하고 본 테이블에 INSERT한다. 이렇게 하면 오류가 난 행과 정상 행을 구분하기 쉽고, 운영 테이블의 제약 조건 오류를 사용자에게 더 친절하게 설명할 수 있다.

배치 작업은 트랜잭션 크기도 조절해야 한다. 너무 큰 트랜잭션은 오래 잠금을 잡고 WAL을 크게 만들며, 실패 시 되돌리는 비용도 커진다. 반대로 너무 작은 단위는 커밋 비용이 많아진다. 정산처럼 전체 일관성이 중요한 작업은 작업 단위를 명확히 나누고, 각 단위의 시작·성공·실패 상태를 별도 테이블에 기록한다.

애플리케이션 코드에서 확인할 기준

PostgreSQL을 애플리케이션에 연결한 뒤에는 다음 기준으로 점검한다.

  • 요청마다 새 연결을 만들지 않고 풀을 사용하는가?

  • 전체 애플리케이션 인스턴스 수를 곱해도 데이터베이스 연결 예산 안에 들어오는가?

  • 모든 사용자 입력이 파라미터로 바인딩되는가?

  • 정렬 열, 테이블 이름처럼 바인딩할 수 없는 값은 허용 목록으로 제한하는가?

  • ORM이 만든 SQL을 로그나 pg_stat_statements에서 확인하는가?

  • N+1 쿼리와 불필요한 select *가 없는가?

  • 트랜잭션 안에서 외부 API 호출이나 긴 계산을 하지 않는가?

  • 40001, 40P01 같은 동시성 오류는 전체 트랜잭션 단위로 제한 횟수 안에서 재시도하는가?

  • 페이지네이션, 배치, 대량 삽입에 맞는 인덱스와 적재 방식을 선택했는가?

데이터베이스 실력은 SQL 콘솔에서 끝나지 않는다. 실제 서비스에서는 연결, 트랜잭션, 오류 처리, 쿼리 생성 방식이 모두 데이터 품질과 성능에 영향을 준다. 애플리케이션 코드는 PostgreSQL의 사용자가 아니라 PostgreSQL 운영 환경의 일부다.

참고

1Connection pooling strategy for Azure Database for PostgreSQL using PgBouncer

2Transactions management - psycopg 3 documentation

3PostgreSQL Documentation: Serialization Failure Handling

책 목록

PostgreSQL 입문부터 실전 운영까지

전체 목차 보기

이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.

틸노트는 사용자가 AI를 활용해 노트를 쉽게 작성하거나 편집할 수 있는 서비스입니다.

회원 가입

AI 스퀘어 단톡방에 함께해요

AI 활용과 틸노트 사용 팁을 나누는 오픈채팅방입니다. 입장 비밀번호는 tilnote입니다.

참여하기
이용약관·개인정보처리방침