OGG / shp2pgsql_copy.md

`shp2pgsql`의 COPY 동작 방식에 대해 자세히 설명하겠습니다. ## 🔍 **COPY 모드(-D) 동작 방식** ### **1. 기본 동작 원리** ```bash shp2pgsql -D -W "UTF-8" -s 4326 data.shp public.roads ``` **`-D` 옵션의 효과:** - **INSERT 문 대신 COPY 문 생성** - **PostgreSQL의 COPY 명령어 사용** - **배치 처리로 대량 데이터 최적화** ### **2. INSERT vs COPY 비교** #### **INSERT 모드 (기본)** ```sql -- shp2pgsql이 생성하는 SQL INSERT INTO public.roads (gid, name, geom) VALUES (1, 'Main St', ST_GeomFromText('LINESTRING(...)', 4326)); INSERT INTO public.roads (gid, name, geom) VALUES (2, 'Oak Ave', ST_GeomFromText('LINESTRING(...)', 4326)); INSERT INTO public.roads (gid, name, geom) VALUES (3, 'Pine Rd', ST_GeomFromText('LINESTRING(...)', 4326)); -- ... 수천/수만 개의 INSERT 문 ``` #### **COPY 모드 (-D 옵션)** ```sql -- shp2pgsql이 생성하는 SQL COPY public.roads (gid, name, geom) FROM STDIN; 1 Main St LINESTRING(...) 2 Oak Ave LINESTRING(...) 3 Pine Rd LINESTRING(...) -- ... 탭으로 구분된 데이터 \. ``` ## ⚡ **COPY 모드의 성능 우위** ### **3. 성능 차이 원인** | 구분 | INSERT | COPY | |------|--------|------| | **트랜잭션** | 각 INSERT마다 개별 | 단일 트랜잭션 | | **WAL 로깅** | 각 행마다 로그 | 배치 로그 | | **인덱스 업데이트** | 실시간 | 배치 후 | | **메모리 사용** | 높음 | 낮음 | | **처리 속도** | 느림 | **10-100배 빠름** | ### **4. COPY 모드의 내부 동작** ```bash # 1단계: SHP 파일 파싱 shp2pgsql이 .shp, .dbf 파일을 읽어서 구조 분석 # 2단계: COPY SQL 생성 테이블 구조에 맞는 COPY 문장 생성 # 3단계: 데이터 스트리밍 PostgreSQL로 데이터를 직접 스트리밍 전송 # 4단계: 배치 처리 PostgreSQL이 내부적으로 배치 단위로 처리 ``` ## ��️ **COPY 모드의 데이터 처리 흐름** ### **5. 단계별 처리 과정** ``` SHP 파일 → shp2pgsql 파싱 → COPY SQL 생성 → PostgreSQL COPY 명령 → 데이터베이스 적재 ↓ ↓ ↓ ↓ ↓ Shapefile 구조 분석 COPY 문장 배치 처리 테이블 저장 ``` ### **6. 메모리 효율성** ```bash # INSERT 모드: 각 행마다 메모리 할당/해제 INSERT INTO table VALUES (...); # 메모리 사용 INSERT INTO table VALUES (...); # 메모리 사용 INSERT INTO table VALUES (...); # 메모리 사용 ... Let me reevaluate and take a different approach. _(It may take a moment for the assistant to restart.)_ _(It may take a moment for the assistant to restart.)_ `shp2pgsql`의 COPY 모드(`-D`) 동작 방식을 자세히 설명드리겠습니다. ## 🔄 **COPY 모드(-D) 동작 원리** ### **1. 기본 개념** ```bash # 일반 INSERT 모드 shp2pgsql -W "UTF-8" -s 4326 data.shp public.roads # COPY 모드 (-D 옵션) shp2pgsql -D -W "UTF-8" -s 4326 data.shp public.roads ``` ## 📋 **INSERT vs COPY 출력 비교** ### **2. INSERT 모드 출력** ```sql BEGIN; CREATE TABLE public.roads ( gid serial PRIMARY KEY, name varchar(254), geom geometry(LINESTRING,4326) ); INSERT INTO public.roads (name, geom) VALUES ('Main St', ST_GeomFromText('LINESTRING(-122.4 37.8, -122.5 37.9)', 4326)); INSERT INTO public.roads (name, geom) VALUES ('Oak Ave', ST_GeomFromText('LINESTRING(-122.3 37.7, -122.4 37.8)', 4326)); INSERT INTO public.roads (name, geom) VALUES ('Pine Rd', ST_GeomFromText('LINESTRING(-122.2 37.6, -122.3 37.7)', 4326)); -- ... 수천/수만 개의 INSERT 문 COMMIT; ``` ### **3. COPY 모드 출력** ```sql BEGIN; CREATE TABLE public.roads ( gid serial PRIMARY KEY, name varchar(254), geom geometry(LINESTRING,4326) ); COPY public.roads (name, geom) FROM stdin; Main St 0102000020E6100000020000000000000000C05EC0000000000000... Oak Ave 0102000020E6100000020000000000000000A05EC0000000000000... Pine Rd 0102000020E6100000020000000000000000805EC0000000000000... \. COMMIT; ``` ## ⚡ **COPY 모드의 성능 우위** ### **4. 성능 차이 이유** | 측면 | INSERT 모드 | COPY 모드 | |------|-------------|-----------| | **처리 방식** | 행별 개별 처리 | 배치 스트림 처리 | | **트랜잭션** | 각 INSERT마다 오버헤드 | 단일 트랜잭션 | | **SQL 파싱** | 각 문장마다 파싱 | 한 번만 파싱 | | **네트워크** | 많은 왕복 통신 | 스트림 전송 | | **메모리** | 높은 사용량 | 효율적 사용 | | **속도** | 기준 (1x) | **10-100배 빠름** | ### **5. 내부 동작 과정** ``` 1. SHP 파일 읽기 ├─ .shp (지리 정보) ├─ .dbf (속성 정보) └─ .shx (인덱스 정보) 2. 데이터 변환 ├─ 좌표계 변환 (필요시) ├─ 인코딩 변환 └─ PostgreSQL 형식으로 변환 3. COPY 문장 생성 ├─ COPY table_name FROM stdin; ├─ 탭으로 구분된 데이터 스트림 └─ 종료 마커 (\.) 4. PostgreSQL 처리 ├─ 배치 단위로 메모리에 적재 ├─ 인덱스 업데이트 지연 └─ WAL 로그 최적화 ``` ## 💾 **메모리 및 I/O 최적화** ### **6. COPY 모드의 효율성** **INSERT 모드:** ```bash # 각 행마다 메모리 할당/해제 반복 PostgreSQL: INSERT 파싱 → 실행 계획 → 실행 → 해제 PostgreSQL: INSERT 파싱 → 실행 계획 → 실행 → 해제 PostgreSQL: INSERT 파싱 → 실행 계획 → 실행 → 해제 # ... 수만 번 반복 ``` **COPY 모드:** ```bash # 한 번의 설정으로 모든 데이터 처리 PostgreSQL: COPY 파싱 → 실행 계획 → 배치 스트림 처리 → 완료 ``` ### **7. 네트워크 효율성** **INSERT 모드:** - 수만 개의 개별 SQL 문장 전송 - 각 INSERT마다 응답 대기 - 높은 네트워크 오버헤드 **COPY 모드:** - 단일 COPY 명령 + 데이터 스트림 - 응답 대기 최소화 - 낮은 네트워크 오버헤드 ## 🎯 **실제 사용 시 주의사항** ### **8. COPY 모드 제한사항** ```bash # 장점 ✅ 빠른 속도 (10-100배) ✅ 낮은 메모리 사용량 ✅ 효율적인 네트워크 사용 ✅ 트랜잭션 오버헤드 최소화 # 주의사항 ⚠️ 오류 발생 시 전체 롤백 ⚠️ 트리거 실행 안됨 (일부 버전) ⚠️ 실시간 모니터링 어려움 ⚠️ 중간 취소 어려움 ``` ### **9. 대용량 파일 처리 시** **4GB SHP 파일 처리:** - **INSERT 모드**: 10-20시간 소요 - **COPY 모드**: 1-2시간 소요 - **메모리 사용**: COPY가 50-70% 적음 따라서 현재 스크립트에서 `-D` 옵션을 사용하는 것은 **매우 효율적인 선택**입니다!