PRAGMA 문

PRAGMA 문 (PRAGMA Statements)

이 문서는 SQLite의 PRAGMA 문장을 설명해요. PRAGMA는 SQLite에 특화된 SQL 확장으로, SQLite의 동작을 수정하거나 내부 상태를 조회하는 데 사용해요.

출처: 문서

본문

PRAGMA 문은 SQLite에만 있는 SQL 확장으로, SQLite 라이브러리의 동작을 바꾸거나, 테이블·인덱스·내부 상태에 대한 정보를 조회하는 데 사용해요. PRAGMA 문은 다른 SQL 문장과 비슷하게 실행하지만, 몇 가지 독특한 특징이 있어요.

  • 일부 PRAGMA는 결과 행을 반환해요(조회용).
  • 일부 PRAGMA는 연결/데이터베이스 상태를 설정해요(설정용).
  • 대부분의 PRAGMA는 각 데이터베이스 연결에 대해 로컬로 동작해요.

PRAGMA의 일반 문법:

PRAGMA pragma_name;
PRAGMA pragma_name = value;

자주 쓰이는 PRAGMA들

integrity_check

데이터베이스의 무결성을 검사해요. 문제가 없으면 ok를 반환해요.

PRAGMA integrity_check;

journal_mode

저널(트랜잭션 로그) 모드를 설정하거나 조회해요. 값에는 delete, truncate, persist, memory, wal, off가 있어요. WAL(Write-Ahead Logging) 모드는 특히 쓰기 성능에 좋아요.

PRAGMA journal_mode = WAL;

synchronous

디스크 동기화 정책을 설정해요. OFF(0), NORMAL(1), FULL(2), EXTRA(3) 값이 있어요. 값이 낮을수록 빠르지만 충돌 시 데이터 손실 위험이 커져요.

PRAGMA synchronous = NORMAL;

cache_size

페이지 캐시의 최대 크기를 설정해요(페이지 수 단위). 기본은 보통 2000페이지예요.

PRAGMA cache_size = -20000;   -- 음수면 킬로바이트(20MB) 단위

foreign_keys

외래 키 제약을 강제할지 여부를 설정해요.

PRAGMA foreign_keys = ON;

busy_timeout

데이터베이스가 잠겨 있을 때 얼마나 기다릴지(밀리초) 설정해요.

PRAGMA busy_timeout = 5000;

table_info

테이블의 열 정보(이름, 타입, 제약 등)를 반환해요.

PRAGMA table_info('mytable');

index_list / index_info

테이블의 인덱스 목록과, 인덱스의 열 정보를 조회해요.

PRAGMA index_list('mytable');
PRAGMA index_info('idx_name');

user_version

데이터베이스 파일에 정수 버전 번호를 저장하거나 조회해요. 스키마 마이그레이션 버전 관리에 유용해요.

PRAGMA user_version = 3;

auto_vacuum

자동 진공(vacuum) 모드를 설정해요. NONE(0), FULL(1), INCREMENTAL(2) 값이 있어요.

PRAGMA auto_vacuum = FULL;

incremental_vacuum

auto_vacuum=INCREMENTAL일 때, 여유 페이지를 정리해요.

PRAGMA incremental_vacuum;

hard_heap_limit

힙 메모리 사용 상한을 설정해요(바이트). 초과하면 메모리 할당이 실패할 수 있어요.

PRAGMA hard_heap_limit = 100000000;

temp_store

임시 파일을 저장할 위치를 설정해요. DEFAULT(0), FILE(1), MEMORY(2) 값이 있어요.

PRAGMA temp_store = MEMORY;

wal_autocheckpoint

WAL 모드에서 자동 체크포인트를 수행할 WAL 크기(페이지 수)를 설정해요.

PRAGMA wal_autocheckpoint = 1000;

analysis_limit

ANALYZE가 한 테이블당 분석할 행 수의 상한을 설정해요.

PRAGMA analysis_limit = 100000;

query_only

쓰기 작업을 막고 읽기만 허용할지 설정해요.

PRAGMA query_only = ON;

reverse_unordered_selects

ORDER BY 없이 SELECT가 반환하는 행의 순서를 뒤집어 출력하도록 할 수 있어요(디버깅용).

PRAGMA reverse_unordered_selects = ON;

encoding

데이터베이스 파일의 텍스트 인코딩을 조회해요. 보통 UTF-8이에요.

PRAGMA encoding;

page_size

데이터베이스 페이지 크기(바이트)를 조회하거나 설정해요. 보통 4096이에요.

PRAGMA page_size = 4096;

PRAGMA 함수 (테이블 값 함수)

SQLite 3.16.0부터, 많은 PRAGMA를 테이블 값 함수(table-valued function) 형태로도 사용할 수 있어요. 예를 들어 pragma_index_info는 다음과 같이 사용할 수 있어요.

PRAGMA index_info('idx52');
-- 또는 같은 내용을 조회:
SELECT * FROM pragma_index_info('idx52');

테이블 값 함수 형식의 장점은, 그 결과를 SELECT의 일부로 조합해 필요한 부분만 가져올 수 있다는 점이에요. 대부분의 정보용 PRAGMA는 함수 형태를 지원해요.

그 외 주의사항

  • PRAGMA 중 일부는 문장이 준비(prepare)될 때가 아니라 단계(step)로 실행될 때 동작해요.
  • PRAGMA 설정은 기본적으로 해당 연결에서만 유효하며, 다른 연결이나 데이터베이스 파일에 저장되지 않아요(파일에 저장되는 user_version 같은 예외는 있어요).
  • PRAGMA page_size를 비롯한 일부 설정은 데이터베이스가 비어 있을 때만 변경할 수 있어요.

더 알아보기 (Learn more)