User guide

number of cursors for your cluster, and to achieve the ideal balance between maximum result set size
and concurrency.
Important
Before you modify this parameter, you should understand the considerations involved with using
cursors and the impact that they have on query performance. For more information about the
considerations, see Cursor Constraints in the Amazon Redshift Database Developer Guide.
Each Amazon Redshift cluster has a maximum size of data that can be stored across all currently active
cursors.This limit is not configurable and is different for each node type.The number of concurrent cursors
that you can have for your cluster is determined by the maximum total size allowed for your node type
divided by the max-cursor-result-set-size parameter value.
For example, if you have a DW1 XL single node cluster, and you use the default value of two cursors and
32000 MB of data per cursor, the ratio in megabytes is 64000/32000=2. This means that you can have
up to two concurrent active cursors, each storing 32000 megabytes of data. However, if you want to have
four concurrent cursors for this same node type, you can set max_cursor_result_set_size to 16000.
The ratio then becomes 64000/16000=4.
The number of concurrent cursors cannot exceed the maximum number of concurrent queries. Amazon
Redshift limits the concurrent queries at 50, and adjusts the max_cursor_result_set_size accordingly
if the value you set causes the concurrent cursor number to be higher.
The following table shows the maximum total result set size for each cluster node type, the default
maximum cursor result set, and the number of concurrent cursors based on the default maximum cursor
size. Result set sizes are in megabytes:
Concurrent
cursors (de-
fault)
Maximum res-
ult set per
cursor (de-
fault)
Maximum result set per cluster (MB)Node type
23200064000DW1 XL single
node
44500001800000DW1 XL mul-
tiple nodes
1596000014400000DW1 8XL mul-
tiple nodes
11600016000DW2 Large
single node
2192000384000DW2 Large
multiple nodes
47500003000000DW2 8XL mul-
tiple nodes
When you create a parameter group, you can set any value in the globally allowed range for
max_cursor_result_set_size. However, once the parameter group is associated with a cluster,
Amazon Redshift performs validation to ensure that the max_cursor_result_set_size parameter is
within the allowable range. This validation occurs when you create, modify, or resize an existing cluster
and when you modify an existing parameter group that is already associated with one or more clusters.
If the parameter group is associated with more than one cluster, Amazon Redshift uses the lowest value
allowed for all of the clusters. If the value is not valid, an error is returned that indicates that the parameter
group is incompatible with the cluster because the max_cursor_result_set_size parameter is out
API Version 2012-12-01
58
Amazon Redshift Management Guide
Configuring the Maximum Size of a Cursor Result Set