How Do I Determine Whether UPDATE or DELETE Has Been Executed on a Table?

Symptom

You need to check for UPDATE or DELETE operations on a table in either of the following scenarios:

1. Frequent update or delete operations on a table generate a large number of disk page fragments, which affect query performance. To improve performance, you need to identify which table has been updated, and then do VACUUM FULL to restore disk page fragments and OS memory.
2. To determine whether a table is a dimension table and whether it can be changed from a hash table to a replication table, check whether the table has been updated or deleted. If it does, it cannot be changed to a replication table.

Solution

Run the following command to query the tables on which the UPDATE and DELETE operations have been performed:

1
2
3
4
5
6
7
8
9

ANALYZE tablename;
SELECT  
    n.nspname , c.relname, 
    pg_stat_get_tuples_deleted(x.pcrelid) as deleted,
pg_stat_get_tuples_updated(x.pcrelid) as updated
FROM pg_class c
INNER JOIN pg_namespace n ON n.oid = c.relnamespace
INNER JOIN pgxc_class x ON x.pcrelid = c.oid
WHERE c.relkind = 'r' and c.relname='tablename' ;