Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
# Query troubleshooting
This section provides a quick reference for identifying and addressing some of the most common and most serious issues that you are likely to encounter with Amazon Redshift queries.
**Topics**
+ [Connection fails](queries-troubleshooting-connection-fails.md)
+ [Query hangs](queries-troubleshooting-query-hangs.md)
+ [Query takes too long](queries-troubleshooting-query-takes-too-long.md)
+ [Load fails](queries-troubleshooting-load-fails.md)
+ [Load takes too long](queries-troubleshooting-load-takes-too-long.md)
+ [Load data is incorrect](queries-troubleshooting-load-data-incorrect.md)
+ [Setting the JDBC fetch size parameter](set-the-JDBC-fetch-size-parameter.md)
These suggestions give you a starting point for troubleshooting. You can also refer to the following resources for more detailed information.
For information about behavior changes in Amazon Redshift functionality that may affect your application, see [Behavior Changes](https://docs.aws.amazon.com/redshift/latest/mgmt/behavior-changes.html).
+ [Accessing Amazon Redshift clusters and databases](https://docs.aws.amazon.com/redshift/latest/mgmt/using-rs-tools.html)
+ [Automatic table optimization](t_Creating_tables.md)
+ [Loading data in Amazon Redshift](t_Loading_data.md)
+ [Tutorial: Loading data from Amazon S3](tutorial-loading-data.md)
# Connection fails
Your query connection can fail because for the following reasons. We suggest the following troubleshooting approaches.
**Client cannot connect to server**
If you are using SSL or server certificates, first remove this complexity while you troubleshoot the connection issue. Then add SSL or server certificates back when you have found a solution. For more information, go to [Configure Security Options for Connections](https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html) in the *Amazon Redshift Management Guide.*
**Connection is refused**
Generally, when you receive an error message indicating that there is a failure to establish a connection, it means that there is an issue with the permission to access the cluster. For more information, go to [The connection is refused or fails](https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-refusal-failure-issues.html) in the *Amazon Redshift Management Guide.*
# Query hangs
Your query can hang, or stop responding, for the following reasons. We suggest the following troubleshooting approaches.
**Connection to the database is dropped**
Reduce the size of maximum transmission unit (MTU). The MTU size determines the maximum size, in bytes, of a packet that can be transferred in one Ethernet frame over your network connection. For more information, go to [The connection to the database is dropped](https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-drop-issues.html) in the *Amazon Redshift Management Guide.*
**Connection to the database times out**
Your client connection to the database appears to hang or time out when running long queries, such as a COPY command. In this case, you might observe that the Amazon Redshift console displays that the query has completed, but the client tool itself still appears to be running the query. The results of the query might be missing or incomplete depending on when the connection stopped. This effect happens when idle connections are terminated by an intermediate network component. For more information, go to [Firewall Timeout Issue](https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html) in the *Amazon Redshift Management Guide.*
**Client-side out-of-memory error occurs with ODBC**
If your client application uses an ODBC connection and your query creates a result set that is too large to fit in memory, you can stream the result set to your client application by using a cursor. For more information, see [DECLARE](declare.md) and [Performance considerations when using cursors](declare.md#declare-performance).
**Client-side out-of-memory error occurs with JDBC**
When you attempt to retrieve large result sets over a JDBC connection, you might encounter client-side out-of-memory errors. For more information, see [Setting the JDBC fetch size parameter](set-the-JDBC-fetch-size-parameter.md).
**There is a potential deadlock**
If there is a potential deadlock, try the following:
+ View the [STV\$1LOCKS](r_STV_LOCKS.md) and [STL\$1TR\$1CONFLICT](r_STL_TR_CONFLICT.md) system tables to find conflicts involving updates to more than one table.
+ Use the [PG\$1CANCEL\$1BACKEND](PG_CANCEL_BACKEND.md) function to cancel one or more conflicting queries.
+ Use the [PG\$1TERMINATE\$1BACKEND](PG_TERMINATE_BACKEND.md) function to terminate a session, which forces any currently running transactions in the terminated session to release all locks and roll back the transaction.
+ Schedule concurrent write operations carefully. For more information, see [Managing concurrent write operations](c_Concurrent_writes.md).
# Query takes too long
Your query can take too long for the following reasons. We suggest the following troubleshooting approaches.
**Tables are not optimized**
Set the sort key, distribution style, and compression encoding of the tables to take full advantage of parallel processing. For more information, see [Automatic table optimization](t_Creating_tables.md)
**Query is writing to disk**
Your queries might be writing to disk for at least part of the query execution. For more information, see [Query performance improvement](query-performance-improvement-opportunities.md).
**Query must wait for other queries to finish**
You might be able to improve overall system performance by creating query queues and assigning different types of queries to the appropriate queues. For more information, see [Workload management](cm-c-implementing-workload-management.md).
**Queries are not optimized**
Analyze the explain plan to find opportunities for rewriting queries or optimizing the database. For more information, see [Creating and interpreting a query plan](c-the-query-plan.md).
**Query needs more memory to run**
If a specific query needs more memory, you can increase the available memory by increasing the [wlm\$1query\$1slot\$1count](r_wlm_query_slot_count.md).
**Database requires a VACUUM command to be run**
Run the VACUUM command whenever you add, delete, or modify a large number of rows, unless you load your data in sort key order. The VACUUM command reorganizes your data to maintain the sort order and restore performance. For more information, see [Vacuuming tables](t_Reclaiming_storage_space202.md).
## Additional resources for troubleshooting long-running queries
The following are system-view topics and other documentation sections that are helpful for query tuning:
+ The [STV\$1INFLIGHT](r_STV_INFLIGHT.md) system view shows which queries are running on the cluster. It can be helpful to use it together with [STV\$1RECENTS](r_STV_RECENTS.md) to determine which queries are currently running or recently completed.
+ [SYS\$1QUERY\$1HISTORY](SYS_QUERY_HISTORY.md) is useful for troubleshooting. It shows DDL and DML queries with relevant properties like their current status, such as `running` or `failed`, the time it took each to run, and whether a query ran on a concurrency-scaling cluster.
+ [STL\$1QUERYTEXT](r_STL_QUERYTEXT.md) captures the query text for SQL commands. Additionally, [SVV\$1QUERY\$1INFLIGHT](r_SVV_QUERY_INFLIGHT.md), which joins STL\$1QUERYTEXT to STV\$1INFLIGHT, shows more query metadata.
+ A transaction-lock conflict can be a possible source of query-performance issues. For information about transactions that currently hold locks on tables, see [SVV\$1TRANSACTIONS](r_SVV_TRANSACTIONS.md).
+ [Identifying queries that are top candidates for tuning](https://docs.aws.amazon.com/redshift/latest/dg/diagnostic-queries-for-query-tuning.html#identify-queries-that-are-top-candidates-for-tuning) provides a troubleshooting query that helps you determine which recently-run queries were the most time consuming. This can help you focus your efforts on queries that need improvement.
+ If you want to explore query management further and understand how to manage query queues, [Workload management](cm-c-implementing-workload-management.md) shows how to do it. Workload management is an advanced feature and we recommend automated workload management in most cases.
# Load fails
Your data load can fail for the following reasons. We suggest the following troubleshooting approaches.
**Data Source is in a different AWS Region**
By default, the Amazon S3 bucket or Amazon DynamoDB table specified in the COPY command must be in the same AWS Region as the cluster. If your data and your cluster are in different Regions, you receive an error similar to the following:
```
The bucket you are attempting to access must be addressed using the specified endpoint.
```
If at all possible, make sure your cluster and your data source are in the same Region. You can specify a different Region by using the [REGION](copy-parameters-data-source-s3.md#copy-region) option with the COPY command.
**Note**
If your cluster and your data source are in different AWS Regions, you incur data transfer costs. You also have higher latency.
**COPY command fails**
Query STL\$1LOAD\$1ERRORS to discover the errors that occurred during specific loads. For more information, see [STL\$1LOAD\$1ERRORS](r_STL_LOAD_ERRORS.md).
# Load takes too long
Your load operation can take too long for the following reasons. We suggest the following troubleshooting approaches.
**COPY loads data from a single file**
Split your load data into multiple files. When you load all the data from a single large file, Amazon Redshift is forced to perform a serialized load, which is much slower. The number of files should be a multiple of the number of slices in your cluster, and the files should be about equal size, between 1 MB and 1 GB after compression. For more information, see [Amazon Redshift best practices for designing queries](c_designing-queries-best-practices.md).
**Load operation uses multiple COPY commands**
If you use multiple concurrent COPY commands to load one table from multiple files, Amazon Redshift is forced to perform a serialized load, which is much slower. In this case, use a single COPY command.
# Load data is incorrect
Your COPY operation can load incorrect data in the following ways. We suggest the following troubleshooting approaches.
**Wrong files are loaded**
Using an object prefix to specify data files can cause unwanted files to be read. Instead, use a manifest file to specify exactly which files to load. For more information, see the [copy_from_s3_manifest_file](copy-parameters-data-source-s3.md#copy-manifest-file) option for the COPY command and [Example: COPY from Amazon S3 using a manifest](r_COPY_command_examples.md#copy-command-examples-manifest) in the COPY examples.
# Setting the JDBC fetch size parameter
By default, the Redshift JDBC driver uses a ring buffer to manage memory efficiently and prevent out-of-memory errors. The fetch size parameter is only applicable when the ring buffer is explicitly disabled. For more information, review the [link](https://docs.aws.amazon.com/redshift/latest/mgmt/jdbc20-configuration-options.html#jdbc20-enablefetchringbuffer-option). In this configuration, you should set the fetch size to control how many rows are retrieved in each batch.
Use the fetch size parameter when:
+ You need fine-grained control over row-based batching
+ Working with legacy applications that require traditional fetch size behavior
When ring buffer is disabled, the JDBC driver collects all results for a query at one time by default. Queries that return large result sets can consume excessive memory. To retrieve result sets in batches instead of all at once, set the JDBC fetch size parameter in your application.
**Note**
Fetch size is not supported for ODBC.
For the best performance, set the fetch size to the highest value that does not lead to out of memory errors. A lower fetch size value results in more server trips, which prolong execution times. The server reserves resources, including the WLM query slot and associated memory, until the client retrieves the entire result set or the query is canceled. When you tune the fetch size appropriately, those resources are released more quickly, making them available to other queries.
**Note**
If you need to extract large datasets, we recommend using an [UNLOAD](https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html) statement to transfer the data to Amazon S3. When you use UNLOAD, the compute nodes work in parallel to speed up the transfer of data.
For more information about setting the JDBC fetch size parameter, go to [Getting results based on a cursor](https://jdbc.postgresql.org/documentation/query/#getting-results-based-on-a-cursor) in the PostgreSQL documentation.