database-size-monitor

name	database-size-monitor
description	Dashboard for monitoring PostgreSQL and MySQL table sizes over time, with growth tracking, threshold alerts, and snapshot comparison
triggers	["database size monitor","table size tracking","db growth monitoring","postgres table sizes","mysql table sizes","database storage monitor"]

database-size-monitor Skill

When to Use

Use this skill when you need to:

• Track how individual database tables grow over time
• Get alerted when a table exceeds a size or row count threshold
• Find which tables are growing fastest before they cause storage incidents
• Compare two snapshots to see what changed between them
• Monitor multiple PostgreSQL and MySQL databases from a single dashboard

Prerequisites

• Node.js 20+
• pnpm 9+
• At least one PostgreSQL or MySQL database to monitor (read-only access is sufficient)

Quick Start

cd database-size-monitor
cp .env.example .env
pnpm install
pnpm --filter backend dev     # API on :3000
pnpm --filter frontend dev    # Dashboard on :5173

Add a database via the Databases page or API. The first poll runs immediately.

Docker Quick Start

docker compose up
# Dashboard: http://localhost:8080
# API: http://localhost:3000

Adding a Database

Via the Dashboard

Open Databases, click "Add Database", fill in the name, type, and connection string, then click "Test Connection" to verify before saving.

Via the API

curl -X POST http://localhost:3000/api/databases \
  -H "Content-Type: application/json" \
  -d '{
    "name": "prod-postgres",
    "type": "postgres",
    "connection_string": "postgresql://readonly_user:[e-mail]:5432/mydb",
    "poll_interval_seconds": 300
  }'

The backend uses pg.Pool for PostgreSQL and mysql2 (promise interface) for MySQL. A read-only database user is recommended.

API Reference

Databases

Method	Path	Description
GET	/api/databases	List all databases
POST	/api/databases	Add a database
PUT	/api/databases/:id	Update database config
DELETE	/api/databases/:id	Remove database
POST	/api/databases/:id/test	Test connection
POST	/api/databases/:id/poll	Trigger immediate poll

Sizes

Method	Path	Description
GET	/api/databases/:id/snapshots	Snapshot list (paginated)
GET	/api/databases/:id/tables	Current table sizes with growth
GET	/api/databases/:id/tables/:table/history	Single table size history

Alerts

Method	Path	Description
GET	/api/alerts	All alert events
GET	/api/alert-rules	Alert rule list
POST	/api/alert-rules	Create a rule
DELETE	/api/alert-rules/:id	Delete a rule

Settings

Method	Path	Description
GET	/api/settings	Global settings
POST	/api/settings	Update settings

Alert Rules

Alert rules fire when a table's measured value crosses a threshold after each poll.

Create a rule via API:

curl -X POST http://localhost:3000/api/alert-rules \
  -H "Content-Type: application/json" \
  -d '{
    "database_id": 1,
    "table_pattern": "public.events",
    "metric": "size_bytes",
    "operator": "gt",
    "threshold": 8589934592,
    "severity": "critical"
  }'

Metric Types

Metric	Unit	Example threshold
size_bytes	bytes	8589934592 (8 GB)
row_count	integer	50000000 (50M rows)
growth_pct	percent per week	10 (10% weekly growth)

Table Pattern

The table_pattern field uses glob syntax (via minimatch):

• public.events -- exact match
• public.* -- all tables in the public schema
• *.audit_* -- any table with "audit_" in the name across all schemas

Alert Webhook

Set ALERT_WEBHOOK_URL to receive POST notifications when alerts fire:

{
  "alert_id": 42,
  "database_name": "prod-postgres",
  "schema_name": "public",
  "table_name": "events",
  "metric": "size_bytes",
  "value": 9013043200,
  "threshold": 8589934592,
  "severity": "critical",
  "triggered_at": "2024-03-15T08:12:00Z"
}

This format is compatible with Slack incoming webhooks when wrapped in a simple proxy.

Environment Variables

Variable	Default	Description
PORT	3000	Backend API port
DB_PATH	./data/monitor.db	SQLite monitoring database path
DEFAULT_POLL_INTERVAL	300	Default poll interval in seconds
RETENTION_DAYS	90	Delete snapshot records older than N days
ALERT_WEBHOOK_URL	``	POST alert payload here when rule fires
CORS_ORIGIN	http://localhost:5173	Dashboard CORS origin
VITE_API_URL	http://localhost:3000	API base URL for dashboard

Data Notes

PostgreSQL Row Counts

The row_count value comes from pg_stat_user_tables.n_live_tup, which is an estimate maintained by autovacuum. After a large DELETE without VACUUM, this value may be stale. For exact counts on specific tables, trigger a poll after running ANALYZE table_name.

MySQL Row Counts

MySQL's information_schema.TABLES.TABLE_ROWS is also an estimate for InnoDB tables. It is typically accurate within 20% of the true count.

First Snapshot

Growth percentage is only available after at least two successful polls. The first snapshot shows "--" in the growth column.

Troubleshooting

Poll fails with "ECONNREFUSED" Check that the connection string hostname and port are reachable from the backend container. If using Docker, use service names or host network mode.

Poll fails with "permission denied" The database user lacks read access to pg_stat_user_tables or information_schema.TABLES. Grant the required permissions:

• PostgreSQL: GRANT SELECT ON pg_stat_user_tables TO monitor_user;
• MySQL: GRANT SELECT ON information_schema.* TO 'monitor_user'@'%';

Dashboard shows no data after adding a database The first poll may not have completed yet. Click "Poll Now" on the Databases page to trigger an immediate poll and wait a few seconds.

Alerts not firing Verify the alert rule's table_pattern matches your schema.table format exactly. Test the pattern with minimatch('public.events', 'public.*') in a Node REPL.