Use natural language observability with the Amazon Redshift MCP server to investigate Amazon Redshift performance issues

Introduction

The Amazon Redshift system tables capture a detailed stream of operational signals: every query, connection, and load. However, the system tables retain only 7 days of history by default. This short window sometimes ends before you diagnose a performance issue, and it doesn't keep a baseline for before-and-after analysis. Analyzing the remaining history also requires you to write SQL across more than 25 system views.

This article explains how to integrate the following two recently launched capabilities that close the gap:

• Amazon Redshift system table integration with Amazon S3 Tables retains system-table data in open Apache Iceberg format for months or years. The integration retains history beyond the default 7-day window.

• The Amazon Redshift MCP server lets you search the extended history in plain English. The AI agents translate the natural language query into SQL and run the SQL statements against an Amazon Redshift cluster or serverless workgroup, so you don't need SQL fluency or memorized system-view schemas. For more information, see Accelerating SQL analytics with Amazon Redshift MCP server.

Together, these capabilities turn multi-day troubleshooting into a fast, conversational investigation. This workflow serves database administrators (DBAs), platform engineers, and AWS Support and Technical Account Manager (TAM) teams. This article walks through a customer scenario of a real post-resize performance regression and five hands-on observability examples.

Use case

A database administrator at a financial services company noticed that several business-critical extract, transform, and load (ETL) queries ran much more slowly. The slowdown followed a recent cluster resize from ra3.xlplus to ra3.4xlarge. The team needed to compare query execution patterns from the 2 weeks before the resize against the current state to determine whether the resize caused the regression, or a concurrent schema change was to blame.

The DBA needed to compare performance across a window that exceeded the default 7-day system table data retention. But by the time the team identified the performance degradation pattern, Amazon Redshift had already removed the pre-resize operational data. Without a historical baseline, the DBA had no way to run a meaningful before-and-after comparison.

The TAM recommended Amazon Redshift system table integration with Amazon S3 Tables and the MCP server. The team implemented the capabilities. Then, the DBA entered the following natural language prompt on the customer’s AI interface:

“Compare the average query execution time and queue wait time for the 2 weeks before July 15 versus the 2 weeks after July 15. Break it down by query type (SELECT, INSERT, COPY, UNLOAD).”

The AI agent generated a before-and-after comparison query across the resize window, accessed the extended retention data in S3 Tables, and presented the results.

The analysis revealed that a concurrent schema change, not the resize itself, was the root cause. The TAM used the results to help the customer optimize the problematic schema change, quickly remediate the ETL slowdown, and restore performance to baseline levels.

Solution overview

Amazon Redshift system table integration with Amazon S3 Tables keeps data for months or years in Apache Iceberg format. This retention supports long-term performance analysis, compliance auditing, and capacity planning.

However, querying more than 25 system views across extended time ranges requires deep knowledge of system table schemas, Iceberg query syntax. and the relationships between views such as SYS_QUERY_HISTORY, SYS_CONNECTION_LOG, and SYS_LOAD_HISTORY. With the integration, a DBA can instead ask: "What queries degraded after the cluster was resized last month?"

The integration of S3 Tables and the MCP server gives you conversational access to months of operational data.

Solution workflow

The workflow to conversationally query Redshift data includes the following steps:

1. You ask a question in natural language to your MCP client.

2. The AI agent uses the Redshift MCP server to discover your cluster and external schemas that are the S3 Tables.

3. The server translates your question into SQL that queries the external schema.

4. The AI agent returns the results in a human-readable format.

Solution implementation

Prerequisites

To implement Amazon Redshift system table integration with Amazon S3 Tables and the MCP server, check that you meet the following prerequisites.

System requirements

Check that your system meets the following requirements:

• You use Python 3.10 or later.

• You installed uv package manager. To install uv package manager, see uv on the GitHub website.

• You use the Kiro IDE, The Kiro Command Line Interface (CLI) or Claude Desktop. To download the Claude Desktop, see Download Claude on the Claude website.

AWS requirements

Check that your AWS configuration meets the following requirements:

• You configured your AWS credentials with the AWS CLI, environment variables, or AWS Identity and Access Management (IAM) roles.

• You have at least one Amazon Redshift RA3 provisioned cluster or serverless workgroup.

• You activated system table integration with S3 Tables.

• You created an external schema in Redshift that points to the AWS Glue Catalog resource link.

Required IAM permissions

Add the following permissions statement to the policy that's attached to your IAM identity:

  {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "redshift:DescribeClusters",
        "redshift:GetClusterCredentialsWithIAM",
        "redshift-serverless:ListWorkgroups",
        "redshift-serverless:GetWorkgroup",
        "redshift-serverless:GetCredentials",
        "redshift-data:ExecuteStatement",
        "redshift-data:DescribeStatement",
        "redshift-data:GetStatementResult"
      ],
      "Resource": "*"
    }
  ]
}

Installation and configuration

Configure the MCP server

Edit your MCP configuration file at one of the following locations based on your client:

Client	Configuration file
Kiro IDE/CLI	~/.kiro/settings/mcp.json
Amazon Q Developer CLI	~/.aws/amazonq/mcp.json

Add the following configuration to your MCP configuration file:

{
  "mcpServers": {
    "awslabs.redshift-mcp-server": {
      "command": "uvx",
      "args": ["awslabs.redshift-mcp-server@latest"],
      "env": {
        "AWS_PROFILE": "default",
        "AWS_DEFAULT_REGION": "us-east-1",
        "FASTMCP_LOG_LEVEL": "ERROR"
      },
      "disabled": false,
      "autoApprove": []
    }
  }
}

Note: Replace AWS_PROFILE and AWS_DEFAULT_REGION with values that match your environment. On Windows, use uv instead of uvx with additional args. See the Amazon Redshift MCP server documentation on the Python Package Index website for the Windows-specific configuration.

Verify the connection

To confirm that you correctly configured your MCP client, start the client and then enter the following natural language prompt into your AI interface, such as Kiro or Claude:

“List my available Amazon Redshift clusters.”

If you correctly configured the client, then the AI agent discovers your clusters and lists them with identifiers, status, node types, and endpoints.

Verify the external schema

Enter the following prompt to confirm that you can access your external schema that points to the S3 Tables system data:

“What schemas are available in my analytics-cluster? Show me the external schemas.”

If you correctly configured the client, then the AI agent discovers the external schema that contains the long-term system table data, for example, sys_tables_s3.

Natural language observability for Redshift use cases

The following example scenarios represent the types of investigations that Support engineers and TAMs assist customers with. In these investigations, extended retention makes the difference between a quick resolution and a prolonged troubleshooting cycle.

Identify slow queries over extended periods

Without the MCP*

To identify slow queries over an extended period without the MCP server, enter the following SQL query in the Redshift query editor:

SELECT query_id, query_text, elapsed_time, queue_time,
       execution_time, start_time
FROM sys_tables_s3.sys_query_history
WHERE start_time >= DATEADD(day, -30, GETDATE())
  AND elapsed_time > 60000000
ORDER BY elapsed_time DESC
LIMIT 20;

With the MCP

With the server, you can enter the following prompt to perform the same analysis as the preceding SQL query:

“Show me the top 20 slowest queries from the past 30 days.

Include the query text, total elapsed time, queue time, and when they ran.”

The AI agents translate the natural language query into SQL, execute it against the external schema, and return the results in a formatted table. You don't need to know the column names or table structure to generate the correct output.

Connection pattern analysis for capacity planning

To perform a connection pattern analysis, enter the following prompt:

“Show me the hourly connection count for the past 90 days. Identify the peak hours and days of the week.

Are there seasonal patterns on month-end dates?”

The agent queries SYS_CONNECTION_LOG across 90 days of retained data to identify patterns that help you plan capacity for recurring business cycles.

Failed query investigation

To identify queries that failed, enter the following prompt:

“Show me all failed queries from the past 2 weeks. Group them by error type and user. Which errors are recurring daily?”

The agent queries the error-related columns in SYS_QUERY_HISTORY, groups and aggregates the results, and highlights recurring patterns that require attention.

Cross-warehouse comparison

If you configured the shared S3 table deployment model, then enter the following prompt to compare consolidated data from multiple warehouses across clusters:

“Compare the total query volume and average execution time between my analytics-cluster and reporting-cluster for the past month.

Which cluster has higher queue wait times?”

Security considerations

Before you use the integration, review the following security considerations:

• The Redshift MCP server operates in READ ONLY mode by default, which reduces the risk of modifying your audit data. However, the read-only mode applies only to queries that route through the ExecuteStatement tool. If IAM credentials have write permissions and CLI commands, such as aws redshift-data execute-statement, then bypass this guardrail. Use a least-privilege IAM policy.

• To preserve audit integrity, S3 Tables access is read-only by default.

• Use IAM policies to scope access to only necessary system tables.

• It's a best practice to use separate IAM roles for production and development observability access.

• Before you run it on sensitive environments, review generated SQL.

Best practices

Writing effective prompts

For best results with natural language observability prompts, adhere to the following best practices:

1. Be specific about time ranges. For example, "Past 30 days" is better than "recently".

2. Mention the metrics that are important to help you troubleshoot the issue, such as "completion time, queue time, and rows returned".

3. Specify groupings. For example, state "Break down by user" or "Group by hour of day".

4. Reference your deployment model. If you use shared tables, then mention which warehouse.

Show the generated SQL

To show the SQL that the server uses to query your S3 Tables, enter the following prompt at the beginning of your session:

“For every query you run against Redshift, show me the exact SQL alongside the results.”

The agent also displays its SQL queries for the rest of the conversation. Review the SQL to understand and validate what the agent executes.

Cost considerations

The natural language interface adds no additional cost because it translates your questions into the same SQL that you write manually.

Cleanup

If you must remove the MCP server configuration, then complete the following steps:

1. Remove the awslabs.redshift-mcp-server entry from your MCP configuration file.

2. Restart your MCP client.

The system table integration with S3 Tables continues to operate independently of the MCP server.

Conclusion

The customer scenario in this article explains how Amazon Redshift system table integration with Amazon S3 Tables pairs with the Amazon Redshift MCP server. This integration transforms multi-day investigations into conversational, interactive analyses. Instead of memorizing system view schemas and writing complex SQL, you can ask questions in plain English and get answers in seconds.

For Support engineers and TAMs, this workflow reduces the time to diagnose customer issues that require historical operational data. For more information about Support plans, see Compare AWS Support plans.

About the authors

Anant Jain is a Technical Consultant at AWS specializing in storage, resilience, and networking. He architects data protection and migration solutions for large-scale enterprise customers, helping them build secure, highly available cloud infrastructures. As a trusted advisor, he partners with organizations to design tailored cloud strategies that align technical solutions with business objectives.

Rajat Antil is a Technical Consultant at AWS with experience in cloud infrastructure, operations, and automation. A passionate technology enthusiast, he enjoys solving complex problems, automating mundane tasks, and building solutions for large scale enterprises that are secure, scalable, and resilient.

Soumya Gupta is a Technical Account Manager at AWS, passionate about gen AI, data analytics, and cloud security. She partners with enterprise customers to strengthen their cloud posture, drive adoption, and build scalable, resilient architectures. She has a keen interest in turning emerging AI/ML capabilities into practical, real-world solutions.