Running out of Disk Space on ra3.xlplus Node with Managed Storage

You're correct that RA3 nodes use managed storage, which automatically offloads less frequently accessed "cold" data to Amazon S3 while keeping "hot" data on the node's local SSDs.

To address your questions:

1. How can I tell how much data has been moved to S3? You can check your storage usage by querying the STV_NODE_STORAGE_CAPACITY system table. This table shows both the used storage and total capacity. For RA3 nodes, the "used" column includes both locally cached blocks and blocks persisted in Amazon S3. You can run:

SELECT node, used, capacity 
FROM STV_NODE_STORAGE_CAPACITY;

This will show you the total storage used across both local SSD and S3.

2. Shouldn't the amount of data stored on the node's SSD go down at some point if cold data is moved to S3? The behavior you're experiencing is actually expected. With RA3 nodes, Amazon Redshift automatically manages the placement of data between local SSDs and S3, but it doesn't necessarily reduce the amount of data shown as "used" on the node. The system is designed to keep as much data as possible on the fast local SSDs for performance reasons, and only offload to S3 when necessary.

When you're seeing 70% usage of your 4TB SSD, Redshift is likely keeping that data locally because it has the space to do so. The managed storage system will only start actively offloading to S3 when the local storage gets closer to capacity.

If you're occasionally running out of space when creating large new tables, this suggests that at those moments, the system is hitting the local SSD capacity limit before it can efficiently offload data to S3. In these cases, you might want to:

1. Consider adding more nodes to your cluster if you frequently need more local SSD capacity
2. Break very large table creations into smaller batches to give the system time to manage storage placement
3. Monitor your storage usage more closely before large operations

Remember that with RA3 nodes, you're not limited by the local SSD capacity for your total storage - you can store much more data than what fits on the local SSDs. The system will automatically manage the tiered storage architecture, keeping frequently accessed data on SSDs and moving less frequently accessed data to S3.
Sources
Redshift ra3.large type provides only 1TB storage even though in the docs it says it gives 8TB per node | AWS re:Post
Amazon Redshift provisioned clusters - Amazon Redshift
STV_NODE_STORAGE_CAPACITY - Amazon Redshift