Database monitoring, performance tuning and query optimization are critical operations for maintaining an efficient database system. A key component in PostgreSQL for this purpose is the pg_stat_all_tables view.

pg_stat_all_tables offers real time statistics on table activities such as number of sequential and index scans performed on a table, updates, deletes, inserts, and more. It also offers information on dead tuples along with vacuum and analyze stats which enables DB admins to make data-driven decisions. Here’s a table detailing the columns in the pg_stat_all_tables:

For more detailed information, you can refer to PostgreSQL’s official documentation on monitoring statistics views: Monitoring Stats Views.

1: How to identify tables with the highest frequency of sequential scans in a PostgreSQL database?

SELECT
    schemaname,
    relname,
    seq_scan,
    idx_scan,
    seq_tup_read,
    seq_tup_read / seq_scan AS avg_seq_read
FROM
    pg_stat_all_tables
WHERE
    seq_scan > 0
    AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    avg_seq_read DESC
LIMIT 10;

This query will list the top 10 tables based on the average number of tuples read in sequential scans (avg_seq_read). We can also change the orderby clause to seq_scan parameter. Observing a high number of sequential scans with a low count of index scans on a table could indicate that the table may benefit from indexing, especially if the queries executed on this table frequently use WHERE clauses.

2: How to identify unused or infrequently accessed tables in postgresql?

SELECT
    schemaname,
    relname,
    seq_scan,
    Idx_scan,
    (COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) as total_scans_performed
FROM
    pg_stat_all_tables
WHERE
    (COALESCE(seq_scan, 0) + COALESCE(idx_scan, 0)) < 10
    AND schemaname not in ('pg_catalog', 'information_schema')
ORDER BY
    5 DESC;

This query will identify tables that have a total scan count of less than 10(threshold). In PostgreSQL v16, the addition of the last_seq_scan and last_idx_scan columns enables us to determine the Last Access Time of the tables.

More from the Blog: Database Concurrency: Two phase Locking (2PL) to MVCC – Part 1

3: How to check the write activity of tables in PostgreSQL?

SELECT
    st.schemaname,
    st.relname,
    pg_size_pretty(pg_total_relation_size(st.relid)) AS Total_Size,
    st.seq_scan,
    st.idx_scan,
    st.n_tup_ins,
    st.n_tup_upd,
    st.n_tup_del,
    st.n_tup_hot_upd,
    st.n_tup_hot_upd * 100 / (CASE WHEN st.n_tup_upd > 0 THEN st.n_tup_upd ELSE 1 END) AS hot_percentage
FROM
    pg_stat_all_tables st
WHERE
    st.schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    Total_Size DESC;

This data helps us grasp the DML activity on the table. The n_tup_upd and n_tup_hot_upd columns in the view indicate the total counts of regular and HOT(Heap-Only Tuple) updates for each table. It’s important to focus on tables with a low hot_percentage and a high frequency of write operations indicated by n_tup_ins, n_tup_upd. Regular monitoring of these statistics is beneficial in order to understand the write pattern.

4: How to determine the number of live and dead tuples in a table and check their vacuum status?

SELECT
    schemaname,
    relname,
    n_live_tup,
    n_dead_tup,
    n_dead_tup * 100 / (CASE WHEN n_live_tup > 0 THEN n_live_tup ELSE 1 END) AS dead_rows_percent,
    last_autovacuum,
    last_autoanalyze,
    n_dead_tup,
    relname
FROM
    pg_stat_all_tables
WHERE
    schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    n_dead_tup DESC;

A high count of n_dead_tup typically indicates extensive update or delete operation on a table. Excessive dead tuples can cause performance problems, as it may lead the query planner to make inaccurate estimations, potentially resulting in suboptimal plans. These dead tuples are cleaned up by the autovacuum daemon. Monitoring the last_autovacuum and last_autoanalyze timestamps helps in understanding when the autovacuum last operated on this table. This information is useful for adjusting autovacuum configurations and planning routine maintenance activities.

More from the Blog: Vacuum Best Practices

The pg_stat_all_tables view is indeed a valuable resource for PostgreSQL database administrators. 

By effectively analyzing and interpreting the data from this view, admins can significantly improve the performance by identifying tables that require maintenance or removal for storage reclamation, as well as by fine-tuning query performance.