오래된 표현식 인덱스

오래된 표현식 인덱스 (Stale Expression Index)

개요 (Overview)

표현식 인덱스(expression index)는 가상 생성 열(VIRTUAL generated column)이나 테이블 열의 표현식에 대한 인덱스예요. 표현식 인덱스는 키 값 중 하나 이상이 인덱싱되는 테이블의 열을 단순히 복사하는 것이 아니라 표현식의 계산 결과라는 점에서 달라요.

인덱스가 올바르게 동작하려면, 해당 테이블 행이 변하지 않는 한 표현식의 값도 변하면 안 돼요. 따라서 인덱싱된 표현식은 결정적 함수(deterministic function)를 사용해야 해요. 결정적 함수는 같은 입력이 주어지면 항상 같은 출력을 반환하는 함수예요. "abs(X)"나 "concat(X,Y)" 같은 함수가 결정적 함수예요. 반면 "datetime('now')"나 "random()" 같은 함수는 비결정적 함수예요.

때때로 "결정적"으로 분류된 함수가 서로 다른 CPU 아키텍처, 운영체제, SQLite 버전에 따라 완전히 결정적이지 않을 수 있어요. 그런 경우, 한 CPU/OS/SQLite-버전에서 만든 표현식 인덱스가 다른 CPU/OS/SQLite-버전에서는 부정확할 수 있어요. 이런 것을 "오래된 표현식 인덱스"(stale expression index)라고 불러요.

오래된 표현식 인덱스는 드물게 발생해요. 알려진 모든 발생 사례는 부동소수점 값에 대한 인덱스에 다음 상황 중 하나가 결합된 경우예요.

  • SQLite 데이터베이스가 한 CPU 아키텍처에서 다른 아키텍처로 이동한 경우(예: x86_64에서 ARM으로)
  • SQLite 데이터베이스가 한 운영체제에서 다른 운영체제로 이동한 경우(예: Windows에서 Mac으로)
  • SQLite 라이브러리 버전이 바뀌거나 SQLite가 SQL 함수를 구현하는 데 사용하는 외부 라이브러리에 버전 변경이 있는 경우

출처: 문서

본문

항상 100% 결정적이지 않은 결정적 함수 (Deterministic Functions That Are Not Always 100% Deterministic)

결정적 함수는 다른 플랫폼과 다른 SQLite 버전에서 실행해도 항상 같은 결과를 주어야 해요. 하지만 때때로, 드물게, 한 운영체제에서 다른 운영체제로 이동하거나 SQLite 버전을 바꿀 때 결정적이라고 여겨지는 함수의 출력이 약간 달라질 수 있어요. 이런 일이 발생할 수 있는 몇 가지 예시예요.

  1. 초월적 SQL 수학 함수는 해당 시스템 C 라이브러리 함수를 호출해 동작해요. 예를 들어 "sin(X)" SQL 함수는 표준 C 수학 라이브러리의 sin() 함수를 호출할 뿐이에요. 오늘날 사용되는 다양한 C 수학 라이브러리는 놀라울 정도로 일관돼요. 그럼에도 C 언어 표준에는 sin(X)가 시스템 간에 정확히 같은 값을 반환하도록 요구하는 것이 없어요. sin(X)는 "정확한 값"이라 간주되려면 올바른 값의 1~2 ULP 안에 있는 "근사한" 숫자를 반환하기만 하면 돼요.

  2. 시스템의 ICU 확장 함수 "lower()"를 사용하고 시스템의 libicu.so에 링크한다고 가정해 보세요. 그러면 시스템 libicu.so를 더 새 버전으로 업그레이드하는 일이 발생할 수 있는데, 아마 다른 유니코드 버전일 거예요. 극단적인 모서리 경우에서 lower() 함수의 출력이 아주 약간 바뀔 수 있어요.

  3. 때때로 SQLite의 내장 함수에 버그 수정이나 업그레이드가 있어서 극단적인 모서리 경우에서 그 동작이 아주 약간 바뀔 수 있어요. 개발자조차 그 변화를 인지하지 못하는 경우도 있어요.

  4. 데이터베이스가 애플리케이션 정의 SQL 함수(ADF)를 사용하고 있는데, 그 ADF에 버그 수정이나 개선이 있다면 반환되는 값이 달라질 수 있어요.

3번 경우의 주목할 만한 사례는 SQLite 내부의 textfloat 변환 루틴이 정확성 또는 성능 개선을 위해 업그레이드될 때예요. 예를 들어 버전 3.42에서 3.43으로 이동할 때, 그리고 다시 3.51에서 3.52로 이동할 때 일어났어요. textfloat 변환을 강제하기 위해 명시적인 "CAST(...AS REAL)"가 있을 필요는 없어요. 변환은 자동 타입 강제 변환 때문에 발생할 수 있어요. 또 다른 흔한 "숨겨진" textfloat 변환은 ->> 연산자로 JSON 문서에서 부동소수점 값을 추출할 때 발생해요. Unix epoch 이후의 부동소수점 초 수인 "mtime" 값의 범위에 따라 대량의 JSON 문서 묶음을 검색할 수 있기를 원한다고 가정해 보세요. 다음과 같이 쓸 수 있어요.

CREATE INDEX doc_mtime ON docstore(doc->'mtime');

그런 다음 textfloat 변환을 다르게 구현하는 한 SQLite 버전에서 다른 버전으로 전환하면, "doc->'mtime'" 표현식에서 인덱스에 저장된 값과 1 ULP 다른 값을 얻을 때가 있어요.

표현식 인덱스의 표현식에 대해 계산된 값이 실제로 인덱스에 저장되어 인덱스 키로 사용되는 값과 다르면 그것을 "오래된 표현식 인덱스"라고 불러요.

오래된 표현식 인덱스는 정말 문제가 되나요? (Are Stale Expression Indexes Really A Problem?)

오래된 표현식 인덱스는 드물게 발생해요. 실제로 마주칠 가능성은 낮아요.

2023년, SQLite 개발자들이 오래된 표현식 인덱스가 존재할 수 있고 문제를 일으킬 수 있다는 것을 깨닫기 거의 3년 전에, SQLite 내부의 textfloat 변환 로직에 업데이트가 있었고 이로 인해 일부 부동소수점 값 계산이 1 ULP만큼 이동했어요. 이 변경은 오래된 표현식 인덱스를 일으켰어요. (우리가 돌아가서 시험해 봤기 때문에 압니다.) 그런데도 그 변경이 수십억 대의 휴대전화와 개인용 컴퓨터에 들어갔음에도 SQLite 개발자들은 그로 인한 인덱스 손상 보고를 한 번도 받지 못했어요.

오래된 표현식 인덱스는 발생하더라도 보통 애플리케이션에 문제를 일으키지 않아요. 오래된 값이 1 ULP 차이가 나는 부동소수점 숫자(실제 현장에서 관찰된 유일한 경우)라면 그 오류는 보통 검색에 문제를 일으키지 않아요. 이상한 항목이 검색 범위의 경계에 정확히 있을 때는 결과가 달라질 수 있지만, 부동소수점 값은 근사치예요. 확실히 가까운 근사치지만 어쨌든 근사치이므로, 검색 경계에 정확히 있는 항목을 놓치는 것은 보통 문제로 간주되지 않아요.

오래된 표현식 인덱스 항목이 있는 행을 DELETE나 UPDATE하려고 하면 이전 SQLite 버전에서는 오류가 발생해요. (단, SQLite 3.53.0(2026-04-09) 이상에서는 그렇지 않아요. 아래 참조). 또 PRAGMA integrity_check를 실행하면 오류가 나타나요. 하지만 그런 문제는 REINDEX를 실행하면 쉽게 해결돼요. 현장의 사용자들이 그런 문제를 겪었다 해도 SQLite 개발자에게 알린 적은 없어요.

요약하면, 오래된 표현식 인덱스는 드물게 발생하고, 발생하더라도 애플리케이션에 치명적이지 않아요. SQLite 전문가가 되려는 사람들은 오래된 표현식 인덱스에 대해 알아야 하지만, 평균적인 개발자에게 오래된 표현식 인덱스는 결코 문제가 되지 않아야 해요.

자기 치유 표현식 인덱스 (Self-Healing Expression Indexes)

SQLite 버전 3.53.0(2026-04-09)부터 SQLite는 어떤 상황에서는 오래된 표현식 인덱스를 자동으로 고치려고 해요. 오래된 표현식 인덱스 항목과 연관된 테이블 행을 DELETE나 UPDATE하려고 할 때 오류를 발생시키는 대신, SQLite는 이제 인덱스를 고쳐요. 오류가 발생하지 않아요. 애플리케이션은 무언가 잘못되었다는 것을 결코 알지 못해요.

PRAGMA integrity_check 명령은 여전히 출력에 오래된 인덱스 항목을 보고해요. 하지만 오래된 항목이 1~2 ULP만 차이가 나는 부동소수점 숫자인 일반적인 경우라면, 인덱스가 손상되었다고 말하는 대신 integrity_check가 내는 메시지는 다음과 같은 형태예요.

index NAME stores an imprecise floating-point value for row N

(인덱스 NAME이 행 N에 대해 부정확한 부동소수점 값을 저장하고 있습니다)

오래된 표현식 인덱스 피하거나 고치는 방법 (How To Avoid And/Or Fix Stale Expression Indexes)

오래된 표현식 인덱스를 만날 가능성을 전혀 원하지 않는다면 표현식 인덱스를 사용하지 마세요. 좋은 대안은 자동으로 계산하려는 표현식에 대해 STORED 생성 열을 만들고, 그 STORED 생성 열에 인덱스를 만드는 것이에요.

위에 보여준 doc_mtime 인덱스 예시는 다음과 같이 바뀔 거예요.

CREATE TABLE doc(
  doc JSON,
  mtime AS (doc->'mtime') STORED
);
CREATE INDEX doc_mtime ON doc(mtime);

직접 설계하지 않은 데이터베이스가 있고 어떤 인덱스가 표현식 인덱스인지 알고 싶다면 CLI(버전 3.53.0 이상)에서 데이터베이스를 열고 다음 명령을 입력하면 돼요.

.indexes --expr

오래된 표현식 인덱스가 있다고 생각하면(아마 인덱스 표현식에 사용된 ADF를 바꿨다는 것을 안다면) REINDEX 명령을 실행해 모든 인덱스를 새로 고칠 수 있어요. SQLite 버전 3.53.0(2026-04-09)부터는 시간과 I/O를 절약하기 위해 다음을 실행할 수 있어요.

REINDEX EXPRESSIONS;

REINDEX EXPRESSIONS 명령은 표현식 인덱스만 다시 만들어 다른 인덱스는 그대로 둬요. 데이터베이스에 표현식 인덱스가 없으면(보통은 그렇죠) REINDEX EXPRESSIONS는 아무것도 하지 않아요.

더 알아보기 (Learn more)