Useful PostgreSQL Queries

I have been actively using PostgreSQL for the last 7 years. As with many databases engines, PostgreSQL provides us with many capabilities when it comes to helping developers identify slow performing queries.

This article focuses on several useful (primarily administrative queries) for PostgreSQL which may be helpful in debugging you database performance issues.
Queries

Show Running Queries

Often 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

Query is similar to just looking at running queries but specifically looks for queries which are blocked and reports the pid of the query which blocks them.

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

This shows the maximum number of connections PostgreSQL will allow. Note that this is the number allowed by DB server, not your application. You need to configure you DB Driver with the number of connections you want to the application to consume. AWS RDS as an example by default sets max_connections based on the RDS Instance Type you choose. If you wish to increase it, do so using an RDS Parameter Group.

SHOW max connections;

Killing Queries

You can kill running queries by their pid. Get this from the pg_stat_activity table.

-- 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 

VACUUM FULL physically deletes dead tuples, reducing table sizes to the actual space used. However, it also locks the table, potentially causing an outage. 

The extension pg_repack is supported by AWS RDS, and performs the same function as VACUUM FULL without a table lock.

Determining Live and Dead Tuples

Following query can be run to show you a ratio of live and dead tuples for all tables. 

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

Following query can be run to show you size information on a given table. 

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

Most of us know that select count(*) from my_table is a useful way to get the number of rows. However this query can be quite slow as PostgreSQL needs to determine the visibility of each row. As an alternative we can consult the table statistics collected during ANALYZE to get an estimate of the number of rows. This result should only be seen as an estimate. 

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.