postgreSql using brin index란?

PostgreSql using brin index란?

BRIN (Block Range INdex)은 PostgreSQL에서 제공하는 인덱스 유형 중 하나입니다. BRIN 인덱스는 대량의 데이터를 가진 테이블에서 성능을 향상시키기 위해 설계되었습니다. BRIN은 블록 범위를 기반으로 한 인덱스로, 특히 정렬된 데이터에 적합합니다. 

CREATE TABLE member (
    id         integer,
    created_at timestamp,
    -- other columns
);

-- BRIN 인덱스 생성
CREATE INDEX idx_created_at ON member USING BRIN (created_at);

 

 

BRIN 인덱스는 데이터를 블록 단위로 그룹화하고 각 블록에 대한 최소값과 최대값을 저장합니다. 블록은 데이터를 물리적으로 저장하는 단위를 나타냅니다. PostgreSQL에서 블록은 일반적으로 8KB의 고정 크기로 정의됩니다. 데이터베이스 시스템은 이러한 블록을 사용하여 디스크 공간을 관리하고 데이터를 읽거나 쓰는 작업을 수행합니다.

 

BRIN 인덱스는 이러한 블록을 기반으로 동작합니다. 데이터를 블록 단위로 묶어서 인덱스를 생성하고 관리합니다. 이때 각 블록에 대해 해당하는 최소값과 최대값을 저장하게 됩니다. 블록은 일반적으로 정렬된 데이터 범위를 포함하며, BRIN 인덱스는 이러한 블록을 활용하여 성능을 향상시킵니다. 특히 시간 기반 데이터의 경우, 블록은 시간 범위에 해당하게 되고 각 블록에는 해당 시간 범위 내의 최소값과 최대값이 저장됩니다.

 

예를 들어, 8KB의 블록 크기로 가정하면, BRIN 인덱스는 8KB 블록 단위로 최소값과 최대값을 저장하여 쿼리에서 특정 시간 범위를 찾는 작업을 빠르게 수행할 수 있게 됩니다. 블록을 사용함으로써 인덱스의 크기를 줄이고, 특정 시간 범위에 대한 쿼리를 빠르게 처리할 수 있도록 최적화됩니다. 아래의 그림은 각 블록에 대한 timestamp 컬럼의 최소값과 최대값을 저장한 그림입니다.

 

 

예를 들어, 블록의 범위가 2023-01-01 12:00:00부터 2023-01-01 18:00:00인 경우, 이 블록에는 timestamp의 최소값은 2023-01-01 12:00:00이고 최대값은 2023-01-01 18:00:00입니다. 이런 식으로 블록 범위마다 최소값과 최대값을 저장하여 쿼리 최적화에 활용합니다.

 

이처럼 데이터를 묶어 저장하므로 인덱스 크기가 상대적으로 작고 쿼리 속도를 높일 수 있습니다. 특히 정렬된 범위 데이터에 효과적이며, 시계열 데이터나 유사한 형태의 데이터에 적합합니다. 주로 시계열 데이터, 로그 데이터, 이벤트 데이터 등이 포함됩니다. 다음은 BRIN 인덱스가 시간 기반 데이터에 어떻게 적합한지에 대한 구체적인 설명입니다:

1. 시간 범위 스캔 최적화: 시간 기반 데이터에서는 주로 최신 데이터에 대한 쿼리 또는 특정 시간 범위의 데이터에 대한 쿼리가 자주 수행됩니다. BRIN 인덱스는 각 블록의 최소 및 최대 값만을 저장하므로, 특정 시간 범위에 대한 스캔 작업을 효과적으로 최적화할 수 있습니다.
2. 인덱스 크기 최소화: BRIN 인덱스는 블록 범위에 대한 최소 및 최대 값만을 저장하기 때문에, 인덱스 크기가 다른 인덱스 유형에 비해 상대적으로 작을 수 있습니다. 이는 디스크 공간을 절약하고 메모리 효율성을 높일 수 있습니다.
3. 대규모 테이블 성능 향상: BRIN 인덱스는 대량의 데이터를 다루는 테이블에서 성능을 향상시킬 수 있습니다. 특히 데이터가 시간에 따라 정렬되어 있고 쿼리 패턴이 최신 데이터에 집중되는 경우에 이점을 얻을 수 있습니다.

그럼 8KB면 정렬 범위 조건도 제한적인거 아닌가요?

BRIN 인덱스의 블록 크기가 8KB인 경우, 각 블록에는 최소값과 최대값을 저장하게 되며, 이는 8KB의 블록 단위로 데이터를 처리하는 것을 의미합니다. 이로 인해 정렬된 데이터 범위를 조건으로 하는 쿼리에 있어서는 제한적인 측면이 있을 수 있습니다.

 

블록의 크기가 작다는 것은 블록 안에 저장되는 데이터의 범위가 제한적이라는 의미이므로, 특정 쿼리에서 인덱스를 효과적으로 활용할 수 있을지에 대한 고려가 필요합니다. 특히, 정렬된 데이터 중에서도 특정 범위를 넘어가는 경우에는 블록 크기 제한이 인덱스의 효과를 제한할 수 있습니다. 이러한 제약을 고려하여 BRIN 인덱스를 사용할 때는 다음과 같은 상황에서 성능을 기대하기 어려울 수 있습니다:

• 블록 크기를 넘어가는 정렬된 데이터 범위: 블록 크기보다 큰 정렬된 데이터 범위를 조회하는 경우에는 BRIN 인덱스의 효과가 제한될 수 있습니다.
• 최신 데이터가 블록을 넘어가는 경우: 특히 시간 기반 데이터에서 최신 데이터가 블록을 넘어가는 경우, 최신 데이터에 대한 검색이나 범위 쿼리에서 블록 크기의 제약이 영향을 줄 수 있습니다.

이러한 제한 사항을 고려하여 데이터의 특성과 쿼리 패턴을 분석한 후, BRIN 인덱스를 적절하게 활용하는 것이 중요합니다. 데이터의 크기, 정렬 상태, 쿼리의 특성에 따라 BRIN 인덱스가 최적인 상황을 판단하는 것이 좋습니다.

블록의 크기가 8KB면 row의 수는 대략 어느정도 될까요?

블록 크기가 8KB인 경우, 로우의 수는 저장되는 데이터의 크기에 따라 다릅니다. PostgreSQL의 블록은 고정 크기(8KB)이므로 실제로 저장되는 로우의 수는 해당 로우의 크기에 따라 결정됩니다.

 

블록에는 최소값과 최대값을 저장하기 위한 인덱스 정보 외에도 실제 데이터 로우가 저장됩니다. 또한, PostgreSQL는 다양한 메타데이터 및 관리 정보를 블록에 저장하므로 실제 데이터로우의 수는 그에 따라 감소할 것입니다.

 

로우 크기가 블록 크기를 넘어가지 않는 한, 블록에는 여러 로우가 저장됩니다. 예를 들어, 로우 크기가 100바이트인 경우, 8KB 블록에는 대략 80개의 로우가 저장될 수 있습니다. 그러나 이는 블록에 저장되는 다른 정보들과 실제 데이터의 구조에 따라 변할 수 있습니다.

이러한 계산은 정확한 예측을 위해서는 실제 데이터와 테이블 구조에 대한 자세한 정보가 필요하며, 예상치 못한 오버헤드 및 추가 메타데이터를 고려해야 합니다.

BRIN 인덱스도 별도의 인덱스 테이블을 생성할까요?

BRIN 인덱스는 기본적으로 기존의 테이블과는 별도로 관리되는 별도의 인덱스 테이블을 생성하지 않습니다. BRIN 인덱스는 원래의 데이터 테이블과 함께 존재하며, 각 블록에 대한 최소값과 최대값을 저장하는 데 사용됩니다.

 

BRIN 인덱스는 블록 범위의 정보를 추적하기 위해 테이블 자체의 메타데이터를 사용합니다. 인덱스 데이터는 블록 범위에 대한 최소값과 최대값을 저장하는 식으로 테이블과 함께 관리됩니다. 이러한 정보는 BRIN 인덱스를 사용하여 범위 쿼리를 최적화하는 데 사용됩니다.

따라서 BRIN 인덱스를 사용하는 경우 별도의 인덱스 테이블이 생성되지 않고, 기존 테이블과 연결되어 사용됩니다.

메타데이터 테이블은 어디에 존재하나요?

BRIN 인덱스의 메타데이터는 PostgreSQL 시스템 카탈로그 테이블에 저장됩니다. PostgreSQL은 시스템 카탈로그를 사용하여 데이터베이스의 메타데이터를 관리합니다. 이러한 메타데이터는 데이터베이스 객체(테이블, 인덱스 등)에 대한 정보를 담고 있습니다.

 

BRIN 인덱스의 경우, 해당 테이블과 관련된 메타데이터는 pg_class 시스템 카탈로그 테이블에 저장됩니다. pg_class 테이블에는 데이터베이스 내의 모든 테이블 및 인덱스에 대한 정보가 포함되어 있습니다. BRIN 인덱스는 특히 pg_class의 relkind 필드가 'i' (인덱스)이면서 relname 필드에 인덱스의 이름이 있는 레코드를 찾아서 해당하는 정보를 얻습니다.

 

블록 범위의 최소값과 최대값은 pg_class에 있는 pg_statistic 시스템 카탈로그 테이블에서도 찾을 수 있습니다. 이 테이블은 테이블과 인덱스의 통계 정보를 저장합니다.

 

블록 범위의 최소값과 최대값은 pg_class와 pg_statistic 등의 시스템 카탈로그에서 관리되지만 직접적으로 접근하기보다는 PostgreSQL가 이 정보를 활용하여 쿼리를 최적화하는 데 사용됩니다. 직접적인 조작이 필요한 경우에는 이러한 시스템 카탈로그 테이블을 읽어들여서 분석할 수 있습니다.