Overview
Poge provides two statistics endpoints for monitoring connection pooling and query caching performance. These endpoints help track resource usage, cache efficiency, and connection health.
Pool Statistics
Retrieves statistics for all active connection pools.
Response
Array of pool statistics objects, one per connection pool
Unique identifier for the connection pool
Total number of connections in the pool
Number of idle connections available
stats[].activeConnections
Number of connections currently in use
Number of clients waiting for a connection
Example
curl http://localhost:3000/api/stats/pools
{
"stats": [
{
"poolId": "localhost:5432:postgres:mydb",
"totalConnections": 5,
"idleConnections": 3,
"activeConnections": 2,
"waitingClients": 0
},
{
"poolId": "db.example.com:5432:admin:analytics",
"totalConnections": 8,
"idleConnections": 5,
"activeConnections": 3,
"waitingClients": 1
}
]
}
{
"error": "Failed to retrieve pool statistics",
"stats": []
}
Implementation
From /app/api/stats/pools/route.ts:4-14:
export async function GET() {
try {
const stats = dbPool.getPoolStats()
return NextResponse.json({ stats })
} catch (error: any) {
console.error('Failed to get pool stats:', error)
return NextResponse.json({
error: 'Failed to retrieve pool statistics',
stats: []
}, { status: 500 })
}
}
Use Cases
- Performance Monitoring: Track connection pool utilization
- Capacity Planning: Identify when pools are saturated
- Debugging: Diagnose connection leaks or bottlenecks
- Health Checks: Verify database connectivity status
Cache Statistics
Retrieves query cache statistics and recent cache entries.
Response
Cache performance statistics
Total number of queries executed
Number of queries served from cache
Number of queries that missed cache
Cache hit rate as a percentage (0-100)
Average query execution time in milliseconds
Number of entries currently in cache
Array of up to 10 most recent cache entries
SQL query that was cached
Original execution time in milliseconds
ISO 8601 timestamp when cached
Number of times this cache entry was accessed
Example
curl http://localhost:3000/api/stats/cache
{
"stats": {
"totalQueries": 1500,
"cacheHits": 1200,
"cacheMisses": 300,
"hitRate": 80,
"avgExecutionTime": 45,
"cacheSize": 250
},
"entries": [
{
"query": "SELECT * FROM users WHERE id = 1",
"executionTime": 12,
"timestamp": "2024-03-20T10:30:00.000Z",
"hits": 15
},
{
"query": "SELECT COUNT(*) FROM orders WHERE status = 'pending'",
"executionTime": 35,
"timestamp": "2024-03-20T10:28:00.000Z",
"hits": 8
}
]
}
{
"error": "Failed to retrieve cache statistics",
"stats": {
"totalQueries": 0,
"cacheHits": 0,
"cacheMisses": 0,
"hitRate": 0,
"avgExecutionTime": 0,
"cacheSize": 0
},
"entries": []
}
Implementation
From /app/api/stats/cache/route.ts:4-28:
export async function GET() {
try {
const stats = queryCache.getStats()
const entries = queryCache.getCacheEntries()
return NextResponse.json({
stats,
entries: entries.slice(0, 10) // Limit to 10 most recent entries
})
} catch (error: any) {
console.error('Failed to get cache stats:', error)
return NextResponse.json({
error: 'Failed to retrieve cache statistics',
stats: {
totalQueries: 0,
cacheHits: 0,
cacheMisses: 0,
hitRate: 0,
avgExecutionTime: 0,
cacheSize: 0
},
entries: []
}, { status: 500 })
}
}
Use Cases
- Cache Optimization: Monitor cache effectiveness
- Performance Tuning: Identify frequently cached queries
- Resource Management: Track cache memory usage
- Query Analysis: Find slow queries that benefit from caching
Clear Cache
Clears all entries from the query cache.
Response
Whether cache was cleared successfully
Success confirmation message
Example
curl -X DELETE http://localhost:3000/api/stats/cache
{
"success": true,
"message": "Cache cleared successfully"
}
{
"error": "Failed to clear cache"
}
Implementation
From /app/api/stats/cache/route.ts:30-40:
export async function DELETE() {
try {
queryCache.clear()
return NextResponse.json({ success: true, message: 'Cache cleared successfully' })
} catch (error: any) {
console.error('Failed to clear cache:', error)
return NextResponse.json({
error: 'Failed to clear cache'
}, { status: 500 })
}
}
Use Cases
- Cache Invalidation: Clear stale cache after data changes
- Testing: Reset cache for performance benchmarks
- Debugging: Clear cache to diagnose caching issues
- Memory Management: Free cache memory when needed
Monitoring Dashboard Example
Here’s how to build a simple monitoring dashboard:
// Fetch both pool and cache statistics
const [poolData, cacheData] = await Promise.all([
fetch('/api/stats/pools').then(r => r.json()),
fetch('/api/stats/cache').then(r => r.json())
]);
// Calculate totals
const totalPools = poolData.stats.length;
const totalActiveConnections = poolData.stats.reduce(
(sum, pool) => sum + pool.activeConnections, 0
);
console.log(`Active Pools: ${totalPools}`);
console.log(`Active Connections: ${totalActiveConnections}`);
console.log(`Cache Hit Rate: ${cacheData.stats.hitRate}%`);
console.log(`Avg Query Time: ${cacheData.stats.avgExecutionTime}ms`);
- Pool stats: Less than 5ms (no database queries)
- Cache stats: Less than 10ms (in-memory operations)
Statistics endpoints do not require database credentials and can be called without authentication.