Teradata Spaces - Perm , Spool, Temp - Understanding

Space in Teradata

In order to better manage the space and queries, it is important to understand the concepts of space management in Teradata which will enable one to write optimized queries.

A user or database is assigned two types of space. They are Permanent Space and Spool Space. PERM space is used to store tables and SPOOL space is for users to run their queries. If your query exceeds your allocated Spool space you will need a second chance because your query is immediately aborted.
This paragraph is vital in understanding space on Teradata so pay attention.

Lets say you give a user or database 4 Gigabytes of PERM space to hold tables. Space is always divided equally over the number of AMPs to ensure reasonable data distribution. Assume we have a 2 AMP system and the user was given 4 Gigabytes then each AMP is assigned 2 Gigabytes of PERM space for the users tables. If uneven distribution occurs then your table will stop loading because you are out of space. The same theory goes with SPOOL space. If you are given 2 Gigabytes of Spool Space then your queries can grow until they reach 2 Gigabytes, but remember that space is equally divided over the number of AMPs.

Once again since there are 2 AMPs (picture below) in the system your query cannot exceed 1 Gigabyte per AMP. If you have what is called a “Hot AMP” where more data is stored is on one AMP versus the others you can run out of space prematurely. Permanent Space is where objects (i.e., databases, users, tables) are created and stored. Permanent Space is released when data is deleted or when objects are dropped.

Spool Space is PERM space on the system that has not been allocated. The Primary reason for SPOOL space is to be available to store intermediate results or queries that are being processed in Teradata. Spool Space is released when the query is over or when the query no longer needs it.

Volatile Tables consume Spool Space and Permanent Tables consumes Perm Space. Apart from this, there is TEMP SPACE which is all unused space of PERM SPACE and it is used by Global Temporary Tables.