/**
* PostgreSQL Performance Tools - Optimization
*/
import type { PostgresAdapter } from '../../PostgresAdapter.js';
import type { ToolDefinition, RequestContext } from '../../../../types/index.js';
import { z } from 'zod';
import { readOnly } from '../../../../utils/annotations.js';
import { getToolIcons } from '../../../../utils/icons.js';
// Helper to handle undefined params (allows tools to be called without {})
const defaultToEmpty = (val: unknown): unknown => val ?? {};
// Preprocess partition strategy params with tableName/name aliases
function preprocessPartitionStrategyParams(input: unknown): unknown {
const normalized = defaultToEmpty(input) as Record<string, unknown>;
const result = { ...normalized };
// Alias: tableName/name → table
if (result['table'] === undefined) {
if (result['tableName'] !== undefined) result['table'] = result['tableName'];
else if (result['name'] !== undefined) result['table'] = result['name'];
}
return result;
}
export function createPerformanceBaselineTool(adapter: PostgresAdapter): ToolDefinition {
const PerformanceBaselineSchema = z.preprocess(
defaultToEmpty,
z.object({
name: z.string().optional().describe('Baseline name for reference')
})
);
return {
name: 'pg_performance_baseline',
description: 'Capture current database performance metrics as a baseline for comparison.',
group: 'performance',
inputSchema: PerformanceBaselineSchema,
annotations: readOnly('Performance Baseline'),
icons: getToolIcons('performance', readOnly('Performance Baseline')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = PerformanceBaselineSchema.parse(params);
const baselineName = parsed.name ?? `baseline_${new Date().toISOString()}`;
const [cacheHit, tableStats, indexStats, connections, dbSize] = await Promise.all([
adapter.executeQuery(`
SELECT
sum(heap_blks_hit) as heap_hits,
sum(heap_blks_read) as heap_reads,
round(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) as cache_hit_ratio
FROM pg_statio_user_tables
`),
adapter.executeQuery(`
SELECT
sum(seq_scan) as total_seq_scans,
sum(idx_scan) as total_idx_scans,
sum(n_tup_ins) as total_inserts,
sum(n_tup_upd) as total_updates,
sum(n_tup_del) as total_deletes,
sum(n_live_tup) as total_live_tuples,
sum(n_dead_tup) as total_dead_tuples
FROM pg_stat_user_tables
`),
adapter.executeQuery(`
SELECT
count(*) as total_indexes,
sum(idx_scan) as total_index_scans
FROM pg_stat_user_indexes
`),
adapter.executeQuery(`
SELECT
count(*) as total_connections,
count(*) FILTER (WHERE state = 'active') as active_connections,
count(*) FILTER (WHERE state = 'idle') as idle_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
`),
adapter.executeQuery(`SELECT pg_database_size(current_database()) as size_bytes`)
]);
return {
name: baselineName,
timestamp: new Date().toISOString(),
metrics: {
cache: cacheHit.rows?.[0],
tables: tableStats.rows?.[0],
indexes: indexStats.rows?.[0],
connections: connections.rows?.[0],
databaseSize: dbSize.rows?.[0]
}
};
}
};
}
export function createConnectionPoolOptimizeTool(adapter: PostgresAdapter): ToolDefinition {
return {
name: 'pg_connection_pool_optimize',
description: 'Analyze connection usage and provide pool optimization recommendations.',
group: 'performance',
inputSchema: z.object({}),
annotations: readOnly('Connection Pool Optimize'),
icons: getToolIcons('performance', readOnly('Connection Pool Optimize')),
handler: async (_params: unknown, _context: RequestContext) => {
const [connStats, settings, waitEvents] = await Promise.all([
adapter.executeQuery(`
SELECT
count(*) as total_connections,
count(*) FILTER (WHERE state = 'active') as active,
count(*) FILTER (WHERE state = 'idle') as idle,
count(*) FILTER (WHERE state = 'idle in transaction') as idle_in_transaction,
count(*) FILTER (WHERE wait_event_type IS NOT NULL) as waiting,
max(EXTRACT(EPOCH FROM (now() - backend_start))) as max_connection_age_seconds,
avg(EXTRACT(EPOCH FROM (now() - backend_start))) as avg_connection_age_seconds
FROM pg_stat_activity
WHERE backend_type = 'client backend'
`),
adapter.executeQuery(`
SELECT
current_setting('max_connections')::int as max_connections,
current_setting('superuser_reserved_connections')::int as reserved_connections
`),
adapter.executeQuery(`
SELECT wait_event_type, wait_event, count(*) as count
FROM pg_stat_activity
WHERE wait_event IS NOT NULL AND backend_type = 'client backend'
GROUP BY wait_event_type, wait_event
ORDER BY count DESC
LIMIT 10
`)
]);
const conn = connStats.rows?.[0];
const config = settings.rows?.[0];
const recommendations: string[] = [];
if (conn && config) {
const totalConnections = Number(conn['total_connections'] ?? 0);
const maxConnections = Number(config['max_connections'] ?? 1);
const idleInTransaction = Number(conn['idle_in_transaction'] ?? 0);
const active = Number(conn['active'] ?? 0);
const idle = Number(conn['idle'] ?? 0);
const maxConnectionAge = Number(conn['max_connection_age_seconds'] ?? 0);
const utilization = (totalConnections / maxConnections) * 100;
if (utilization > 80) {
recommendations.push('Connection utilization is high (>80%). Consider increasing max_connections or using a connection pooler like PgBouncer.');
}
if (idleInTransaction > active) {
recommendations.push('Many idle-in-transaction connections. Check for uncommitted transactions or application issues.');
}
if (idle > active * 3) {
recommendations.push('High ratio of idle to active connections. Consider reducing pool size or idle timeout.');
}
if (maxConnectionAge > 3600) {
recommendations.push('Long-lived connections detected. Consider connection recycling.');
}
}
return {
current: conn,
config,
waitEvents: waitEvents.rows,
recommendations: recommendations.length > 0 ? recommendations : ['Connection pool appears healthy']
};
}
};
}
export function createPartitionStrategySuggestTool(adapter: PostgresAdapter): ToolDefinition {
const PartitionStrategySchema = z.preprocess(
preprocessPartitionStrategyParams,
z.object({
table: z.string().describe('Table to analyze'),
schema: z.string().optional().describe('Schema name')
})
);
return {
name: 'pg_partition_strategy_suggest',
description: 'Analyze a table and suggest optimal partitioning strategy.',
group: 'performance',
inputSchema: PartitionStrategySchema,
annotations: readOnly('Partition Strategy Suggest'),
icons: getToolIcons('performance', readOnly('Partition Strategy Suggest')),
handler: async (params: unknown, _context: RequestContext) => {
const parsed = PartitionStrategySchema.parse(params);
const schemaName = parsed.schema ?? 'public';
const [tableInfo, columnInfo, tableSize] = await Promise.all([
adapter.executeQuery(`
SELECT
relname, n_live_tup, n_dead_tup,
seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE relname = $1 AND schemaname = $2
`, [parsed.table, schemaName]),
adapter.executeQuery(`
SELECT
a.attname as column_name,
t.typname as data_type,
s.n_distinct,
s.null_frac
FROM pg_attribute a
JOIN pg_class c ON a.attrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
JOIN pg_type t ON a.atttypid = t.oid
LEFT JOIN pg_stats s ON s.tablename = c.relname
AND s.attname = a.attname
AND s.schemaname = n.nspname
WHERE c.relname = $1 AND n.nspname = $2
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
`, [parsed.table, schemaName]),
adapter.executeQuery(`
SELECT pg_size_pretty(pg_table_size($1::regclass)) as table_size,
pg_table_size($1::regclass) as size_bytes
`, [`"${schemaName}"."${parsed.table}"`])
]);
const table = tableInfo.rows?.[0];
const columns = columnInfo.rows;
const size = tableSize.rows?.[0];
const suggestions: { strategy: string; column: string; reason: string }[] = [];
if (columns) {
for (const col of columns) {
const colName = col['column_name'] as string;
const dataType = col['data_type'] as string;
const nDistinct = col['n_distinct'] as number;
if (['date', 'timestamp', 'timestamptz'].includes(dataType)) {
suggestions.push({
strategy: 'RANGE',
column: colName,
reason: `${dataType} column ideal for time-based range partitioning (monthly/yearly)`
});
}
if (nDistinct > 0 && nDistinct < 20) {
suggestions.push({
strategy: 'LIST',
column: colName,
reason: `Low cardinality (${String(nDistinct)} distinct values) - good for list partitioning`
});
}
if (['int4', 'int8', 'integer', 'bigint'].includes(dataType) && (nDistinct < 0 || nDistinct > 100)) {
suggestions.push({
strategy: 'HASH',
column: colName,
reason: 'High cardinality integer - suitable for hash partitioning to distribute load'
});
}
}
}
const rowCount = Number(table?.['n_live_tup'] ?? 0);
const sizeBytes = Number(size?.['size_bytes'] ?? 0);
let partitioningRecommended = false;
let reason = '';
if (rowCount > 10_000_000) {
partitioningRecommended = true;
reason = `Table has ${String(rowCount)} rows - partitioning recommended for manageability`;
} else if (sizeBytes > 1_000_000_000) {
partitioningRecommended = true;
reason = 'Table is over 1GB - partitioning can improve query performance and maintenance';
}
return {
table: `${schemaName}.${parsed.table}`,
tableStats: table,
tableSize: size,
partitioningRecommended,
reason,
suggestions: suggestions.slice(0, 5),
note: 'Consider your query patterns when choosing partition key. Range partitioning on date columns is most common.'
};
}
};
}
