iCli: A Read-Only PostgreSQL Profiler for Debugging Slow Queries and Index Problems

Ok At some point, every backend developer ends up debugging PostgreSQL from a terminal you are not alone .

A query is slow. CPU is high. Something is holding a lock. An endpoint that used to respond in 100ms is suddenly taking seconds.

So you open psql, search through an old Notion page(or saved somewhere , ahh good old pre ai days) for a pg_stat_statements query, copy it, modify it, run it, and start reading a wall of SQL and execution-plan output.

I’ve done this more times than I’d like.

The problem usually isn’t that PostgreSQL is doing something mysterious. It’s often something much simpler:

• An ORM is generating an N+1 query pattern.
• A large table is being sequentially scanned.
• A foreign key has no supporting index.
• An index exists but is never being used.
• A sequence is getting dangerously close to its integer limit.
• A query plan contains an obvious bottleneck that is buried inside a large EXPLAIN output.

That was the reason I built iCli.

What is iCli?

iCli is a read-only PostgreSQL profiler written in Go.

The goal is simple: instead of remembering a collection of pg_stat_*, pg_catalog, and EXPLAIN queries, I wanted one CLI that could run common PostgreSQL diagnostics and present the results in a way that is actually useful from a terminal.

It currently covers:

• Slow query analysis
• Query execution plans
• N+1 query detection
• Missing foreign-key indexes
• Unused indexes
• Sequence and integer overflow risks
• Basic database health information
• Suggest you right sql query for all the above problems so that you can fix instantly

And one constraint was important to me from the beginning:

iCli should not modify the database.

It uses read-only metadata queries and EXPLAIN analysis. There are no INSERT, UPDATE, or DELETE operations performed by the profiler.

1. Finding N+1 Queries

One of the problems I wanted iCli to catch is the classic N+1 query.

For example, an API might first fetch 100 users:

SELECT * FROM users LIMIT 100;

And then the application does this:

SELECT * FROM orders WHERE user_id = $1;
SELECT * FROM orders WHERE user_id = $2;
SELECT * FROM orders WHERE user_id = $3;
...

Each query might be fast enough to look harmless in isolation.

The problem is that you’re now making 100 database round trips instead of one batched query.

You can run:

icli n1query -m 50

The idea is to look at repeated parameterized queries and identify patterns that are consistent with N+1 behaviour.

When possible, iCli also suggests alternatives such as batching IDs with ANY(...) or using the ORM's preloading/prefetching functionality.

This is particularly useful because traditional slow-query thresholds don’t necessarily catch N+1 problems. A query that takes 0.5ms isn’t interesting by itself. Running it 500 times in a single request is a different story.

2. Analyze Slow PostgreSQL Queries

PostgreSQL already gives us excellent information through pg_stat_statements and EXPLAIN.

The problem is that the raw output isn’t always pleasant to work with when you’re trying to debug something quickly.

With:

icli analyze

iCli looks at query statistics and can inspect execution plans using:

EXPLAIN (FORMAT JSON)

It then walks the plan tree and highlights things worth looking at, such as:

• Sequential scans on large tables
• Expensive joins
• Nested-loop operations
• Large sorts
• Sorts that spill to disk
• Other expensive plan nodes

For example:

icli analyze -n 5

shows the top queries that iCli considers worth investigating.

The goal isn’t to replace EXPLAIN.

It’s to get you to the interesting part of the plan faster.

3. Find Missing Foreign-Key Indexes

This is one of those PostgreSQL details that’s easy to forget until it causes a production problem.

Suppose you have:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id)
);

The foreign key enforces the relationship, but PostgreSQL doesn’t automatically create an index on orders.user_id.

That can matter when rows in the parent table are updated or deleted. PostgreSQL may need to check the referencing rows in the child table, and without a suitable index that check can become expensive as the table grows.

iCli checks PostgreSQL’s catalog information and looks for foreign-key columns without supporting indexes.

icli index missing-fk

The useful part here is that this is something you can check before it becomes a production debugging session.

4. Find Unused Indexes

Indexes aren’t free.

They consume storage and have to be maintained when data changes.

If a table has several indexes that aren’t being used, they can add unnecessary write overhead without providing much value.

Run:

icli index unused

iCli uses PostgreSQL index statistics to identify indexes that have recorded zero scans, while excluding indexes such as primary-key and unique indexes from the basic cleanup check.

This isn’t an automatic “delete these indexes” command.

Index usage statistics are workload-dependent and can reset, so the output should be treated as a list of candidates for investigation rather than instructions to blindly drop indexes.

That’s an important distinction.

5. Check Sequence and Integer Overflow

This one is less common, but when it happens, it’s painful.

An INTEGER in PostgreSQL has a maximum value of:

2,147,483,647

If an ID sequence is getting close to that limit, eventually inserts can start failing.

iCli checks sequence usage and reports how much capacity remains.

icli sequence

You can also configure the warning threshold:

icli sequence -t 50

For sequences approaching their configured threshold, iCli can provide migration SQL for moving an integer-based ID to BIGINT.

The useful part isn’t the migration command itself.

It’s finding the problem before the sequence actually reaches its limit.

6. Get a Quick PostgreSQL Health Summary

Sometimes you don’t need a full query analysis.

You just want a quick answer to:

“How does this database look right now?”

That’s what:

icli report

is for.

It presents information such as:

• Connection usage
• Cache hit ratio
• Server uptime
• Relevant PostgreSQL configuration information
• Other basic health metrics

The output is designed for a terminal rather than a dashboard.

Why a CLI?

There are already excellent PostgreSQL monitoring and APM products.

I wasn’t trying to build another dashboard.

I wanted something I could install quickly and run against a database when I’m already debugging from a terminal.

That means:

icli analyze
icli index
icli sequence
icli report

instead of keeping a collection of SQL snippets around.

There is also a practical benefit to keeping the tool read-only: I can use the same profiler during local development, staging investigations, or production incidents without giving it permissions to change application data.

Built with Go

iCli is written in Go.

For the terminal UI, I’m using Lipgloss, which makes it possible to keep the output readable without turning the CLI into a giant wall of plain text.

For example, the output uses:

• Severity indicators
• Tables
• Formatted query information
• Human-readable explanations
• SQL suggestions where appropriate

I’m trying to keep the output useful first and pretty second.

A database profiler shouldn’t need a dashboard to tell you that you have a problem.

Install iCli

If you already have Go installed:

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

Or you can download a pre-built binary from the GitHub releases.

Then point it at PostgreSQL using PGDSN:

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

Run the interactive CLI:

icli

Or go directly to a specific check:

icli analyze
icli index
icli sequence
icli report

What’s Next?

iCli is still evolving.

Some areas I’m interested in improving are:

• Better N+1 detection heuristics
• More execution-plan analysis
• More PostgreSQL configuration checks
• Better index recommendations
• More useful query remediation suggestions
• Additional database capacity checks

The interesting part of building this isn’t just writing SQL against pg_catalog.

It’s figuring out how to turn PostgreSQL’s enormous amount of internal information into something a developer can understand in a few seconds.

If you work with PostgreSQL and have a diagnostic you’d normally solve with a 30-line SQL query, I’d like to hear about it.

GitHub: https://github.com/abhiraj-ku/iCli
X: https://x.com/abhirajabhi312

The project is open source under the MIT License.

If you find it useful, star the repo, open an issue, or send a PR.