PostgreSQL activity statistics: Questions and Answers.

This is a list of questions which might occur during PostgreSQL administration and possible ways of how to answer on these questions.

This should be used with pgstats.dev

Content

1. Client Backends

   • [x] what is client backend?
   • [x] how many clients connected to the server?
   • [x] how many clients connected to the database?
   • [x] how many clients connected remotely?
   • [x] what states of connected clients?
   • [x] do the connected clients use SSL?
   • [x] how much time spent by sessions?
   • [x] how many sessions were established and terminated?
   • [x] how much memory is used by backend?

2. Query Planning

   • [x] what is planner?
   • [x] how long queries are planned?
   • [x] what is the ratio of planning time to executing time?
   • [x] how to get plan of the query?

3. Query Execution

   • [ ] what is query executor?
   • [ ] how many queries executed on the server, database, or from specific address?
   • [ ] what queries are executed right now?
   • [ ] is there any long queries or transactions on the server?
   • [ ] what is the duration of the running xacts and queries?
   • [ ] are there any blocked activity?
   • [ ] which transactions or queries are blocked/waiting and who is blocking them?
   • [ ] what the top of wait events occurs right now?
   • [ ] how much time my functions are executed?
   • [ ] how much time my queries are executed?
   • [ ] how many times my queries have been called?
   • [ ] how much time my queries are executed?
   • [ ] how much time my queries spent doing IO?
   • [ ] how many locks and what types of locks on the server?
   • [ ] how much time waiting queries are waiting?
   • [ ] how many of specific queries are executed now? how many SELECTS, UPDATES, and so on.
   • [ ] how many CREATE INDEX opearions are running and what its progress?
   • [ ] how many CLUSTER or VACUUM FULL opearions are running and what its progress?
   • [ ] how many COPY opearions are running and what its progress?

4. Tables Usage

   • [ ] what is tables?
   • [ ] how many tables in my database?
   • [ ] how my tables are accessed and how many rows retrieved?
   • [ ] what workload is on my tables?

5. Indexes Usage

   • [ ] what is the main purpose of indexes?
   • [ ] how many indexes is in my database or table?
   • [ ] how often my indexes are used?

6. Buffers IO

   • [ ] what is buffers IO?
   • [ ] how much often my tables and indexes are read from cache?
   • [ ] what cache hit ratio of my queries?
   • [ ] how much temporary IO is produced by queries?
   • [ ] how much local IO is produced by queries?

7. Shared Buffers

   • [ ] what is shared buffers?
   • [ ] how much of shared buffers are used?
   • [ ] what tables and indexes are in the shared buffers?
   • [ ] how much space allocated by internal structures in the shared buffers?

8. SLRU Caches

   • [ ] what is SLRU cache?

9. Server configuration

   • [ ] how Postgres server is configured?
   • [ ] how to list all configuration settings?
   • [ ] how to check for unapplied settings?
   • [ ] how to list all HBA rules?
   • [ ] how to change settings?
   • [ ] how to check when configuration was applied?

10. Postmaster

    • [ ] what is Postmaster?

11. Background Workers

    • [ ] what is background workers?
    • [ ] how many bg workers are running (for particular pid)?

12. Autovacuum Launcher

    • [ ] what is autovacuum launcher?
    • [ ] what about wraparound?

13. Autovacuum Workers

    • [ ] what is autovacuum workers?
    • [ ] how many (auto)vacuum workers are running?
    • [ ] how long vacuum workers are executed?
    • [ ] what the progress of vacuum?
    • [ ] what the progress of running analyze commands?
    • [ ] what tables have to be vacuumed or analyzed?
    • [ ] how far autovacuum had on table?

14. Write-Ahead Log

    • [ ] what is Write-Ahead Log?
    • [ ] how to calculate distance between two WAL locations?
    • [ ] how to understand current state of WAL?
    • [ ] how many WAL are generated by server?
    • [ ] how many FPW are performed by server?
    • [ ] how to count number of WAL segments and its size?
    • [ ] how to find the most fresh standby server?

15. Logger Process

    • [ ] what is logger process?
    • [ ] what the current logfile and where is it?
    • [ ] how much size log files take?

16. Stats Collector

    • [ ] what is stats collector?
    • [ ] why there is nothing about stats collector?

17. Logical Replication

    • [ ] what is logical replication?
    • [ ] how many replication slots are opened?
    • [ ] what the status of subscriptions?

18. WAL Sender Process

    • [ ] what is WAL sender process?
    • [ ] how many standbys are connected to server?
    • [ ] how far connected standbys are left behind of master?

19. WAL Archiver Process

    • [ ] what is WAL archiver?
    • [ ] what the status of WAL archiver?
    • [ ] how much WAL segments are not archived?

20. Background Writer Process

    • [ ] what is background writer?
    • [ ] how much amount of data written by bgwriter?
    • [ ] how much amount of data written by backends?
    • [ ] how often bgwriter has to forced to stop?

21. Checkpointer Process

    • [ ] what is checkpointer?
    • [ ] how much time are spent on write and sync phases?
    • [ ] how much amount of data written by checkpointer?

22. Network

    • [ ] how network is used in cluster topology?

23. WAL Receiver Process

    • [ ] what is wal receiver process?
    • [ ] how much data recevied by wal receiver?

24. Recovery Process

    • [ ] what is recovery process?
    • [ ] what the latest WAL location or transaction is replayed?
    • [ ] what is the state of recovery?

25. Storage

    • [ ] how storage is used by Postgres server?
    • [ ] where Postgres stores its data (WAL, logs, TS, etc)?

26. Tables and Indexes Data Files

    • [ ] what the size of my Postgres?
    • [ ] what the size of my databases?
    • [ ] what the size of my tables?
    • [ ] what the size of my indexes?
    • [ ] what the size of temporary files?