쿼리 데이터
이 단원에서는 AI 애플리케이션에 필수적인 PostgreSQL 관련 쿼리 기능 및 패턴을 다룹니다. 이 단원에서는 PostgreSQL의 고유한 기능과 대화 기록, 처리 상태 및 애플리케이션 메타데이터를 관리하기 위한 고급 패턴에 중점을 둡니다.
이 단원에서는 SELECT, 조인, WHERE 및 집계와 같은 표준 SQL 개념을 잘 알고 있다고 가정합니다. 새로 고침이 필요한 경우 Transact-SQL 학습 경로를 사용하여 쿼리를 시작하는 것이 좋은 리소스입니다.
쿼리 실행 및 별칭 범위
숙련된 SQL 개발자도 실행 순서에 대해, 특히 열 별칭을 사용할 때, 혼동하게 됩니다. SQL 문은 작성하는 방법과 다른 특정 논리적 순서로 실행되며, 이 순서를 이해하면 비밀스러운 "열이 존재하지 않음" 오류를 해결하는 데 도움이 됩니다.
| 순서 | 조항 | 목적 |
|---|---|---|
| 1 | FROM |
원본 테이블 식별 |
| 2 | WHERE |
행 필터 |
| 3 | GROUP BY |
집계를 위한 행 그룹화 |
| 4 | HAVING |
필터 그룹 |
| 5 | SELECT |
열 선택 및 계산식 정의 |
| 6 | ORDER BY |
결과 정렬 |
| 7 | LIMIT / OFFSET |
결과 수 제한 |
별칭 범위 규칙: SELECT에서 정의된 열 별칭은 이후에SELECT 실행되는 절, 즉 ORDER BY 및 LIMIT/OFFSET에만 표시됩니다. 아직 정의되지 않았기 때문에 이전(WHERE, GROUP BY, HAVING)에 실행된 절은 이러한 별칭을 참조할 수 없습니다.
별칭을 기준으로 WHERE에서 필터링할 수는 없지만, ORDER BY에서 정렬할 수는 있습니다.
-- This FAILS: WHERE executes before SELECT, so 'msg_date' doesn't exist yet
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE msg_date > '2024-01-01'; -- Error: column "msg_date" does not exist
-- This WORKS: repeat the expression in WHERE
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01';
-- This also WORKS: ORDER BY executes after SELECT, so aliases are available
SELECT DATE(created_at) AS msg_date, content
FROM messages
WHERE DATE(created_at) > '2024-01-01'
ORDER BY msg_date;
PostgreSQL 관련 필터링
PostgreSQL은 일반적인 필터링 작업을 간소화하는 연산자를 사용하여 표준 SQL을 확장합니다. 이러한 기능은 유연한 텍스트 검색 및 구조적 메타데이터 쿼리가 필요한 AI 애플리케이션에 유용합니다.
연산자는 ILIKE 다음과 같은 LOWER()함수를 요구하지 않고 대/소문자를 구분하지 않는 패턴 일치를 제공합니다. 이는 대/소문자 표시 SELECT * FROM messages WHERE content ILIKE '%error%'와 관계없이 일치하려는 사용자 관련 검색 기능에 유용합니다.
PostgreSQL을 사용하면 NULLS FIRST 또는 NULLS LAST를 사용하여 정렬된 결과에서 값이 표시되는 위치를 제어할 수 있습니다NULL. 기본적으로 값은 NULL 다른 값보다 큰 것처럼 정렬됩니다. 마지막에 불완전한 대화를 유지하거나 ORDER BY ended_at NULLS LAST 처리되지 않은 작업을 먼저 표시하는 데 사용합니다ORDER BY completed_at NULLS FIRST.
함수는 COALESCE 인수에서 null이 아닌 첫 번째 값을 반환합니다. 쿼리 결과에 기본값을 제공하거나 COALESCE(title, 'Untitled') 식에서 null 허용 열을 처리하는 데 사용합니다.
JSONB 데이터 쿼리
PostgreSQL의 JSONB 형식은 고정 스키마에 맞지 않는 구조적 데이터를 저장합니다. AI 애플리케이션은 일반적으로 메타데이터, 구성, 모델 매개 변수 및 변수 응답 구조에 JSONB를 사용합니다. PostgreSQL은 JSON 콘텐츠를 기반으로 값을 추출하고, 구조를 확인하고, 필터링하기 위한 특수 연산자를 제공합니다.
JSON 요소를 JSON으로 추출하거나 -> 텍스트로 추출하는 데 사용합니다->>(비교 및 표시용). 중첩 경로의 경우 JSON을 사용하거나 #> (텍스트를 반환) 합니다 #>> . 예를 들어 metadata->>'status' 상태 필드를 텍스트로 추출하고 checkpoint_data#>>'{results,0,score}' 중첩된 경로를 탐색하여 특정 값을 가져옵니다.
존재 및 포함 연산자를 사용하면 효율적인 필터링이 가능합니다. 연산자 ?은 키 존재(WHERE metadata ? 'priority')를 확인하고, @>은 포함(WHERE checkpoint_data @> '{"status": "completed"}')을 테스트합니다. 이러한 연산자는 큰 테이블에서 효율적인 필터링을 위해 GIN 인덱스를 사용할 수 있습니다.
JSONB 열에 배열이 포함된 경우, 필터링이나 집계를 위해 jsonb_array_elements를 사용하여 확장합니다.
-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';
키 집합 페이지 매김을 사용하여 효율적인 페이지 매김
PostgreSQL은 건너뛴 모든 행을 검색하고 삭제해야 하므로 기존 OFFSET기반 페이지 매김은 큰 테이블에서 느려집니다. 페이지당 20개 행이 있는 1,000페이지에서는 20,000개의 행을 스캔하고 19,980개의 행을 삭제해야 합니다. 키 집합 페이지 매김(커서 기반 페이지 매김이라고도 함)은 WHERE 절을 사용하여 행을 건너뜁니다. 페이지 매김의 깊이에 관계없이 이 방법은 일관된 성능을 제공합니다.
페이지 번호를 추적하는 대신 마지막으로 본 값을 추적하고 여기에서 필터링합니다. 이 방법을 사용하려면 정렬 가능한 고유한 열(또는 열 조합)이 필요합니다.
-- First page: get the 20 most recent messages
SELECT id, conversation_id, content, created_at
FROM messages
ORDER BY created_at DESC, id DESC
LIMIT 20;
-- Next page: filter by the last seen timestamp and id
SELECT id, conversation_id, content, created_at
FROM messages
WHERE (created_at, id) < ('2024-06-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;
여러 행에 동일한 타임스탬프가 있는 경우 id를 ORDER BY 절과 WHERE절 모두에 포함하여 동률을 처리합니다. 애플리케이션은 마지막 행의 정렬 값을 저장하고 다음 쿼리에 전달합니다. 오름차순으로 정렬하려면 <을(를) >으로 변경하고 DESC을(를) ASC으로 변경합니다.
CTE(공통 테이블 식)
CTE는 쿼리를 실행하는 동안에만 존재하는 명명된 임시 결과 집합을 정의합니다. 복잡한 쿼리를 단계별로 빌드하여 가독성을 향상시키고 계층적 데이터에 대한 재귀 쿼리를 사용하도록 설정합니다.
CTE를 사용하여 복잡한 쿼리를 논리적 단계로 분할합니다. 각 CTE는 이전에 정의된 CTE를 참조하여 변환 파이프라인을 만들 수 있습니다.
WITH recent_conversations AS (
SELECT id, user_id, started_at
FROM conversations
WHERE started_at > CURRENT_DATE - INTERVAL '7 days'
),
message_stats AS (
SELECT conversation_id, COUNT(*) AS message_count, MAX(created_at) AS last_message_at
FROM messages
GROUP BY conversation_id
)
SELECT rc.user_id, rc.started_at, COALESCE(ms.message_count, 0) AS message_count
FROM recent_conversations rc
LEFT JOIN message_stats ms ON rc.id = ms.conversation_id;
재귀 CTE는 작업 계층 구조, 조직도 또는 스레드 대화와 같은 트리 구조를 쿼리합니다. 기본 사례(앵커) 및 CTE 자체를 참조하는 재귀 사례로 구성됩니다. 데이터에 주기가 포함된 경우 무한 루프를 방지하기 위해 항상 깊이 제한 또는 기타 종료 조건을 포함합니다.
WITH RECURSIVE task_tree AS (
-- Base case: start with the parent task
SELECT id, parent_id, title, 1 AS depth
FROM tasks WHERE id = 1
UNION ALL
-- Recursive case: find children of current level
SELECT t.id, t.parent_id, t.title, tt.depth + 1
FROM tasks t
INNER JOIN task_tree tt ON t.parent_id = tt.id
WHERE tt.depth < 10
)
SELECT * FROM task_tree ORDER BY depth, id;
AI 애플리케이션은 메시지가 부모 메시지를 참조하는 대화 스레드를 검색해야 하는 경우가 많습니다.
WITH RECURSIVE thread AS (
-- Start with the root message
SELECT id, parent_id, content, role, 0 AS depth
FROM messages
WHERE id = :root_message_id
UNION ALL
-- Get all replies
SELECT m.id, m.parent_id, m.content, m.role, t.depth + 1
FROM messages m
INNER JOIN thread t ON m.parent_id = t.id
WHERE t.depth < 50
)
SELECT * FROM thread ORDER BY depth, id;
RETURNING을 사용하여 INSERT
PostgreSQL의 RETURNING 구문은 단일 왕복으로 삽입, 업데이트 또는 삭제된 행의 값을 가져옵니다. 이는 별도의 쿼리 없이 자동 생성된 ID, 타임스탬프 또는 계산된 기본값을 가져오는 데 필수적입니다.
-- Get the generated ID after inserting a conversation
INSERT INTO conversations (user_id, session_id)
VALUES ('user123', 'sess_abc')
RETURNING id;
-- Get multiple generated values
INSERT INTO messages (conversation_id, role, content)
VALUES (1, 'user', 'Hello')
RETURNING id, created_at;
-- Use RETURNING with UPDATE
UPDATE tasks SET status = 'completed', completed_at = CURRENT_TIMESTAMP
WHERE id = 5
RETURNING id, status, completed_at;
ON CONFLICT을 사용하는 Upserts
INSERT ... ON CONFLICT 절은 고유한 제약 조건 위반 문제를 해결하여 새 행을 삽입하거나 기존 행을 업데이트하는 "upsert" 작업을 수행할 수 있도록 합니다. 이 패턴은 AI 애플리케이션에서 멱등 연산 및 상태 관리에 유용합니다.
고유 제약 조건에서 충돌이 발생하면 .를 사용하여 DO UPDATE기존 행을 새 값으로 업데이트할 수 있습니다.
EXCLUDED 의사 테이블은 삽입될 예정이었던 값을 참조합니다.
INSERT INTO user_preferences (user_id, preference_key, preference_value)
VALUES ('user123', 'theme', 'dark')
ON CONFLICT (user_id, preference_key)
DO UPDATE SET
preference_value = EXCLUDED.preference_value,
updated_at = CURRENT_TIMESTAMP;
제약 조건을 위반하는 행을 자동으로 건너뛰는 데 사용합니다DO NOTHING. INSERT INTO tags (name) VALUES ('important') ON CONFLICT (name) DO NOTHING
조건부 논리를 위해 WHERE에 절을 추가할 수 있습니다. 새 값이 기존 값과 다른 경우에만 값이 업데이트됩니다. Combine ON CONFLICT 와 RETURNING 를 결합하여 행이 삽입되었는지 또는 업데이트되었는지를 알 수 있습니다. 식 (xmax = 0) 는 새로 삽입된 행에 대해서는 true 를 반환하고, 업데이트된 행에 대해서는 false 를 반환합니다.
AI 애플리케이션에 대한 실용적인 패턴
이러한 패턴은 앞에서 설명한 기능을 일반적인 AI 애플리케이션 요구 사항에 대한 솔루션으로 결합합니다. 다음 쿼리는 컨텍스트 빌드에 대한 메타데이터를 사용하여 대화 기록을 검색합니다.
WITH conversation_context AS (
SELECT c.id, c.session_id, c.metadata->>'model' AS model
FROM conversations c
WHERE c.session_id = :session_id AND c.ended_at IS NULL
)
SELECT cc.session_id, cc.model, m.role, m.content, m.created_at
FROM conversation_context cc
INNER JOIN messages m ON cc.id = m.conversation_id
ORDER BY m.created_at
LIMIT 50;
다음 예제에서는 upsert를 사용하여 상태 관리를 사용하여 작업 검사점을 기록하는 방법을 보여 줍니다.
INSERT INTO task_checkpoints (task_id, step_number, checkpoint_data)
VALUES (:task_id, :step_number, :checkpoint_json::jsonb)
ON CONFLICT (task_id, step_number)
DO UPDATE SET checkpoint_data = EXCLUDED.checkpoint_data, updated_at = CURRENT_TIMESTAMP
RETURNING id, created_at, (xmax = 0) AS is_new;
다음 예제에서는 JSONB 필터를 사용하여 페이지를 매긴 검색을 보여 줍니다.
SELECT c.id, c.session_id, c.started_at, c.metadata->>'status' AS status
FROM conversations c
WHERE c.user_id = :user_id
AND c.metadata @> :filter_json::jsonb
AND (c.started_at, c.id) < (:last_started_at, :last_id)
ORDER BY c.started_at DESC, c.id DESC
LIMIT 20;