PostgreSQL

// PostgreSQL 연결 및 CRUD - Node.js + pg
const { Pool } = require('pg');

// 커넥션 풀 설정
const pool = new Pool({
    connectionString: 'postgresql://user:password@localhost:5432/mydb',
    max: 20,                  // 최대 연결 수
    idleTimeoutMillis: 30000, // 유휴 연결 타임아웃
    connectionTimeoutMillis: 2000,
});

// 트랜잭션 헬퍼
async function withTransaction(callback) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        const result = await callback(client);
        await client.query('COMMIT');
        return result;
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    } finally {
        client.release();
    }
}

// JSONB 데이터 조작
async function jsonbOperations() {
    // JSONB 삽입
    const insertResult = await pool.query(`
        INSERT INTO products (name, price, metadata)
        VALUES ($1, $2, $3)
        RETURNING *
    `, [
        'Galaxy S24 Ultra',
        1299.00,
        {
            brand: 'Samsung',
            specs: { display: '6.8"', ram: '12GB', storage: '256GB' },
            tags: ['smartphone', 'android', 'flagship']
        }
    ]);
    console.log('삽입됨:', insertResult.rows[0]);

    // JSONB 배열에서 검색
    const searchResult = await pool.query(`
        SELECT name, price, metadata->'tags' as tags
        FROM products
        WHERE metadata->'tags' ? $1
    `, ['flagship']);
    console.log('flagship 태그:', searchResult.rows);

    // JSONB 필드 업데이트 (jsonb_set)
    await pool.query(`
        UPDATE products
        SET metadata = jsonb_set(metadata, '{specs,ram}', '"16GB"')
        WHERE name = $1
    `, ['Galaxy S24 Ultra']);
}

// 배치 삽입 (COPY 대안)
async function batchInsert(products) {
    const values = products.map((p, i) => {
        const offset = i * 3;
        return `($${offset + 1}, $${offset + 2}, $${offset + 3})`;
    }).join(', ');

    const params = products.flatMap(p => [p.name, p.price, p.metadata]);

    await pool.query(`
        INSERT INTO products (name, price, metadata)
        VALUES ${values}
        ON CONFLICT (name) DO UPDATE
        SET price = EXCLUDED.price,
            metadata = products.metadata || EXCLUDED.metadata
    `, params);
}

// 스트리밍 대용량 조회
async function streamLargeResult() {
    const client = await pool.connect();
    try {
        // 커서 사용
        await client.query('BEGIN');
        await client.query(`
            DECLARE product_cursor CURSOR FOR
            SELECT * FROM products ORDER BY created_at
        `);

        let batch;
        while (true) {
            const result = await client.query('FETCH 1000 FROM product_cursor');
            batch = result.rows;
            if (batch.length === 0) break;

            // 배치 처리
            await processBatch(batch);
        }

        await client.query('CLOSE product_cursor');
        await client.query('COMMIT');
    } finally {
        client.release();
    }
}

// Listen/Notify (실시간 알림)
async function setupNotifications() {
    const client = await pool.connect();

    client.on('notification', (msg) => {
        console.log('알림 수신:', msg.channel, JSON.parse(msg.payload));
    });

    await client.query('LISTEN product_changes');

    // 다른 세션에서 NOTIFY 호출 시 알림 수신
    // NOTIFY product_changes, '{"action": "insert", "id": 123}';
}

// 실행
(async () => {
    await jsonbOperations();
    console.log('✅ PostgreSQL 작업 완료');
    await pool.end();
})();

-- PostgreSQL 고급 SQL 예제

-- ============================================
-- 1. JSONB 고급 쿼리
-- ============================================
-- JSONB 생성 및 조작
SELECT jsonb_build_object(
    'name', name,
    'price', price,
    'discounted', price * 0.9
) as product_json
FROM products;

-- JSONB 배열 펼치기
SELECT p.name, tag
FROM products p,
     jsonb_array_elements_text(p.metadata->'tags') as tag
WHERE tag LIKE '%pro%';

-- JSONB 집계
SELECT
    metadata->>'brand' as brand,
    jsonb_agg(name) as products,
    jsonb_object_agg(name, price) as price_map
FROM products
GROUP BY metadata->>'brand';


-- ============================================
-- 2. 윈도우 함수
-- ============================================
SELECT
    name,
    price,
    metadata->>'brand' as brand,
    -- 브랜드별 순위
    RANK() OVER (PARTITION BY metadata->>'brand' ORDER BY price DESC) as rank_in_brand,
    -- 누적 합계
    SUM(price) OVER (ORDER BY created_at) as running_total,
    -- 이전 행과 비교
    LAG(price) OVER (ORDER BY created_at) as prev_price,
    -- 이동 평균
    AVG(price) OVER (ORDER BY created_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM products;


-- ============================================
-- 3. 전문검색 (Full-Text Search)
-- ============================================
-- 검색 벡터 업데이트
UPDATE products
SET search_vector =
    setweight(to_tsvector('english', name), 'A') ||
    setweight(to_tsvector('english', COALESCE(metadata->>'brand', '')), 'B');

-- 전문검색 쿼리
SELECT name, ts_rank(search_vector, query) as rank
FROM products, to_tsquery('english', 'apple | samsung') as query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 하이라이트
SELECT ts_headline('english', name, to_tsquery('macbook'))
FROM products
WHERE search_vector @@ to_tsquery('macbook');


-- ============================================
-- 4. CTE와 재귀 쿼리
-- ============================================
-- 카테고리 계층 조회 (재귀 CTE)
WITH RECURSIVE category_tree AS (
    -- 기본 케이스: 최상위 카테고리
    SELECT id, name, parent_id, 1 as depth, ARRAY[name] as path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 재귀 케이스: 하위 카테고리
    SELECT c.id, c.name, c.parent_id, ct.depth + 1, ct.path || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;


-- ============================================
-- 5. UPSERT (INSERT ON CONFLICT)
-- ============================================
INSERT INTO products (name, price, metadata)
VALUES ('iPhone 15', 999, '{"brand": "Apple"}')
ON CONFLICT (name) DO UPDATE
SET
    price = EXCLUDED.price,
    metadata = products.metadata || EXCLUDED.metadata,
    updated_at = CURRENT_TIMESTAMP
WHERE products.price != EXCLUDED.price
RETURNING *;


-- ============================================
-- 6. 성능 분석
-- ============================================
-- 쿼리 실행 계획 분석
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}'
ORDER BY price DESC
LIMIT 10;

-- 테이블 통계 확인
SELECT
    relname as table,
    n_live_tup as rows,
    n_dead_tup as dead_rows,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables;


-- ============================================
-- 7. 파티셔닝
-- ============================================
-- 범위 파티션 테이블 생성
CREATE TABLE orders (
    id SERIAL,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- 월별 파티션 생성
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');