pg_stat_insightsAPI Reference

import asyncio
import aiohttp
from datetime import datetime, timedelta

class PgStatInsightsClient:
    def __init__(self, base_url="http://localhost:8000/api/v1/insights"):
        self.base_url = base_url
        self.session = None
    
    async def __aenter__(self):
        self.session = aiohttp.ClientSession()
        return self
    
    async def __aexit__(self, exc_type, exc_val, exc_tb):
        if self.session:
            await self.session.close()
    
    async def get_dashboard(self):
        """Get complete insights dashboard"""
        async with self.session.get(f"{self.base_url}/dashboard") as response:
            return await response.json()
    
    async def get_slow_queries(self, threshold=100):
        """Get slowest queries"""
        params = {'threshold': threshold}
        async with self.session.get(f"{self.base_url}/queries/slow", params=params) as response:
            return await response.json()
    
    async def get_table_bloat(self, min_bloat=5.0):
        """Get tables with significant bloat"""
        params = {'bloat_threshold': min_bloat}
        async with self.session.get(f"{self.base_url}/tables", params=params) as response:
            return await response.json()
    
    async def get_recommendations(self, priority='high'):
        """Get optimization recommendations"""
        params = {'priority': priority}
        async with self.session.get(f"{self.base_url}/recommendations", params=params) as response:
            return await response.json()

# Usage
async def main():
    async with PgStatInsightsClient() as client:
        # Get dashboard overview
        dashboard = await client.get_dashboard()
        print(f"Total queries: {dashboard['query_stats']['total_queries']}")
        print(f"Cache hit ratio: {dashboard['cache_stats']['buffer_hit_ratio']}%")
        
        # Get slow queries
        slow_queries = await client.get_slow_queries(threshold=50)
        for query in slow_queries['slow_queries']:
            print(f"Slow query: {query['query'][:100]}...")
            print(f"Avg time: {query['avg_time']}ms")
        
        # Get recommendations
        recommendations = await client.get_recommendations('high')
        for rec in recommendations['recommendations']:
            print(f"Recommendation: {rec['title']}")
            print(f"Impact: {rec['impact']}, Effort: {rec['effort']}")

# Run the client
asyncio.run(main())

class PgStatInsightsClient {
    constructor(baseUrl = 'http://localhost:8000/api/v1/insights') {
        this.baseUrl = baseUrl;
    }
    
    async request(endpoint, params = {}) {
        const url = new URL(`${this.baseUrl}${endpoint}`);
        Object.keys(params).forEach(key => 
            url.searchParams.append(key, params[key])
        );
        
        const response = await fetch(url);
        if (!response.ok) {
            throw new Error(`HTTP error! status: ${response.status}`);
        }
        
        return response.json();
    }
    
    async getDashboard() {
        return this.request('/dashboard');
    }
    
    async getSlowQueries(threshold = 100) {
        return this.request('/queries/slow', { threshold });
    }
    
    async getTableBloat(minBloat = 5.0) {
        return this.request('/tables', { bloat_threshold: minBloat });
    }
    
    async getRecommendations(priority = 'high') {
        return this.request('/recommendations', { priority });
    }
    
    async getCacheStats() {
        return this.request('/cache');
    }
    
    async getReplicationStatus() {
        return this.request('/replication');
    }
}

// Usage
const client = new PgStatInsightsClient();

async function analyzePerformance() {
    try {
        // Get overall dashboard
        const dashboard = await client.getDashboard();
        console.log('Performance Overview:');
        console.log(`Total queries: ${dashboard.query_stats.total_queries}`);
        console.log(`Cache hit ratio: ${dashboard.cache_stats.buffer_hit_ratio}%`);
        
        // Analyze slow queries
        const slowQueries = await client.getSlowQueries(50);
        console.log('\nSlow Queries:');
        slowQueries.slow_queries.forEach(query => {
            console.log(`- ${query.query.substring(0, 100)}...`);
            console.log(`  Avg time: ${query.avg_time}ms`);
        });
        
        // Check table bloat
        const tableBloat = await client.getTableBloat(10);
        console.log('\nTables with Bloat:');
        tableBloat.tables.forEach(table => {
            console.log(`- ${table.table_name}: ${table.bloat_percentage}% bloat`);
        });
        
        // Get recommendations
        const recommendations = await client.getRecommendations('high');
        console.log('\nHigh Priority Recommendations:');
        recommendations.recommendations.forEach(rec => {
            console.log(`- ${rec.title}`);
            console.log(`  Impact: ${rec.impact}, Effort: ${rec.effort}`);
        });
        
    } catch (error) {
        console.error('Error analyzing performance:', error);
    }
}

analyzePerformance();

# Get complete dashboard
curl -X GET "http://localhost:8000/api/v1/insights/dashboard" \
  -H "Content-Type: application/json"

# Get slow queries (threshold 100ms)
curl -X GET "http://localhost:8000/api/v1/insights/queries/slow?threshold=100" \
  -H "Content-Type: application/json"

# Get frequent queries
curl -X GET "http://localhost:8000/api/v1/insights/queries/frequent?limit=5" \
  -H "Content-Type: application/json"

# Get table bloat analysis
curl -X GET "http://localhost:8000/api/v1/insights/tables?bloat_threshold=5.0" \
  -H "Content-Type: application/json"

# Get index usage statistics
curl -X GET "http://localhost:8000/api/v1/insights/indexes?unused_only=true" \
  -H "Content-Type: application/json"

# Get missing index recommendations
curl -X GET "http://localhost:8000/api/v1/insights/indexes/missing?min_benefit=50" \
  -H "Content-Type: application/json"

# Get cache statistics
curl -X GET "http://localhost:8000/api/v1/insights/cache" \
  -H "Content-Type: application/json"

# Get replication status
curl -X GET "http://localhost:8000/api/v1/insights/replication" \
  -H "Content-Type: application/json"

# Get current locks
curl -X GET "http://localhost:8000/api/v1/insights/locks?blocking_only=true" \
  -H "Content-Type: application/json"

# Get optimization recommendations
curl -X GET "http://localhost:8000/api/v1/insights/recommendations?priority=high&limit=10" \
  -H "Content-Type: application/json"

# Get vacuum recommendations
curl -X GET "http://localhost:8000/api/v1/insights/recommendations/vacuum" \
  -H "Content-Type: application/json"