데이터베이스 기초 (7) - Using a Database from Application Code

데이터베이스 기초 시리즈의 7편입니다. 전체 목차는 0편에 있습니다.

드라이버

psql은 사람이 터미널에서 SQL을 입력할 때 쓰는 client이고, 애플리케이션은 psql을 거치지 않고 언어별 드라이버 라이브러리로 database server에 직접 접속합니다. 드라이버는 PostgreSQL의 통신 프로토콜을 구현한 라이브러리로, 접속을 열고 SQL을 보내고 결과를 그 언어의 자료형으로 돌려줍니다. Python의 표준적인 PostgreSQL 드라이버는 psycopg이고, 현재 메이저 버전은 psycopg 3입니다.

pip install "psycopg[binary]"

접속 정보는 코드에 적지 않고 환경 변수로 주입합니다. 이 원칙은 PostgreSQL 편에서 다뤘으므로 여기서는 반복하지 않습니다. 접속 문자열의 형식은 postgresql://계정:비밀번호@host:port/database입니다. 시리즈 실습 container 기준 값은 다음과 같습니다. 실습 스키마는 1편에서 만든 board database에 있으므로 마지막 경로가 board입니다.

export DATABASE_URL="postgresql://postgres:mysecret@localhost:5432/board"

SELECT 결과를 받는 최소 예제입니다.

import os
import psycopg

conn_str = os.environ["DATABASE_URL"]

with psycopg.connect(conn_str) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT id, title FROM posts ORDER BY id")
        for row in cur.fetchall():
            print(row)

cursor는 접속 위에서 SQL을 실행하고 결과를 읽어 오는 객체로, execute가 SQL 실행, fetchall이 결과 전체 수신입니다. 실행하면 2편에서 넣은 시드 데이터의 글 4건이 Python tuple로 출력됩니다.

(1, '첫 글입니다')
(2, 'PostgreSQL 질문 있습니다')
(3, '중고 키보드 팝니다')
(4, '이번 주 모임 공지')

with 블록이 예외 없이 끝나면 psycopg가 transaction을 commit하고 접속을 닫습니다. psycopg는 기본적으로 autocommit이 꺼져 있으므로, with 블록 없이 접속을 다루면서 INSERT나 UPDATE를 실행했다면 conn.commit()을 직접 호출해야 반영됩니다.

parameterized query와 SQL injection

사용자 입력을 문자열 연결로 SQL에 붙이면 입력에 SQL 문법이 섞여 들어옵니다. 이 절의 예제 두 개는 앞 절의 예제처럼 접속을 연 상태에서 cur로 실행한다고 가정합니다. username으로 사용자를 찾는 코드를 f-string으로 짠 경우입니다.

username = "' OR '1'='1"  # 사용자가 입력창에 이렇게 입력했다고 가정합니다
cur.execute(f"SELECT id, username FROM users WHERE username = '{username}'")
print(cur.fetchall())  # [(1, 'alice'), (2, 'bob'), (3, 'carol')]

server에 도착한 SQL은 다음과 같습니다.

SELECT id, username FROM users WHERE username = '' OR '1'='1';

입력에 들어 있던 따옴표가 문자열을 조기에 닫고, OR ‘1’=’1’ 조건이 WHERE를 항상 참으로 만들어 users의 세 row가 전부 반환됩니다. 같은 방식으로 입력에 UPDATE나 DELETE를 끼워 넣는 변형도 가능합니다. 이것이 SQL injection입니다.

해결은 문자열 연결을 버리고 자리 표시자를 쓰는 것입니다.

cur.execute("SELECT id, username FROM users WHERE username = %s", (username,))
print(cur.fetchall())  # []

%s는 Python의 문자열 포매팅이 아니라 드라이버의 자리 표시자입니다. 드라이버는 SQL 문장과 값을 분리해 server에 전달하고, server는 그 값을 SQL 문법으로 해석하지 않고 값으로만 취급합니다. 따옴표가 섞인 입력이 들어와도 그 문자열과 정확히 일치하는 username을 찾는 조건이 될 뿐이라 결과는 0건입니다. 사용자 입력이 SQL에 들어가는 모든 자리에서 예외 없이 자리 표시자를 씁니다.

connection pool 사용

요청마다 접속을 새로 만들면 비용이 큽니다. TCP 연결과 인증 절차가 매번 반복되고, PostgreSQL 편에서 다뤘듯 PostgreSQL은 접속마다 server 프로세스를 하나씩 만들기 때문입니다. connection pool은 접속을 미리 여러 개 만들어 두고, 요청이 오면 하나를 빌려주고 처리가 끝나면 돌려받는 구조입니다.

pip install "psycopg[binary,pool]"

from psycopg_pool import ConnectionPool

with ConnectionPool(conn_str, min_size=2, max_size=10) as pool:
    with pool.connection() as conn:
        with conn.cursor() as cur:
            cur.execute("SELECT count(*) FROM posts")
            print(cur.fetchone())  # (4,)

pool.connection()은 pool에서 접속 하나를 빌려 오고, with 블록이 끝나면 접속을 닫는 대신 pool에 반납합니다. 빌릴 접속이 없으면 max_size까지 새로 만들고, max_size에 도달해 있으면 다른 요청이 반납할 때까지 기다립니다.

pool 크기는 무작정 키우지 않습니다. PostgreSQL의 max_connections 기본값은 100이고, 접속 하나가 프로세스 하나이므로 접속이 늘수록 server의 메모리 사용도 늘어납니다. 웹 애플리케이션은 보통 같은 프로그램을 프로세스 여러 개(워커)로 띄워 요청을 나눠 처리하고, pool은 워커마다 하나씩 생깁니다. 애플리케이션 워커 프로세스가 8개이고 워커마다 max_size가 10이면 최대 80개 접속이 생기므로, 워커 수 곱하기 pool 크기가 max_connections보다 여유 있게 작도록 계산해서 정합니다.

ORM과 N+1 문제

ORM은 언어의 객체와 table의 row를 대응시키고 SQL을 대신 생성해 주는 계층입니다. Python에서는 SQLAlchemy가 대표적입니다. SQLAlchemy 설치와 모델 선언은 이 편의 범위 밖이라 생략하고, ORM이 SQL을 대신 만든다는 동작만 봅니다. posts table을 Post class로 선언해 두면 다음 한 줄이 SELECT 문을 생성해 실행합니다.

posts = session.scalars(select(Post).order_by(Post.id)).all()

SQL을 직접 쓰지 않아도 되는 것처럼 보이지만, ORM이 어떤 SQL을 생성하는지 모르면 문제가 생깁니다. 대표적인 사례가 N+1 문제입니다. 글 목록 화면에서 목록을 1번 조회한 뒤, 화면에 작성자 이름을 표시하려고 글마다 작성자를 한 건씩 추가 조회하면 목록 1번에 글 수 N번을 더한 쿼리가 나갑니다. ORM의 기본 동작인 lazy loading이 작성자 객체에 처음 접근하는 순간 조회를 실행하기 때문입니다. 시드 데이터 기준으로 글이 4건, 작성자가 3명이므로 쿼리 4개가 나갑니다. 같은 작성자는 SQLAlchemy가 이미 읽어 둔 객체를 재사용해 조회를 건너뜁니다.

SELECT id, user_id, title, body, created_at FROM posts;
SELECT id, username, joined_at FROM users WHERE id = 1;
SELECT id, username, joined_at FROM users WHERE id = 2;
SELECT id, username, joined_at FROM users WHERE id = 3;

글이 100건이고 작성자가 전부 다르면 쿼리가 101개로 늘어납니다. 해결은 3편에서 배운 JOIN 한 번으로 목록과 작성자를 같이 가져오는 것입니다.

SELECT p.id, p.title, u.username
FROM posts p
JOIN users u ON u.id = p.user_id;

ORM에서는 이렇게 연관 데이터를 처음부터 같이 가져오는 방식을 eager loading이라고 부르고, SQLAlchemy에서는 joinedload 같은 옵션으로 지정합니다. ORM을 쓰더라도 실행되는 SQL을 로그로 확인하고 이 시리즈에서 배운 지식으로 읽을 수 있어야 이런 문제를 찾아냅니다.

migration 도구

운영 중인 서비스의 스키마 변경을 psql에서 손으로 실행하면 어느 환경에 어떤 변경이 적용됐는지 기록이 남지 않고, 개발 환경과 운영 환경의 스키마가 어긋나기 시작합니다. migration 도구는 ALTER TABLE 같은 스키마 변경을 버전 번호가 매겨진 스크립트 파일로 코드 저장소에 두고, 어느 버전까지 적용됐는지를 database 안의 관리 table에 기록하고, 아직 적용되지 않은 스크립트를 순서대로 실행합니다. 모든 환경에 같은 변경이 같은 순서로 적용되고, 스키마 변경도 코드처럼 리뷰할 수 있게 됩니다. Python에서는 SQLAlchemy와 함께 쓰는 Alembic이, 언어와 무관하게 SQL 파일 기반으로 동작하는 도구로는 Flyway가 널리 쓰입니다.

큰 table의 ALTER TABLE은 주의해서 실행합니다. ALTER TABLE은 대부분 해당 table에 가장 강한 lock을 잡아서 실행되는 동안 다른 세션의 읽기와 쓰기가 대기합니다. 변경 자체가 순식간에 끝나면 문제가 없지만, column 타입 변경처럼 table 전체를 다시 써야 하는 변경은 데이터 양에 비례해 오래 걸리고 그동안 lock을 계속 잡습니다. row가 많은 table의 migration은 어떤 lock을 얼마나 잡는지 미리 확인하고 트래픽이 적은 시간에 적용합니다.

MySQL 차이

Python 드라이버 이름이 다릅니다. MySQL은 mysql-connector-python이나 PyMySQL을 씁니다. 두 드라이버 모두 자리 표시자로 %s를 쓰기 때문에 parameterized query 작성법은 이 편의 예제와 같습니다. 다만 psycopg 3가 SQL 문장과 값을 분리해 server로 보내는 것과 달리, 이 두 드라이버는 기본 설정에서 값을 client 쪽에서 escape해 문장에 끼워 넣습니다. injection을 막는 효과는 같습니다. MySQL server는 접속마다 프로세스가 아니라 thread를 만들어 접속 생성 비용이 상대적으로 작지만, connection pool을 두는 관행은 동일합니다. N+1 문제는 ORM 계층에서 생기는 문제라 database 종류와 무관하게 똑같이 발생합니다. migration 개념도 같고, Alembic과 Flyway 모두 MySQL을 지원합니다.

다음 편

다음 8편에서는 백업, 권한 관리, 모니터링, VACUUM 같은 운영 기초를 다룹니다.