Service Hierarchy Performance Optimisation Guide

Purpose: Query patterns, caching strategies, and performance benchmarks for service hierarchies.

---

Recursive CTE Query Patterns

Pattern 1: Get Full Hierarchy (Template Level)

Use Case: Display complete service catalogue hierarchy.

WITH RECURSIVE service_hierarchy AS (
  -- Base case: PRIMARY services (roots)
  SELECT
    id,
    code,
    role,
    parent_service_template_id,
    description,
    inclusion_type,
    sort_order,
    0 as depth,
    ARRAY[code] as path
  FROM cat_service_template
  WHERE parent_service_template_id IS NULL
    AND role = 'PRIMARY'

  UNION ALL

  -- Recursive case: children
  SELECT
    st.id,
    st.code,
    st.role,
    st.parent_service_template_id,
    st.description,
    st.inclusion_type,
    st.sort_order,
    sh.depth + 1,
    sh.path || st.code
  FROM cat_service_template st
  JOIN service_hierarchy sh ON st.parent_service_template_id = sh.id
  WHERE sh.depth < 5  -- Depth limit for safety
)
SELECT
  REPEAT('  ', depth) || code as display_code,
  role,
  inclusion_type,
  depth
FROM service_hierarchy
ORDER BY path;

Performance Tips

• Add WHERE sh.depth < 5 to prevent infinite recursion
• Index on parent_service_template_id is essential
• Filter by service type early for smaller result sets

Pattern 2: Get Instance Hierarchy for Subscription

Use Case: Display customer's active services in hierarchy.

WITH RECURSIVE instance_hierarchy AS (
  -- Base case: root instances (no parent)
  SELECT
    si.id,
    si.status,
    si.activation_date,
    st.code,
    st.role,
    si.parent_service_id,
    0 as depth,
    ARRAY[si.id] as path
  FROM billing_service_instance si
  JOIN cat_service_template st ON si.service_template_id = st.id
  WHERE si.subscription_id = $1  -- Subscription ID parameter
    AND si.parent_service_id IS NULL

  UNION ALL

  -- Recursive case: children
  SELECT
    child.id,
    child.status,
    child.activation_date,
    child_st.code,
    child_st.role,
    child.parent_service_id,
    ih.depth + 1,
    ih.path || child.id
  FROM billing_service_instance child
  JOIN cat_service_template child_st ON child.service_template_id = child_st.id
  JOIN instance_hierarchy ih ON child.parent_service_id = ih.id
  WHERE ih.depth < 5
)
SELECT * FROM instance_hierarchy
ORDER BY path;

Performance Tips

• Always filter by subscription_id first
• Use prepared statement with parameter binding
• Index on (subscription_id, parent_service_id)

Pattern 3: Get All Children of a Service

Use Case: Cascade operations (suspend, terminate).

WITH RECURSIVE children AS (
  -- Start from specific service
  SELECT id, code, parent_service_template_id, 0 as depth
  FROM cat_service_template
  WHERE id = $1  -- Starting service ID

  UNION ALL

  SELECT st.id, st.code, st.parent_service_template_id, c.depth + 1
  FROM cat_service_template st
  JOIN children c ON st.parent_service_template_id = c.id
  WHERE c.depth < 5
)
SELECT id, code FROM children
WHERE id != $1;  -- Exclude starting service

Pattern 4: Find Path to Root

Use Case: Determine ancestry for eligibility checks.

WITH RECURSIVE ancestry AS (
  SELECT id, code, parent_service_template_id, 0 as depth
  FROM cat_service_template
  WHERE id = $1  -- Starting service ID

  UNION ALL

  SELECT parent.id, parent.code, parent.parent_service_template_id, a.depth - 1
  FROM cat_service_template parent
  JOIN ancestry a ON a.parent_service_template_id = parent.id
)
SELECT code, depth FROM ancestry
ORDER BY depth;

---

Index Strategies

Essential Indexes

-- Primary lookup by parent
CREATE INDEX idx_service_template_parent
ON cat_service_template(parent_service_template_id)
INCLUDE (code, role, inclusion_type, sort_order);

-- Role-based queries
CREATE INDEX idx_service_template_role
ON cat_service_template(role);

-- Code lookups (unique)
CREATE UNIQUE INDEX idx_service_template_code
ON cat_service_template(code);

-- Instance parent relationships
CREATE INDEX idx_service_instance_parent
ON billing_service_instance(parent_service_id)
INCLUDE (status, subscription_id);

-- Subscription-based queries (most common access pattern)
CREATE INDEX idx_service_instance_subscription
ON billing_service_instance(subscription_id, parent_service_id, status);

-- Customer-based queries
CREATE INDEX idx_service_instance_customer
ON billing_service_instance(customer_id, status)
WHERE status = 'ACTIVE';

Composite Indexes for Common Queries

-- Hierarchy display (ordered by sort_order)
CREATE INDEX idx_template_hierarchy_display
ON cat_service_template(parent_service_template_id, sort_order, code);

-- Active instances for subscription
CREATE INDEX idx_instance_active_subscription
ON billing_service_instance(subscription_id, parent_service_id)
WHERE status = 'ACTIVE';

Index Usage Analysis

-- Check index usage
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as times_used,
  idx_tup_read as rows_read,
  idx_tup_fetch as rows_fetched
FROM pg_stat_user_indexes
WHERE tablename IN ('cat_service_template', 'billing_service_instance')
ORDER BY idx_scan DESC;

---

Caching Strategies

Template Caching (Recommended)

Rationale: Service templates rarely change; cache is long-lived.

// Redis caching for service templates
@Injectable()
export class ServiceTemplateCache {
  private readonly TTL = 3600; // 1 hour

  constructor(private readonly redis: RedisClient) {}

  async getTemplate(code: string): Promise<ServiceTemplate | null> {
    const cached = await this.redis.get(`template:${code}`);
    if (cached) return JSON.parse(cached);
    return null;
  }

  async setTemplate(template: ServiceTemplate): Promise<void> {
    await this.redis.setex(
      `template:${template.code}`,
      this.TTL,
      JSON.stringify(template)
    );
  }

  async getHierarchy(parentCode: string): Promise<ServiceTemplate[]> {
    const cached = await this.redis.get(`hierarchy:${parentCode}`);
    if (cached) return JSON.parse(cached);
    return null;
  }

  async setHierarchy(parentCode: string, children: ServiceTemplate[]): Promise<void> {
    await this.redis.setex(
      `hierarchy:${parentCode}`,
      this.TTL,
      JSON.stringify(children)
    );
  }

  async invalidate(code: string): Promise<void> {
    // Invalidate template and all parent hierarchies
    await this.redis.del(`template:${code}`);

    // Find and invalidate parent hierarchies
    const template = await this.findTemplate(code);
    if (template?.parentServiceTemplateId) {
      const parent = await this.findTemplate(template.parentServiceTemplateId);
      await this.redis.del(`hierarchy:${parent.code}`);
    }
  }
}

Instance Caching (Not Recommended)

Rationale: Service instances change frequently; cache invalidation is complex.

Alternative: Use database query optimisation instead of caching.

Hierarchy Tree Caching

// Pre-computed hierarchy tree
interface HierarchyNode {
  template: ServiceTemplate;
  children: HierarchyNode[];
}

@Injectable()
export class HierarchyTreeCache {
  private readonly TTL = 3600;

  async getTree(serviceType: ServiceTypeCode): Promise<HierarchyNode | null> {
    const cached = await this.redis.get(`tree:${serviceType}`);
    if (cached) return JSON.parse(cached);
    return null;
  }

  async buildAndCacheTree(serviceType: ServiceTypeCode): Promise<HierarchyNode> {
    const tree = await this.buildTree(serviceType);
    await this.redis.setex(
      `tree:${serviceType}`,
      this.TTL,
      JSON.stringify(tree)
    );
    return tree;
  }

  private async buildTree(serviceType: ServiceTypeCode): Promise<HierarchyNode> {
    // Fetch all templates for service type
    const templates = await this.templateRepo.find({
      where: { serviceType },
      order: { sortOrder: 'ASC' },
    });

    // Build tree in memory
    const nodeMap = new Map<string, HierarchyNode>();
    const roots: HierarchyNode[] = [];

    for (const template of templates) {
      nodeMap.set(template.code, { template, children: [] });
    }

    for (const template of templates) {
      const node = nodeMap.get(template.code)!;
      if (template.parentServiceTemplateId) {
        const parentCode = templates.find(t => t.id === template.parentServiceTemplateId)?.code;
        if (parentCode) {
          nodeMap.get(parentCode)?.children.push(node);
        }
      } else {
        roots.push(node);
      }
    }

    return roots[0]; // Assuming single root for service type
  }
}

---

Query Optimisation Patterns

Pattern 1: Eager Loading Children

Instead of N+1 queries

const parent = await templateRepo.findOne({ where: { code: 'SVC_MOBILE_BASE' } });
for (const childId of parent.childIds) {
  const child = await templateRepo.findOne({ where: { id: childId } });
  // Process child...
}

Use single query with joins

const hierarchy = await templateRepo
  .createQueryBuilder('parent')
  .leftJoinAndSelect(
    'cat_service_template',
    'child',
    'child.parent_service_template_id = parent.id'
  )
  .where('parent.code = :code', { code: 'SVC_MOBILE_BASE' })
  .orderBy('child.sort_order', 'ASC')
  .getMany();

Pattern 2: Batch Instance Queries

Instead of per-subscription queries

for (const sub of subscriptions) {
  const instances = await instanceRepo.find({ where: { subscriptionId: sub.id } });
}

Batch with IN clause

const subscriptionIds = subscriptions.map(s => s.id);
const allInstances = await instanceRepo.find({
  where: { subscriptionId: In(subscriptionIds) },
});
const groupedBySubscription = groupBy(allInstances, 'subscriptionId');

Pattern 3: Selective Field Loading

Instead of SELECT *

SELECT * FROM cat_service_template WHERE parent_service_template_id = ?;

Select only needed fields

SELECT code, role, inclusion_type, sort_order
FROM cat_service_template
WHERE parent_service_template_id = ?
ORDER BY sort_order;

---

Performance Benchmarks

Expected Query Times

Query Type	Records	Target Time	Max Acceptable
Template by code	1	< 1ms	5ms
Children of parent	~10	< 5ms	20ms
Full hierarchy tree	~100	< 50ms	200ms
Instance hierarchy	~20	< 10ms	50ms
Recursive 3-level	~50	< 30ms	100ms
Recursive 5-level	~100	< 100ms	500ms

Benchmark Query

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
WITH RECURSIVE service_hierarchy AS (
  SELECT id, code, parent_service_template_id, 0 as depth
  FROM cat_service_template
  WHERE parent_service_template_id IS NULL

  UNION ALL

  SELECT st.id, st.code, st.parent_service_template_id, sh.depth + 1
  FROM cat_service_template st
  JOIN service_hierarchy sh ON st.parent_service_template_id = sh.id
  WHERE sh.depth < 5
)
SELECT * FROM service_hierarchy;

Monitoring Slow Queries

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = 100; -- Log queries > 100ms
SELECT pg_reload_conf();

-- Check for slow hierarchy queries
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
WHERE query LIKE '%service_hierarchy%' OR query LIKE '%RECURSIVE%'
ORDER BY mean_time DESC
LIMIT 10;

---

Depth Limit Recommendations

Why Limit Depth?

Depth	Recursive Iterations	Typical Time	Use Case
1	1	< 5ms	Direct children only
2	2-3	< 20ms	Most service types
3	4-7	< 50ms	NBN hierarchy
4	8-15	< 100ms	Complex nesting
5	16-31	< 200ms	Absolute max

Implementing Depth Limits

In SQL:

WITH RECURSIVE hierarchy AS (
  SELECT *, 0 as depth FROM ...
  UNION ALL
  SELECT *, h.depth + 1 FROM ...
  JOIN hierarchy h ON ...
  WHERE h.depth < 5  -- Hard limit
)
SELECT * FROM hierarchy;

In Application:

async function getHierarchy(parentId: string, maxDepth: number = 5): Promise<ServiceNode[]> {
  if (maxDepth <= 0) return [];

  const children = await templateRepo.find({
    where: { parentServiceTemplateId: parentId },
    order: { sortOrder: 'ASC' },
  });

  return Promise.all(children.map(async child => ({
    template: child,
    children: await getHierarchy(child.id, maxDepth - 1),
  })));
}

---

Pagination Strategies for Large Hierarchies

Keyset Pagination (Recommended)

-- First page
SELECT code, role, sort_order
FROM cat_service_template
WHERE parent_service_template_id = ?
ORDER BY sort_order, code
LIMIT 20;

-- Next page (using last item's values)
SELECT code, role, sort_order
FROM cat_service_template
WHERE parent_service_template_id = ?
  AND (sort_order, code) > (?, ?)
ORDER BY sort_order, code
LIMIT 20;

Virtual Scrolling for UI

// Frontend: Only load visible items
interface VirtualHierarchy {
  items: ServiceTemplate[];
  totalCount: number;
  hasMore: boolean;
  cursor: string;
}

async function loadHierarchyPage(
  parentCode: string,
  cursor?: string,
  limit: number = 20
): Promise<VirtualHierarchy> {
  const response = await api.get('/catalog/services/hierarchy', {
    params: { parentCode, cursor, limit },
  });
  return response.data;
}

---

Real-Time vs Batch Processing

Real-Time Operations

Use real-time for:

• Service activation (immediate feedback needed)
• Status changes (customer-facing)
• Eligibility checks (interactive)

Optimisations:

• Use cached templates
• Minimise database round-trips
• Async carrier notifications

Batch Operations

Use batch for:

• Bulk hierarchy updates
• Migration operations
• Reporting/analytics

Optimisations:

-- Batch update with single query
UPDATE billing_service_instance
SET status = 'SUSPENDED'
WHERE parent_service_id IN (
  SELECT id FROM billing_service_instance
  WHERE subscription_id = ? AND status = 'ACTIVE'
);

---

Connection Pool Tuning

// TypeORM connection pool for hierarchy-heavy workloads
const connectionOptions: ConnectionOptions = {
  type: 'postgres',
  // Hierarchy queries are read-heavy
  extra: {
    max: 50,              // Max connections
    min: 10,              // Min connections
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 5000,
  },
  // Enable query caching for templates
  cache: {
    type: 'redis',
    options: {
      host: process.env.REDIS_HOST,
      port: 6379,
    },
    duration: 3600000, // 1 hour
  },
};