Product Documentation

Appearance

All platforms

sys.log_retention ​

This view records the history of the retention policy process. 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.
start_timetimestamp with time zoneWhen the retention process started running against this table.
delete_countbigintThe number of records removed by this retention cycle.
retention_msnumeric(18,3)The duration of this retention cycle, in milliseconds.
delete_msnumeric(18,3)The duration of the DELETE statement for this retention cycle, in milliseconds.
vacuum_msnumeric(18,3)The duration of the VACUUM statement for this retention cycle, in milliseconds.

Examples ​

We can join sys.log_retention with sys.schema and sys.view to have string names instead of IDs:

sql
SELECT v.name AS relation, r.start_time, r.delete_count, r.retention_ms
    FROM sys.log_retention r
    JOIN sys.view v ON r.class_id = v.view_id AND r.database_id = v.database_id
    ORDER BY r.start_time DESC
    LIMIT 10;
console
premdb=#
      relation      |          start_time           | delete_count | retention_ms
--------------------+-------------------------------+--------------+--------------
 log_restore        | 2023-11-20 10:46:33.451834+00 |            0 |        1.528
 log_query_analyze  | 2023-11-20 10:46:33.451834+00 |            0 |        1.551
 log_cluster_event  | 2023-11-20 10:46:33.451834+00 |            0 |        1.329
 log_backup         | 2023-11-20 10:46:33.451834+00 |            0 |        1.418
 log_analyze        | 2023-11-20 10:46:33.451834+00 |            0 |        1.505
 log_unload         | 2023-11-20 10:46:33.451834+00 |            0 |        1.337
 log_query          | 2023-11-20 10:46:33.451834+00 |            0 |      481.642
 log_load           | 2023-11-20 10:46:33.451834+00 |            0 |        1.760
 log_retention      | 2023-11-20 10:46:33.451834+00 |            0 |        0.882
 log_replica_status | 2023-11-20 10:46:33.451834+00 |            0 |        2.648
(10 rows)