Find When was a table last changed in PostgreSQL?

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: 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. 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.
About Shiv Iyer 72 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.

Be the first to comment

Leave a Reply