Product Documentation

Appearance

All platforms

sys.retention ​

This system view captures information about the retention policies on system relations. See also Retention policies for system relations.

Column NameData TypeDescription
database_idbigintThe unique ID for the database.
class_idbigintThe unique ID for the relation. Relations in different schemas within the same database may have the same name, so you can use this ID to uniquely identify a relation.
retention_msbigintThe retention age for that relation, in milliseconds. See ALTER TABLE to set that field.
retention_bytesbigintThe retention age for that relation, in bytes. See ALTER TABLE to set that field.
enabledbooleanWhether this retention policy is enabled.
last_retention_runtimestamp with time zoneThe last time the retention process run against this relation. See sys.log_retention for more history information.

Examples ​

We can join sys.retention with sys.view to have string names instead of IDs:

sql
SELECT v.name AS relation, r.retention_ms, r.retention_bytes, r.enabled, r.last_retention_run
    FROM sys.retention r
    JOIN sys.view v ON r.class_id = v.view_id AND r.database_id = v.database_id
    ORDER BY relation;
console
premdb=#
      relation      | retention_ms | retention_bytes | enabled |      last_retention_run
--------------------+--------------+-----------------+---------+-------------------------------
 log_analyze        |   7776000000 |      1073741824 | t       | 2023-11-19 13:05:24.226491+00
 log_authentication |              |                 | f       |
 log_backup         |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:12.004477+00
 log_cluster_event  |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:12.001678+00
 log_load           |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:11.995431+00
 log_query          |   2592000000 |     34359738368 | t       |
 log_query_analyze  |   2592000000 |     34359738368 | t       | 2023-11-19 13:05:24.214316+00
 log_replica_status |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:12.012928+00
 log_restore        |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:12.00742+00
 log_retention      |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:12.010452+00
 log_session        |              |                 | f       |
 log_unload         |   7776000000 |      1073741824 | t       | 2023-11-19 13:06:11.998813+00
(12 rows)