Saltar al contenido principal

Database Optimization

Database query optimization and indexing strategies.

Connection Pooling​

DB_POOL_SIZE=40         # Max connections
DB_POOL_SIZE_KNEX=10    # Knex pool size
DB_CONNECTION_TIMEOUT=5000
DB_IDLE_TIMEOUT=10000

Indexing Strategy​

Automatic Indexes​

TypeORM and MikroORM create indexes for:

  • Primary keys (id)
  • Foreign keys (tenantId, organizationId)
  • Unique constraints

Custom Indexes​

@MultiORMEntity("time_log")
@Index(["tenantId", "organizationId", "employeeId"])
@Index(["startedAt", "stoppedAt"])
export class TimeLog extends TenantOrganizationBaseEntity {
  // ...
}

Key Indexes​

TableColumnsPurpose
time_logtenantId, organizationId, employeeIdEmployee time queries
time_logstartedAt, stoppedAtDate range lookups
tasktenantId, projectId, statusTask filtering
employeetenantId, organizationId, isActiveActive employee lists

Query Optimization​

Use Relations Wisely​

// ❌ N+1 problem
const employees = await employeeRepository.find();
for (const emp of employees) {
  emp.user = await userRepository.findOne(emp.userId);
}

// ✅ Eager load relations
const employees = await employeeRepository.find({
  relations: ["user"],
});

Pagination​

Always paginate large result sets:

const result = await repository.findAndCount({
  take: 10,
  skip: 0,
  order: { createdAt: "DESC" },
});