파이썬 PostgreSQL 데이터베이스 프로그래밍 UPSERT와 배치 upsert 활용법

🔗 UPSERT와 ON CONFLICT 개념 이해하기

데이터베이스를 다루다 보면 중복 키 문제를 피할 수 없는 경우가 많습니다.
특히 기본 키(primary key)나 고유 제약조건(unique constraint)이 설정된 테이블에 데이터를 삽입할 때, 이미 동일한 값이 존재한다면 단순한 INSERT 문은 오류를 발생시킵니다.
이때 UPSERT 구문을 사용하면 충돌 상황에서 오류를 내지 않고 데이터 갱신을 처리할 수 있습니다.

PostgreSQL은 이를 위해 ON CONFLICT 절을 제공합니다.
즉, 데이터 삽입 시 충돌이 발생하면 특정 컬럼을 갱신하거나 무시하도록 명령할 수 있는 기능이죠.
대표적으로 두 가지 패턴이 존재합니다.

• 🛠️ON CONFLICT DO NOTHING → 충돌이 발생하면 해당 INSERT를 무시
• ⚙️ON CONFLICT DO UPDATE → 충돌이 발생하면 기존 데이터를 업데이트

예를 들어, 사용자 정보를 저장하는 users 테이블에서 같은 email 주소가 중복 삽입될 경우를 가정해 보겠습니다.
단순히 무시할 수도 있고, 혹은 이름이나 마지막 로그인 시각을 갱신하는 방식으로 처리할 수도 있습니다.

INSERT INTO users (email, name, last_login)
VALUES ('test@example.com', '홍길동', NOW())
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name,
              last_login = EXCLUDED.last_login;

위 코드에서 EXCLUDED 키워드는 충돌이 발생한 새로운 값을 의미합니다.
즉, 기존 행은 그대로 두고 새 값으로 필요한 필드만 업데이트하는 방식이 가능합니다.

🛠️ 파이썬에서 UPSERT 구현 방법

PostgreSQL의 UPSERT 기능은 파이썬과 함께 사용할 때 특히 강력한 효과를 발휘합니다.
대표적으로 많이 쓰이는 라이브러리는 psycopg2와 SQLAlchemy가 있습니다.
이 라이브러리들을 활용하면 SQL 구문을 직접 작성하거나 ORM 방식으로 손쉽게 UPSERT 로직을 구현할 수 있습니다.

🐍 psycopg2를 이용한 UPSERT

psycopg2는 PostgreSQL을 위한 가장 널리 쓰이는 파이썬 드라이버입니다.
기본 SQL 문을 직접 작성해 실행할 수 있으며, UPSERT는 단일 SQL 문으로 처리할 수 있어 코드가 간결해집니다.

import psycopg2

conn = psycopg2.connect("dbname=test user=postgres password=1234")
cur = conn.cursor()

cur.execute("""
    INSERT INTO users (email, name)
    VALUES (%s, %s)
    ON CONFLICT (email)
    DO UPDATE SET name = EXCLUDED.name
""", ("test@example.com", "홍길동"))

conn.commit()
cur.close()
conn.close()

이처럼 간단하게 UPSERT를 구현할 수 있으며, 필요한 경우 추가 필드를 동시에 갱신하도록 확장할 수도 있습니다.

⚡ SQLAlchemy ORM에서의 UPSERT

ORM을 선호한다면 SQLAlchemy에서도 UPSERT를 지원합니다.
SQLAlchemy의 insert() 함수와 on_conflict_do_update() 옵션을 활용하면 충돌 발생 시 특정 필드를 자동으로 업데이트할 수 있습니다.

from sqlalchemy.dialects.postgresql import insert
from models import User
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine("postgresql://postgres:1234@localhost/test")
Session = sessionmaker(bind=engine)
session = Session()

stmt = insert(User).values(email="test@example.com", name="홍길동")
stmt = stmt.on_conflict_do_update(
    index_elements=['email'],
    set_=dict(name="홍길동")
)

session.execute(stmt)
session.commit()

이 방식은 데이터베이스 제약조건과 ORM 객체 모델을 동시에 활용할 수 있어 유지보수성이 높습니다.
따라서 규모가 있는 프로젝트에서는 SQLAlchemy 기반 UPSERT가 더 적합할 수 있습니다.

⚙️ 배치 upsert 패턴과 성능 최적화

단일 UPSERT는 소규모 데이터 처리에는 적합하지만, 수천 건 이상의 레코드를 다루는 경우 성능 저하가 발생할 수 있습니다.
이때 유용한 방법이 바로 배치 upsert 패턴입니다.
여러 데이터를 한 번에 INSERT한 뒤, 충돌이 발생하면 동시에 UPDATE를 수행하는 방식으로 대량 데이터를 효율적으로 다룰 수 있습니다.

📦 여러 행을 한 번에 upsert 하기

PostgreSQL은 다중 행 삽입(multi-row insert) 구문을 지원합니다.
이를 UPSERT와 결합하면 단일 쿼리로 여러 건의 데이터를 동시에 처리할 수 있습니다.

INSERT INTO users (email, name, last_login)
VALUES
  ('a@example.com', '사용자A', NOW()),
  ('b@example.com', '사용자B', NOW()),
  ('c@example.com', '사용자C', NOW())
ON CONFLICT (email)
DO UPDATE SET
  name = EXCLUDED.name,
  last_login = EXCLUDED.last_login;

위 쿼리는 세 개의 사용자 데이터를 한 번에 삽입하고, email 값이 충돌하는 경우 해당 행을 업데이트합니다.
이 방식은 네트워크 요청 횟수를 줄이고 트랜잭션 처리 속도를 크게 개선합니다.

⚡ 파이썬에서의 배치 upsert

파이썬에서는 executemany() 메서드를 활용해 다수의 레코드를 한 번에 처리할 수 있습니다.
또는 SQLAlchemy의 bulk_insert 기능과 UPSERT 옵션을 결합해 대량 데이터를 효율적으로 저장할 수도 있습니다.

data = [
    ("a@example.com", "사용자A"),
    ("b@example.com", "사용자B"),
    ("c@example.com", "사용자C")
]

cur.executemany("""
    INSERT INTO users (email, name)
    VALUES (%s, %s)
    ON CONFLICT (email)
    DO UPDATE SET name = EXCLUDED.name
""", data)

이 접근 방식은 반복문으로 하나씩 쿼리를 실행하는 방식보다 훨씬 효율적입니다.
특히 로그, 센서 데이터, 거래 내역 등 대량의 실시간 데이터를 처리할 때 유용합니다.

🔌 실무에서의 활용 예시와 주의사항

UPSERT와 배치 upsert는 이론적으로는 간단하지만, 실제 프로젝트에 적용할 때는 고려해야 할 점이 많습니다.
예를 들어 사용자 프로필, 주문 내역, 로그 데이터 등에서는 데이터 무결성과 성능을 동시에 보장해야 하므로 적절한 전략이 필요합니다.

🧑‍💻 사용자 데이터 업데이트

웹 서비스에서는 신규 가입자와 기존 사용자의 정보 업데이트가 동시에 일어날 수 있습니다.
이 경우 단순 INSERT는 오류를 발생시키지만, UPSERT를 적용하면 기존 데이터는 최신 상태로 갱신되고 신규 사용자는 문제없이 추가됩니다.

💬 실시간 서비스에서는 UPSERT를 통해 중복 데이터 입력 오류를 방지하면서도, 항상 최신 정보를 유지할 수 있습니다.

📊 로그 및 센서 데이터 관리

IoT 센서 데이터나 웹 로그는 짧은 시간에 대량으로 발생합니다.
이 데이터를 UPSERT로 처리하면 동일 키에 대한 데이터 중복을 방지할 수 있으며, 배치 upsert를 적용하면 대규모 데이터 삽입에서도 성능을 확보할 수 있습니다.

⚠️ 주의해야 할 부분

• 🔑UPSERT 충돌 조건은 반드시 고유 제약조건에 기반해야 합니다.
• ⚡너무 많은 데이터를 한꺼번에 처리하면 트랜잭션 크기가 커져 오히려 성능이 저하될 수 있습니다.
• 📌데이터 충돌 처리 로직을 잘못 설계하면 기존 데이터를 의도치 않게 덮어쓸 위험이 있습니다.

💡 성능 향상을 위한 추가 팁

UPSERT와 배치 upsert는 매우 강력하지만, 추가적인 최적화 전략을 적용하면 성능을 한층 더 끌어올릴 수 있습니다.
실제 서비스 환경에서는 초당 수천 건의 요청이 발생하기 때문에, 작은 차이가 전체 시스템 성능에 큰 영향을 미칠 수 있습니다.

🚀 인덱스 최적화

UPSERT의 성능은 충돌을 감지하는 과정에서 인덱스 의존도가 높습니다.
따라서 고유 인덱스(unique index)를 적절히 설계해야 하며, 불필요한 인덱스는 제거하는 것이 좋습니다.
과도한 인덱스는 INSERT 및 UPDATE 성능을 저하시킬 수 있습니다.

📂 파티셔닝 활용

데이터가 방대해질수록 단일 테이블에 모든 데이터를 쌓는 방식은 비효율적입니다.
PostgreSQL의 파티셔닝(partitioning) 기능을 이용하면 데이터를 구간별로 나누어 저장할 수 있어 충돌 감지와 업데이트 속도가 빨라집니다.

⚡ 트랜잭션 크기 조절

대량 데이터를 배치 처리할 때 한 번에 너무 많은 데이터를 넣으면 트랜잭션 크기가 커져 메모리와 잠금 경합이 발생할 수 있습니다.
따라서 적절한 배치 크기를 설정하고, 일정 단위마다 커밋하는 것이 안정적인 성능을 유지하는 비결입니다.

🛠️ 병렬 처리 적용

PostgreSQL은 병렬 쿼리와 비동기 처리를 지원합니다.
파이썬에서도 멀티스레딩 또는 비동기(asyncio) 패턴을 활용해 여러 배치를 동시에 처리하면 처리량을 높일 수 있습니다.
단, 동시 실행 시 잠금 경합이 발생하지 않도록 주의해야 합니다.

❓ 자주 묻는 질문 (FAQ)