Find When was a table last changed in PostgreSQL?

In PostgreSQL, you can use the pg_class system catalog table to determine when a table was last changed. The pg_class table contains metadata about all tables in the database, including the timestamp of the last modification. Here is an example of a query that you can use to find when a table was last changed:

SELECT relname, relkind, reltuples, relpages, relacl,
       to_char(pg_relation_last_system_change(oid), 'YYYY-MM-DD HH24:MI:SS') as last_change
FROM pg_class
WHERE relname = 'mytable';

SELECT relname, relkind, reltuples, relpages, relacl,

to_char(pg_relation_last_system_change(oid), 'YYYY-MM-DD HH24:MI:SS') as last_change

FROM pg_class

WHERE relname = 'mytable';

This query will return the name, kind, number of rows, number of pages, access control list and last modification time of the table named 'mytable' Alternatively, you can use the pg_stat_all_tables view, which also contains a last_vacuum and last_analyze columns that show when these operations were last run on the table.

SELECT relname, last_vacuum, last_analyze, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'mytable';

SELECT relname, last_vacuum, last_analyze, last_autovacuum

FROM pg_stat_all_tables

WHERE relname = 'mytable';

The pg_stat_all_tables view also contains other information about the table such as how many rows, how many dead rows, how many live rows and how many updates, deletes and insertions are made on the table. It's important to note that these timestamps are only updated when a vacuum or analyze operation is run on the table and they only give the time of the last vacuum or analyze operation and not when the last change was made on the table.

The WebScale Database Infrastructure Operations Experts in PostgreSQL, MySQL, MariaDB and ClickHouse

Enterprise-class 24*7 Consultative Support and Managed Services for PostgreSQL, MySQL, MariaDB and ClickHouse

Find When was a table last changed in PostgreSQL?

Find When was a table last changed in PostgreSQL?