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
-
- what is client backend?
- how many clients connected to the server?
- how many clients connected to the database?
- how many clients connected remotely?
- what states of connected clients?
- do the connected clients use SSL?
- how much time spent by sessions?
- how many sessions were established and terminated?
- how much memory is used by backend?
-
- what is planner?
- how long queries are planned?
- what is the ratio of planning time to executing time?
- how to get plan of the query?
-
- 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?
-
- 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?
-
- what is the main purpose of indexes?
- how many indexes is in my database or table?
- how often my indexes are used?
-
- 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?
-
- 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?
-
- what is SLRU cache?
-
- 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?
-
- what is Postmaster?
-
- what is background workers?
- how many bg workers are running (for particular pid)?
-
- what is autovacuum launcher?
- what about wraparound?
-
- 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?
-
- 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?
-
- what is logger process?
- what the current logfile and where is it?
- how much size log files take?
-
- what is stats collector?
- why there is nothing about stats collector?
-
- what is logical replication?
- how many replication slots are opened?
- what the status of subscriptions?
-
- what is WAL sender process?
- how many standbys are connected to server?
- how far connected standbys are left behind of master?
-
- what is WAL archiver?
- what the status of WAL archiver?
- how much WAL segments are not archived?
-
- 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?
-
- what is checkpointer?
- how much time are spent on write and sync phases?
- how much amount of data written by checkpointer?
-
- how network is used in cluster topology?
-
- what is wal receiver process?
- how much data recevied by wal receiver?
-
- what is recovery process?
- what the latest WAL location or transaction is replayed?
- what is the state of recovery?
-
- how storage is used by Postgres server?
- where Postgres stores its data (WAL, logs, TS, etc)?
-
- 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?