Database and schema organization
PostgreSQL organiza los objetos en una jerarquía:
- el server instance contiene una o múltiples BBDD
- las BBDD contienen schemas
- los schemas contienen tablas y otros objetos
Un schema provee de un namespace para poder organizar objetos. El schema por defecto es
public, que es el que se asigna cuando no especificas uno diferente.
IMPORTANTE
Usar databases separadas cuando queremos aislamiento total y completo entre datasets, cuando diferentes apps compartan server pero no quieres que accedan a los datos de la otra, o cuando quieres backups independientes.
Usar schemas separados dentro de una misma database cuando objetos relacionados necesitan referenciarse a través de FK, cuando quieras ejecutar queries con JOIN a través de namespaces, o cuando quieras una separación lógica sin tener un aislamiento total
/IMPORTANTE
Crear tablas
En este ejemplo id define una PK con BIGSERIAL PRIMARY KEY, la cual es autom-incremental
CREATE TABLE conversations (
id BIGSERIAL PRIMARY KEY,
session_id UUID NOT NULL,
user_id VARCHAR(255) NOT NULL,
started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
ended_at TIMESTAMP WITH TIME ZONE,
metadata JSONB DEFAULT '{}'::jsonb
);
Data types relevantes para AI apps
IMPORTANTE
- JSONB: almacena JSON data en un formation binario que soporta indexing y querying eficiente. Usar JSONB cuando la estructura de datos varía entre records, necesites guardar nested objects o arrays, o necesites schema flexibility
- TEXT and VARCHAR: ambos son para texto.
TEXTno tiene límite yVARCHAR(n)enfuerza un máximo. No hay diferencia de performance. - TIMESTAMP WITH TIME ZONE: Usar siempre
TIMESTAMPTZpara datos de tiempo que operen a través de time zones. - BYTEA: almacena binary data como byte array. Se usa para small binary objects que necesitan ser almacenados junto a datos relacionales. Para large binary files usar Azure Blob Storage con una referencia en la BBDD
- SERIAL and BIGSERIAL: pseudo-types que crean ints auto-incrementales. PostgreSQL crea automáticamente una sequencia y pone un valor default. Usar
BIGSERIALpar tablas que excedan de 2 billion rows.
/IMPORTANTE
Define relationships
IMPORTANTE
FK establecen relaciones entre tablas y fuerzan integridad referencial. Cuando defines una FK, PostgreSQL enfuerza que existen los valores en la columna referida.
One-to-many: conecta una single row de una tabla con multiple rows en otra. Ejemplos: Una conversación tiene múltiples mensajes; Un user tiene múltiples sesiones;
CREATE TABLE messages (
id BIGSERIAL PRIMARY KEY,
conversation_id BIGINT NOT NULL REFERENCES conversations(id),
role VARCHAR(50) NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
content TEXT NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
Many-to-many: requieren de una junction table que referencia ambas tablas relacionadas. Ejemploss: las conversaciones pueden tener múltiples tags, y los tags pueden aplicarse a múltiples conversaciones
la composite PK fuerza que cada combinación aparezca una única vez
CREATE TABLE conversation_tags (
conversation_id BIGINT REFERENCES conversations(id) ON DELETE CASCADE,
tag_id BIGINT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (conversation_id, tag_id)
);
Las acciones referenciales definen lo que pasa cuando haces update o delete de una fila referenciada
RESTRICT(default): previene la operación si la referencia existeCASCADE: borra o hace update de las referenciasSET NULL: pone la FK a nullSET DEFAULT: pone la FK con el defaultON DELETE CASCADE: usarla con cuidado - si borramos una conversación borraremos también automáticamente todos los mensajes, lo que puede borrar muchos más datos de los que hubiéramos querido si las relaciones son complejas
/IMPORTANTE
Index basics
Los indices son estructuras que aceleran data retrieval con el coste de más almacenamiento y writes más lentos. PostgreSQL crea automáticamente indices para PKs y UNIQUE contraints.
el orden de las columnas en un indice compuesto importa, ya que se aplica a queries que filtren solo por los primeros items, pero no a las que solo filtren por los últimos
CREATE INDEX idx_messages_conversation_id ON messages(conversation_id);
CREATE INDEX idx_messages_created_at ON messages(created_at);
CREATE INDEX idx_messages_conversation_created ON messages(conversation_id, created_at);
Schema management commands
si algo va mal, puedes usar ROLLBACK para no cambiar el schema, ya que las operaciones son transaccionales
BEGIN;
ALTER TABLE conversations ADD COLUMN category VARCHAR(100);
CREATE INDEX idx_conversations_category ON conversations(category);
COMMIT;