Table access statistics

In this recipe, we will be discussing how to study the table access statistics by using PostgreSQL catalog views.

Getting ready

PostgreSQL's statistics collector process keeps track of all kinds of operations that are performing on any table or an index. These statistics will be helpful in determining the importance of an object in the business, and also defines whether the required maintenance operations are running on it when it is required.

How to do it...

Let's create a test table, which we will perform a few operations on:

postgres=# CREATE TABLE test (t INT); 
CREATE TABLE

PostgreSQL provides a few catalog views, which we are going to query to analyze the statistics, which statistics collector process is collected in the background.

pg_stat_user_tables

This catalog view provides most of the transactional metrics as well as maintenance operations metrics. Now, let's go and perform a few transactions and see how they reflect in this view:

postgres=# INSERT INTO...