PostgreSQL 인덱스 최적화 방법

소개

이 랩에서는 인덱싱을 통해 PostgreSQL 데이터베이스 성능을 최적화하는 방법을 배우게 됩니다. 샘플 users 테이블을 생성하고 데이터를 채우는 것부터 시작합니다. 그런 다음 단일 열 인덱스를 생성하고, EXPLAIN을 사용하여 쿼리 계획을 분석하며, 다중 열 인덱스를 구축하고, 마지막으로 사용되지 않는 인덱스를 제거하여 데이터베이스 효율성을 유지하는 방법을 배웁니다. 이 실습 경험을 통해 PostgreSQL 인덱스 관리에 대한 실질적인 기술을 습득할 수 있습니다.

단일 열 인덱스 생성

이 단계에서는 users라는 샘플 테이블을 생성한 다음 email 열에 단일 열 인덱스를 생성합니다. 인덱스는 데이터베이스 쿼리 성능을 향상시키는 데 매우 중요하며, 특히 대규모 테이블을 다룰 때 더욱 그렇습니다.

먼저 postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.

sudo -u postgres psql

이제 users 테이블을 생성합니다. 다음 SQL 명령을 실행합니다.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

이 명령은 id, username, email, created_at 열을 가진 users라는 테이블을 생성합니다. id 열은 기본 키이며 자동으로 증가합니다.

다음으로 users 테이블에 샘플 데이터를 삽입합니다. 다음 SQL 명령을 실행합니다.

INSERT INTO users (username, email, created_at) VALUES
('john_doe', 'john.doe@example.com', NOW()),
('jane_smith', 'jane.smith@example.com', NOW()),
('peter_jones', 'peter.jones@example.com', NOW()),
('mary_brown', 'mary.brown@example.com', NOW()),
('alice_wilson', 'alice.wilson@example.com', NOW()),
('bob_davis', 'bob.davis@example.com', NOW()),
('carol_garcia', 'carol.garcia@example.com', NOW()),
('david_martinez', 'david.martinez@example.com', NOW()),
('emma_anderson', 'emma.anderson@example.com', NOW()),
('frank_taylor', 'frank.taylor@example.com', NOW());

-- 인덱스 사용에 충분하도록 테이블을 크게 만들기 위해 추가 행 삽입
INSERT INTO users (username, email, created_at)
SELECT
    'user_' || generate_series(1, 1000),
    'user' || generate_series(1, 1000) || '@example.com',
    NOW();

이제 users 테이블에 1000 개 이상의 행을 삽입했습니다. 이 더 큰 데이터셋은 인덱스 사용을 더 효과적으로 시연하는 데 도움이 될 것입니다. PostgreSQL 은 일반적으로 전체 테이블을 스캔하는 것보다 성능 이점을 제공하는 경우 인덱스를 사용하기 때문입니다.

email 열을 기반으로 쿼리를 더 빠르게 실행하려면 email 열에 인덱스를 생성합니다. 다음 SQL 명령을 실행합니다.

CREATE INDEX idx_users_email ON users (email);

이 명령은 users 테이블의 email 열에 idx_users_email이라는 인덱스를 생성합니다.

인덱스가 생성되었는지 확인하려면 psql에서 \di 명령을 사용할 수 있습니다. 다음 명령을 실행합니다.

\di

출력에서 idx_users_email 인덱스가 나열된 것을 볼 수 있습니다.

마지막으로 다음을 입력하여 psql 셸을 종료합니다.

\q

EXPLAIN 을 사용하여 쿼리 계획 분석

이 단계에서는 PostgreSQL 의 EXPLAIN 명령을 사용하여 쿼리 계획을 분석하는 방법을 배웁니다. 쿼리 계획을 이해하는 것은 데이터베이스 쿼리를 최적화하고 효율적인 성능을 보장하는 데 필수적입니다.

먼저 postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.

sudo -u postgres psql

이제 EXPLAIN 명령을 사용하여 간단한 쿼리를 분석해 보겠습니다. 다음 명령을 실행합니다.

EXPLAIN SELECT * FROM users WHERE email = 'jane.smith@example.com';

이 명령은 SELECT 문의 쿼리 계획을 표시합니다. 더 큰 데이터셋을 사용하면 출력에서 "Index Scan" 또는 "Bitmap Index Scan"이 표시되어 PostgreSQL 이 idx_users_email 인덱스를 사용하여 특정 이메일 주소를 가진 행을 효율적으로 찾고 있음을 나타냅니다.

비용을 포함한 더 자세한 정보를 얻으려면 EXPLAIN ANALYZE를 사용할 수 있습니다. 그러나 이 기본 예제에서는 EXPLAIN으로 충분합니다.

이제 이메일 인덱스를 사용하지 않는 쿼리를 분석해 보겠습니다. 다음 명령을 실행합니다.

EXPLAIN SELECT * FROM users WHERE username LIKE 'user_%';

출력에는 users 테이블에 대한 "Seq Scan" (Sequential Scan) 이 표시됩니다. 이는 PostgreSQL 이 일치하는 행을 찾기 위해 전체 테이블을 스캔하고 있음을 의미합니다. 이는 다음과 같은 이유로 발생합니다.

username 열에 인덱스가 없습니다.
끝에 와일드카드가 있는 LIKE 연산자는 인덱스의 이점을 얻을 수 있지만, 인덱스가 없으면 PostgreSQL 은 모든 행을 스캔해야 합니다.

이는 WHERE 절에서 자주 사용되는 열에 인덱스를 생성하는 것의 중요성을 보여줍니다.

EXPLAIN으로 쿼리 계획을 분석함으로써 잠재적인 성능 병목 현상을 식별하고 인덱스가 효과적으로 사용되고 있는지 확인할 수 있습니다.

마지막으로 다음을 입력하여 psql 셸을 종료합니다.

\q

다중 열 인덱스 빌드

이 단계에서는 PostgreSQL 에서 다중 열 인덱스를 생성하는 방법을 배웁니다. 다중 열 인덱스는 두 개 이상의 열을 포함하는 인덱스입니다. 여러 열을 동시에 필터링하거나 정렬하는 쿼리의 성능을 크게 향상시킬 수 있습니다.

먼저 postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.

sudo -u postgres psql

users 테이블을 username과 email 열 모두를 기준으로 자주 쿼리한다고 가정해 보겠습니다. 이러한 쿼리를 최적화하기 위해 이 두 열에 다중 열 인덱스를 생성할 수 있습니다. 다음 SQL 명령을 실행합니다.

CREATE INDEX idx_users_username_email ON users (username, email);

이 명령은 users 테이블의 username 및 email 열에 idx_users_username_email이라는 인덱스를 생성합니다. 인덱스 정의에서 열의 순서는 중요합니다. 쿼리가 인덱스에 나타나는 순서와 동일한 순서로 열을 필터링할 때 인덱스가 가장 효과적입니다.

인덱스가 생성되었는지 확인하려면 psql에서 \di 명령을 사용할 수 있습니다. 다음 명령을 실행합니다.

\di

출력에서 idx_users_username_email 인덱스가 나열된 것을 볼 수 있습니다.

이제 이 다중 열 인덱스의 이점을 얻을 수 있는 쿼리를 분석해 보겠습니다. 다음 명령을 실행합니다.

EXPLAIN SELECT * FROM users WHERE username = 'user_1' AND email = 'user1@example.com';

출력에서는 PostgreSQL 이 쿼리를 효율적으로 실행하기 위해 idx_users_username_email 인덱스를 사용하고 있음을 보여야 합니다. 쿼리 계획에서 "Index Scan" 또는 "Bitmap Index Scan"이 표시되어 다중 열 인덱스가 활용되고 있음을 나타냅니다.

마지막으로 다음을 입력하여 psql 셸을 종료합니다.

\q

사용하지 않는 인덱스 제거

이 단계에서는 PostgreSQL 에서 사용되지 않는 인덱스를 제거하는 방법을 배웁니다. 인덱스는 쿼리 성능을 향상시키지만 저장 공간을 소비하고 쓰기 작업 (삽입, 업데이트, 삭제) 을 느리게 할 수도 있습니다. 따라서 더 이상 사용되지 않는 인덱스를 식별하고 제거하는 것이 중요합니다.

먼저 postgres 사용자로 PostgreSQL 데이터베이스에 연결합니다.

sudo -u postgres psql

쿼리 패턴을 분석한 후 idx_users_email 인덱스가 거의 사용되지 않는다고 판단한다고 가정해 보겠습니다. 이 인덱스를 제거하려면 다음 SQL 명령을 실행합니다.

DROP INDEX idx_users_email;

이 명령은 데이터베이스에서 idx_users_email이라는 인덱스를 제거합니다.

인덱스가 제거되었는지 확인하려면 psql에서 \di 명령을 사용할 수 있습니다. 다음 명령을 실행합니다.

\di

출력에서 더 이상 idx_users_email 인덱스가 나열된 것을 볼 수 없을 것입니다.

인덱스를 삭제하기 전에 실제로 사용되지 않는지 확인하는 것이 중요합니다. PostgreSQL 의 통계 수집기 (statistics collector) 를 사용하여 인덱스 사용에 대한 정보를 수집할 수 있습니다. 그러나 이러한 통계를 활성화하고 분석하는 것은 이 실습의 범위를 벗어납니다. 실제 시나리오에서는 인덱스를 삭제하기 전에 일정 기간 동안 인덱스 사용량을 모니터링하게 됩니다.

참고: PostgreSQL 의 쿼리 플래너는 지능적이므로 인덱스가 성능 이점을 제공할 때만 사용합니다. 매우 작은 테이블 (일반적으로 몇백 행 미만) 의 경우 인덱스 사용 오버헤드가 이점보다 크기 때문에 PostgreSQL 은 인덱스 스캔보다 순차 스캔을 선택할 수 있습니다. 이것이 우리가 users 테이블에 많은 행을 추가한 이유입니다. 현실적인 인덱스 사용 시나리오를 보여주기 위해서입니다.

잘못된 인덱스를 삭제하면 쿼리 성능에 부정적인 영향을 미칠 수 있습니다. 따라서 주의를 기울이고 인덱스를 제거하기 전에 쿼리 패턴을 철저히 분석하십시오.

마지막으로 다음을 입력하여 psql 셸을 종료합니다.

\q

요약

이 실습에서는 쿼리 성능을 향상시키기 위해 PostgreSQL 에서 단일 열 및 다중 열 인덱스를 생성하는 방법을 배웠습니다. 또한 EXPLAIN을 사용하여 쿼리 계획을 분석하여 인덱스가 효과적으로 사용되고 있는지 확인하는 방법을 배웠습니다. 주요 내용은 다음과 같습니다.

PostgreSQL 의 쿼리 플래너는 테이블 크기와 쿼리 특성에 따라 인덱스 사용 여부를 지능적으로 선택합니다.
인덱스는 인덱스 조회 비용이 모든 행을 스캔하는 비용보다 적은 대형 테이블에 가장 유익합니다.
다중 열 인덱스는 여러 열을 기준으로 필터링하는 쿼리의 성능을 크게 향상시킬 수 있습니다.
사용되지 않는 인덱스를 정기적으로 모니터링하고 제거하면 최적의 데이터베이스 성능을 유지하는 데 도움이 됩니다.

이러한 기술은 실제 애플리케이션에서 PostgreSQL 데이터베이스 성능을 최적화하는 데 필수적입니다.