How to Control Result Cache Behavior in Redshift

Galaxy Glossary

How do I disable or enable query result caching in Amazon Redshift?

enable_result_cache_for_session toggles whether Redshift serves a cached result or re-executes a query.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does the result cache do?

Redshift stores the final result of read-only SELECT statements. When an identical query arrives, Redshift can return the saved result instantly, skipping scan and join work.

When should I disable the cache?

Disable the cache when you need real-time data (e.g., after an UPDATE on Orders) or when benchmarking query performance. Otherwise, leave it on for speed.

How do I turn the cache on or off for my session?

Use the SET enable_result_cache_for_session command. Changing the parameter affects only the current session and reverts on disconnect.

Syntax breakdown

SET enable_result_cache_for_session TO { on | off }; — Switches cache use.
RESET enable_result_cache_for_session; — Returns to the default (on).

Practical example

1. Ensure fresh totals after inserting a new order.
2. Compare runtimes with and without caching.

-- Disable caching for real-time accuracy SET enable_result_cache_for_session TO off; SELECT customer_id, SUM(total_amount) AS lifetime_spend FROM Orders GROUP BY customer_id; -- Re-enable caching for the rest of your work SET enable_result_cache_for_session TO on;

Best practices for result cache management

Keep caching enabled in dashboards and ad-hoc analysis. Toggle it off only for data-freshness-critical queries or performance testing.

Common mistakes and fixes

Mistake 1 – Assuming data is stale when cache is off. Verify the session parameter; cached results appear only when it’s on.

Mistake 2 – Disabling cache globally. Use session-level SET, not parameter groups, unless every workload needs fresh results.

Does SELECT … FOR UPDATE use the cache?

No. Only read-only SELECT statements are eligible. Any write or lock excludes caching automatically.

How can I confirm a hit or miss?

Query STL_QUERY. A value of 1 in result_cache_hit means the cache satisfied the query.

Why How to Control Result Cache Behavior in Redshift is important

How to Control Result Cache Behavior in Redshift Example Usage


-- Disable caching to ensure we read the order just inserted
SET enable_result_cache_for_session TO off;

INSERT INTO Orders (id, customer_id, order_date, total_amount)
VALUES (1001, 5, CURRENT_DATE, 299.00);

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_spend
FROM   Orders
GROUP  BY customer_id;

How to Control Result Cache Behavior in Redshift Syntax


-- Turn cache off for the current session
SET enable_result_cache_for_session TO off;

-- Restore default behavior (ON)
RESET enable_result_cache_for_session;

-- Optional: specify in one call
SET enable_result_cache_for_session = on;

-- Example in ecommerce context
SET enable_result_cache_for_session TO off;
SELECT p.name,
       SUM(oi.quantity) AS units_sold
FROM   Products p
JOIN   OrderItems oi ON p.id = oi.product_id
GROUP  BY p.name;

Common Mistakes

Leaving caching disabled for long sessions: Queries run slower. Always RESET or SET to on after the freshness-critical statement.
Assuming cache ignores data changes automatically: Redshift serves stale results when cache is on. Toggle off after DML if fresh data is required.

Frequently Asked Questions (FAQs)

Does disabling the cache impact write queries?

No. The parameter affects only read-only SELECT statements. INSERT, UPDATE, DELETE, and COPY operations remain unaffected.

Can I disable caching for a single statement?

Yes. Wrap the query between SET enable_result_cache_for_session TO off and RESET enable_result_cache_for_session or turn it back to on immediately after the statement.