Create and manage schemas (PostgreSQL)

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. TEXT no tiene límite y VARCHAR(n) enfuerza un máximo. No hay diferencia de performance.
  • TIMESTAMP WITH TIME ZONE: Usar siempre TIMESTAMPTZ para 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 BIGSERIAL par 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 existe
  • CASCADE: borra o hace update de las referencias
  • SET NULL: pone la FK a null
  • SET DEFAULT: pone la FK con el default
  • ON 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;