이 랩에서는 인덱싱을 통해 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이 단계에서는 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 psqlusers 테이블을 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 데이터베이스 성능을 최적화하는 데 필수적입니다.
