This article focuses on several useful (primarily administrative queries) for PostgreSQL which may be helpful in debugging you database performance issues.
Show Running QueriesOften times it is useful to see which queries are running and how long they have been running from. The below is a sample query to find 'active' queries and information about them.
SELECT pid, client_addr, query_start, age(query_start, clock_timestamp()), usename, query, state, wait_event_type, -- Only available in >9.6 wait_event -- Only available in >9.6 FROM pg_stat_activity WHERE query != '
' AND query NOT ILIKE '%pg_stat_activity%' AND state = 'active' ORDER BY query_start desc;
- Particularly useful attribute is the calculated age column to see how long queries are running for.
- By default the query is trimmed to 1024 character. Don't be surprised if your query appears incomplete.
- The wait_event_type and wait_type columns are useful in highlighting why a query may be long running. The query may be blocked by another process or may be waiting on IO access.
- The username column can be useful in distinguishing between application queries and maintenance items.
- Note that the query in the query column is only "running" if the state is "active". Otherwise it is likely showing the last executed query and is 'idle'.
Note that the pg_stat_activity table has a lot of useful information in it, we are only selecting some columns in the above example. Feel free to explore. https://www.postgresql.org/docs/current/monitoring-stats.html provides details about all the columns and possible values.
Find Blocked Queries
select pid, client_addr,username, query_start, age(query_start, clock_timestamp()), pg_blocking_pids(pid) as blocked_by, query as blocked_query from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0 ORDER BY query_start desc
Get Maximum Connections
SHOW max connections;
-- Kill a running query SELECT pg_cancel_backend(procpid); -- Kill an idle query SELECT pg_terminate_backend(procpid);
Determine When Tables were Last Vacuumed and Analyzed
VACUUM is used to clean up dead tuples (rows) in PostgreSQL tables after DELETE and UPDATE operations are run. This process frees up storage space and improves performance, but it costs CPU resources to achieve. PostgresSQL instances created with RDS will have a setting autovacuum turned on by default, which will periodically schedule the vacuum operation. It is recommended to leave this on during most circumstances. Even with autovacuum on there still may be cases where you will want to manually run VACUUM, such as after a large batch processing has taken place and autovacuum has not yet kicked in.
ANALYZE is used to gather statistics about your PostgreSQL tables. These statistics can then be used to optimize queries as execution time or to provide useful summaries about the tables, such as estimated row count. autovacuum by default will also handle automatically running ANALYZE as well. Again, there may be reasons why you would want to run ANALYZE manually.
Since these are critical maintenance processes it is valuable to understand when they have been run last. The following query can be used to determine this information.
select relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;
Reclaim Space Without Locking Tables
Determining Live and Dead Tuples
SELECT relname, n_live_tup, n_dead_tup, trunc(100*n_dead_tup/(n_live_tup+1))::float "ratio", to_char(last_autovacuum, 'YYYY-MM-DD HH24:MI:SS') as autovacuum_date, to_char(last_autoanalyze, 'YYYY-MM-DD HH24:MI:SS') as autoanalyze_date FROM pg_stat_all_tables WHERE relname not like 'pg_%' ORDER BY ratio desc;
Determining Table Size
SELECT pg_size_pretty( pg_total_relation_size('table_name')); -- includes indexes SELECT pg_size_pretty( pg_table_size('table_name')); -- excludes indexes
Estimating The Number of Rows in a Table
SELECT relname, relkind, reltuples::bigint as estimated_rows, relpages FROM pg_class where relname = 'my_table';
Note the casting to bigint is done for readability.
Be sure to consult the pg_stat_user_tables table to determine WHEN analysis happened. See the section above on how to do that. If you need a more accurate statistic you could always manually execute an ANALYSIS against the table.