Fix N+1 query pattern in ListTables with include_size option

[sgaunet]

Problem

The ListTables method in internal/app/app.go (lines 121-131) executes a separate query for each table when include_size=true, creating an N+1 query pattern.

Current implementation:

if opts != nil && opts.IncludeSize {
    for _, table := range tables {
        stats, err := a.client.GetTableStats(table.Schema, table.Name)
        if err != nil {
            logger.Log().Warn().
                Str("schema", table.Schema).
                Str("table", table.Name).
                Err(err).
                Msg("Failed to get table stats")
            continue
        }
        table.RowCount = stats.EstimatedRows
        table.TotalSize = stats.TotalSize
    }
}

Performance Impact

For a database with 100 tables, this executes:

• 1 query to list tables
• 100 additional queries for table statistics
• Total: 101 queries instead of potentially 1-2

This causes:

• Increased latency (especially over network connections)
• Higher database load
• Poor scalability with large table counts

Proposed Solution

Option 1: Batch query for all table stats

Modify GetTableStats to accept multiple tables or create a new batch method:

func (c *PostgreSQLClientImpl) GetBatchTableStats(tables []TableIdentifier) (map[string]*TableStats, error) {
    // Single query with UNION ALL or array filtering
    query := `
        SELECT 
            schemaname,
            tablename,
            pg_total_relation_size(schemaname||'.'||tablename)::bigint as total_size,
            n_live_tup as estimated_rows
        FROM pg_stat_user_tables
        WHERE (schemaname, tablename) IN (VALUES ($1, $2), ($3, $4), ...)
    `
    // ...
}

Option 2: Single query for all tables in schema

Since most calls are for a single schema, fetch all stats at once:

query := `
    SELECT 
        schemaname,
        tablename,
        pg_total_relation_size(quote_ident(schemaname)||'.'||quote_ident(tablename))::bigint,
        n_live_tup
    FROM pg_stat_user_tables
    WHERE schemaname = $1
`

Impact

• Severity: MEDIUM
• Type: Performance
• Location: internal/app/app.go:121-131
• Expected improvement: 100+ queries → 1-2 queries

Checklist

• Design batch statistics query
• Refactor GetTableStats or add GetBatchTableStats method
• Update ListTables to use batch method
• Add benchmarks comparing old vs new approach
• Test with databases containing 100+ tables
• Document performance characteristics in README