software

대규모 데이터 애플리케이션을 위한 PostgreSQL 쿼리 최적화

발행: (2025년 12월 27일 오후 01:31 GMT+9)
11 min read
원문: Dev.to

Source: Dev.to

위에 제공된 링크에 포함된 전체 텍스트를 알려주시면, 해당 내용을 한국어로 번역해 드리겠습니다. 코드 블록이나 URL은 그대로 유지하고, 마크다운 형식과 기술 용어는 원본 그대로 보존합니다. 텍스트를 복사해서 붙여넣어 주세요.

소개

지난 2년 동안 우리는 쿼리 시간을 > 15 초에서 훨씬 낮은 값으로 줄였습니다.

❌ 쿼리 시간: 18 초 – 응답성이 저하되었습니다.

2. 인덱싱 전략

2.1 B‑tree 인덱스 (동등 및 범위)

-- Single‑column indexes
CREATE INDEX idx_orders_created_at   ON orders(created_at);
CREATE INDEX idx_orders_customer_id  ON orders(customer_id);

-- Composite index for multi‑column filter
CREATE INDEX idx_orders_customer_date
    ON orders(customer_id, created_at);

영향: 쿼리 시간 ↓ 8 seconds.

2.2 파셜 인덱스 (활성 행만 인덱싱)

CREATE INDEX idx_orders_active
    ON orders(created_at)
    WHERE status = 'active';

인덱스 크기를 약 60 % 감소시킵니다.

영향: 쿼리 시간 ↓ 4.5 seconds.

2.3 JSONB 컬럼용 GIN 인덱스

CREATE INDEX idx_orders_metadata
    ON orders USING GIN (metadata);
-- Fast JSONB lookup
SELECT *
FROM orders
WHERE metadata @> '{"priority": "high"}';

빠른 JSONB 조회

2.4 모니터링 및 유지보수

-- Index usage statistics
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
-- Drop unused indexes
DROP INDEX IF EXISTS unused_index_name;

-- Rebuild bloated indexes concurrently
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

사용되지 않는 인덱스 삭제
비대해진 인덱스를 동시에 재구축

3. 쿼리‑레벨 최적화

3.1 필요한 컬럼만 선택

-- ❌ Bad
SELECT * FROM orders WHERE status = 'active';

-- ✅ Good
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'active';

Impact: 데이터 전송 시간 약 40 % 감소.

3.2 Explain & Analyze

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

주요 지표 확인

플랜 노드해석
Seq Scan불량 – 인덱스 필요
Index Scan양호
Bitmap Heap Scan대량 결과 집합에 적합
Nested Loop대형 테이블에 부적합
Hash Join대형 테이블에 적합

3.3 조인 유형 선택

-- ❌ Bad: many LEFT JOINs returning NULLs
SELECT o.*, c.*, p.*, s.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
LEFT JOIN products  p ON o.product_id = p.id
LEFT JOIN shipments s ON o.id = s.order_id;

-- ✅ Good: use INNER JOIN when rows must exist
SELECT o.id, o.total_amount, c.name, p.title
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products  p ON o.product_id = p.id
WHERE o.status = 'completed';

4. 파티셔닝

4.1 created_at 기준 범위 파티셔닝

-- Parent table
CREATE TABLE orders (
    id           SERIAL,
    customer_id  INTEGER,
    total_amount DECIMAL(10,2),
    created_at   TIMESTAMP NOT NULL,
    status       VARCHAR(50)
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE orders_2024_01 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- Index on each partition
CREATE INDEX idx_orders_2024_01_customer
    ON orders_2024_01(customer_id);

영향: 날짜 범위 스캔이 10배 빨라졌습니다.

4.2 status 기준 리스트 파티셔닝

CREATE TABLE orders (
    id           SERIAL,
    customer_id  INTEGER,
    status       VARCHAR(50),
    created_at   TIMESTAMP
) PARTITION BY LIST (status);

CREATE TABLE orders_active PARTITION OF orders
FOR VALUES IN ('pending', 'processing', 'shipped');

CREATE TABLE orders_completed PARTITION OF orders
FOR VALUES IN ('delivered', 'completed');

5. 연결 풀링 (PgBouncer)

pgbouncer.ini

[databases]
myapp_db = host=localhost port=5432 dbname=production

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
pool_mode   = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5

6. Node.js 구현

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  port: 6432,               // PgBouncer port
  database: 'myapp_db',
  user: 'app_user',
  password: process.env.DB_PASSWORD,
  max: 20,                  // max connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Efficient query execution
async function getActiveOrders() {
  const client = await pool.connect();
  try {
    const result = await client.query(
      'SELECT id, total_amount FROM orders WHERE status = $1',
      ['active']
    );
    return result.rows;
  } finally {
    client.release(); // always release!
  }
}

Impact: 연결 오버헤드 ↓ 70 %.

7. Redis 캐싱 레이어

// cache.js
const redis = require('redis');
const client = redis.createClient();

async function getCustomerOrders(customerId) {
  const cacheKey = `customer:${customerId}:orders`;

  // 1️⃣ Check cache first
  const cached = await client.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2️⃣ Fallback to DB
  const result = await pool.query(
    'SELECT * FROM orders WHERE customer_id = $1',
    [customerId]
  );

  // 3️⃣ Cache for 5 minutes
  await client.setEx(cacheKey, 300, JSON.stringify(result.rows));

  return result.rows;
}

8. 대량 집계를 위한 물리화된 뷰

CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
    DATE(created_at)          AS sale_date,
    COUNT(*)                  AS total_orders,
    SUM(total_amount)        AS total_revenue,
    AVG(total_amount)         AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

필요에 따라 새로 고침(예: 매일 밤)하여 보고 속도를 빠르게 유지합니다.

TL;DR

  1. 적절한 B‑tree, partial, 그리고 GIN 인덱스를 추가한다.
  2. 필요한 컬럼만 선택하고 가능한 경우 INNER JOIN을 사용한다.
  3. 날짜 또는 상태별로 큰 테이블을 파티션한다.
  4. 인덱스 사용량을 모니터링하고 부풀어진 인덱스를 재인덱싱한다.
  5. PgBouncer로 연결을 풀링하고 Redis에 자주 조회되는 데이터를 캐시한다.
  6. 비싼 집계에 대해 물리화 뷰(materialized view)를 사용한다.

이러한 단계들을 종합하면 15초 걸리던 쿼리를 200 ms 미만의 응답으로 단축시켜, 일일 수백만 건의 트랜잭션을 처리하면서 애플리케이션을 원활하게 확장할 수 있게 되었습니다.

물리화된 뷰 인덱싱

CREATE INDEX idx_daily_sales_date 
ON daily_sales_summary(sale_date);

뷰 새로 고침 (예약 가능)

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

성능 영향: 대시보드 쿼리가 12 초에서 100 밀리초로 감소했습니다.

Source: …

대량 삽입

❌ 나쁨 – 여러 개의 개별 삽입

// 10 000 separate INSERTs
for (let i = 0; i  1000
    `),
    connections: await pool.query(`
      SELECT COUNT(*) FROM pg_stat_activity 
      WHERE state = 'active'
    `),
    tableSize: await pool.query(`
      SELECT pg_size_pretty(pg_database_size(current_database()))
    `)
  };

  console.log('Database Health:', checks);

  // Alert if thresholds exceeded
  if (parseInt(checks.slowQueries.rows[0].count, 10) > 10) {
    error('⚠️ Too many slow queries detected!');
  }
}

// Run every 5 minutes
setInterval(healthCheck, 5 * 60 * 1000);

✅ 좋음 – 단일 대량 삽입

// 10 000 rows in ONE INSERT
await pool.query(`
  INSERT INTO users (name, email) VALUES 
  ${Array.from({ length: 10000 })
    .map((_, i) => `('User ${i}', 'user${i}@example.com')`)
    .join(',\n')}
`);

✅ 좋음 – 대량 데이터 로드를 위한 COPY 사용

const copyFrom = require('pg-copy-streams').from;
const fs = require('fs');

const stream = client.query(copyFrom('COPY users (name, email) FROM STDIN WITH (FORMAT csv)'));
fs.createReadStream('users.csv').pipe(stream);

📊 실제 예시: 헬스‑체크 스크립트

const { Pool } = require('pg');
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function healthCheck() {
  const checks = {
    // Slow queries (> 1 s)
    slowQueries: await pool.query(`
      SELECT count(*) FROM pg_stat_activity 
      WHERE state = 'active' AND now() - query_start > interval '1 second'
    `),
    // Active connections
    connections: await pool.query(`
      SELECT COUNT(*) FROM pg_stat_activity 
      WHERE state = 'active'
    `),
    // Database size
    tableSize: await pool.query(`
      SELECT pg_size_pretty(pg_database_size(current_database()))
    `)
  };

  console.log('Database Health:', checks);

  // Threshold 초과 시 알림
  if (parseInt(checks.slowQueries.rows[0].count, 10) > 10) {
    console.error('⚠️ 너무 많은 느린 쿼리가 감지되었습니다!');
  }
}

// 5분마다 실행
setInterval(healthCheck, 5 * 60 * 1000);

Query Patterns

❌ Bad – N+1 queries

const orders = await pool.query('SELECT * FROM orders LIMIT 100');
for (let order of orders.rows) {
  const customer = await pool.query(
    'SELECT * FROM customers WHERE id = $1',
    [order.customer_id]
  );
  order.customer = customer.rows[0];
}

✅ Good – Single JOIN

const result = await pool.query(`
  SELECT o.*, c.name, c.email
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  LIMIT 100
`);

❌ Bad – SQL‑injection risk & no plan caching

const query = `SELECT * FROM orders WHERE id = ${userId}`;

✅ Good – Prepared statement

const query = 'SELECT * FROM orders WHERE id = $1';
await pool.query(query, [userId]);

전체 텍스트 검색 설정

-- Add tsvector column
ALTER TABLE products ADD COLUMN search_vector tsvector;

-- Create GIN index
CREATE INDEX idx_products_search 
ON products USING GIN(search_vector);
-- Update trigger to maintain search_vector
CREATE TRIGGER tsvector_update 
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, description);
-- Efficient full‑text search
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'laptop & gaming');

고쓰기 워크로드 조정

wal_compression = on
wal_writer_delay = 200ms
commit_delay = 10
commit_siblings = 5

모범 사례 체크리스트

  • 먼저 측정: 최적화하기 전에 EXPLAIN ANALYZE를 사용하세요.
  • 인덱스는 현명하게: 인덱스가 많다고 성능이 좋아지는 것은 아닙니다.
  • 지속적인 모니터링: pg_stat_statements가 최고의 친구입니다.
  • 프로덕션과 유사한 환경에서 테스트: 로컬 DB에서는 확장성 문제를 발견하기 어렵습니다.
  • 점진적인 변경 적용: 한 번에 하나씩 최적화하세요.
  • 모든 것을 문서화: 미래의 당신이 현재의 당신에게 감사할 것입니다.

PostgreSQL을 대규모 애플리케이션에 최적화하는 것은 반복적인 과정입니다. 여기서 공유한 기법으로 쿼리 시간이 100× 감소하고 인프라 비용이 60 % 절감되었습니다.

주요 요점

  • ✅ 전략적 인덱싱 (과도한 인덱싱 금지)
  • ✅ 쿼리 재구성 및 올바른 JOIN
  • ✅ 시계열 데이터를 위한 파티셔닝
  • ✅ PgBouncer를 이용한 커넥션 풀링
  • ✅ 다중 레벨 캐싱
  • ✅ 정기적인 모니터링 및 유지보수

도구: pgAdmin (GUI 관리), pg_stat_statements

  • 쿼리 통계
  • PgBouncer – 커넥션 풀링
  • pgBadger – 로그 분석기
  • explain.depesz.com – 시각적 EXPLAIN 분석기

비슷한 확장성 문제를 겪어보셨나요? 어떤 최적화 기법이 여러분의 경우에 효과적이었는지 댓글로 알려주세요!

태그: performance #optimization #sql #backend #devops #scalability

Back to Blog

관련 글

더 보기 »

인덱스와 DBMS의 부상

안녕하세요, 저는 Maneshwar입니다. 저는 FreeDevTools online https://hexmos.com/freedevtools에서 작업하고 있으며, 현재 모든 dev tools, cheat codes, 그리고 TLDRs를 한 곳에 모으는 작업을 하고 있습니다.