우리 이미 여러 번 인덱스가 어떻게 쿼리 속도를 올려주고, 데이터베이스가 전부 다 뒤지지 않게 도와주는지 얘기했었지. 이제 진짜로 인덱스를 어떻게 만드는지, CREATE INDEX 명령어에 어떤 파라미터가 있는지, 그리고 언제 UNIQUE나 CONCURRENTLY 같은 옵션을 써야 하는지 알아보자. 이런 것들 알아두면 그냥 인덱스 쓰는 게 아니라, 제대로 관리할 수 있게 돼.
CREATE INDEX 문법
인덱스는 CREATE INDEX 명령어로 만들 수 있어. 기본 문법은 이래:
CREATE INDEX index_name
ON table_name (column_name);
index_name— 인덱스 이름. 인덱스가 뭘 위한 건지 알 수 있게 짓는 게 좋아. 예를 들어idx_users_email은users테이블의email컬럼에 인덱스 만든 거란 뜻이야.table_name— 인덱스를 만들 테이블 이름.column_name— 인덱스 걸 컬럼.
간단한 예시 들어볼게. users라는 테이블이 있다고 하자:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
age INT
);
email 필드로 사용자 찾는 걸 빠르게 하고 싶어. 인덱스 만들어보자:
CREATE INDEX idx_users_email
ON users (email);
이제 이런 식으로 쿼리할 때:
SELECT * FROM users WHERE email = 'example@example.com';
PostgreSQL이 idx_users_email 인덱스를 써서 원하는 행을 금방 찾아줄 거야.
유니크 인덱스 (UNIQUE)
유니크 인덱스는 지정한 컬럼(들)에 값이 중복되지 않게 보장해주는 거야. 만약 중복된 값을 넣으려고 하면 PostgreSQL이 막아버려.
유니크 인덱스는 email, username 같은 중복되면 안 되는 키에 자주 써. 다른 식별자들도 마찬가지고.
유니크 인덱스 만드는 문법
유니크 인덱스 만드는 문법은 일반 인덱스랑 거의 똑같은데, UNIQUE 키워드만 추가하면 돼:
CREATE UNIQUE INDEX index_name
ON table_name (column_name);
예를 들어, users 테이블에서 email이 유니크해야 한다면, 두 명이 같은 이메일을 못 쓰게 이렇게 하면 돼:
CREATE UNIQUE INDEX idx_users_email_unique
ON users (email);
이제 만약 이런 식으로 쿼리하면:
INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);
INSERT INTO users (name, email, age) VALUES ('Jane', 'john@example.com', 25);
PostgreSQL이 에러를 던질 거야. 왜냐면 email은 유니크해야 하니까.
CONCURRENTLY 파라미터로 인덱스 만들기
상상해봐, 너가 프로덕션에서 엄청 큰 테이블을 다루고 있는데, 계속 데이터가 들어오고 있어(예를 들면 주문 같은 거). 그냥 CREATE INDEX로 인덱스를 만들면, 그 테이블이 잠겨서 다른 쿼리들이 insert, update, delete 못 해. 이러면 서비스가 멈출 수도 있지. 이런 상황을 피하려면 CONCURRENTLY 파라미터로 "비동기" 인덱스를 만들 수 있어.
문법
CREATE INDEX CONCURRENTLY index_name
ON table_name (column_name);
CONCURRENTLY 키워드는 PostgreSQL한테 인덱스를 테이블 잠그지 않고, 병렬로 만들라고 알려주는 거야.
예를 들어, orders라는 테이블이 있고, 거기에 수백만 개의 주문이 계속 쌓인다고 해보자:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_number VARCHAR(50) NOT NULL,
order_date DATE NOT NULL,
customer_id INT NOT NULL
);
order_date로 검색을 빠르게 하고 싶은데, 테이블을 잠그고 싶지 않다면:
CREATE INDEX CONCURRENTLY idx_orders_order_date
ON orders (order_date);
이렇게 하면 데이터베이스가 테이블을 잠그지 않고 인덱스를 만들어주니까, 유저들은 아무것도 못 느껴.
CONCURRENTLY의 특징 몇 가지:
- 인덱스가 일반 모드보다 느리게 만들어져. PostgreSQL이 여러 단계로 작업하거든.
- 만약 인덱스 만드는 중에 에러(예: 중복 데이터)가 나면, 직접 지우고 다시 만들어야 해.
추가 인덱싱 파라미터
PostgreSQL은 인덱스 만들 때 추가 파라미터도 쓸 수 있어. 예를 들어, 여러 컬럼을 한 번에 인덱싱할 수 있지. 이건 여러 필드로 자주 쿼리할 때 유용해.
CREATE INDEX idx_users_name_email
ON users (name, email);
이제 WHERE name = 'John' AND email = 'john@example.com' 같은 조건으로 쿼리하면 더 빨라질 거야.
한 컬럼씩 인덱스 두 개 만드는 거랑, 두 컬럼을 한 번에 인덱스 만드는 건 완전 달라! 여러 컬럼 인덱스는 WHERE에 그 컬럼들이 다 들어갈 때만 효과가 있어.
에러 예시와 해결법
인덱스 만들 때 여러 가지 에러를 만날 수 있어. 대표적인 것들 몇 개 볼게:
유니크 인덱스 만들 때 중복 데이터 에러. 이미 테이블에 중복된 행이 있으면 PostgreSQL이 유니크 인덱스를 못 만들어. 이럴 땐 먼저 중복을 지우거나 수정해야 해.
DELETE FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(email) > 1
);
인덱스 만들 때 락 에러. 그냥 인덱스 만들면 실서비스에서 클라이언트가 느려지거나 에러날 수 있어. CONCURRENTLY 파라미터를 써서 이런 문제를 피하자.
이제 상상해봐, 너가 회사에서 수백만 개 데이터가 있는 데이터베이스를 최적화하라고 맡겨졌어. 인덱스를 잘 쓰면 병목을 찾고, 유저 경험을 확 올릴 수 있어. 예를 들어, 인덱스 하나 잘 추가하면 쿼리 시간이 10초에서 몇 밀리초로 줄어들 수도 있지. 멋지지 않아?
GO TO FULL VERSION