How to find the number of SELECTs, INSERTs, DELETEs, and UPDATEs on a specific table in PostgreSQL?

Knowing the ratio of SELECT, INSERT, DELETE, and UPDATE operations in PostgreSQL databases (or any other database management system) is crucial for several reasons, reflecting performance, optimization, and architectural planning.

To find the number of SELECTs, INSERTs, DELETEs, and UPDATEs on a specific table in PostgreSQL, you can utilize the pg_stat_all_tables view for basic DML statistics (INSERT, UPDATE, DELETE) but unfortunately, this view does not track SELECT operations directly.

For DML operations, you can query the pg_stat_all_tables view. This view provides counts for INSERTs, UPDATEs, and DELETEs, but not for SELECTs.

Here’s how you can get those counts for a specific table in a specific schema:

SELECT relname AS table_name,
schemaname AS schema_name,
coalesce(seq_scan, 0) + coalesce(idx_scan, 0) AS total_selects, 
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_del AS deletes
FROM pg_stat_all_tables
WHERE relname = 'your_table_name'
AND schemaname = 'public'
ORDER by (coalesce(seq_scan, 0) + coalesce(idx_scan, 0) + n_tup_ins + n_tup_upd + n_tup_del) DESC;

Here’s how you can get those counts for all the tables in a specific schema:

SELECT relname AS table_name,
schemaname AS schema_name,
coalesce(seq_scan, 0) + coalesce(idx_scan, 0) AS total_selects, 
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_del AS deletes
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER by (coalesce(seq_scan,0) + coalesce(idx_scan,0) + n_tup_ins + n_tup_upd + n_tup_del) DESC;

In these queries, seq_scan + idx_scan gives us an approximation of SELECT operations: it counts the number of sequential and index scans, which is not a direct measure of SELECT statements but can give you an idea of the reading activity on the table.