실무 기본 · X02

운영 DB(Postgres): 제약·인덱스·권한

데이터 엔지니어가 가져오는 데이터의 상당수는 서비스가 쓰는 운영 DB에서 옵니다. 공고 31건(33%)이 MySQL·PostgreSQL 같은 RDB나 NoSQL 경험을, 28건(30%)이 보안·권한을 언급합니다. PostgreSQL 18.7로 제약 조건, 실행 계획, 행 수준 보안을 확인했습니다.


실행 환경: 이 교안의 실습 컴퓨터에서는 공유 메모리 제한 때문에 Postgres 서버(initdb)가 could not create shared memory segment: Operation not permitted로 뜨지 않았습니다. 그래서 Node 안에서 도는 PostgreSQL 18.7 빌드인 PGlite 0.5.17로 실행했습니다. SQL과 실행 계획은 일반 Postgres와 같습니다. 여러분 컴퓨터에서는 Postgres를 설치하거나 X10의 compose.yaml로 띄워도 됩니다.

적재와 제약 조건

// P1. 운영 DB(Postgres)에 쇼핑몰 데이터를 넣고 인덱스·실행 계획·권한을 확인합니다.
// PGlite는 브라우저·Node 안에서 도는 실제 PostgreSQL 18 빌드입니다. 설치할 서버가 없습니다.
import { PGlite } from '@electric-sql/pglite'
import fs from 'node:fs'
const db = new PGlite('./pgdata')
const csv = p => fs.readFileSync(p, 'utf8').trim().split('\n').slice(1).map(l => l.split(','))
const q = (s, a) => db.query(s, a)
await db.exec(`
  DROP TABLE IF EXISTS orders, customers, products CASCADE;
  CREATE TABLE customers (customer_id int PRIMARY KEY, name text, region text, signup_date timestamp);
  CREATE TABLE products  (product_id int PRIMARY KEY, category text, product_name text, price int);
  CREATE TABLE orders (order_id int PRIMARY KEY, customer_id int REFERENCES customers, product_id int REFERENCES products,
                       qty int CHECK (qty > 0), status text, ordered_at timestamp, updated_at timestamp);`)
async function load(table, rows, cols) {
  for (let i = 0; i < rows.length; i += 500) {
    const part = rows.slice(i, i + 500), ph = part.map((r, j) => '(' + r.map((_, k) => '$' + (j * cols + k + 1)).join(',') + ')').join(',')
    await q(`INSERT INTO ${table} VALUES ${ph}`, part.flat())
  }
}
await load('customers', csv('../landing/customers.csv'), 4)
await load('products', csv('../landing/products.csv'), 4)
const files = fs.readdirSync('../landing/orders').filter(f => !f.includes('updates')).sort()
for (const f of files) await load('orders', csv('../landing/orders/' + f), 7)
console.log('적재:', (await q('select (select count(*) from customers) c, (select count(*) from products) p, (select count(*) from orders) o')).rows[0])

// 1) 제약 조건이 잘못된 데이터를 막는지
for (const bad of ["INSERT INTO orders VALUES (1, 999999, 1, 1, 'paid', now(), now())",
                   "INSERT INTO orders VALUES (2, 1, 1, 0, 'paid', now(), now())"]) {
  try { await q(bad) } catch (e) { console.log('막힘:', e.message) }
}
// 2) 인덱스 전후 실행 계획
const plan = async () => (await q("EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42")).rows.map(r => r['QUERY PLAN'])
console.log('\n[인덱스 전]\n' + (await plan()).join('\n'))
await q('CREATE INDEX idx_orders_customer ON orders (customer_id)')
console.log('\n[인덱스 후]\n' + (await plan()).join('\n'))
await db.close()
npm install && node p1_oltp.mjs
적재: { c: 5000, p: 200, o: 32200 }
막힘: insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
막힘: new row for relation "orders" violates check constraint "orders_qty_check"

없는 고객(999999)의 주문과 수량 0인 주문이 DB 단계에서 거부됐습니다. 08장에서 dbt 테스트로 잡던 규칙을, 운영 DB는 넣는 순간에 막습니다. 반대로 분석용 웨어하우스는 보통 이런 제약을 강제하지 않으니 dbt 테스트가 필요합니다.

인덱스 전후 실행 계획

[인덱스 전]
Seq Scan on orders  (actual time=0.353..1.552 rows=5.00 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 32195
  Buffers: shared hit=276
Execution Time: 1.601 ms

[인덱스 후]
Bitmap Heap Scan on orders  (cost=5.54..255.18 rows=161 width=64) (actual time=0.320..0.326 rows=5.00 loops=1)
  ->  Bitmap Index Scan on idx_orders_customer
        Index Cond: (customer_id = 42)
Buffers: shared hit=5 read=2
Execution Time: 0.355 ms

인덱스가 없을 때는 32,200행을 모두 읽고 32,195행을 버렸습니다(블록 276개). 인덱스를 만든 뒤에는 블록 7개만 읽었고 시간은 1.6ms → 0.36ms로 줄었습니다. 계획의 rows=161(추정)과 rows=5(실제)가 다른 것은 아직 통계를 모으지 않았기 때문입니다. 대량 적재 뒤에는 ANALYZE orders;로 통계를 갱신합니다(이 실습에서는 돌리지 않았습니다).

💡 운영 DB에서 큰 테이블을 통째로 SELECT * 하면 서비스가 느려질 수 있습니다. 적재는 읽기 전용 복제본(replica)에서, 인덱스가 있는 열(updated_at 등)로 범위를 좁혀 가져옵니다. 04장의 증분 적재가 이 조건을 씁니다.

권한: 가린 뷰와 행 수준 보안

// P2. 권한: 분석가는 이름을 가린 뷰만, 부산 담당자는 부산 고객만 보게 합니다.
import { PGlite } from '@electric-sql/pglite'
const db = new PGlite('./pgdata')
const q = s => db.query(s)
await db.exec(`
  DROP VIEW IF EXISTS customers_masked; DROP POLICY IF EXISTS busan_only ON customers;
  DROP ROLE IF EXISTS analyst; DROP ROLE IF EXISTS busan_mgr;
  CREATE ROLE analyst; CREATE ROLE busan_mgr;
  -- 분석가: 원본 테이블 권한 없음, 이름을 가린 뷰만
  CREATE VIEW customers_masked AS
    SELECT customer_id, left(name, 2) || '***' AS name, region, signup_date FROM customers;
  GRANT SELECT ON customers_masked TO analyst;
  -- 부산 담당자: 행 수준 보안(RLS)으로 부산 행만
  ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
  CREATE POLICY busan_only ON customers FOR SELECT TO busan_mgr USING (region = '부산');
  GRANT SELECT ON customers TO busan_mgr;`)
await q('SET ROLE analyst')
try { await q('SELECT * FROM customers LIMIT 1') } catch (e) { console.log('analyst → customers:', e.message) }
console.log('analyst → customers_masked:', (await q('SELECT * FROM customers_masked ORDER BY customer_id LIMIT 2')).rows)
await q('RESET ROLE'); await q('SET ROLE busan_mgr')
console.log('busan_mgr 지역별 행 수:', (await q('SELECT region, count(*) n FROM customers GROUP BY region')).rows)
await q('RESET ROLE')
console.log('관리자(postgres) 전체 행 수:', (await q('SELECT count(*) n FROM customers')).rows[0])
await db.close()
analyst → customers: permission denied for table customers
analyst → customers_masked: [ { customer_id: 1, name: '고객***', region: '광주', signup_date: 2025-05-26T15:00:00.000Z }, ... ]
busan_mgr 지역별 행 수: [ { region: '부산', n: 724 } ]
관리자(postgres) 전체 행 수: { n: 5000 }

분석가는 원본 테이블에 접근하지 못하고 이름을 가린 뷰만 봅니다. 부산 담당자는 같은 customers 테이블을 조회해도 정책(RLS) 때문에 부산 고객 724명만 보입니다. 쿼리를 바꾸지 않아도 DB가 걸러 줍니다.

시간대 함정: 가입일 2025-05-27 00:00이 결과에서 2025-05-26T15:00:00.000Z로 보입니다. 시간대 없는 timestamp를 JavaScript가 Date로 바꾸면서 UTC로 표시했기 때문입니다. 05장, A03장과 같은 9시간 문제가 드라이버 단계에서도 생깁니다.

이것만 기억하세요

운영 DB는 넣을 때 막고(제약), 찾을 때 빠르게(인덱스), 볼 사람만 보게(권한·RLS) 합니다. 실행 계획에서 "몇 행을 읽고 버렸나"를 먼저 봅니다.

다음
X03. Git과 CI
이어보기 →