AI CRM 툴 개발 기록 (3) SQLite에서 PostgreSQL로 949,934건 옮기기

SQLite로 시작한 이유, 떠난 이유 CRM 콘솔은 처음에 SQLite로 시작했다. 사내 몇 명이 쓰는 도구였고, 서버에 DB를 따로 띄우지 않아도 파일 하나로 바로 돌아간다는 점이 좋았다. 동시 접근을 위해 WAL 모드를 켜고 busy_timeout 을 30초로 잡아 두면, 화면 몇 개를 동시에 여는 정도는 버텼다. 문제는 쓰기 작업이 늘어나면서 생겼다. 새벽마다 카페24 통계 CSV 적재, 멘토리 DB 동기화, 네이버 주문 동기화, 최종 주문일 재계산, 자동 오디언스 캐시 갱신이 줄줄이 돌았다. 크롤러는 별도 프로세스로 실행되면서 같은 DB 파일에 쓰고, 웹 서버는 Gunicorn 워커 여러 개가 같은 파일을 읽었다. 저장된 오디언스 목록을 열면 모든 오디언스의 대상자 수를 다시 계산해 이력에 기록하는데, 이것도 쓰기다. SQLite는 쓰기를 한 번에 하나만 받기 때문에, 이런 작업이 겹치면 대기 시간이 길어진다. 데이터도 테이블 20개에 90만 건 이상으로 늘어난 상태였다. 그래서 PostgreSQL로 옮기기로 했다. 이 글은 SQLite에 쌓인 949,934건을 옮기면서 만난 타입 차이와, 운영 중인 서비스의 스키마를 계속 바꿔 나간 방법을 정리한 것이다. 목표와 제약 20개 테이블의 데이터를 하나도 빠뜨리지 않고 옮긴다. 모델 코드(SQLAlchemy)는 그대로 쓰고, 연결 문자열만 바꿔서 앱이 PostgreSQL에서 돌게 한다. 마이그레이션 도구(Alembic)를 새로 도입하지 않고, 스크립트 한 번 실행으로 끝낸다. 옮긴 뒤에도 새 컬럼 추가가 잦을 것이므로, 배포만 하면 스키마가 따라오게 한다. Alembic을 쓰지 않은 건, 그동안 컬럼을 앱 시작 시점에 ALTER TABLE 로 추가해 왔기 때문이다. 이력을 처음부터 만드는 것보다 "지금 모델 기준으로 테이블을 만들고 데이터를 붓는" 편이 빨랐다. 마이그레이션 스크립트의 흐름 Flask 앱을 import해서 db.create_all() 로 PostgreSQL에 현재 모델 기준 테이블을 만든다. 모든 VARCHAR 컬럼을 TEXT 로 바꾸고, 외래키 제약을 전부 내린다. 정해진 순서로 테이블을 하나씩 비우고( TRUNCATE ), 2,000건씩 읽어 executemany 로 넣는다. 테이블마다 id 시퀀스를 최댓값으로 맞춘다. 외래키를 다시 만든다. 스크립트는 몇 번이고 다시 실행해도 같은 결과가 나오도록 했다. 테이블을 매번 비우고 시작하기 때문에, 중간에 실패하면 원인을 고치고 처음부터 다시 돌리면 된다. 시행착오 1. SQLite가 눈감아 주던 것들 SQLite와 PostgreSQL은 같은 SQL처럼 보여도 타입을 다루는 태도가 다르다. 옮기면서 부딪힌 차이는 세 가지였다. 항목 SQLite PostgreSQL 대응 VARCHAR 길이 선언만 하고 검사하지 않음 길이를 넘으면 INSERT 실패 전부 TEXT로 확장 Boolean 0/1 정수로 저장 true/false 타입 컬럼 타입을 보고 bool() 변환 자동 증가 id 테이블 최댓값 기준 별도 시퀀스 이전 후 setval 로 맞춤 가장 까다로운 건 VARCHAR였다. 모델에는 VARCHAR(200) 처럼 길이를 적어 두었지만 SQLite는 그 길이를 검사하지 않는다. 그래서 크롤링과 CSV 적재로 들어온 값 중 선언한 길이를 넘는 것이 그동안 문제 없이 저장되어 있었고, PostgreSQL은 이런 값을 거부한다. 어느 컬럼에서 넘칠지 일일이 찾는 대신, information_schema 에서 character varying 컬럼을 모두 찾아 TEXT로 바꿨다. PostgreSQL에서 TEXT와 VARCHAR는 성능 차이가 없어서 잃을 것이 없었다. 시퀀스는 놓치기 쉬운 부분이었다. id 값을 그대로 복사해 넣으면 시퀀스는 1에 머물러 있어서, 이전이 끝난 뒤 앱에서 새 행을 넣는 순간 이미 있는 id와 부딪힌다. 테이블마다 MAX(id) 로 시퀀스를 맞추는 단계를 넣었다. bool_indices = {i for i, c in enumerate(common_cols) if pg_col_info[c] == "boolean"} values = [] for row in rows: row_list = list(row) for idx in bool_indices: # SQLite 0/1 → PostgreSQL bool row_list[idx] = bool(row_list[idx]) if row_list[idx] is not None else None values.append(tuple(row_list)) # 이전 후 시퀀스를 현재 최댓값으로 pg_cur.execute(f""" SELECT setval(pg_get_serial_sequence('"{table}"', 'id'), COALESCE((SELECT MAX(id) FROM "{table}"), 1)) """) 컬럼 목록도 SQLite 쪽과 PostgreSQL 쪽의 교집합만 썼다. SQLite에는 예전에 쓰다 만 컬럼이 남아 있었고, 반대로 모델에는 새로 생긴 컬럼이 있었다. 양쪽에 다 있는 컬럼만 옮기면 이런 차이 때문에 멈추지 않는다. 2. 외래키와 테이블 순서 테이블을 아무 순서로 옮기면, 주문을 넣을 때 아직 고객 테이블이 비어 있어 외래키 위반이 난다. 그래서 테이블 사이 의존 관계에 맞게 고객·회원 → 상품·오디언스 → 주문·이벤트 → 캠페인·메시지 로그 → 일별 통계 순서로 우선순위 목록을 만들었다. 그런데 순서만으로는 부족했다. SQLite는 외래키 검사가 기본으로 꺼져 있어서, 이미 지워진 고객을 가리키는 주문 같은 고아 행이 섞여 있을 수 있다. 이런 행은 순서를 아무리 맞춰도 들어가지 않는다. 그래서 이전하는 동안에는 pg_constraint 에서 외래키를 모두 찾아 내리고, 데이터를 다 넣은 뒤 필요한 11개 관계를 ALTER TABLE ... ADD CONSTRAINT 로 하나씩 다시 걸었다. 복원은 관계마다 따로 커밋해서, 하나가 실패해도 나머지는 걸리도록 했다. 3. 배치 하나가 실패하면 2,000건이 사라진다 2,000건씩 executemany 로 넣다 보면 그중 한 행만 문제가 있어도 배치 전체가 롤백된다. 그대로 두면 문제 있는 행 하나 때문에 멀쩡한 1,999건까지 빠진다. 그래서 실패한 배치는 롤백한 뒤 한 행씩 다시 넣고, 들어간 행만 세도록 바꿨다. 오류 메시지는 같은 내용이 반복되지 않게 테이블별로 한 번씩만 모아 마지막에 출력했다. 이렇게 하니 어떤 테이블에서 어떤 종류의 오류가 났는지 한눈에 보였고, 원인을 고쳐 다시 돌리는 주기가 짧아졌다. 최종적으로 20개 테이블, 949,934건이 모두 옮겨졌다. 4. 이전 후: 운영 중에 컬럼 추가하기 DB를 옮긴 뒤에도 기능은 계속 늘었다. 브랜드 메시지 발송 이력에 예약 시각과 사이트 구분이 필요해졌고, 멘토리 주문 집계에는 이탈률·구매 점수 같은 컬럼이 붙었다. db.create_all() 은 없는 테이블만 만들고 기존 테이블에 컬럼을 추가해 주지 않는다. 그래서 앱 시작 시점의 컬럼 추가 코드를 PostgreSQL 문법에 맞춰 ADD COLUMN IF NOT EXISTS 로 바꿨다. (테이블, 컬럼, 정의) 목록을 두고, 배포하면 빠진 컬럼만 추가되게 했다. 기존 데이터를 새 컬럼 기준으로 채워야 할 때는 같은 자리에서 백필 UPDATE도 함께 돌렸다. _migrate_columns = [ ("audiences", "source", "VARCHAR(20) DEFAULT 'cafe24'"), ("brand_send_histories", "reserve_time", "VARCHAR(20) DEFAULT ''"), ("brand_send_histories", "site", "VARCHAR(20) DEFAULT 'motherk'"), ("mentory_order_stats", "churn_rate", "DOUBLE PRECISION DEFAULT 0"), # ... ] with db.engine.connect() as _conn: for _table, _col, _col_def in _migrate_columns: _conn.execute(db.text( f"ALTER TABLE {_table} ADD COLUMN IF NOT EXISTS {_col} {_col_def}")) # 기존 발송 이력의 사이트 구분 백필 _conn.execute(db.text( "UPDATE brand_send_histories SET site='mentory' " "WHERE site='motherk' AND audience_source='mentory'")) _conn.commit() 연결 설정도 바꿨다. SQLite에서 쓰던 WAL·PRAGMA 코드를 지우고, 커넥션 풀을 pool_size=10 , max_overflow=20 으로 잡았다. 새벽 배치가 오래 쉬었다가 다시 붙을 때 끊긴 커넥션을 받지 않도록 pool_pre_ping 도 켰다. 결과 SQLite의 20개 테이블, 949,934건을 PostgreSQL로 전량 옮겼고, 앱은 연결 문자열을 DATABASE_URL 환경변수로 바꾸는 것만으로 새 DB에서 돌기 시작했다. 이후 새벽 배치와 웹 요청이 같은 시간대에 겹쳐도 쓰기 대기 때문에 작업이 줄줄이 밀리는 구조에서 벗어났고, 새 컬럼은 목록에 한 줄 추가하고 배포하는 것으로 반영된다. 회고 VARCHAR를 전부 TEXT로 바꾼 것 은 빠른 해결책이었지만, 길이 제한이 의미 있는 컬럼(전화번호, 상태 코드)까지 풀어 버렸다. 다시 한다면 넘친 컬럼만 골라 TEXT로 바꾸고, 나머지는 길이를 지키게 두겠다. 고아 행을 그대로 옮긴 것 도 아쉽다. 외래키를 다시 걸 때 실패한 관계가 있으면 조용히 넘어가도록 짰는데, 고아 행을 먼저 찾아 정리하거나 최소한 개수를 출력했어야 했다. 앱 시작 시 ALTER TABLE 목록 은 지금 규모에서는 편하지만, 컬럼 이름 변경이나 삭제는 다루지 못한다. 목록이 수십 줄로 늘어난 지금이라면 Alembic을 도입해 이력을 남기는 게 맞다. 처음부터 PostgreSQL로 시작했어도 비용은 크지 않았을 것이다. "작은 사내 도구라 SQLite면 충분하다"는 판단은 데이터가 매일 자동으로 쌓이는 시스템에는 오래 맞지 않았다. 기간: 2026.04 ~ 2026.09 기술: Python, Flask, SQLAlchemy, SQLite(WAL), PostgreSQL, psycopg2, Gunicorn, APScheduler 관련 프로젝트: AI CRM 툴