PostgreSQL Live Query Monitor - RustyRoad Edition

A real-time PostgreSQL query profiler designed to work seamlessly with RustyRoad project configurations. Inspired by SQL Server Management Studio's Activity Monitor, this tool provides live monitoring of active database queries with automatic connection management.

Features

• RustyRoad Configuration Integration - Automatically reads database credentials from rustyroad.toml
• Real-Time Monitoring - 1-second polling of PostgreSQL pg_stat_activity
• Query Deduplication - MD5 hashing to identify identical queries
• Auto-Reconnect - Exponential backoff reconnection on connection loss
• Query Management:
  • Kill running queries via pg_terminate_backend
  • Get EXPLAIN plans for queries
  • Export to CSV/JSON
• 10,000 Query Buffer - Maintains history of recent queries
• Windows Native UI - Fast Win32 interface with ListView grid
• Filtering & Search - Filter by database, user, duration, and search query text

Architecture

Technology Stack

[dependencies]
tokio = { version = "1.41", features = ["full"] }
tokio-postgres = { version = "0.7", features = ["with-chrono-0_4"] }
windows = { version = "0.62.1", features = [...] }
serde = { version = "1.0", features = ["derive"] }
toml = "0.8"
chrono = "0.4"
anyhow = "1.0"
md5 = "0.7"

Module Structure

src/
├── main.rs          # Entry point, RustyRoad config loading
├── config.rs        # RustyRoadConfig struct and parsing
├── db.rs            # PostgreSQL connection and query methods
├── monitor.rs       # QueryMonitor with polling and buffering
├── ui.rs            # Win32 GUI implementation
├── filter.rs        # Query filtering logic
└── export.rs        # CSV/JSON export functionality

Configuration

Create a rustyroad.toml file in the same directory as the executable:

[rustyroad_project]
name = "My Project"

[database]
database_name = "spotlessbinco_dev"
database_user = "postgres"
database_password = "spike2"
database_host = "192.168.50.70"
database_port = "5432"
database_type = "postgresql"

Usage

Building

# Debug build
cargo build

# Release build (optimized)
cargo build --release

Running

# Ensure rustyroad.toml is in the current directory or project root
cargo run

# Or run the compiled executable
./target/release/rusty-db-profiler.exe

On Startup

1. Application reads rustyroad.toml from current directory
2. Automatically connects to the configured PostgreSQL server
3. Begins monitoring the specified database
4. Windows UI displays real-time query activity

UI Controls

• Connect/Disconnect - Manual connection control (auto-connected on startup)
• Pause/Resume - Temporarily stop query polling
• Clear - Empty the query display
• Export CSV - Save current queries to CSV file
• Kill Query - Terminate selected query's backend process
• Search - Filter queries by text content
• Database/User Filters - Dropdown filters

Query Monitoring Details

Monitored Information

Each query event captures:

• PID - Process ID
• User - Database user
• Database - Target database name
• Client IP - Source connection address
• Duration - Query execution time in milliseconds
• State - active, idle in transaction, etc.
• Start Time - Query start timestamp
• Query Hash - MD5 hash for deduplication
• Query Text - Full SQL statement

SQL Query Used

SELECT 
    pid,
    COALESCE(usename, '') as usename,
    COALESCE(application_name, '') as application_name,
    host(client_addr) as client_addr,
    COALESCE(datname, '') as datname,
    COALESCE(state, '') as state,
    query_start,
    state_change,
    wait_event_type,
    wait_event,
    EXTRACT(EPOCH FROM (NOW() - query_start)) * 1000 AS duration_ms,
    COALESCE(query, '') as query,
    md5(COALESCE(query, '')) as query_hash
FROM pg_stat_activity
WHERE state != 'idle' 
  AND pid != pg_backend_pid()
  AND datname IS NOT NULL
ORDER BY query_start DESC
LIMIT 1000

Auto-Reconnect Behavior

The monitor implements exponential backoff for reconnection:

1. Initial delay: 2 seconds
2. Max delay: 60 seconds
3. Backoff formula: 2^attempt seconds (capped at 60s)
4. Continuous: Never gives up, keeps retrying

Example Reconnect Timeline

Attempt	Delay
1	2s
2	4s
3	8s
4	16s
5	32s
6+	60s

Key Features Explained

Query Buffer

• Maintains a rolling buffer of up to 10,000 query events
• FIFO queue - oldest queries are removed when limit is reached
• Allows historical analysis of query patterns
• Accessible via UI for export

MD5 Query Hashing

Calculates MD5 hash of query text to:

• Identify duplicate/repeated queries
• Group similar query patterns
• Optimize storage and display

Kill Query Functionality

Uses PostgreSQL's pg_terminate_backend():

pub async fn kill_query(&self, pid: i32) -> Result<()> {
    client.execute("SELECT pg_terminate_backend($1)", &[&pid]).await?;
    Ok(())
}

EXPLAIN Query

Retrieves execution plan without running the query:

pub async fn explain_query(&self, query: &str) -> Result<String> {
    let explain_query = format!("EXPLAIN (FORMAT JSON, ANALYZE false) {}", query);
    let rows = client.query(&explain_query, &[]).await?;
    Ok(serde_json::to_string_pretty(&rows[0].get(0))?)
}

Deployment

Standalone Executable

# Build optimized release
cargo build --release

# Copy executable
cp target/release/rusty-db-profiler.exe /path/to/deployment/

# Copy configuration
cp rustyroad.toml /path/to/deployment/

# Run
cd /path/to/deployment/
./rusty-db-profiler.exe

Configuration Management

The application looks for rustyroad.toml in:

1. Current working directory
2. Same directory as executable (if different from CWD)

Troubleshooting

"rustyroad.toml not found"

Solution: Create the configuration file in the directory where you're running the executable.

# Check current directory
pwd

# Create config
cat > rustyroad.toml << EOF
[rustyroad_project]
name = "My Project"

[database]
database_name = "your_database"
database_user = "postgres"
database_password = "your_password"
database_host = "localhost"
database_port = "5432"
database_type = "postgresql"
EOF

"Failed to connect to database"

Possible causes:

1. PostgreSQL server not running
2. Incorrect host/port in rustyroad.toml
3. Invalid credentials
4. Firewall blocking connection
5. Database doesn't exist

Debug steps:

# Test connection manually
psql -h 192.168.50.70 -p 5432 -U postgres -d spotlessbinco_dev

# Check PostgreSQL is listening
netstat -an | grep 5432

# Verify pg_hba.conf allows connection
# Look for appropriate host/md5/trust entries

Slow Query Performance

If monitoring causes performance issues:

1. Increase poll interval - Modify monitor.rs:

   QueryMonitor::new(db_connection, config, 5000) // 5 seconds
   

2. Reduce buffer size - Modify monitor.rs:

   if buffer.len() >= 5000 { // Reduce from 10000
   

3. Filter at database level - Modify SQL in db.rs to exclude certain queries

RustyRoad Integration Benefits

• Zero Configuration UI - No manual connection setup needed
• Project Context - Database settings tied to project
• Version Control - rustyroad.toml can be committed to git
• Multi-Environment - Different configs for dev/staging/prod
• Standardized Format - Consistent across all RustyRoad tools

Future Enhancements

Potential additions:

• Real-time charts/graphs of query metrics
• Query performance history and trending
• Alert system for slow queries
• Multiple database monitoring (switch between DBs)
• Session management (track connection pools)
• Lock monitoring from pg_locks
• Table statistics from pg_stat_user_tables
• Index usage analysis

License

This project is part of the RustyRoad ecosystem.

Related Tools

• RustyRoad - Project configuration and management
• pgAdmin - Full-featured PostgreSQL administration
• psql - PostgreSQL interactive terminal

---

Note: This tool is designed for development and monitoring purposes. For production monitoring, consider dedicated solutions like pgBadger, pg_stat_statements, or commercial APM tools.