How to see which tables accessed in postgreSQL?

Question:

How to view the tables that are being accessed by the select command?

EX: select *from pg_stats

I would like to get the tables that are having the most access on a given day in the database.

Answer:

Internally, PostgreSQL has a subsystem (known as Statistics Collector ) responsible for monitoring all activities that are performed by the server.

This monitoring information is made available through a few dozen system VIEWS .

A VIEW called pg_stat_activity contains real-time information about all running processes, including queries that are currently running.

For example, to look up activity in the database named foobar :

SELECT * FROM pg_stat_activity WHERE datname = 'foobar'; 

Another alternative is to use a log analyzer to gather statistics from the log files generated by Postgres.

PgBadger does this job very well, generating reports in HTML format.

Scroll to Top
AllEscort