Why does Amazon Redshift create temporary tables on my cluster and do the tables consume node memory space?

Resolution

Amazon Redshift creates temporary tables to store the intermediate query processing results of data definition language (DDL) and data manipulation language (DML) operations. Amazon Redshift stores these tables in a separate, session-specific schema with a pg_temp_1 naming convention. You can't specify a name for this schema. The materialized view refresh also uses temporary tables to store intermediate query results.

Users can also use the TEMPORARY and TEMP keywords in CREATE TABLE statements to create temporary tables.

Important: If you specify a table name that begins with #, then Amazon Redshift creates the table as a temporary table. Example command:

create table #newtable (id) as select * from oldtable;

Temporary tables are visible only within the current session. After the session ends, Amazon Redshift automatically deletes the temporary tables from that session.

When you activate data sharing on the cluster, you can set the cluster as the consumer. In this scenario, Amazon Redshift creates temporary objects to store the data blocks that end users retrieve from the producer cluster.

By default, database users have permissions to create temporary tables through their automatic membership in the PUBLIC group. To block this permission, run the following query to revoke the TEMP permissions from the PUBLIC group:

Revoke TEMPORARY | TEMP on DATABASE db_name from GROUP PUBLIC;

Note: Replace db_name with your database name.

Then, explicitly grant TEMP permissions to users or user groups that you want to allow to create temporary tables.

Understand temporary table storage

Temporary tables use local storage on the compute nodes. After the session ends and Amazon Redshift drops the temporary tables, Amazon Redshift releases the storage back to the node. Temporary tables adhere to the following storage requirements:

• Although temporary tables use the overall storage allocation for your cluster, the storage is transient and doesn't contribute to your persistent data storage costs.
• The amount of storage that temporary tables use depends on the size of the data and the complexity of your queries.
• When you have large, complex queries that require large temporary tables, the cluster storage space might temporarily spike.

Note: By default, Amazon Redshift applies the same automatic compression to temporary tables as it does to permanent tables. Temporary tables are designed for efficient query processing and store intermediate results to improve query performance.

Related information

STV_TBL_PERM

STL_SAVE