Issues with Disabling Result Cache in Amazon Redshift Provisioned vs. Redshift Serverless

0

Question:

To whom it may concern,

We have encountered a discrepancy in the behavior of result cache management between Amazon Redshift provisioned clusters and Redshift Serverless instances. Our objective is to discuss the effectiveness of disabling result cache for specific users in both environments.

Problem Statement:

When utilizing the following SQL queries to manage result cache for specific users in Amazon Redshift:

  • Disabling cache for a specific user:
ALTER USER our_user SET enable_result_cache_for_session TO off;
  • Enabling cache for a specific user:
ALTER USER our_user SET enable_result_cache_for_session TO on;
  • Checking cache status:
SHOW enable_result_cache_for_session;

In addition to the queries above, as official documentation states, we followed this approach:

Redshift Provisioned:
SELECT userid, query, elapsed, source_query 
FROM svl_qlog 
WHERE userid > 1 
ORDER BY query desc; 

column source_query will have the query id of cached result

Redshift Serverless:
SELECT user_id, query_id, elapsed_time,result_cache_hit,query_text 
FROM SYS_QUERY_HISTORY 
WHERE user_id > 1 
ORDER BY start_time desc;

column result_cache_hit will be set to true/false

We have observed that while in Redshift provisioned clusters with three nodes, the cache is effectively disabled, the same does not hold true for Redshift Serverless instances. Despite executing the query indicating cache disablement, the cache seems to persist in Redshift Serverless environments.

Queries:

  1. Is there a known explanation for the disparity in result cache behavior between Redshift provisioned clusters and Redshift Serverless instances?
  2. Are there alternative methods or configurations to ensure effective result cache management in Redshift Serverless environments?
  3. Could this discrepancy potentially be attributed to underlying architectural differences between Redshift provisioned and Redshift Serverless setups?

We appreciate any insights or experiences the AWS team can provide to address this issue effectively.

Thank you in advance for your time reading and answering to our issue and thank you for helping :D

profile picture
asked a month ago101 views
1 Answer
0

The behavior, as you have mentioned it, is unexpected. I think its best for you to open up a Support Ticket so this gets investigated and resolved throughly.

profile pictureAWS
EXPERT
answered a month ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions