Backend #

2025-08-01 ⋯ Docker #2 작년 작업 복기: netmhcpan image 불러와서 패키지 돌리기

2024.11.24 MutClust 작업중에 netmhcpan을 돌려야되는 상황이 왓었는데 netmhcpan이 유료였나 그래서 패키지 다운은 안되고 담당 박사님은 그만두셧고.. 서버 뒤지다가 위 README 파일 발견해서 결과물 저장까진 했던 기억이있다. 이때먼가 의문이 들었던게 새로운 conda 환경에 접속한거같은 느낌이 아니라 완전 다른 제2의서버에 접속한 느낌이었는데 이상하게 연구실 디렉토리들은 그대로 접근이 가능해서 혼란스럽지만 그냥 절대경로 다 박고 수행했는데 결과들이 문제없이 저장됐었다. 그래서 그뒤로 걍잊어버리고있었는데 docker 배우고나니까 먼가 이해돼서 이해된김에 정리해보기! 2. 도커 이미지 pull 및 수행 1. 도커 이미지 다운로드 당시 chatgpt 응답 > `docker pull * (*: 링크 블라인드 처리)` 명령어는 Docker Hub에서 *의 1.0 버전을 다운로드합니다. 이미지 크기가 953.76 MB이므로, 다운로드에는 네트워크 속도에 따라 몇 분 정도 소요될 수 있습니다. > 2. 도커 이미지 수행 당시 chatgpt 응답 > 이미지 다운로드 후, 다음과 같은 명령어로 Docker 컨테이너를 생성하고 실행할 수 있습니다. 예를 들어, 로컬 시스템의 특정 디렉토리를 컨테이너에 마운트하고 싶다면: > > `docker run -it -v /path/to/local/datac:/data *` > > 이 명령은 다음과 같은 작업을 수행합니다: > > - `it`: 컨테이너를 대화식 모드로 실행하고 터미널 입력을 위한 인터페이스를 제공합니다. > - `v /path/to/local/data:/data`: 호스트 시스템의 `/path/to/local/data` 디렉토리를 컨테이너의 `/data` 디렉토리에 마운트합니다. 이렇게 하면 컨테이너 내부에서 호스트 시스템의 데이터에 접근할 수 있습니다. 당시 내 코드 몰랐는데 호스트 시스템(우리 서버)의 내 작업 디렉토리("/data/home/ysh980101/2411/data-mhc")를 컨테이너에 마운트 했었다. 3. netMHCpan 실행 1. netMHCpan 설치 경로 찾기 당시 chatgpt 응답 > > 컨테이너가 시작되면 기본적으로 `/home/RSEM-1.3.3` 디렉토리에서 시작되는 것 같습니다. 이 디렉토리는 RSEM 관련 작업을 위한 것으로 보이며, netMHCpan 작업을 수행하려면 적절한 디렉토리로 이동해야 할 수 있습니다. > > 컨테이너 내에서 netMHCpan 설치 디렉토리로 이동하려면, 해당 디렉토리의 경로를 알아야 합니다. netMHCpan의 설치 위치를 확인하고, cd 명령어로 그 위치로 이동합니다. > > `cd /path/to/netMHCpan` 당시 내 코드 netMHCpan의 설치 디렉토리 /netMHCpan-4.1 를 찾음. 2. netMHCpan 사용 당시 chatgpt 응답 > 디렉토리로 이동: 이제 확인된 설치 경로로 이동하여 작업을 시작합니다. > > `cd /netMHCpan-4.1` > > 작업 실행: netMHCpan을 실행하려면, 해당 디렉토리 내에서 실행 파일을 찾아 해당 명령을 사용합니다. 예를 들어, 다음과 같은 명령어를 사용할 수 있습니다: > > `./netMHCpan -a HLA-A02:01 -l 9 -f your_peptide_file.txt -o output.txt` > > 이 명령은 HLA-A02:01 앨리얼을 대상으로 하는 9-mer 펩타이드를 your_peptide_file.txt에서 읽어들여 output.txt에 결과를 출력합니다. 당시 내 코드 patient_id.txt에 있는 각 환자 ID에 대해 루프를 돌림 환자별 .csv 파일에서 첫 번째 컬럼(펩타이드 서열)만 추출하고, 헤더를 제거해서 .txt 파일로 만들고 Affinity prediction하려는 hla allele를 HLA_I_ALLELES_FILE에 저장해놧엇는데 HLA_I_ALLELES_FILE에 대해 루프를 돌림. 각 allele마다 NetMHCpan 실행 결과를 allele 이름으로 된 .txt 파일로 저장. 해당 환자의 모든 allele에 대한 결과 파일을 하나의 .csv로 병합. sc.py는? 대충 이런식인데 최종적으로는 Allele,Peptide,Affinity 컬럼 갖는 테이블을 반환. 전체 bash script는 이랬다 4. Docker 종료 그때 노션 보니까 챗지피티가 이런말도 해줫다. > 작업이 완료되면, exit 명령어를 입력하여 컨테이너에서 나올 수 있습니다. 컨테이너를 종료하지 않고 나온 경우, 다음과 같이 컨테이너를 다시 시작하거나 종료할 수 있습니다. > > 컨테이너 재시작: `docker start [container_id_or_name]` > > 컨테이너 내부로 들어가기: `docker attach [container_id_or_name]` 이때 이해를못한상태니깐 exit를 하면 그냥 완전 나가기가 된다고 생각했던거같다. 그래서 한 10번 넘게 들어가서 작업했는데 내가 컨테이너를 하나도 종료안해놔서 한 6개월뒤에 사람들이 ys910111 누구냐고 머라했던기억이 ㅋㅋ ㅠㅠ 그래도 이제 먼가 이해되니깐조은듯.

2025-08-01 ⋯ Docker #1 Python 실행 컨테이너 만들기

Background - RDE Local PC에서 RDE 환경 구성에서 Harbor registry로부터 RdE Container download를 수행했음 - 아이콘을 클릭해서 RDE 런처를 실행한다. 1. 웹 서비스 실행 컨테이너 만들기 1. /config/workspace/cloud/container/00.container-linux 경로로 이동 2. 디렉토리 구조는? 3. Dockerfile 내용 확인하기 내용은? - Alpine 리눅스를 기반으로 Python 3.11 환경 사용 - Python 빌드에 필요한 기본 패키지 설치 (bash, curl, gcc, musl-dev, linux-headers, jq) - Python 패키지 설치 (fastapi uvicorn psutil python-multipart) 그리고 "/mycode"를 볼륨으로 지정. - webserver.py를 컨테이너 내 디렉토리 /mycode 아래에 복사하기 (-> 컨테이너가 종료되더라도 그대로 남아있게) - 웹 서버를 무한 대기 상태로 유지 (컨테이너가 실행되었을 때 바로 종료되지 않고 계속 살아있도록) 2. Docker 이미지 빌드 3. mydata에 webserver.py 복사 후 실행 1. 현재 파일 구조 내부 확인해보면? 비어있음. 2. 다른 터미널 열어서 복사해주고 확인 3. 수행후 파일 구조 4. webserver.py 수행 하면 "http://localhost:8888/login"에서 welcome page가 뜬다. 4. 컨테이너를 실행 즉시 webserver가 되도록 해보자 1. Dockerfile 내용 수정 컨테이너가 실행되면 FastAPI 서버가 자동으로 실행되게함 2. 컨테이너 실행 하면 "http://localhost:8888/login"에서 welcome page가 뜬다. 3. 아까랑 다른점? 아까는 `python /mycode/webserver.py` 해야 떴는데 이제는 바로뜬다는 차이가있음. 5. 의문점 정리 1. RDE 아이콘 클릭하는것의 의미는? RDE 런처를 수행하면 사전에 정의된 Docker 컨테이너를 자동으로 구동한다. 이 컨테이너는 실습에 필요한 파일, 디렉토리, 개발 환경 설정, 코드 파일, 그리고 VS Code 서버를 포함한 완성된 개발 환경이 포함된 이미지로 구성되어있음. 2. /config/workspace/cloud/container/00.container-linux와 같은 경로에 접근 가능한 이유는? 컨테이너 이미지 안에 이미 내장되어 있어서 컨테이너가 실행됨과 동시에 해당 경로가 자동으로 구성된다. 즉 디렉토리를 다운로드했다는 개념보다는 컨테이너 실행과 동시에 그 안에 포함된 실습 디렉토리가 자동 마운트되었다고 이해하는 것이 더 정확하다. 3. 볼륨으로 지정? Docker 컨테이너는 기본적으로 임시 환경이기 때문에, 컨테이너 내부에서 생성한 파일이나 수정한 내용은 컨테이너가 삭제되면 모두 함께 사라진다. 하지만 VOLUME 명령을 통해 지정된 경로는 영속적인 볼륨으로 연결되며, 컨테이너가 삭제되어도 해당 경로에 저장된 데이터는 유지된다. "/mycode"를 볼륨으로 지정하고 컨테이너 내부에서 /mycode 경로에 파일을 생성하거나 수정하면 그 경로가 호스트 시스템의 익명 볼륨에 저장되기 때문에 컨테이너가 종료되더라도 그대로 남아 있게 된다.

2025-07-31 ⋯ SQL #6 AI 서비스 리뷰 시스템

1. 문제 AI 서비스 리뷰 시스템: 키워드 기반 텍스트 필터링과 AI 기반 방식의 비교를 통해 유사도 기반 검색에 대한 개념 이해 - 테이블 개요 - Day 3 – ai_service_creator_ranking.sql - 주제: AI 서비스 리뷰 (WITH (CTE) + 집계로 인기 기획자 추출) - 목적: CTE(Common Table Expression)로 집계 테이블을 구성, AVG(평점)과 COUNT(리뷰)를 기준으로 인기 있는 기획자 선정, ROW_NUMBER()로 랭킹 부여, 향후 AI 추천(예: 유사도 기반 + 평점 기반 추천) 전단 필터링에 활용 - 실습 문제 - 아래의 실습에 대해 각각 SQL문 + 결과 화면 Capture + 성능 분석결과 + 속도 차이에 대한 원인을 정리하셔서 제출하세요. - 각 기획자의 평균 평점과 리뷰 수를 계산하고, 리뷰 수가 2개 이상인 사람 중에서 평점이 높은 순으로 랭킹 정리 - 최소 쿼리를 2개 이상 작성하고 각각에 대한 실행결과값이 어떻게 나오는지 비교하여 원인에 대한 의견 정리 2. 데이터 생성 3. 쿼리 수행하고 실행결과 확인 1. CTE + ROW_NUMBER() - 설명 - 소요 시간: 3.7 ms - ROW_NUMBER 순위 부여를 통해 추천 우선순위를 생성 - AI 확장성 o - AI 응용 예시 | 구성요소 | 설명 | | --- | --- | | creator_stats | 평균 평점 + 리뷰수로 인기 기획자 후보군 필터링 | | ROW_NUMBER() | 상위 N명의 기획자 순위화하여 추천 순서 정렬 | | AI 연계 | 좋은 평가 순으로 상위 N명을 추려 벡터 유사도 필터에 결합해서, 추천 우선순위를 정해주는 전처리용 순위 테이블로 사용 | 2. 서브쿼리 + ORDER BY - 설명 - 소요 시간: 1.48 ms - 빠르지만 순위 컬럼이 없음 - AI 응용 예시 | 구성요소 | 설명 | | --- | --- | | 서브쿼리 | 집계 후 리뷰수 ≥ 2 필터링, 평점순 정렬 | | ORDER BY | 순위 부여 없이 정렬만 수행 | | AI 연계 | 유사도 추천 이전에 단순 평점 정렬 필터로 사용 가능 | 3. RANK() - 설명 - 소요 시간: 1.35 ms - RANK는 동점 처리 가능 - AI 확장성 o - AI 응용 예시 | 구성요소 | 설명 | | --- | --- | | creator_stats | 리뷰 수 + 평균 평점 기준으로 필터링된 기획자 집계 | | RANK | 평점 기준 동점순위 허용 → 보다 유연한 랭킹구조 제공 | | AI 연계 | 동점 순위를 허용해 같은 우선순위의 여러 추천 후보를 제공 가능 → 유사도 추천 결과와 합쳐서 유연하게 순위 적용 가능 | 4. FILTER() - 설명 - 소요 시간: 1.05 ms - 가장 빠른 쿼리, 리뷰가 없는 기획자도 분석 가능 - AI 확장성 o - AI 응용 예시 | 구성요소 | 설명 | | --- | --- | | FILTER() | 조건부 집계를 통해 빠르게 평점 평균 계산 | | LEFT JOIN | 리뷰가 없는 기획자까지 포함하여 전체 후보군 생성 가능 | | AI 연계 | 실시간 추천이나 전체 기획자 간 유사도 비교를 빠르게 할 수 있다. 또한 리뷰가 없더라도 모든 기획자 정보를 포함해서 추천 후보에 넣을 수 있다. | 4. 성능 비교 쿼리1 vs 쿼리2 쿼리1은 랭킹 컬럼을 제공하므로 상위 N명을 추출하거나 사용자가 현재 몇 위에 있는지를 알려주는 추천 시스템에서 유리하다. 하지만 성능 측면에서는 다소 비용이 든다. 쿼리2는 순위를 부여하는 컬럼이 없기 때문에 추천 알고리즘에서 특정 위치를 식별하거나 상위 몇 명을 구분하는 데는 추가 처리 또는 래퍼 함수가 필요하지만, 성능은 빠르다. 쿼리1 vs 쿼리4 쿼리 1은 조인된 리뷰 데이터를 기준으로 필터링과 정렬, 순위까지 모두 수행하며 리뷰가 없는 기획자는 전혀 포함되지 않는다. 이에 비해 쿼리 4는 LEFT JOIN을 통해 리뷰가 존재하지 않는 기획자까지 포함하고, FILTER() 구문으로 조건부 집계를 수행한다. 이로 인해 전체 기획자에 대한 벡터 기반 유사도 분석에 활용하기 유리하다. 또한 Postgres 전용 함수를 사용해서 실행 시간과 Planning 시간이 빠른 편이다. 쿼리1 vs 쿼리3 쿼리1의 ROW_NUMBER()은 단순히 정렬된 순서대로 1, 2, 3... 순위를 부여하는 반면, 쿼리3의 RANK()는 동점 처리 시 동일한 순위를 부여하고 그 다음 순위를 건너뛴다. 예를 들어, 동일한 평점이 2개 있다면 ROW_NUMBER()는 각각 1, 2로 부여하고, RANK()는 둘 다 1로 부여한 뒤 다음은 3이 된다. 성능 측면에서 RANK()는 ROW_NUMBER()보다 처리량이 적다. ROW_NUMBER()는 모든 행을 고유하게 구분해 정렬해야 하지만, RANK()는 동점 처리를 허용하기 때문에 정렬 이후 중복값을 묶는 처리를 덜 수행하고 실제 성능도 쿼리3이 더 빠르다. 또한 기능 측면에서 기능적으로도 동일 평점을 받은 기획자를 "동일 순위"로 처리하는 구조이기 때문에 RANK()는 사용자에게 더 유연한 결과를 제공할 수 있다.

2025-07-30 ⋯ SQL #5 소셜미디어 포스트 리뷰 시스템

1. 문제 JSONB 기반의 메타정보 필드 설계 + 검색 + AI 분석 연계 - 테이블 개요 - Day 2 – jsonb_metadata_sql_practice.sql - 주제: 소셜미디어 포스트 리뷰 - 목적: 포스트에 대한 사용자 평가 + 해시태그/속성을 JSONB로 저장하여 AI 추천/필터 기반 만들기 - 실습 준비 - 특정 메타 속성 포함 검색(JSONB 검색 쿼리 실습) - GIN 인덱스 생성 - AI 필터링 활용 시나리오 (Hybrid Filtering 기반) - 문제 - sentiment가 negative인 리뷰만 출력 - 메타데이터에 "language" 키가 포함된 행 찾기 (? 연산자 사용) - "topic"이 "productivity"이 아닌 리뷰만 출력 (힌트: NOT (metadata @> ...)) 2. 소셜미디어 포스트 리뷰 테이블 생성 3. sentiment가 negative인 리뷰만 출력 4. 메타데이터에 "language" 키가 포함된 행 찾기 5. "topic"이 "productivity"이 아닌 리뷰만 출력 cf) GIN 인덱스

2025-07-30 ⋯ SQL #4 AI 피드백 분석 시스템의 테이블 정규화

1. 문제 AI 피드백 분석 시스템의 테이블 정규화 - 시나리오 - 여러분은 AI 피드백 분석 시스템을 위한 데이터 모델링을 맡았습니다. - 현재는 여러 실험 데이터를 한 테이블에 모아두었지만, 벡터 임베딩 처리, 학습데이터 전처리, RAG 문서 기반 검색 등을 고려해 정규화 설계가 필요합니다. [비정규 테이블 예시: Day 2 – 정규화와 제약조건_실습1_예제_ai_feedback_raw.csv] - 실습 목표 - LLM Feedback 데이터 정규화 (3NF까지 고려) - model, user, prompt-response, tags 분리 - tags 필드는:TEXT[ ] 배열로 유지한 구조 (빠른 전처리, FAISS 등 용이) - feedback_tag라는 별도 테이블로 정규화 (통계, RAG 전처리 유리) - AI 분석 목적의 전처리 성능 관점에서 두 방식 비교 설명 2. Objective - 비정규화된 AI 피드백 테이블을 정규화 - 사용자, 모델, 질문-응답(prompt-response), 태그(tags) 정보를 분리 - 태그를 배열 형태(TEXT[]), 또는 별도 정규 테이블(feedback_tag)로 관리하여 AI 분석 목적(임베딩, 전처리, RAG)에 적합한 구조로 테이블 설계 3. AI 피드백 테이블 확인 원본 데이터를 확인해보면 아래와같고 정규화할 대상과 정규화방법은 아래와 같다. - model: 모델 정보 테이블로 분리 (models) - user_id, user_name: 사용자 테이블로 분리 (users) - prompt, response: 피드백 본문 테이블로 분리 (feedbacks) - tags: 별도 테이블로 정규화( 배열 유지 방식 + 연결 테이블 (feedback_tags)) - created_at: feedbacks 테이블에 포함 4. 테이블 정규화 3NF까지 고려해서 정규화하기. 정규화 후 데이터 삽입하기 5. ERD 작성 dbdiagram 사용 https://dbdiagram.io/ 구조 dbdiagram으로 ERD 작성 6. AI 분석 목적의 전처리 성능 관점에서 두 방식 비교 설명 (TEXT[] 배열로 tags를 저장하는 방식 vs tags를 별도 테이블(feedback_tag)로 정규화하는 방식) 먼저 TEXT[] 배열로 tags를 저장하는 방식은 한 피드백에 대한 여러 태그 정보를 하나의 행에 함께 저장하는 구조인데 예를 들어 어떤 사용자 피드백이 "positive", "concise", "creative"라는 태그를 갖는다면, 이 세 단어를 배열로 묶어 하나의 셀에 저장합니다. 이 방식의 장점은 빠른 접근성과 효율적인 처리 속도입니다. LLM 기반 피드백 시스템에서는 종종 전체 텍스트나 임베딩을 이용한 벡터 검색(ex. pgvector)을 수행하는데 이때 태그 정보가 같은 행에 묶여 있으면 텍스트 단위 처리 또는 배치 임베딩에 용이하고 특히 모델 학습이나 벡터 임베딩 시 태그 정보를 문맥 정보로 같이 넘겨야 하는 경우 이 구조는 파이프라인 단순화에 큰 도움이 됩니다. 하지만 태그 단위로 집계하거나 통계 분석을 하고자 할 경우 배열 내부 요소를 하나하나 파싱하거나 unnest() 같은 SQL 함수로 분리해 처리해야 하는데 예를 들어 "어떤 태그가 가장 자주 사용되었는가?"라는 질문을 하려면 배열에서 모든 태그를 추출하고 세는 별도 과정이 필요합니다. 반대로 tags를 별도 테이블(feedback_tag)로 정규화하는 방식은 각 태그를 하나의 행으로 저장하고 피드백 ID와 연결하는데 이때 feedback_tag 테이블은 "feedback_id - tag" 형태로 구성되며, 각 피드백에 여러 태그가 있을 경우 그 수만큼의 행이 생성됩니다. 이 방식의 가장 큰 장점은 쿼리 처리에서의 유연성입니다. 앞서 언급한 "가장 많이 쓰인 태그"나 "특정 태그가 달린 피드백 목록"을 매우 쉽게 쿼리할 수 있습니다. 그러나 데이터가 다소 늘어난다는 단점도 있습니다. 예를 들어 100개의 피드백에 평균 4개의 태그가 달려 있다면, feedback_tag 테이블에는 400개의 레코드가 추가로 생기고 피드백을 조인하여 조회하는 경우 JOIN 연산의 비용이 듭니다. 이로 인해 벡터 임베딩이나 배치 학습 시에는 조인을 반복해야 하므로 배열 기반보다 느릴 수 있습니다. 결론적으로, 태그를 배열로 유지하는 방식은 벡터 기반 검색, 임베딩 처리, LLM 학습에 적합하고 시스템의 입력-출력 속도를 높이는 반면, 태그를 별도 테이블로 정규화하는 방식은 태그 관리 및 유지 보수에 유리합니다.

2025-07-29 ⋯ SQL #3 스키마 분리와 AI 분석

생각 정리 1. AI 분석이 들어갈 때 왜 별도 스키마로 나누는 것이 유리할까요? 2. 스키마 vs. 테이블 분리, 어떤 방식이 어떤 상황에 적합할까요? 3. 향후 pgvector 또는 AI 모델 결과를 넣기 위해 어떻게 테이블을 확장할 수 있을까요? 1. AI 분석이 들어갈 때 왜 별도 스키마로 나누는 것이 유리할까요? AI 분석이 포함된 시스템에서 데이터를 다룰 때, 별도 스키마로 나누는 것이 유리한 이유는 (1) 데이터의 사용 목적이 다르기 때문이고, (2) 데이터의 구조와 속성이 근본적으로 다르기 때문입니다. 먼저 데이터의 사용 목적이 다르면 별도의 스키마로 운영하는것이 유리합니다. 예를 들어, 우리가 학교에서 수업을 들을 때 쓰는 노트와, 친구와 놀러 갈 계획을 적는 다이어리는 서로 내용도 다르고, 사용 목적도 다르기 때문에 같은 공책에 막 섞어 쓰면 나중에 헷갈리고 찾기 어렵습니다. 데이터베이스도 마찬가지인데, 예를 들어 수강생(Student), 강사(Instructor), 수강신청(Enrollment) 같은 테이블은 대부분 운영 데이터를 담고 있고 실제 서비스가 돌아가기 위해 실시간으로 쓰이고 조회됩니다. 그런데 AI 분석에 사용되는 데이터, 예를 들면 수강 리뷰의 임베딩 값(Embedding)이나 학생 행동 로그에서 추출한 패턴 정보는 운영 목적이 아니라 분석 목적입니다. AI 분석 데이터는 실시간보다는 주기적으로 갱신되고, 대량의 수학적 계산을 거쳐 만들어지며, 사용자가 직접 보는 게 아니라 모델이 참고합니다. 그러니까 이 두 데이터를 같은 공간에 두는 건, 수업 노트 옆에 여행계획을 적는 것과 같이 찾기도 어렵고, 실수도 생기고, 결국 혼란을 유발할 수 있습니다. 두 번째로 데이터의 구조와 속성이 다른 경우 별도의 스키마로 운영하는것이 유리합니다. 운영 데이터는 일반적으로 사람이 입력한 명확한 값으로 구성됩니다. 예를 들어 이름, 전화번호, 수강과목 등은 짧고, 일정한 형식을 갖고 있다. 그런데 AI 분석을 위한 데이터는 길고 복잡한데, 예를 들어 학생이 쓴 리뷰를 BERT 모델로 임베딩하면 768차원의 벡터로 바뀌고, 이런 벡터는 숫자 덩어리이기 때문에 일반 SQL 쿼리로는 다루기 어렵습니다. 또 AI 분석에서는 반복 실험을 하거나 다양한 모델 결과를 저장해야 하므로, 새로운 컬럼이 자주 생기고 스키마 구조도 자주 바뀔 수 있습니다. 예를 들어 '학생 행동을 기반으로 예측된 이탈 위험 점수'나 '추천 과정 리스트' 같은 컬럼은 운영 데이터에서는 필요 없지만, 분석에서는 매우 중요한데, 이처럼 구조적으로 유연하고 실험적인 데이터를 기존 운영 스키마에 억지로 끼워 넣으면, 전체 시스템이 복잡해지고 에러도 많아집니다. 그래서 아예 AI 분석용 스키마를 따로 만들어 거기에 AI 전용 테이블을 모아두면, 운영 시스템은 안정성을 유지하면서도 분석팀은 자유롭게 데이터를 다룰 수 있습니다. 예를 들어 서울캠퍼스 학생 데이터를 담은 테이블이 `seoul.students`이고, AI 분석 결과로 얻은 학생 행동 임베딩이 `analytics.student_embeddings`에 저장돼 있으면, 운영 데이터는 수업 등록이나 점수 관리에 집중하고, 분석 스키마는 AI 모델의 입력 및 출력 데이터 저장에 집중합니다. 운영 스키마는 변경이 거의 없지만, 분석 스키마는 새로운 모델이 생길 때마다 컬럼이 추가되거나 테이블이 생길 수 있는데 서로 독립적이기 때문에 안정성과 유연성을 동시에 확보할 수 있습니다. 2. 스키마 vs. 테이블 분리, 어떤 방식이 어떤 상황에 적합할까요? 스키마와 테이블 분리는 둘 다 데이터베이스를 논리적으로 구분하고 정리하기 위한 방법인데, 접근 제어가 필요한 경우와 같은 구조이지만 맥락(도메인)이 다를 경우에 스키마 분리가 적합하고, 같은 도메인 안에서 구조나 의미가 다른 데이터를 함께 관리할 때는 테이블 분리가 적합합니다. 먼저 스키마는 일종의 '공간'입니다. 예를 들어, 회사에서 부서마다 각기 다른 문서를 관리한다고 가정해봤을때, 경영팀은 예산 파일, 인사팀은 사원 평가표, 개발팀은 코드 문서를 관리합니다. 이걸 한 폴더에 몰아넣으면 각 부서가 실수로 다른 부서 문서를 건드릴 수 있습니다. 대신 부서별 폴더를 따로 만들어 놓고 권한을 설정하면, 인사팀은 인사 폴더만 접근 가능하고 경영팀은 경영 폴더만 볼수있습니다. 데이터베이스에서 이 '부서별 폴더'가 바로 스키마입니다. 예를 들어 `seoul.students`, `jeju.students`처럼 캠퍼스별 학생 데이터를 스키마로 구분하면, 서울 캠퍼스 운영자는 `seoul`만 접근할 수 있게 만들고, 제주 캠퍼스 운영자는 `jeju`만 다루게 할 수 있습니다. 테이블 분리만으로는 특정 테이블에만 접근을 제한하기 어렵고, 관리가 복잡해질 수 있습니다. 두 번째로, 같은 구조이지만 맥락(도메인)이 다를 경우에도 스키마 분리가 좋다. 예를 들어, 대학의 학사 시스템이 있을 때 서울과 제주 두 캠퍼스가 있는데, 학생, 수강, 강의 리뷰 등의 테이블 구조는 동일하지만 운영은 독립적입니다. 이럴 때는 `seoul.enrollments`와 `jeju.enrollments`처럼 스키마로 구분하면 같은 종류의 데이터를 혼동 없이 관리할 수 있습니다. 반면, 만약 '서울학생'과 '제주학생'을 한 테이블 `students`에 `campus` 열을 추가해서 구분한다면, 운영이나 통계 측면에서 실수하기 쉽고, 특정 캠퍼스의 데이터만 쿼리하려면 매번 조건문을 붙여야 합니다. 즉, 스키마 분리는 구조는 같지만 실질적으로 분리된 독립 단위를 표현하는 데 유리합니다. 테이블로만 나누면 이런 맥락 구분이 흐릿해지고, 유지보수가 어려워질 수 있습니다. 반면 테이블 분리는 같은 도메인 안에서 구조나 의미가 명확히 다른 데이터를 함께 관리할 때 적합합니다. 예를 들어, 한 학사 시스템 안에서 `courses` 테이블은 개설된 과목 정보를 담고, `course_descriptions`는 그 과목에 대한 상세한 설명을 담는다고 하면 이 둘은 같은 "강의"라는 도메인에 속해 있지만, 정보의 종류와 목적이 다르기 때문에 하나의 테이블에 섞지 않고, 테이블을 분리해서 관리하는 것이 바람직합니다. `courses`와 `course_descriptions`처럼 성격이 다른 데이터를 스키마 분리로도 독립성 확보가 가능하긴 합니다. 그러나 둘다 둘 다 "강의"라는 도메인에 속하며 도메인은 동일하되 데이터의 성격만 다른, 이러한 경우에는 기능적 분리만 필요하지, 운영 주체나 보안 경계까지 분리해야 하는 것은 아닙니다. 성능 및 관리 효율성 측면에서 테이블 분리가 더 실용적이므로 테이블 분리 수준에서 멈추는 것이 더 적합합니다. 3. (스키마가 이와 같을 때) 향후 pgvector 또는 AI 모델 결과를 넣기 위해 어떻게 테이블을 확장할 수 있을까요? 향후 pgvector를 도입하거나 AI 모델의 예측 결과를 넣기 위해 테이블을 확장하려면 2가지를 고려해서 확장해야 합니다. 첫째, 벡터 임베딩과 같은 AI 결과물은 원본 데이터와 별도로 관리되도록 전용 테이블을 분리해 저장해야 합니다. pgvector는 고차원 벡터 데이터를 다루기 위한 PostgreSQL 확장 기능인데, 이건 보통 텍스트나 이미지, 수강 패턴 등과 같은 복잡한 데이터를 수치화한 결과물이다. 그런데 이걸 기존 테이블, 예를 들어 `courses`나 `students` 테이블 안에 `vector(768)` 같은 컬럼으로 그냥 넣어버리면, 일단 저장은 가능하지만 문제가 생기는데 하나의 테이블이 너무 많은 역할을 하게 되면 데이터의 의미가 혼재되고 AI 모델이 바뀔 때마다 갱신도 어렵고 이전 값과 새 값을 비교하기도 어려워집니다. 예를 들어 `courses` 테이블에 있는 `title`이 바뀌지는 않았는데, AI 임베딩 벡터만 업데이트하려면 전체 레코드를 다시 수정해야 하므로 비효율적입니다. 그래서 AI 관련 벡터는 따로 관리하는것이 효율적입니다. 예를 들어 `analytics.course_vectors`라는 테이블을 만들고 여기에 `course_id`, `vector`, `updated_at`이라는 컬럼만 두면 AI 임베딩의 저장과 갱신이 훨씬 단순해집니다 즉, 벡터 데이터를 넣고 싶다면 기존 테이블을 확장하는 것이 아니라, 벡터만 따로 저장하는 전용 테이블을 만들고, 필요한 ID만 외래키로 연결하는 게 가장 안정적이고 관리가 쉬운 방법입니다. 둘째, AI 결과와 원본 데이터를 명확히 연결해주는 참조 구조가 중요합니다. AI 분석은 결국 원본 데이터를 바탕으로 나온 결과물입니다. 그러니까 이 결과가 어떤 데이터에 기반해서 나왔는지를 명확히 추적할 수 있어야 합니다. 예를 들어 `student_embeddings`라는 테이블이 있다고 하면, 벡터값은 단순히 512차원짜리 수치 덩어리일 뿐인데, 이걸 어떤 학생을 표현한 것인지, 서울 캠퍼스인지 제주 캠퍼스인지, 언제 생성된 것인지 명확히 기록하지 않으면 나중에 분석이나 추천 시스템에 쓸 수 없습니다. 그래서 실제 테이블 구조는 이렇게 설계 가능합니다: `student_id`, `campus`, `embedding`, `updated_at`. 여기서 `student_id`는 기존의 `seoul.students` 혹은 `jeju.students`의 기본키와 매칭되고, `campus`는 데이터 출처를 명확히 하기 위한 메타데이터 역할을 합니다. 즉, 단순히 벡터만 저장하는 게 아니라, AI 결과가 어느 테이블의 어느 엔티티에 대응되는지를 명시적으로 외래키 또는 참조 메타데이터로 남겨야 한다는 점이 매우 중요합니다. 그래야만 벡터 기반 검색이나 추천 알고리즘을 구현할 때 “어떤 학생의 임베딩”인지, “어떤 리뷰의 감성 점수인지” 등을 정확히 추적할 수 있습니다. 예를 들어, 학생 리뷰(comment)를 기반으로 감정 분석 점수와 벡터를 저장한다고 하면 `analytics.review_embeddings`라는 테이블을 만들고, 여기에 `review_id(FK)`, `embedding`, `sentiment_score`, `updated_at` 컬럼을 만들면 이 구조는 리뷰와 AI 결과를 연결할 뿐 아니라 AI 분석이 언제 수행되었고 어떤 데이터를 기반으로 했는지를 명확히 하고 이는 향후 모델이 바뀌거나 벡터를 다시 계산해야 할 때 매우 중요한 기준이 됩니다.

2025-07-29 ⋯ SQL #2 학사 관리 시스템 설계 - 스키마 분리 및 멀티 프로젝트 설계

1. 문제 이전에 만든 ERD를 기반으로 PostgreSQL 로 스키마 분리 및 멀티 프로젝트 설계합니다. - 주제 . 서울캠퍼스/제주캠퍼스별 학사 관리 시스템 (Learning Management System) 동일한 학사관리 시스템 구조를 기반으로, 캠퍼스에 따라 데이터를 스키마 단위로 분리 설계하고 향후 AI 분석 결과의 멀티 벡터 저장 구조로 확장 가능하도록 구조 설계 - 요구사항 . 교육과정, 수강생 과정운영자, 강사, 과정 설명 텍스트, Review 등으로 구성하되, 캠퍼스별 특성을 고려하여 스키마 분리 . 서울 캠퍼스와 제주 캠퍼스 간 교수/강사/과정은 중복될 수 있음 2. 서울캠퍼스/제주캠퍼스별 학사 관리 시스템 엔티티 도출 스키마 3. ERD 작성 dbdiagram 사용 https://dbdiagram.io/ 4. PostgreSQL에 생성

2025-07-29 ⋯ SQL #1 학사 관리 시스템 설계 - 엔터티 도출 및 ERD 작성

1. 문제 AI 기반 학사 관리 시스템 (Learning Management System) 설계를 위한 엔터티 도출 및 ERD 작성 실습입니다. - 요구사항 . 교육과정, 수강생, 과정운영자, 강사, 과정 설명 텍스트, Review 등으로 구성 . 과정 설명 텍스트는 향후 AI 임베딩 대상이므로 충분한 길이와 자유 텍스트로 정의 - 순서 . 학사관리시스템 엔티티 도출 및 검증 . ERD 변환 작업 . 변환된 ERD로 설치된 PostgreSQL 에 생성 2. 학사관리시스템 엔티티 도출 3. ERD 작성 dbdiagram 사용 https://dbdiagram.io/ 4. PostgreSQL에 생성 cf) 원래 데이터, 스키마 지우기