The STV_TBL_PERM table contains information about the permanent tables in Amazon Redshift, including temporary tables created by a user for the current session. STV_TBL_PERM contains information for all tables in all databases.
This table differs from STV_TBL_TRANS, which contains information about transient database tables that the system creates during query processing.
STV_TBL_PERM is visible only to superusers. For more information, see Visibility of Data in System Tables and Views.
|Column Name||Data Type||Description|
|slice||integer||Node slice allocated to the table.|
|rows||bigint||Number of data rows in the slice.|
|sorted_rows||bigint||Number of rows in the slice that are already sorted on disk. If this number does not match the ROWS number, vacuum the table to resort the rows.|
|temp||integer||Whether or not the table is a temporary table. 0 = false; 1 = true.|
|db_id||integer||ID of the database where the table was created.|
|insert_pristine||integer||For internal use.|
|delete_pristine||integer||For internal use.|
|backup||integer||Value that indicates whether the table is included in cluster snapshots. 0 = no; 1 = yes. For more information, see the BACKUP parameter for the CREATE TABLE command.|
The following query returns a list of distinct table IDs and names:
select distinct id, name from stv_tbl_perm order by name; id | name --------+------------------------- 100571 | category 100575 | date 100580 | event 100596 | listing 100003 | padb_config_harvest 100612 | sales ...
Other system tables use table IDs, so knowing which table ID corresponds to a certain table can be very useful. In this example, SELECT DISTINCT is used to remove the duplicates (tables are distributed across multiple slices).
To determine the number of blocks used by each column in the VENUE table, type the following query:
select col, count(*) from stv_blocklist, stv_tbl_perm where stv_blocklist.tbl = stv_tbl_perm.id and stv_blocklist.slice = stv_tbl_perm.slice and stv_tbl_perm.name = 'venue' group by col order by col; col | count -----+------- 0 | 8 1 | 8 2 | 8 3 | 8 4 | 8 5 | 8 6 | 8 7 | 8 (8 rows)
The ROWS column includes counts of deleted rows that have not been vacuumed (or have been vacuumed but with the SORT ONLY option). Therefore, the SUM of the ROWS column in the STV_TBL_PERM table might not match the COUNT(*) result when you query a given table directly. For example, if 2 rows are deleted from VENUE, the COUNT(*) result is 200 but the SUM(ROWS) result is still 202:
delete from venue where venueid in (1,2); select count(*) from venue; count ------- 200 (1 row) select trim(name) tablename, sum(rows) from stv_tbl_perm where name='venue' group by name; tablename | sum -----------+----- venue | 202 (1 row)
To synchronize the data in STV_TBL_PERM, run a full vacuum the VENUE table.
vacuum venue; select trim(name) tablename, sum(rows) from stv_tbl_perm where name='venue' group by name; tablename | sum -----------+----- venue | 200 (1 row)