List all non-template databases accessible on the connected PostgreSQL server.

Returns database name, encoding, collation, size, and connection limit.

Returns: JSON: { databases: DatabaseInfo[], count: number } Markdown: formatted table

Errors:

• "Cannot connect" if DATABASE_URL/PG_* env vars are wrong

List all user-defined schemas in the current database (excludes pg_catalog, information_schema, pg_toast).

Returns schema name, owner, description, table count, and view count.

Returns: JSON: { schemas: SchemaInfo[], count: number } Markdown: formatted table

List all tables (and optionally views) in a PostgreSQL schema with size and row estimates.

Args:

• schema: Schema name (default: public)

• include_views: Also list views and materialized views (default: false)

• response_format: Output format

Returns: JSON: { tables: TableInfo[], count: number, schema: string } Markdown: formatted table with size and row estimates

Note: estimated_rows is approximate (pg_class.reltuples) — use pg_count_rows for exact count.

Full description of a table: columns, data types, constraints, indexes, and foreign keys.

Args:

• table: Table name (required)

• schema: Schema name (default: public)

• response_format: Output format

Returns: JSON: { table, schema, columns: ColumnInfo[], foreign_keys: ForeignKeyInfo[], check_constraints, indexes: IndexInfo[] } Markdown: multi-section formatted description

Errors:

• "Table not found" if table/schema doesn't exist

List all views and materialized views in a PostgreSQL schema, including their definitions.

Args:

• schema: Schema name (default: public)

• include_definition: Include the SQL definition of each view (default: false)

• response_format: Output format

Returns: JSON: { views: ViewInfo[], count: number, schema: string } Markdown: formatted table

List all user-defined functions, procedures, aggregates, and window functions in a schema.

Args:

• schema: Schema name (default: public)

• response_format: Output format

Returns: JSON: { functions: FunctionInfo[], count: number, schema: string } Markdown: formatted table with name, type, return type, arguments, language

List indexes in a schema, optionally filtered to a specific table.

Args:

• schema: Schema name (default: public)

• table: Filter to a specific table (optional)

• response_format: Output format

Returns: JSON: { indexes: IndexInfo[], count: number } Markdown: formatted table with name, table, type, uniqueness, size, definition

List all extensions currently installed in the PostgreSQL database.

Returns extension name, installed version, default version, and description.

Returns: JSON: { extensions: ExtensionInfo[], count: number } Markdown: formatted table

Execute a read-only SQL SELECT query against the PostgreSQL database.

Only SELECT, WITH (CTE), TABLE, and EXPLAIN statements are allowed. Any attempt to execute INSERT, UPDATE, DELETE, DDL, or other mutating statements will be rejected.

Args:

• sql: The SQL SELECT statement to execute (required)

• limit: Maximum rows to return, 1-1000 (default: 100)

• timeout_ms: Query timeout in milliseconds (default: 30000)

• response_format: Output format

Returns: JSON: { rows: object[], row_count: number, column_names: string[] } Markdown: formatted table of results

Examples:

• "SELECT * FROM users WHERE active = true" → rows with limit applied

• "WITH stats AS (SELECT ...) SELECT * FROM stats" → CTE supported

• "EXPLAIN SELECT * FROM orders" → query plan

Errors:

• "Only read-only queries allowed" if non-SELECT statement attempted

• PostgreSQL syntax/permission errors returned as-is

Show the execution plan for a SQL query using EXPLAIN or EXPLAIN ANALYZE.

Use EXPLAIN to see the planned query strategy. Use EXPLAIN ANALYZE to actually execute the query and see real timing and row count statistics (costs real I/O).

Args:

• sql: SQL query to explain (required, should be a SELECT)

• analyze: Run EXPLAIN ANALYZE (executes the query, default: false)

• buffers: Include buffer usage statistics — only with analyze:true (default: false)

• format: Output format for the plan, 'text' or 'json' (default: text)

• response_format: Response format

Returns: The query plan as text or JSON. Use 'json' format for programmatic parsing.

Warning: EXPLAIN ANALYZE actually executes the query, including any side effects.

Execute a non-SELECT SQL statement: INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, TRUNCATE, etc.

For DROP, TRUNCATE, or DELETE without a WHERE clause, you must set confirm_destructive: true to confirm you understand these operations are irreversible.

Args:

• sql: SQL statement to execute (required)

• confirm_destructive: Required for DROP/TRUNCATE/DELETE without WHERE (default: false)

• timeout_ms: Statement timeout in milliseconds (default: 30000)

Returns: JSON: { command, rows_affected, duration_ms } Markdown: summary of executed statement

Examples:

• INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')

• UPDATE orders SET status = 'shipped' WHERE id = 42

• CREATE INDEX idx_users_email ON users(email)

• DROP TABLE temp_data (requires confirm_destructive: true)

Warning: Mutations are not automatically wrapped in a transaction. Use BEGIN/COMMIT explicitly for multi-statement transactions.

Execute multiple SQL statements atomically in a single transaction.

All statements succeed or all are rolled back. Ideal for multi-step operations that must be atomic (e.g., insert + update, schema migrations).

Args:

• statements: Array of SQL statements to execute in order (required)

• confirm_destructive: Required if any statement is DROP/TRUNCATE/DELETE-without-WHERE

Returns: JSON: { results: [{command, rows_affected}], total_duration_ms, rolled_back: false } On error: { error, rolled_back: true, failed_at_index: number }

Examples: statements: ["INSERT INTO orders ...", "UPDATE inventory SET qty = qty - 1 WHERE ..."]

Retrieve a sample of rows from a PostgreSQL table, with optional filtering and column selection.

Args:

• table: Table name (required)

• schema: Schema name (default: public)

• limit: Number of rows to return, 1-500 (default: 10)

• where_clause: Optional WHERE clause (without the WHERE keyword), e.g. "status = 'active'"

• columns: Comma-separated column names to select (optional, default: all columns)

• order_by: ORDER BY clause (without ORDER BY), e.g. "created_at DESC"

• response_format: Output format

Returns: JSON: { table, schema, row_count, columns: string[], rows: object[] } Markdown: formatted table of results

Security: table and schema names are validated against actual database objects before use.

Get exact row count for a table, optionally with a filter condition.

Args:

• table: Table name (required)

• schema: Schema name (default: public)

• where_clause: Optional WHERE condition (without the WHERE keyword)

Returns: JSON: { table, schema, count, where_clause } Markdown: formatted count with filter info

Note: For large tables (>10M rows), pg_table_stats provides a faster estimated count.

Get detailed statistics for tables: size, live/dead rows, vacuum/analyze info, scan counts.

Useful for identifying bloated tables, missing vacuums, or underused indexes.

Args:

• schema: Filter to a specific schema (optional, shows all user schemas if omitted)

• table: Filter to a specific table name (optional)

• response_format: Output format

Returns: JSON: { stats: TableStats[], count: number } Markdown: formatted table with size, rows, vacuum dates, scan counts

Note: Requires pg_stat_user_tables access. Row counts are from pg_stat_user_tables (updated by autovacuum/analyze) — use pg_count_rows for exact counts.

Get detailed PostgreSQL server information: version, connection details, memory settings, WAL configuration, and runtime parameters.

Returns: JSON: { version, current_database, current_user, settings: {...}, uptime } Markdown: formatted multi-section report

Useful for understanding the server environment, verifying connection details, and checking key configuration values.

List all sequences in a schema with their configuration and current state.

Args:

• schema: Schema name (default: public)

• response_format: Output format

Returns: JSON: { sequences: SequenceInfo[], count: number } Markdown: table with name, type, range, increment, current value, and owning column

Note: last_value reflects the last allocated value, not necessarily the next one to be issued.

List all triggers in a schema, optionally filtered by table.

Args:

• schema: Schema name (default: public)

• table: Filter to a specific table (optional)

• response_format: Output format

Returns: JSON: { triggers: TriggerInfo[], count: number } Markdown: table with trigger name, table, event, timing, orientation, and statement

Search across all database objects (tables, views, functions, indexes, sequences, constraints) by name pattern.

Args:

• pattern: Case-insensitive name pattern, supports SQL LIKE wildcards: % (any chars), _ (one char) (required)

• object_types: Filter by object type — 'table', 'view', 'function', 'index', 'sequence', 'constraint', 'trigger' (optional, searches all types by default)

• schema: Limit search to a specific schema (optional)

• response_format: Output format

Returns: JSON: { matches: SearchResult[], count: number } Markdown: grouped results by type

Examples:

• pattern: "user%" → all objects starting with "user"

• pattern: "%order%" → all objects containing "order"

• pattern: "idx_%" with object_types: ["index"] → all indexes

Get the CREATE statement (DDL) for a table, view, materialized view, function, or index.

Args:

• object_name: Name of the object (required)

• schema: Schema name (default: public)

• object_type: Type of object: 'table', 'view', 'matview', 'function', 'index' (default: 'table')

Returns: JSON: { schema, object_name, object_type, ddl: string } Markdown: formatted SQL code block

Useful for understanding table structure, reproducing objects in other environments, or code review.

Show currently active locks in the database, including waiting queries.

Useful for debugging lock contention, deadlocks, and long-running transactions.

Args:

• include_granted: Include granted locks (not just waiting) — default: true

• response_format: Output format

Returns: JSON: { locks: LockInfo[], blocking_pairs: [{blocker_pid, blocked_pid}] } Markdown: lock table with PID, query, lock type, relation, and wait status

Note: Requires sufficient privileges to view pg_stat_activity.

List all currently running queries in the database (excludes idle connections and the MCP server itself).

Args:

• min_duration_seconds: Only show queries running longer than N seconds (default: 0 = all active)

• include_idle_in_transaction: Include connections stuck in idle-in-transaction state (default: true)

• response_format: Output format

Returns: JSON: { queries: ActiveQuery[], count: number } Markdown: table with PID, user, state, wait event, duration, query text

Useful for finding long-running queries, blocked transactions, and identifying performance bottlenecks.

Analyze slow queries using the pg_stat_statements extension. Shows queries ranked by mean execution time.

Requires the pg_stat_statements extension to be installed and enabled.

Args:

• limit: Number of queries to return (default: 20, max: 100)

• min_calls: Only show queries called at least N times (default: 5)

• order_by: Sort by 'mean_ms', 'total_ms', or 'calls' (default: mean_ms)

• response_format: Output format

Returns: JSON: { queries: SlowQuery[], count: number } Markdown: table with calls, mean/total time, % of total, cache hit %, query text

Errors:

• "pg_stat_statements extension not installed" if extension is missing

Show index scan counts and tuple statistics to identify unused or underused indexes.

Indexes with 0 scans that are not primary keys waste storage and slow writes — candidates for removal.

Args:

• schema: Filter to a specific schema (optional)

• min_table_size_mb: Only show indexes on tables larger than N MB (default: 0)

• show_unused_only: Only show indexes with 0 scans (default: false)

• response_format: Output format

Returns: JSON: { indexes: IndexUsage[], count: number } Markdown: table sorted by scan count ascending (least used first)

Note: Statistics reset on pg_stat_reset() or server restart. Low scans on a new server may not mean unused.

Identify tables with high dead-tuple ratios that need VACUUM or AUTOVACUUM attention.

Dead rows accumulate from UPDATE and DELETE operations. High dead% degrades query performance and wastes storage. Tables over ~20% dead tuples should be vacuumed.

Args:

• schema: Filter to a specific schema (optional)

• min_dead_pct: Minimum dead tuple % to include (default: 5)

• min_dead_rows: Minimum absolute dead row count (default: 1000)

• response_format: Output format

Returns: JSON: { tables: BloatInfo[], count: number } Markdown: table sorted by dead% descending, with last vacuum dates

Show the status of all streaming replication standbys connected to this primary, including replay lag.

Only meaningful on a primary server with active streaming replication.

Returns: JSON: { replicas: ReplicaInfo[], is_standby: boolean, lsn: string } Markdown: replica table with address, state, lag size, sync state

Useful for monitoring replication health and identifying lagging standbys.

List all user-defined types in a schema: enums, composite types, domains, and range types.

Args:

• schema: Schema name (default: public)

• type_category: Filter by 'enum', 'composite', 'domain', 'range', or 'all' (default: all)

• response_format: Output format

Returns: JSON: { types: TypeInfo[], count: number } Markdown: table with type name, category, enum values (for enums), base type (for domains)

Examples:

• Enum "order_status" → values: pending, confirmed, shipped, delivered, cancelled

• Domain "positive_int" → base type: integer CHECK (VALUE > 0)

Show all privileges granted on tables, views, sequences, and functions in a schema.

Args:

• schema: Schema name (default: public)

• object_name: Filter to a specific object (optional)

• grantee: Filter by role/user name (optional)

• response_format: Output format

Returns: JSON: { grants: GrantInfo[], count: number } Markdown: table with grantor, grantee, object, privilege type, and grantable flag

Useful for security audits and understanding who has access to what.

List all Row Level Security (RLS) policies in a schema, including USING and WITH CHECK expressions.

Args:

• schema: Schema name (default: public)

• table: Filter to a specific table (optional)

• response_format: Output format

Returns: JSON: { policies: PolicyInfo[], count: number, rls_enabled_tables: string[] } Markdown: policy table with name, command, roles, permissive flag, and filter expressions

Note: Also shows which tables have RLS enabled even if no policies are defined.

List all partitioned tables and their child partitions with bounds, size, and row estimates.

Args:

• schema: Filter to a specific schema (optional)

• parent_table: Filter to a specific partitioned table (optional)

• response_format: Output format

Returns: JSON: { partitions: PartitionInfo[], parent_tables: string[] } Markdown: grouped by parent table, showing each partition's bounds and size

Execute a SELECT query and return the results formatted as CSV with headers.

Args:

• sql: SELECT statement to export (required — must be read-only)

• limit: Maximum rows to export, 1-5000 (default: 1000)

• delimiter: CSV field delimiter character (default: ',')

• null_value: String to use for NULL values in CSV output (default: '')

Returns: CSV text with header row. Suitable for copy-paste into spreadsheets or piping to files.

Errors:

• Rejected if non-SELECT statement provided

• Returns error if query fails

Cancel or terminate a PostgreSQL backend process by PID.

Two modes:

• cancel (safe): Sends SIGINT — cancels the current query but leaves the connection alive. The client can reconnect and retry.

• terminate (forceful): Sends SIGTERM — kills the connection entirely. Use when cancel doesn't work.

Args:

• pid: Process ID of the backend to cancel/terminate (from pg_active_queries or pg_list_locks)

• mode: 'cancel' to cancel current query, 'terminate' to kill connection (default: cancel)

Returns: Whether the signal was successfully sent.

Note: You cannot cancel/terminate your own backend or superuser backends without superuser privileges.

List all PostgreSQL roles with their attributes, privileges, and group memberships.

Returns both login roles (users) and group roles. Shows superuser status, replication, login capability, connection limits, password expiry, and role membership graph.

Args:

• login_only: Only show roles that can log in (actual users, not groups) (default: false)

• response_format: Output format

Returns: JSON: { roles: RoleInfo[], count: number } Markdown: table with all role attributes + membership info

Useful for access audits, permission reviews, and understanding role hierarchy.

Search and inspect PostgreSQL runtime settings from pg_settings.

Args:

• name_pattern: ILIKE filter on setting name, e.g. "work_mem" or "%memory%" (optional)

• category: Filter by category name, e.g. "Memory", "Connections and Authentication", "WAL" (optional)

• modified_only: Only show settings that differ from their compiled default (default: false)

• response_format: Output format

Returns: JSON: { settings: SettingInfo[], count: number } Markdown: table with name, current value, unit, source, context, description

Common categories: Memory, Connections and Authentication, WAL, Query Tuning, Autovacuum, Logging, Lock Management, Replication, Resource Usage.

Note: 'context' shows where the setting can be changed:

• internal: read-only, compiled in

• postmaster: requires server restart

• sighup: reload only (pg_reload_conf())

• user: changeable per-session

Execute VACUUM, VACUUM ANALYZE, or VACUUM FULL on a table to reclaim dead-tuple storage.

Modes:

• standard: Reclaims dead-tuple space for reuse within the table (non-blocking)

• analyze: VACUUM + updates query planner statistics (recommended for most cases)

• full: Rewrites the table to reclaim disk space to OS — requires exclusive lock, very slow on large tables

Args:

• table: Table name (required)

• schema: Schema name (default: public)

• mode: 'standard', 'analyze', or 'full' (default: analyze)

• confirm_full: Required when mode is 'full' — acknowledges table will be locked

Returns: Duration and confirmation of completion.

Warning: VACUUM FULL acquires an exclusive lock — all queries on the table will block until it completes. Use during maintenance windows only.

Show a summary of current connections grouped by database, user, application, and state.

Useful for monitoring connection pool utilization, finding idle connections, and detecting connection leaks from specific applications.

Args:

• group_by: Group connections by 'database', 'user', 'application', or 'state' (default: state)

• database: Filter to a specific database (optional)

• response_format: Output format

Returns: JSON: { summary: ConnectionStat[], total_connections: number, max_connections: string, usage_pct: number } Markdown: grouped connection count with waiting and max idle age

Note: Excludes the MCP server's own backend connection from counts.