PostgreSQL Multi-Tenant

Overview

Configuración de PostgreSQL 16 para el ecosistema Nostromo con arquitectura schema-per-tenant.

Estrategia: Cada cliente (tenant) tiene su propio schema PostgreSQL dentro de una única base de datos compartida.

Referencia: ADR-001: Multi-Tenant Strategy

---

Arquitectura de Schemas

nostromo (database)
├── central (schema)         # Metadatos globales
│   ├── tenants              # Registro de tenants
│   ├── users                # Usuarios del sistema
│   └── audit_log            # Log de auditoría global
├── parametros (schema)      # Datos SII compartidos
│   ├── indicadores          # UF, UTM, IPC
│   ├── afp                  # Tabla de AFPs
│   ├── isapres              # Tabla de ISAPREs
│   └── comunas              # Comunas de Chile
├── tenant_empresa_123 (schema)  # Tenant específico
│   ├── employees
│   ├── contracts
│   ├── payroll
│   └── ...
└── tenant_empresa_456 (schema)  # Otro tenant
    ├── employees
    ├── contracts
    └── ...

---

Configuración Base

postgresql.conf

Path: /etc/postgresql/16/main/postgresql.conf

# Connection Settings
listen_addresses = 'localhost'
port = 5432
max_connections = 100

# Memory Settings (ajustar según RAM del servidor)
shared_buffers = 2GB              # 25% de RAM
effective_cache_size = 6GB        # 75% de RAM
work_mem = 64MB                   # Para queries complejas
maintenance_work_mem = 512MB      # Para VACUUM, INDEX

# WAL Settings
wal_level = replica
max_wal_size = 2GB
min_wal_size = 1GB
checkpoint_completion_target = 0.9

# Query Planner
random_page_cost = 1.1            # SSD optimized
effective_io_concurrency = 200    # SSD optimized

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'ddl'             # Log DDL statements
log_min_duration_statement = 1000 # Log queries > 1s

# Autovacuum
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60

Tip

Para servidores con más RAM, ajustar shared_buffers y effective_cache_size proporcionalmente.

---

pg_hba.conf

Path: /etc/postgresql/16/main/pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# Application connections (Orchestrator)
host    nostromo        nostromo_user   127.0.0.1/32           scram-sha-256
host    nostromo        nostromo_user   ::1/128                scram-sha-256

# Replication (if needed)
# host    replication     replicator      <REPLICA_IP>/32        scram-sha-256

# Deny all other connections
host    all             all             0.0.0.0/0              reject

Danger

NUNCA permitir conexiones remotas sin autenticación fuerte y firewall.

---

Gestión de Schemas

Crear Nuevo Tenant

-- 1. Crear schema
CREATE SCHEMA tenant_empresa_789;

-- 2. Establecer permisos
GRANT ALL ON SCHEMA tenant_empresa_789 TO nostromo_user;

-- 3. Registrar en central
INSERT INTO central.tenants (id, schema_name, company_name, created_at)
VALUES ('empresa_789', 'tenant_empresa_789', 'Empresa ABC Ltda.', NOW());

-- 4. Crear tablas base (ejecutar migrations)
SET search_path TO tenant_empresa_789;
-- ... ejecutar SQL de migrations ...

Eliminar Tenant

Caution

Esta operación es IRREVERSIBLE. Hacer backup antes de eliminar.

-- 1. Backup del schema (desde shell)
-- pg_dump -U postgres -d nostromo --schema=tenant_empresa_789 > backup.sql

-- 2. Desactivar en central
UPDATE central.tenants SET active = false WHERE id = 'empresa_789';

-- 3. Eliminar schema (con RESTRICT primero para verificar dependencias)
DROP SCHEMA tenant_empresa_789 RESTRICT;

-- 4. Si ok, eliminar con CASCADE
DROP SCHEMA tenant_empresa_789 CASCADE;

-- 5. Eliminar registro
DELETE FROM central.tenants WHERE id = 'empresa_789';

Listar Todos los Schemas de Tenants

SELECT
    t.id,
    t.schema_name,
    t.company_name,
    t.active,
    pg_size_pretty(pg_schema_size(t.schema_name)) as size
FROM central.tenants t
ORDER BY t.company_name;

---

Connection Pool y Schema Switching

Configuración del Pool

El Orchestrator usa pg-pool con schema switching por request:

src/db/pool.ts

import { Pool } from "pg";

const pool = new Pool({
  host: process.env.DB_HOST || "localhost",
  port: parseInt(process.env.DB_PORT || "5432"),
  database: "nostromo",
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  max: 20, // Max connections
  idleTimeoutMillis: 30000, // 30s idle timeout
  connectionTimeoutMillis: 2000,
});

Schema Switching por Request

// Cada request ejecuta esto antes de cualquier query
async function setTenantSchema(client: PoolClient, tenantId: string) {
  const schemaName = `tenant_${tenantId}`;

  // Verificar que schema existe
  const exists = await client.query(
    `SELECT 1 FROM pg_namespace WHERE nspname = $1`,
    [schemaName],
  );

  if (exists.rows.length === 0) {
    throw new Error(`Tenant schema not found: ${schemaName}`);
  }

  // Establecer search_path
  await client.query(`SET search_path TO ${schemaName}, parametros, public`);
}

Referencia: ADR-002: Pool Management

---

Índices Recomendados

Tablas de Alta Concurrencia

-- employees (búsqueda por RUT)
CREATE INDEX idx_employees_rut ON employees(rut);

-- contracts (búsqueda por employee + vigencia)
CREATE INDEX idx_contracts_employee_dates
  ON contracts(employee_id, fecha_inicio, fecha_termino);

-- payroll (búsqueda por período)
CREATE INDEX idx_payroll_period
  ON payroll(year, month);

-- audit_log (búsqueda por fecha)
CREATE INDEX idx_audit_created
  ON audit_log(created_at DESC);

Índices Parciales

-- Solo contratos vigentes
CREATE INDEX idx_contracts_active
  ON contracts(employee_id)
  WHERE fecha_termino IS NULL OR fecha_termino > NOW();

-- Solo usuarios activos
CREATE INDEX idx_users_active
  ON central.users(email)
  WHERE active = true;

---

Mantenimiento

Vacuum y Analyze Automático

El autovacuum está habilitado por defecto. Para tablas muy activas, ajustar:

-- Configurar autovacuum más agresivo para tabla payroll
ALTER TABLE payroll SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02
);

Vacuum Manual (Post-DELETE masivo)

-- Vacuum específico para un schema
SET search_path TO tenant_empresa_123;
VACUUM ANALYZE employees;
VACUUM ANALYZE contracts;

-- Vacuum full (requiere lock exclusivo)
VACUUM FULL ANALYZE employees;

Reindex

-- Reindex de tabla específica
REINDEX TABLE employees;

-- Reindex de schema completo
REINDEX SCHEMA tenant_empresa_123;

---

Monitoring

Queries Lentos

-- Top 10 queries más lentas (requiere pg_stat_statements)
SELECT
    query,
    calls,
    round(total_exec_time::numeric, 2) as total_ms,
    round(mean_exec_time::numeric, 2) as mean_ms
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Tamaño por Schema

SELECT
    schema_name,
    pg_size_pretty(pg_schema_size(schema_name)) as size
FROM (
    SELECT nspname as schema_name
    FROM pg_namespace
    WHERE nspname LIKE 'tenant_%'
) schemas
ORDER BY pg_schema_size(schema_name) DESC;

Conexiones Activas

SELECT
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    NOW() - query_start as duration
FROM pg_stat_activity
WHERE datname = 'nostromo'
  AND state != 'idle'
ORDER BY query_start;

Bloqueos

SELECT
    blocked.pid as blocked_pid,
    blocked.query as blocked_query,
    blocking.pid as blocking_pid,
    blocking.query as blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.database = blocking_locks.database
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

---

Troubleshooting

Error: “schema not found”

Causa: Tenant no existe o typo en schema name.

-- Verificar que schema existe
SELECT nspname FROM pg_namespace WHERE nspname LIKE 'tenant_%';

Error: “too many connections”

Causa: Pool agotado o conexiones no liberadas.

-- Ver conexiones actuales
SELECT count(*) FROM pg_stat_activity WHERE datname = 'nostromo';

-- Matar conexiones idle antiguas (con precaución)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'nostromo'
  AND state = 'idle'
  AND query_start < NOW() - INTERVAL '10 minutes';

Performance degradado

Pasos:

1. Check pg_stat_statements para queries lentas
2. Run EXPLAIN ANALYZE en queries problemáticas
3. Verificar índices con pg_stat_user_indexes
4. Ejecutar VACUUM ANALYZE si necesario

---

Security

Roles y Permisos

-- Rol de aplicación (mínimo privilegio)
CREATE ROLE nostromo_app WITH LOGIN PASSWORD '<STRONG_PASSWORD>';
GRANT CONNECT ON DATABASE nostromo TO nostromo_app;
GRANT USAGE ON SCHEMA central TO nostromo_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA central TO nostromo_app;

-- Rol read-only para reportes
CREATE ROLE nostromo_readonly WITH LOGIN PASSWORD '<READ_PASSWORD>';
GRANT CONNECT ON DATABASE nostromo TO nostromo_readonly;
GRANT USAGE ON SCHEMA central, parametros TO nostromo_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA central, parametros TO nostromo_readonly;

Row Level Security (RLS) - Opcional

Para mayor aislamiento entre tenants:

-- Habilitar RLS en tabla central.users
ALTER TABLE central.users ENABLE ROW LEVEL SECURITY;

-- Política: usuarios solo ven su propio tenant
CREATE POLICY tenant_isolation ON central.users
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant'));

---

Related Documentation

• ADR-001: Multi-Tenant Strategy - Decisión arquitectónica
• ADR-002: Pool Management - Gestión de conexiones
• Runbook: Backup - Backup de schemas
• Infrastructure: Docker - Contenedor PostgreSQL

---

Changelog

Fecha	Version	Cambios
2026-01-18	1.0	Documentación inicial creada