CODEWITHSUNDEEP
postgresql
dpg5000
dpg5000

Reputation: 363

Finding table source for increased UPDATES in Postgres DB

One of the Postgres databases I'm monitoring is getting swamped with a ton of UPDATES. What's the best method in Postgres to easily identify what table or tables in my instance is getting the bulk of these DML requests?

Upvotes: 1

Views: 44

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

you can use pg_stat_all_tables for it, like here:

t=# create table t19 (i int);
CREATE TABLE
t=# insert into t19 select 1;
INSERT 0 1
t=# select schemaname,relname,n_tup_upd from pg_stat_all_tables order by n_tup_upd desc limit 2;
 schemaname |     relname     | n_tup_upd
------------+-----------------+-----------
 pg_catalog | pg_operator     |         0
 pg_catalog | pg_auth_members |         0
(2 rows)

t=# update t19 set i=2;
UPDATE 1
t=# update t19 set i=2;
UPDATE 1
t=# select schemaname,relname,n_tup_upd from pg_stat_all_tables order by n_tup_upd desc limit 2;
 schemaname |   relname   | n_tup_upd
------------+-------------+-----------
 public     | t19         |         2
 pg_catalog | pg_operator |         0
(2 rows)

Upvotes: 2

Related Questions