PostgreSQL Database Analyzer

A CLI tool that analyzes PostgreSQL databases for performance issues: unused / missing / duplicate indexes, foreign keys without indexes, slow queries, table bloat, dead tuples, VACUUM needs, and connection / cache health. Outputs structured JSON for automation or rich Markdown reports for humans.

Working with an AI agent? See .github/copilot-instructions.md for the integrated GitHub Copilot agent workflow and JSON contracts.

Features

Index analysis

Unused indexes (low / no scans, ready-to-run DROP INDEX statements).
Missing indexes (tables with high sequential scan activity).
Duplicate / overlapping indexes.
Foreign keys without supporting indexes.

Query analysis

Slow queries via pg_stat_statements (total / mean / min / max time, calls, hit ratio, rows).
Currently long-running queries.
Blocking / blocked queries (lock graph).

Table & storage

Largest tables, dead-tuple ratio, sequential vs. index scans.
Table bloat detection.
Tables needing VACUUM / ANALYZE, with execution support.

System metrics

Database size, cache hit ratio, index hit ratio.
Connection statistics (active / idle / total).
Server info, configuration settings, installed extensions.
Composite health score (0–100) with prioritized recommendations.

Requirements

Node.js >= 20
pnpm >= 10
PostgreSQL 12+
(Optional but recommended) pg_stat_statements extension for slow-query analysis

Installation

pnpm install
cp .env.example .env
# edit .env with your connection details

Configuration

Environment variables (`.env`)

# Database connection (use `export` to make them available to ts-node)
export DB_HOST=your-host.example.com
export DB_PORT=5432
export DB_NAME=mydb
export DB_USER=postgres
export DB_PASSWORD=secret
export DB_SSL=true

# Alternative: standard libpq variables
# export PGHOST=...
# export PGPORT=...
# export PGDATABASE=...
# export PGUSER=...
# export PGPASSWORD=...

Usage

Interactive mode

pnpm start

npm scripts

# Analysis (JSON output)
pnpm analyze              # Full analysis + Markdown report
pnpm analyze:help         # Help
pnpm analyze:health       # Health score + key metrics
pnpm analyze:indexes      # Unused indexes
pnpm analyze:queries      # Slow queries
pnpm analyze:tables       # Largest tables
pnpm analyze:fk           # Foreign keys without indexes
pnpm analyze:connections  # Connection statistics
pnpm analyze:config       # PostgreSQL configuration

# Server info
pnpm server:info

# Index management
pnpm indexes:drop-sql     # Generate DROP INDEX statements

# VACUUM
pnpm vacuum               # Run VACUUM ANALYZE on tables that need it
pnpm vacuum:check         # List tables that need VACUUM

# pg_stat_statements
pnpm pg-stat-statements:create
pnpm pg-stat-statements:drop

# Development
pnpm build                # Compile TypeScript
pnpm lint                 # Biome check
pnpm lint:fix             # Biome auto-fix

Direct CLI

# Source env first so DB_* are exported into the shell
. ./.env && npx ts-node index.ts -j -c <command>

# Examples
. ./.env && npx ts-node index.ts -j -c health
. ./.env && npx ts-node index.ts -j -c slow-queries

# Without env file
npx ts-node index.ts -h localhost -p 5432 -d mydb -U postgres -W secret --ssl -j -c health

Commands

Command	Description
`full`	Complete analysis (default)
`health`	Health score and metrics
`server-info`	Server version and info
`unused-indexes`	Indexes with low / no usage
`missing-indexes`	Tables with high sequential scan activity
`duplicate-indexes`	Overlapping indexes
`fk-without-indexes`	Foreign keys without supporting indexes
`generate-drop-sql`	Ready-to-run `DROP INDEX` statements
`slow-queries`	Top queries from `pg_stat_statements`
`long-running`	Currently long-running queries
`blocking`	Locks / blocking sessions
`tables`	Largest tables and table stats
`vacuum-needed`	Tables that need `VACUUM`
`run-vacuum`	Execute `VACUUM ANALYZE`
`connections`	Connection statistics
`config`	Configuration settings
`extensions`	Installed extensions
`create-pg-stat-statements`	Create `pg_stat_statements` extension
`drop-pg-stat-statements`	Drop `pg_stat_statements` extension

CLI options

Option	Short	Description	Default
`--host`	`-h`	Database host	`localhost`
`--port`	`-p`	Database port	`5432`
`--database`	`-d`	Database name	`postgres`
`--user`	`-U`	Database user	`postgres`
`--password`	`-W`	Database password	-
`--ssl`		Enable SSL	`false`
`--command`	`-c`	Run a single command (see table)	`full`
`--json`	`-j`	JSON output	`false`
`--quiet`	`-q`	Suppress progress output	`false`
`--output`	`-o`	Reports directory	`./reports`
`--slow-query-threshold`		Slow-query threshold (ms)	`100`
`--min-index-scans`		Min scans to consider an index "used"	`50`
`--interactive`	`-i`	Interactive menu	`false`

Output formats

Markdown report

Generated by pnpm analyze. Includes:

Executive summary with health score
Database metrics (cache / index hit ratio, dead tuples, connections)
Index analysis (unused / missing / duplicate)
Table analysis (largest, high dead tuples, high seq scans)
Slow queries with recommendations
Bloat analysis
Prioritized recommendations

Saved to ./reports/db-analysis-{timestamp}.md.

JSON report

{
  "generatedAt": "2026-01-01T00:00:00.000Z",
  "databaseName": "mydb",
  "healthScore": 90,
  "metrics": {
    "databaseSize": "177 MB",
    "cacheHitRatio": 99.98,
    "indexHitRatio": 99.99,
    "deadTuplesRatio": 4.2,
    "totalConnections": 4,
    "activeConnections": 3
  },
  "unusedIndexes": [],
  "missingIndexes": [],
  "duplicateIndexes": [],
  "tableStats": [],
  "slowQueries": [],
  "bloatedTables": [],
  "recommendations": []
}

Health score

Score	Status	Action
90–100	Excellent	Monitor only
70–89	Good	Plan optimization
50–69	Warning	Needs attention
0–49	Critical	Immediate action needed

Key metric thresholds

Metric	Good	Warning	Critical
Cache hit ratio	> 95%	90–95%	< 90%
Index hit ratio	> 95%	90–95%	< 90%
Dead tuples ratio	< 5%	5–10%	> 10%

Programmatic usage

import { DatabaseAnalyzer } from "@deniscuciuc/db-analyzer-postgres";

const analyzer = new DatabaseAnalyzer({
  host: "localhost",
  port: 5432,
  database: "mydb",
  user: "postgres",
  password: "password",
  ssl: { rejectUnauthorized: false },
});

const report = await analyzer.analyze();
analyzer.printSummary(report);
await analyzer.generateReport(report);
await analyzer.close();

Managed PostgreSQL providers

Connect as a superuser or read-only admin

For full slow-query text and pg_stat_statements visibility, connect with a privileged user (superuser or a role with pg_read_all_stats). Restricted users see <insufficient privilege> instead of query text.

Enable `pg_stat_statements`

-- Check availability
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

-- Create
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Grant access to other users (optional)
GRANT pg_read_all_stats TO your_app_user;

Or use the npm script: pnpm pg-stat-statements:create.

Troubleshooting

Symptom	Cause	Fix
`ECONNREFUSED`	Cannot reach the database	Check host / port and network
`SSL required`	Server requires TLS	Add `--ssl` or `DB_SSL=true`
`permission denied for ...`	User lacks privileges	Use a superuser or `GRANT pg_read_all_stats TO your_user;`
`relation pg_stat_statements ... does not exist`	Extension missing	`pnpm pg-stat-statements:create`
Empty slow queries	No traffic since last reset, or threshold too high	Wait for traffic, or lower `--slow-query-threshold`

Architecture

db-analyzer-postgres/
├── index.ts                         # Entry point + CLI
├── package.json
├── src/
│   ├── types.ts                     # Shared types
│   ├── queries.ts                   # SQL queries
│   ├── interactive.ts               # Interactive CLI
│   ├── analyzers/
│   │   ├── index-analyzer.ts
│   │   ├── query-analyzer.ts
│   │   └── table-analyzer.ts
│   ├── collectors/stats-collector.ts
│   └── reporters/report-generator.ts
├── .github/copilot-instructions.md  # AI agent workflow
├── .env.example
└── reports/                         # Generated reports (gitignored)

Class	Responsibility
`IndexAnalyzer`	Unused / missing / duplicate / FK indexes
`QueryAnalyzer`	Slow / long-running / blocking queries
`TableAnalyzer`	Stats, bloat, dead tuples, VACUUM
`StatsCollector`	Metrics, connections, configuration
`ReportGenerator`	Markdown + JSON report output

Contributing

Contributions are welcome! Here's how to get started:

Fork the repo and create a feature branch
Run pnpm install to install dependencies
Copy .env.example to .env and configure your connection
Make your changes and ensure pnpm lint passes
Open a pull request

Coding standards:

TypeScript with strict mode enabled
Code formatted with Biome (tab indent, 2-space width)
All changes must pass pnpm lint and pnpm build

License

MIT — see LICENSE.

Name		Name	Last commit message	Last commit date
Latest commit History 5 Commits
.github		.github
reports		reports
src		src
.env.example		.env.example
.gitignore		.gitignore
CHANGELOG.md		CHANGELOG.md
LICENSE		LICENSE
Makefile		Makefile
README.md		README.md
biome.json		biome.json
index.ts		index.ts
package.json		package.json
pnpm-lock.yaml		pnpm-lock.yaml
tsconfig.json		tsconfig.json

Folders and files

Latest commit

History

Repository files navigation

PostgreSQL Database Analyzer

Table of Contents

Features

Index analysis

Query analysis

Table & storage

System metrics

Requirements

Installation

Configuration

Environment variables (.env)

Usage

Interactive mode

npm scripts

Direct CLI

Commands

CLI options

Output formats

Markdown report

JSON report

Health score

Key metric thresholds

Programmatic usage

Managed PostgreSQL providers

Connect as a superuser or read-only admin

Enable pg_stat_statements

Troubleshooting

Architecture

Contributing

License

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Environment variables (`.env`)

Enable `pg_stat_statements`

Packages