GitHub - abhiraj-ku/iCli: Suggest the indexing strategy for your database

⚡ iCli — PostgreSQL Query & Index Profiler

iCli is a read-only Go CLI tool for slow query profiling, N+1 loop detection, execution plan analysis, and index auditing.

Demo

Features

• N+1 Query Loop Detection: Identifies parameterized queries executing repeatedly in application loops (WHERE col = $1), correlates parent/child queries, and recommends batched SQL (WHERE col = ANY(...)) or ORM prefetching.
• Expensive Query Analysis: Identifies slow and resource-heavy queries using pg_stat_statements.
• Execution Plan Profiling: Runs EXPLAIN (FORMAT JSON) and detects plan bottlenecks (e.g., sequential scans, disk sort spillage, inefficient joins).
• Missing Foreign Key Index Detection: Finds foreign key constraints on child tables without a covering index, preventing severe sequential scan table locks on parent UPDATE or DELETE operations.
• Unused Index Scanner: Identifies non-primary, non-unique indexes with zero recorded scans that degrade INSERT/UPDATE performance.
• Table Bloat & Dead Tuple Auditor: Inspects pg_stat_user_tables via icli bloat to identify tables with high dead tuple counts and ratio percentages that impair HOT update paths and degrade write throughput.
• Sequence & Integer ID Overflow Auditor: Inspects sequence generators and primary key integer types via icli sequence to flag columns nearing max capacity before production outage risks occur.
• Database Health Reports: Displays real-time database connection metrics, cache hit ratios, and server metadata via icli report.
• Modern Terminal UI: Formats findings into container cards, severity badges, rounded tables, and ready-to-run SQL remediation snippets powered by Lipgloss.

Requirements

• Go 1.25 or later.
• PostgreSQL with pg_stat_statements enabled.

Enable the extension at the server level, then restart PostgreSQL:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

After restarting PostgreSQL, enable it in the target database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

On a Homebrew PostgreSQL installation, restart with:

brew services restart postgresql@18

Quick Start

Install via go install

If you have Go installed, you can install icli directly:

go install github.com/abhiraj-ku/pg_adv/cmd/icli@latest

Run Locally / From Source

Clone the repository and run the CLI with a PostgreSQL connection string.

DSN resolution order:

1. -d / --dsn
2. PGDSN environment variable

Examples:

git clone https://github.com/abhiraj-ku/iCli.git
cd iCli
export PGDSN="postgres://postgres@localhost:5432/postgres?sslmode=disable"

# Display interactive CLI commands menu & guide
icli

# Analyze top slow queries & execution plans
icli analyze -n 5

# Detect N+1 query patterns & loop bottlenecks
icli n1query -m 50

# Scan for unused indexes and missing foreign key indexes
icli index
icli index unused
icli index missing-fk

# Generate an instant database health summary report
icli report

# Audit sequence capacity and integer ID overflow risks (filter threshold >= 50%)
icli sequence -t 50

# Detect table bloat and dead tuples slowing down write operations
icli bloat

# Filter table bloat with custom thresholds (min 10% bloat ratio, min 500 dead tuples, limit top 10)
icli bloat -t 10 -m 500 -l 10

# Help command 
icli --help

Or pass the DSN explicitly:

icli analyze -d="postgres://postgres@localhost:5432/postgres?sslmode=disable"

The long-form flag is also supported:

icli analyze --dsn="postgres://postgres@localhost:5432/postgres?sslmode=disable"

Build a binary with:

go build -o icli ./cmd/icli
export PGDSN="postgres://postgres@localhost:5432/postgres?sslmode=disable"
./icli analyze

Developer Setup

Prerequisites

• Go 1.25 or later
• PostgreSQL instance running locally or remotely
• pg_stat_statements enabled in the target database
• A valid DSN available through either -d/--dsn or PGDSN

Local database setup

Create a local PostgreSQL database and enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Then export the connection string for local development:

export PGDSN="postgres://postgres@localhost:5432/postgres?sslmode=disable"

Repo layout

• cmd/icli — CLI entrypoint and subcommand routing (analyze, index, n1query, report, sequence, update)
• internals/analyzer — execution-plan issue rules, N+1 loop detection heuristics, and sequence overflow analysis
• internals/db — PostgreSQL queries (pg_stat_statements, unused indexes, unindexed foreign keys, sequence capacity, N+1 candidate query stats)
• internals/health — database health stats collection (cache hit ratio, active connections)
• internals/report — modern Lipgloss terminal TUI rendering

Common dev commands

go test ./...
go run ./cmd/icli
go run ./cmd/icli sequence -t 50

go run ./cmd/icli report

Troubleshooting

• If you see connection refused, confirm PostgreSQL is running and the host/port are correct.
• If the slow query report is empty, ensure pg_stat_statements is enabled and queries have been executed.
• If the DSN is missing, pass -d / --dsn or export PGDSN.
• If formatting looks odd in a narrow terminal, run it in a wider terminal or resize the window.

Releases

Prebuilt releases are available for these platforms on the GitHub Releases page:

Download the archive for your platform from the Releases page, extract it, and run icli:

tar -xzf icli_Darwin_arm64.tar.gz
./icli -dsn="postgres://user:password@localhost:5432/database?sslmode=disable"

Windows and Intel macOS binaries are not included in releases.

How It Works

1. Reads the PostgreSQL server version to select the correct execution-time column (total_time vs total_exec_time).
2. Queries pg_stat_statements and excludes iCli's internal telemetry queries.
3. Analyzes high-frequency parameterized queries (WHERE col = $1), detecting N+1 application loop patterns and correlating parent/child queries.
4. Sanitizes parameter placeholders before requesting an EXPLAIN (FORMAT JSON) plan.
5. Walks execution plans and reports bottlenecks like sequential scans, disk sort spillage, and inefficient nested loops.
6. Queries pg_stat_user_indexes and pg_index to find non-primary, non-unique indexes with 0 recorded scans.
7. Inspects pg_constraint, pg_attribute, and pg_index to detect unindexed foreign keys on child tables.
8. Queries pg_sequences and system dependencies to calculate sequence percentage utilization (last_value / max_value) and map sequences to table.column pairs, generating zero-downtime BIGINT migration DDL commands for columns nearing capacity limits.
9. Queries pg_stat_user_tables to analyze dead tuple accumulation (n_dead_tup), bloat ratio percentage (n_dead_tup / (n_live_tup + n_dead_tup)), and autovacuum execution history. Filters results using -t/--threshold, -m/--min-dead, and -l/--limit, outputting actionable VACUUM ANALYZE and autovacuum tuning DDL recommendations.