PostgreSQL 배열의 숨겨진 비용

URL: https://boringsql.com/posts/good-bad-arrays/

Title: PostgreSQL 배열의 숨겨진 비용

PostgreSQL에서 배열을 시작하는 것은 integer[] 같은 컬럼을 선언하고, 값을 몇 개 넣으면 끝날 만큼 간단합니다.

혹은 그때그때 배열을 만들어서 쓸 수도 있습니다.

sql
SELECT '{1,2,3}'::int[];
SELECT array[1,2,3];

text
int4
---------
 {1,2,3}
(1 row)

  array
---------
 {1,2,3}
(1 row)

공식 문서는 좋은 입문 자료를 제공합니다. 하지만 이 단순한 인터페이스 아래에는 대부분이 생각하는 것보다 훨씬 복잡한 속성이 숨어 있습니다. PostgreSQL의 배열은 단순히 “필드 안의 리스트”가 아닙니다. 배열은 고유한 메모리 관리 전략을 갖고, 고유한 인덱스 로직을 갖고, 그리고 수많은 엣지 케이스 시나리오를 갖습니다.

boringSQL의 딥다이브답게, 이 글에서는 운영 환경을 망가뜨릴 수도 있는 배열 기능의 구석구석을 살펴봅니다.

문서 모델의 유혹

잠깐, JSONB 배열 얘기를 하려는 건가요? 전혀 아닙니다. RDBMS에서 배열이라는 개념 자체가 사실상 문서 저장(document storage)으로 위장한 것이기 때문입니다.

데이터베이스 설계에서 지역성(locality)은 관련 데이터를 물리적 저장소에서 서로 가깝게 배치함으로써 더 빠른 조회 시간을 보장합니다. 별도의 integer[] 타입을 쓰든 JSON 리스트 [1, 2, 3]를 쓰든, 여러분은 정확히 같은 아키텍처 결정을 내리고 있습니다. 즉, 정규화(normalisation)보다 지역성을 우선하는 것입니다.

tag_ids를 배열에 저장한다는 것은 관련 데이터를 한 행(row) 안에 직접 포함(embedding)시키는 것입니다. NoSQL 데이터베이스가 서브도큐먼트를 포함시키는 방식과 동일합니다. 이것이 본질적으로 잘못된 것은 아닙니다. 문서형 데이터베이스가 존재하는 데는 이유가 있습니다. 조인을 제거하고, 읽기를 단순화하며, 애플리케이션 객체와 자연스럽게 매핑됩니다.

하지만 PostgreSQL은 관계형 데이터베이스입니다. 관계형 모델을 중심으로 설계되었고, 그 모델에서는:

• **외래 키(foreign keys)**가 참조 무결성(referential integrity)을 강제하고
• **조인(joins)**이 정규화된 테이블을 연결하며
• **업데이트(updates)**가 전체 리스트가 아니라 개별 행을 수정합니다

배열은 문서 모델의 편의성을 주지만, 관계형이 약속하는 것들을 잃게 됩니다. 배열 요소에 대해 외래 키도 없고, ON DELETE referential_action(CASCADE 같은)도 없습니다. tags 엔트리를 삭제해도, 고아(orphan) ID는 배열 안에 영원히 남아 있을 수 있습니다.

경험칙(rule of thumb): 참조 무결성이 필요하다고 느끼는 순간, 여러분이 원하는 것은 대개 배열이 아니라 링크 테이블(link table)입니다. 배열은 부모 행과 동일한 라이프사이클을 공유하는 데이터에 적합합니다. 서로 다른 테이블에 걸친 관계를 표현하는 용도가 아닙니다.

현실적인 예로, 블로그 글의 작성자(author)는 여러 글을 쓸 수 있지만(즉 관계), 서비스 계정의 IP 화이트리스트는 해당 엔티티에만 적용되는 구성값에 가깝습니다.

JSONB가 이렇게 유연한데도 왜 굳이 더 “까다로운” 네이티브 타입을 쓰냐고 생각할 수 있습니다. 답은 데이터베이스의 ‘지루한’ 부분, 즉 예측 가능성과 효율성에 있습니다. integer[] 컬럼은 모든 요소가 정수임을 보장합니다.

또한 배열은 원시 타입(primitive)에 대해 저장 효율이 더 좋습니다. JSON 오브젝트의 메타데이터 오버헤드를 들고 다니지 않기 때문입니다.

문법 함정들

이 글은 배열의 기본을 알고 있다는 전제하에 작성되었습니다. 기본 사용법은 다루지 않습니다.

배열은 1에서 시작할 필요가 없다

기본적으로 SQL 배열은 1에서 시작합니다. 그래서 아래처럼 반복하는 것이 문제 없어 보입니다.

sql
FOR i IN 1 .. array_length(fruits, 1) LOOP
    RAISE NOTICE 'Index % contains: %', i, fruits[i];
END LOOP;

하지만 PostgreSQL은 임의의 경계(bounds)를 갖는 배열을 허용합니다. 그런 배열을 만나는 순간 위 코드는 깨집니다.

sql
SELECT '[-5:-3]={10,20,30}'::int[];

어떤 배열이 오든 올바르게 순회하려면, PL/pgSQL에서는 항상 array_lower()와 array_upper()를 사용하세요.

sql
SELECT array_lower('[-5:-3]={10,20,30}'::int[], 1);

text
array_lower
-------------
          -5
(1 row)

또는 SQL에서는 generate_subscripts()를 쓰세요.

sql
SELECT generate_subscripts('[-5:-3]={10,20,30}'::int[], 1);

text
generate_subscripts
---------------------
                  -5
                  -4
                  -3
(3 rows)

누락된 차원(dimension)

테이블을 만들 때는 엄격한 타입을 기대하기 마련입니다. 거의 모든 것은 그렇습니다 — 배열의 차원은 예외입니다. integer[][]가 2차원 행렬을 강제한다고 생각할 수 있지만, 실제로는 그렇지 않습니다. [] 문법은 사실상 문법적 설탕(syntactic sugar)일 뿐입니다. PostgreSQL은 기본적으로 스키마 수준에서 서브배열의 차원 수를 전혀 강제하지 않습니다.

sql
CREATE TABLE dimension_test (
    matrix integer[][] 
);

INSERT INTO dimension_test VALUES ('{{1,2}, {3,4}}');

-- 이건 실패하지 않습니다
INSERT INTO dimension_test VALUES ('{1,2,3}');

-- 3차원 행렬도 됩니다
INSERT INTO dimension_test VALUES ('{{{1,2},{3,4}}, {{5,6},{7,8}}}');

특정 차원을 강제하고 싶다면 타입 정의에 기대면 안 됩니다. 대신 CHECK 제약조건을 사용해야 합니다.

sql
CREATE TABLE strict_matrix (
    -- dims: 2차원인지 확인
    -- array_length: 정확히 3x3인지 확인
    board integer[] CHECK (array_ndims(board) = 2 AND array_length(board, 1) = 3)
);

sql
INSERT INTO strict_matrix VALUES (
    ARRAY[
        [0, 1, 0],
        [1, 1, 0],
        [0, 0, 1]
    ]
);

유일한 예외는, PostgreSQL이 중첩 레벨마다 배열의 균일성(uniformity)을 강제한다는 점입니다. 즉, 서로 다른 크기의 서브배열은 거부합니다.

sql
INSERT INTO dimension_test VALUES ('{{1,2}, {3}}');
ERROR:  malformed array literal: "{{1,2}, {3}}"
LINE 1: INSERT INTO dimension_test VALUES ('{{1,2}, {3}}');
                                           ^
DETAIL:  Multidimensional arrays must have sub-arrays with matching dimensions.

배열 슬라이싱

배열 값에 접근할 때 [1]과 [1:1] 문법이 다르다는 점을 항상 염두에 둬야 합니다. 전자는 접근자(accessor)이고, 후자는 생성자(constructor)처럼 동작합니다.

sql
select matrix[1][1] from dimension_test ;
 matrix
--------
      1
(1 row)

배열을 슬라이스할 때는, 슬라이스가 단일 요소라 하더라도 스칼라 값이 아니라 단일 요소 배열로 반환됩니다.

sql
select matrix[1:1][1:1], matrix[1][1:1], matrix[1:1][1] from dimension_test ;
 matrix | matrix | matrix
--------+--------+--------
 {{1}}  | {{1}}  | {{1}}
(1 row)

더 지저분한 부분

배열 값 접근에는 관대한(forgiving) 동작이 있어, 숨어 있는 버그를 찾기 더 어렵게 만듭니다.

sql
-- 범위 밖 접근은 NULL을 반환
SELECT (ARRAY[1,2,3])[10];
 array
--------
 (null)
(1 row)

sql
-- 범위 밖 슬라이싱은 빈 배열을 반환
SELECT (ARRAY[1,2,3])[5:10];
 array
-------
 {}
(1 row)

하지만 다른 프로그래밍 언어에서 넘어오면 가장 헷갈릴 수 있는 점은, PostgreSQL이 다차원 배열을 “배열의 배열”이 아니라 하나의 행렬(matrix)로 취급한다는 사실입니다.

sql
-- 차원 불일치
SELECT (ARRAY[[1,2],[3,4]])[1];
 array
--------
 (null)
(1 row)

다른 언어에서는 {1,2}가 나올 것이라 기대하지만 PostgreSQL은 그렇게 줄 수 없습니다. 첫 번째 셀을 반환하려다 실패합니다(인덱스가 완전하지 않기 때문).

그리고 Fletcher의 “Double Bubble” 비유를 패러프레이즈하자면(그것도 매우 잘못 흘러갔죠 — 레퍼런스를 알아맞히면 보너스), 슬라이스 표기법으로도 이를 고칠 수 없습니다.

sql
select ('{{1,2},{3,4}}'::int[])[1:1];
  int4
---------
 {{1,2}}
(1 row)

이 퍼즐을 푸는 유일한 방법은 슬라이스를 unnest로 풀어낸 뒤 다시 집계하는 것입니다.

sql
SELECT array_agg(val) FROM unnest(('{{1,2},{3,4}}'::int[])[1:1]) val;
 array_agg
-----------
 {1,2}
(1 row)

다만 array_agg는 ORDER BY 절을 사용하지 않는 한 집계된 요소의 순서를 보장하지 않습니다. 단순한 unnest 쿼리에서는 보통 의도대로 동작하지만, 암묵적 순서에 기대는 것은 위험할 수 있습니다.

다른 대안은 JSONB로 캐스팅했다가 다시 되돌리는 것입니다. 어떤 관점에서 보든 깔끔하지 않습니다. 각 서브배열이 독립적인 의미를 갖는 복잡한 다차원 구조를 다뤄야 한다면, 그냥 JSONB를 사용하세요. 기대한 대로 정확히 동작합니다.

배열 인덱싱

배열 컬럼에 B-tree 인덱스를 만들 수는 있지만, 전체 배열의 동등성(equality) 비교나 사전식 규칙(dictionary rules)에 따른 정렬을 하지 않는 이상 도움이 되지 않습니다.

sql
-- 무엇이 더 큰가? 정답은 B
-- A: {1, 1000, 1000}
-- B: {2, 0}

이 때문에 B-tree 인덱스는 현실적인 인덱스 작업에서 사실상 쓸모가 없어집니다.

배열을 다룰 때는 실제로 GIN(Generalized Inverted Index)이 필요합니다. B-tree 인덱스가 전화번호부라면, GIN은 책 뒤의 색인(index)입니다. 하나 이상의 요소를 질의하려면 가능한 모든 위치를 찾고, 교집합을 구해 조건에 맞는 위치를 찾습니다.

sql
CREATE INDEX posts_by_tags ON posts USING GIN (tags);

GIN 인덱스는 집합 연산(set operations)을 위해 설계되어, 순서를 무시하고 “존재 여부(presence)”를 핵심 기능으로 삼습니다. 배열에 대해 GIN이 제공하는 연산자는 다음과 같습니다.

포함(Containment) @> - 선택한 항목을 모두 포함하는 행을 매칭

sql
-- 모든 태그를 매칭
tags @> '{urgent, bug}'

겹침(Overlap) && - 선택한 항목 중 하나라도 포함하는가

sql
-- bug 또는 feature를 매칭
tags && '{bug, feature}'

<@와 =(동등)도 있지만, 의미는 직관적일 것입니다.

ANY의 두 얼굴

여기서부터 흥미로워집니다. 동적 SQL로 IN 리스트를 만들지 말고 ANY를 쓰라고 자주들 말합니다(저도 포함해서). 하지만 알아야 할 위험이 있습니다.

ANY 연산자는 배열이 비교의 어느 쪽에 있느냐에 따라 전혀 다르게 동작합니다.

ANY를 쓰라는 조언은 애플리케이션에서 리스트를 DB로 전달할 때는 맞습니다. 100개의 서로 다른 파라미터를 가진 쿼리(WHERE id IN ($1, $2, ... $100))를 생성하면 쿼리 캐시가 부풀고 hard-parse를 유발합니다. 대신 하나의 배열 파라미터를 전달하세요.

sql
-- 좋음: 파라미터 1개, 쿼리 플랜 1개
SELECT * FROM users WHERE id = ANY($1::int[]);

함정은, 배열 컬럼을 질의할 때도 이 문법이 똑같이 효율적이라고 가정하는 것입니다. 그렇지 않습니다. 테이블 컬럼(배열) 안에 값이 있는지 ANY로 체크하면 DB에 루프를 돌리라고 요청하는 것과 같습니다.

sql
-- 나쁨: GIN은 ANY를 지원하지 않음; 결과적으로 seq scan으로 바뀜
SELECT * FROM tickets WHERE 'feature' = ANY(tags);

WHERE 'feature' = ANY(tags)를 쓰면, 실제로 배열 연산자를 사용한 것이 아닙니다. 여러분이 쓴 것은 스칼라 동등 연산자 =를 루프 구조 안에서 적용한 것입니다. 스칼라 연산자 =는 array_ops의 일부가 아니므로, 플래너는 인덱스가 도움이 되지 않는다고 판단하고 순차 스캔(sequential scan)으로 폴백합니다.

올바른 쿼리는 이렇게 다시 써야 합니다.

sql
-- 다시 좋음
SELECT * FROM tickets WHERE tags @> ARRAY['feature'];

빠른 업데이트와 트레이드오프

GIN 인덱스는 집합에 맞춰져 있기 때문에 유지 비용이 비쌉니다. B-tree 인덱스에서는 한 행이 인덱스 엔트리 1개에 대응합니다. GIN 인덱스에서는 한 행이 N개의 인덱스 엔트리에 대응하며, N은 배열 요소 개수입니다.

이는 쓰기(write) 증폭을 유발합니다. 이를 방지하기 위해 PostgreSQL은 기본으로 “fast update” 메커니즘을 사용합니다. 새로운 엔트리를 pending list(정렬되지 않은 임시 버퍼)에 추가해두고, 나중에(VACUUM 중에) 메인 인덱스 구조에 병합하는 전략입니다.

GIN 인덱스의 정렬되지 않은 특성 때문에, 이는 VACUUM is a Lie의 예외입니다. 여기서는 VACUUM이 실제로 구조적 유지보수를 수행합니다. INSERT 성능은 관리 가능한 수준이 되지만, SELECT는 느려질 수 있습니다. 인덱스를 조회할 때마다 PostgreSQL은 정돈된 메인 인덱스와 더불어 지저분한 pending list 전체를 스캔해야 합니다. 이 리스트가 커지면 쿼리 성능이 떨어질 수 있습니다.

읽기 중심(read-heavy) 워크플로우이고 쓰기가 드물다면, 일관된 읽기 성능을 보장하기 위해 이를 비활성화하는 것이 좋습니다.

sql
CREATE INDEX posts_by_tags ON posts USING GIN (tags) WITH (fastupdate = off);

저장과 수정

이제 다시 문서 모델로 돌아가 봅시다. PostgreSQL에서 행은 불변입니다(MVCC). “제자리 업데이트(in-place update)” 같은 것은 없고, 배열은 원자적 값(atomic value)으로 저장됩니다. 그래서 매우 불편한 진실이 하나 생깁니다. 배열의 단일 요소를 수정하려면 PostgreSQL은 전체 행을 복사하고 다시 써야 합니다.

sql
UPDATE user_activity
SET event_ids = event_ids || 10001
WHERE user_id = 50;

매번 append 할 때마다 전체 배열이 다시 작성되고, 결과적으로 전체 행이 다시 작성됩니다.

TOAST 처리됨(TOASTed)

배열이 충분히 커지면(> 2 KB — 아래 참고), PostgreSQL은 TOAST를 사용해 자동으로 별도 저장 영역으로 옮깁니다. 이렇게 하면 행 자체는 가벼워지지만, 배열 업데이트는 심각한 성능 병목이 됩니다.

이 변화는 미묘하지만 영향은 큽니다. 일반적인 MVCC 업데이트는 메인 힙(main heap)에서 행 버전을 단순 복사하면 됩니다. 그러나 TOAST된 배열을 업데이트하면 PostgreSQL은 외부 청크를 모두 가져와 전체 오브젝트를 메모리로 풀어(decompress) 변경을 적용한 뒤 다시 압축(compress)하여 TOAST 테이블에 새로운 전체 크기 blob을 써야 합니다. 즉, 작은 변경이 “델타”만 반영되는 것이 아니라 전체 데이터셋을 다시 쓰는 CPU/I/O 집약 작업으로 바뀝니다.

이 임계값은 TOAST_TUPLES_PER_PAGE(기본: 4)에서 유도되며, 한 페이지에 튜플 4개가 들어가도록 보장합니다. 임계값: 약 2 KB

2 KB라는 값은 어디서 오나요? TOAST 임계값은 하나의 8 KB 힙 페이지에 최소 4개의 튜플이 들어가도록 계산됩니다. PostgreSQL은 이를 통해 효율성과 TOAST 간접 참조(indirection) 오버헤드 사이의 균형을 맞춥니다.

압축들

14 버전 이전의 PostgreSQL은 pglz에 의존했는데, 이는 속도보다 압축률을 우선하는 알고리즘입니다. 이 때문에 TOAST의 “압축 해제-수정-재압축” 사이클이 매우 고통스러웠습니다.

PostgreSQL 14는 대안으로 LZ4를 도입했습니다.

sql
ALTER TABLE articles ALTER COLUMN tags SET COMPRESSION lz4;

LZ4는 압축률이 약간 낮은 대신 압축/해제 모두 훨씬 빠릅니다. 큰 배열을 다룬다면 LZ4로 전환하는 것은 TOAST의 CPU 페널티를 줄이는 가장 쉬운 방법 중 하나입니다.

큰 배열이 의미가 있을 때

배열이 나쁘다는 인상을 받았을 수도 있습니다. 하지만 배열 사용을 평가할 때 진짜 질문은 배열이 얼마나 큰가가 아니라, 얼마나 자주 수정하느냐입니다. 10,000개 요소를 가진 배열이라도 한 번 쓰고 이후 라이프사이클 동안 읽기 전용이라면 완전히 타당한 사용 사례입니다. 반면 50개 요소 배열을 매 요청마다 append하는 것이 진짜 악당입니다.

이를 압축과 결합하면 흥미로운 조합이 나올 수 있습니다.

sql
DROP TABLE IF EXISTS compression_test;
CREATE TABLE compression_test (
    id serial PRIMARY KEY,
    compressed_floats float4[], 
    raw_floats float4[]
);

-- raw_floats는 압축하지 않음
ALTER TABLE compression_test ALTER COLUMN raw_floats SET STORAGE EXTERNAL;

-- 카디널리티가 낮은 준-랜덤 데이터 삽입
INSERT INTO compression_test (compressed_floats, raw_floats)
SELECT semi_random_arr, semi_random_arr
FROM (
    SELECT ARRAY(
        SELECT floor(random() * 50)::float4 
        FROM generate_series(1, 10000)
    ) as semi_random_arr
) as generator;

SELECT 
    'Compressed (EXTENDED)' as strategy,
    pg_size_pretty(pg_column_size(compressed_floats)::bigint) as size_on_disk
FROM compression_test
UNION ALL
SELECT 
    'Raw (EXTERNAL)',
    pg_size_pretty(pg_column_size(raw_floats)::bigint)
FROM compression_test;

text
strategy        | size_on_disk
-----------------------+--------------
 Compressed (EXTENDED) | 15 kB
 Raw (EXTERNAL)        | 39 kB
(2 rows)

배열로 벌크 로딩하기

지금까지는 배열이 큰 이점을 주지 않는 것처럼 보일 수 있습니다. 저장 측면에서 거친 부분이 있긴 하지만, 배열은 전송(transport)에서는 엄청나게 유용합니다.

5,000행을 넣는 가장 빠른 방법은 애플리케이션에서 루프를 도는 것도 아니고, 거대한 VALUES (...), (...) 문자열을 만드는 것도 아닙니다. unnest입니다.

sql
INSERT INTO measurements (sensor_id, value, captured_at)
SELECT * FROM unnest(
    $1::int[],        -- 센서 ID 배열
    $2::float[],      -- 값 배열
    $3::timestamptz[] -- 타임스탬프 배열
);

네트워크 라운드 트립은 한 번, 파싱과 플래닝도 한 번이면 됩니다. PostgreSQL이 내부적으로 배열을 행 단위로 처리해줍니다. 이는 UPSERT와 MERGE에서도 동일하게 적용됩니다.

특수 배열의 활용 사례

표준 PostgreSQL 배열은 다형(polymorphic) 타입(anyarray)입니다. 이는 하나의 함수 정의로 다양한 데이터 타입에서 동작하게 해주는 강력한 기능입니다. 정수, 문자열, 타임스탬프, 커스텀 타입을 모두 동일하게 처리해야 합니다. 하지만 특정 데이터 타입만 다룬다면, 특수 확장을 통해 상당한 성능 향상을 얻을 수 있습니다.

intarray 확장

4바이트 정수(int4/integer)만 다루는 경우라면, 기본 배열 연산은 성능을 충분히 끌어내지 못합니다. intarray 확장은 제네릭 구현보다 훨씬 빠른 특화 함수와 인덱스 연산자를 제공합니다.

사용하려면 명시적으로 활성화해야 합니다.

sql
CREATE EXTENSION IF NOT EXISTS intarray;

개발자 경험의 차이는 즉각적입니다. 표준 SQL에서 배열을 정렬하려면 unnest → 정렬 → 재집계 과정을 거쳐야 합니다. intarray에서는 sort()와 uniq() 같은 네이티브 함수를 제공합니다.

sql
-- 표준 배열
SELECT array_agg(val ORDER BY val)
FROM unnest('{3, 1, 2}'::int[]) val;

-- intarray
SELECT sort('{3, 1, 2}'::int[]);

단순한 관리 함수뿐만 아니라, 복잡한 불리언 로직을 단일 표현으로 작성할 수 있는 특화된 쿼리 문법도 제공합니다. 여러 개의 겹침(&&)과 포함(@>) 체크를 체인으로 엮는 대신, @@ 연산자를 사용해 단일 “쿼리 문자열”로 요구사항을 표현할 수 있습니다.

sql
-- 표준 배열
SELECT * FROM staff
WHERE available_days @> '{1}'          -- 월요일 포함 필수
  AND available_days && '{6, 7}'       -- 토 또는 일 포함
  AND NOT (available_days @> '{2}');   -- 화요일은 포함하면 안 됨

-- intarray
SELECT * FROM staff
WHERE available_days @@ '1 & (6 | 7) & !2';

유일한 단점은 타입 제한입니다. intarray는 부호 있는 32비트 정수에만 제한됩니다. 값이 20억을 넘으면 다시 원점입니다.

pgvector로 AI

2026년에 배열을 이야기하면서 pgvector를 언급하지 않을 수 없습니다. 이는 스스로를 “벡터 스토어(vector store)”로 마케팅하지만, 내부적으로는 다른 수학적 목적을 가진 float 배열일 뿐입니다.

표준 배열은 **이진(binary)**입니다. 정확한 매치(Overlap &&, Containment @>)를 중요하게 생각합니다. 반면 벡터는 퍼지 거리(fuzzy distance)(Cosine <=>, Euclidean <->)가 핵심입니다.

검색이나 추천 기능을 만들고 있다면, pgvector는 배열 컬럼을 “사실의 목록”(태그 A, 태그 B)이 아니라 의미 공간(semantic space)의 좌표로 취급할 수 있게 해줍니다.

그럼에도 불구하고 아키텍처 결정은 표준 배열을 쓰는 것과 정확히 같습니다. 엄격한 구조를 편의성과 맞바꾸는 것입니다. 유사도를 기준으로 두 행을 “조인”할 방법이 없기 때문에, 벡터를 행에 직접 저장합니다. “이것과 가까운 것은 무엇인가?”를 물을 수 있는 능력을 얻는 대신, 테이블 크기가 커지는 것을 받아들입니다.

PS: 이 글을 쓰는 데 영감을 준 Matthias Feist에게 감사드립니다.