How Do I Check Whether a Table Has Been Updated or Deleted?

Symptom

You need to check for update and 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 and 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 swap 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 commands to find the tables that have been updated or deleted:

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' ;