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 textocheckpoint_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 NOTHINGhacemos skip de rows que violarian constraints
A
DO UPDATEse le puede añadirWHEREpara 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;