Query data (PostgreSQL)

PostgreSQL-specific filtering

PostgreSQL extiende el SQL estandard con más operadores.

El operador ILIKE es como LIKE pero ya case-insentive sin tener que hacer un LOWER.

También permite controlar cómo aparecen ordenados los null values en los resultados con NULLS FIRST o NULL LAST

Con COALESCE devuelve el primer valor no null de sus args. Se usa para proveer de default values en los resultados o para handlear nullable columns COALESCE(title, 'Untitled')

Query JSONB data

PostgreSQL’s JSONB type almacena datos estructurados que no se pueden meter en un schema fijo. AI apps usan JSONB normalmente para metadatos, config, model params o estructuras de respuesta variable.

PostgreSQL provee de operadores especializados para JSON content:

  • -> extrae un JSON element como JSON
  • ->> extrae un JSON element como texto (para comparisons y display)
  • #> devuelve JSON para nested paths
  • #>> devuelve texto para nested paths

Ejemplos:

  • metadata->>'status' extrae el status field como texto
  • checkpoint_data#>>'{results,0,score}' navega un nested path para obtener el valor específico

Existence and containment operators enable efficient filtering

  • ? comprueba key existence
  • @> tests containment

Ejemplos:

  • WHERE metadata ? 'priority'
  • WHERE checkpoint_data @> '{"status": "completed"}'

Cuando JSONB contiene arrays, podemos usar jsonb_array_elements para expandirlas y filtrar o agregar

-- Find conversations tagged with 'support'
SELECT DISTINCT c.*
FROM conversations c,
     jsonb_array_elements_text(c.metadata->'tags') AS tag
WHERE tag = 'support';

Efficient pagination with keyset pagination

Una paginación tradicional con OFFSET se vuelve lenta en tablas grandes porque PostgreSQL debe escanear y descartar todas las rows que salta. Cargar la página 1000 con 20 rows por página requiere escanear 20.000 rows y descartar 19.980.

Keyset pagination (also calle cursor-based pagination) usa WHERE para saltar rows, lo cual tiene una performance consistente independientemente de como de profundo pagines.

En vez de tracklear page numbers, trackeas el último valor que has visto y filtras desde ahí. Este approach requiere un unique, sortable column (o combinacion de ellas). Tu página debe almacenar los sort values de la última fila y pasarlos a la siguiente query.

(incluir id en ambos ORDER BY y WHERE gestiona los empates cuando múltiples filas tienen el mismo timestamp)

-- 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;

Common Table Expressions (CTEs)

INVESTIGAR Y PROBAR ESTO. VER SI ES SQL native o de Postgre. Me puede ayudar mucho en mi dia a dia

CTEs definen named temporary result sets que existen solo durante la ejecución de una query. Mejoran la legibilidad y te permiten crear queries complejas paso a paso.

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;

Recursive CTEs query tree structures like task hierarchies, org chats o threaded conversations. Consisten de un base case (anchor) y un recursive case que referencia al CTE en si mismo. Incluir siempre un depth limit u otra condicion de finalizacion para prevenir infinite loops

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 apps a menudo necesita hacer retrieve de conversation threads donde los mensajes referencian parent messages

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;

INSERT with RETURNING

RETURNING hace retrieve de valores de filas insertadas, updateadas o borradas en un single round trip. Es esencial para obtener IDs autogenerados, timestamps o computed defaults sin una query separada

-- 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;

Upserts with ON CONFLICT

INSERT ... ON CONFLICT se encarga de gestionar UNIQUE contraint violations, permitiendo al upsert insertar nuevas filas o actualizar las ya existentes.

Cuando hay un conflicto en una UNIQUE constraint, puedes actualizar la existing row con nuevos valores usando DO UPDATE

Con DO NOTHING hacemos skip de rows que violarian constraints

A DO UPDATE se le puede añadir WHERE para filtrar

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;

Practical patterns for AI apps

Retrieve conversation history with metadata for context building

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;

Show recording task checkpoints with state management using 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;

Show a paginated search with JSONB filters

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;