Skip to main content

​
PostgreSQL Server

​
Overview

The PostgreSQL server provides comprehensive database integration with advanced security features, query optimization tools, and database health monitoring. Built by Crystal DBA, it supports both restricted (read-only with SQL validation) and unrestricted modes.

​
Getting Started

1

Connect to PostgreSQL

Add the PostgreSQL server to your Nexus environment through the server directory.
2

Configure Database Connection

Provide your PostgreSQL connection string with proper authentication and SSL settings.
3

Test Connection

Start with a simple command like list schemas to verify database connectivity.

​
What You Can Do

Schema Exploration

Browse database schemas, tables, views, sequences, and extensions

Query Optimization

Analyze execution plans, recommend indexes, and optimize query performance

Health Monitoring

Monitor database health, connections, buffer cache, and system metrics

Performance Analysis

Identify slow queries, resource usage patterns, and bottlenecks

​
Use Cases

  • Schema Management: Explore database structure and relationships
  • Object Discovery: Find tables, views, sequences, and extensions across schemas
  • Health Monitoring: Track database health metrics and system performance
  • Connection Analysis: Monitor active connections and resource usage
  • Execution Plan Analysis: Understand query performance with EXPLAIN
  • Index Recommendations: Get AI-powered index suggestions for better performance
  • Workload Analysis: Analyze frequently executed queries for optimization opportunities
  • Hypothetical Indexes: Test index scenarios without creating actual indexes
  • Slow Query Detection: Identify resource-intensive and slow-running queries
  • Resource Usage Tracking: Monitor CPU, memory, and I/O usage patterns
  • Buffer Cache Analysis: Optimize memory usage and cache hit ratios
  • Vacuum and Maintenance: Monitor table maintenance and space usage

​
Available Tools (9)

Explore and understand your PostgreSQL database structure.Schema and Object Management
List all schemas in the database for structural overview
List objects (tables, views, sequences, extensions) within a specific schema
Get detailed information about specific database objects including structure and metadata
Object Types Supported: tables, views, sequences, extensions - specify the type for targeted exploration.
Advanced query performance analysis and optimization tools.
Analyze SQL execution plans with cost estimates and optional real-time statistics
  • Parameters: SQL query, analyze mode (true/false), hypothetical indexes
  • Features: Shows execution plans, cost estimates, and actual performance data
Analyze frequently executed queries and recommend optimal indexes
  • Methods: DTA (Database Tuning Advisor) or LLM-based analysis
  • Limits: Configurable maximum index size constraints
Analyze specific queries (up to 10) and get targeted index recommendations
  • Input: List of SQL queries for analysis
  • Output: Optimized index suggestions with performance impact estimates
When using analyze mode in explain_query, the query will actually execute. Use carefully with data-modifying queries.
Monitor database performance and identify optimization opportunities.
Comprehensive database health analysis across multiple dimensions
  • Health Types: all, buffer, connection, constraint, index, replication, sequence, vacuum
  • Features: Connection monitoring, index health, buffer cache analysis, constraint validation
Identify resource-intensive queries using pg_stat_statements data
  • Sort Options: total_time, mean_time, resources
  • Customizable Limits: Configure number of queries to return
The get_top_queries tool requires the pg_stat_statements extension to be enabled in your PostgreSQL instance.
Execute read-only SQL queries with security validation.
Execute read-only SQL queries with built-in security validation
  • Security: Automatic validation prevents data modification
  • Use Cases: Data exploration, reporting, analysis queries
Security Feature: The server operates in restricted mode by default, ensuring only read operations are permitted.

​
Configuration Requirements

​
Database Connection Setup

The PostgreSQL server requires a complete connection string with authentication: 
postgresql://user:password@host.domain.com:5432/dbname?sslmode=require
Required Components:
  • Protocol: postgresql://
  • Credentials: username and password
  • Host: database server hostname or IP
  • Port: database port (default: 5432)
  • Database: target database name
  • SSL Mode: recommended to use sslmode=require
  • SSL/TLS: Always use encrypted connections in production
  • User Permissions: Grant minimum required permissions for intended operations
  • Network Access: Ensure database is accessible from Nexus infrastructure
  • Connection Pooling: Consider connection limits and pooling for high-traffic scenarios

​
Server Configuration

  • Access Mode: Restricted (read-only with SQL validation) by default
  • Transport: HTTP-hosted with SSE (Server-Sent Events)
  • Security: Built-in SQL validation prevents data modification operations
The PostgreSQL server uses Crystal DBA’s secure implementation with advanced query validation and performance optimization features.
Ensure your PostgreSQL instance has the pg_stat_statements extension enabled for full performance monitoring capabilities.