007_add_vulnerability_stats_indexes

Migration 007: Add Vulnerability Statistics Indexes

Purpose

This migration adds performance indexes to optimize the "Most Vulnerable Hosts" dashboard statistics queries. These indexes significantly improve query performance when calculating weighted vulnerability scores across large host databases.

Changes

Indexes Added

1. idx_host_vulnerabilities_host_id: Optimizes lookups by host_id in the junction table
2. idx_host_vulnerabilities_vuln_id: Optimizes lookups by vulnerability_id in the junction table
3. idx_vulnerabilities_risk_score: Optimizes sorting and aggregation by risk_score (descending order)
4. idx_host_vuln_stats: Composite partial index for the main statistics query (filters by active status)

Performance Impact

Before: JOIN queries on host_vulnerabilities without proper indexes can result in full table scans After: Queries use index scans, significantly reducing execution time

Expected Performance

• Small databases (<100 hosts): 10-50ms → 5-10ms
• Medium databases (100-1000 hosts): 100-500ms → 20-50ms
• Large databases (>1000 hosts): 1-5s → 50-200ms

Query Optimization

The composite index idx_host_vuln_stats specifically optimizes this query pattern:

SELECT 
    h.id, h.ip, h.hostname,
    COUNT(DISTINCT hv.vulnerability_id) as total_vulnerabilities,
    SUM(v.risk_score) as weighted_risk_score
FROM hosts h
INNER JOIN host_vulnerabilities hv ON hv.host_id = h.id
INNER JOIN vulnerabilities v ON v.id = hv.vulnerability_id
WHERE hv.status = 'active'
GROUP BY h.id, h.ip, h.hostname
ORDER BY weighted_risk_score DESC

Running the Migration

Apply Migration

cd /Users/oz/Projects/Sirius-Project/minor-projects/go-api
go run migrations/007_add_vulnerability_stats_indexes/main.go

Rollback Migration

go run migrations/007_add_vulnerability_stats_indexes/main.go --rollback

Verification

After running the migration, verify the indexes were created:

-- Check index existence
\d+ host_vulnerabilities
\d+ vulnerabilities

-- Verify query plan uses indexes
EXPLAIN ANALYZE
SELECT 
    h.id, h.ip, h.hostname,
    COUNT(DISTINCT hv.vulnerability_id) as total_vulnerabilities,
    SUM(v.risk_score) as weighted_risk_score
FROM hosts h
INNER JOIN host_vulnerabilities hv ON hv.host_id = h.id
INNER JOIN vulnerabilities v ON v.id = hv.vulnerability_id
WHERE hv.status = 'active'
GROUP BY h.id, h.ip, h.hostname
ORDER BY weighted_risk_score DESC
LIMIT 10;

Look for "Index Scan" or "Index Only Scan" in the query plan instead of "Seq Scan".

Dependencies

• Requires existing tables: hosts, host_vulnerabilities, vulnerabilities
• Compatible with migrations 001-006
• No schema changes, only index additions

Safety

• Uses IF NOT EXISTS clauses to prevent errors on re-runs
• Indexes can be dropped and recreated without data loss
• Does not lock tables for writes (PostgreSQL supports concurrent index creation)

Related Features

This migration supports:

• Dashboard Analytics (Project 3: Most Vulnerable Hosts)
• Host vulnerability statistics API endpoints
• Real-time dashboard metrics with Valkey caching