PostgreSQL - Performance Tuning(Riyaz Alam)

PostgreSQL Query Performance Tuning Guide

Introduction

This document provides a practical guide for PostgreSQL query performance tuning, monitoring database activity, identifying bottlenecks, and maintaining database health. The queries included here help database administrators and developers quickly diagnose performance issues and take corrective actions.

Performance Tuning Approach

1. Identify the top queries consuming resources.

2. Check for missing indexes.

3. Optimize queries to perform more work efficiently.

4. Monitor sessions, wait events, and dead tuples.

5. Maintain tables using VACUUM and ANALYZE.

1. Top Active Queries with Client IP, Wait Events, and Duration

SELECT

  usename,

  datname,

  pid,

  client_addr,

  state,

  wait_event_type,

  wait_event,

  now() - query_start AS age,

  query

FROM pg_stat_activity

WHERE state <> 'idle'

  AND query NOT ILIKE '%pg_stat_activity%'

ORDER BY age DESC

LIMIT 10;

2. Top Active Queries (Simplified View)

SELECT

  query,

  state,

  pid, 

  age(clock_timestamp(), query_start) AS age

FROM pg_stat_activity

WHERE state <> 'idle'

  AND query NOT LIKE '% FROM pg_stat_activity %'

ORDER BY age DESC

LIMIT 10;

3. Find Idle in Transaction Sessions

SELECT

    pid,

    usename,

    application_name,

    client_addr,

    state,

    query,

    state_change,

    now() - state_change AS idle_duration

FROM pg_stat_activity

WHERE state = 'idle in transaction'

  AND now() - state_change > interval '5 minutes'

ORDER BY idle_duration DESC;

Check PostgreSQL Activity for Specific Operations

SELECT pid, state, query

FROM pg_stat_activity

WHERE query ILIKE '%repack%';

Check Idle Sessions

SELECT

  query,

  state,

  pid, 

  age(clock_timestamp(), query_start) AS age

FROM pg_stat_activity

WHERE state = 'idle'

  AND query NOT LIKE '%FROM pg_stat_activity%';

Terminate Idle Sessions

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE state = 'idle'

  AND pid <> pg_backend_pid()

  AND usename <> 'postgres';

4. Validate Dead Tuples

SELECT

  schemaname,

  relname,

  last_autoanalyze,

  last_autovacuum,

  last_vacuum,

  last_analyze,

  n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 20;

Generate VACUUM Commands

SELECT

  'VACUUM ANALYZE ' || schemaname || '.' || relname || ';'

FROM pg_stat_all_tables

WHERE n_dead_tup > 0

ORDER BY n_dead_tup DESC

LIMIT 20;

5. Check Number of Sessions by State

SELECT count(*), state

FROM pg_stat_activity

GROUP BY state;

Active Sessions by User, Application, and IP

SELECT

    usename AS username,

    client_addr AS client_ip,

    application_name,

    COUNT(*) AS session_count

FROM pg_stat_activity

WHERE state = 'active'

GROUP BY usename, client_addr, application_name

ORDER BY session_count DESC;

Terminate Long Running Sessions (Example: > 3 Minutes)

SELECT pg_terminate_backend(pid)

FROM pg_stat_activity

WHERE (state = 'active' AND now() - query_start > interval '3 minutes')

   OR state = 'idle in transaction'

   OR state = 'idle';

6. Check Wait Events

SELECT

  count(*),

  usename,

  wait_event_type,

  wait_event

FROM pg_stat_activity

GROUP BY usename, wait_event_type, wait_event

ORDER BY 1;

7. Take Explain Plan for Long Running Queries

Use EXPLAIN ANALYZE only for SELECT queries. Avoid using it for INSERT, UPDATE, or DELETE in production.

 

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

SELECT * FROM your_table;

8. Identify Missing Indexes (Magic Query)

SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan,

       seq_tup_read / seq_scan AS avg_rows_per_scan

FROM pg_catalog.pg_stat_user_tables

WHERE seq_scan > 0

ORDER BY seq_tup_read DESC;

9. Tables with Heavy DML Activity (Write IOPS)

SELECT

  relname,

  seq_scan,

  n_live_tup,

  n_tup_ins,

  n_tup_upd,

  n_tup_hot_upd,

  n_tup_del,

  last_vacuum,

  last_autovacuum,

  last_analyze,

  pg_relation_size(relid) AS size_bytes,

  pg_size_pretty(pg_relation_size(relid)) AS size_readable

FROM pg_stat_all_tables

ORDER BY n_tup_del DESC;

10. Aggressive Autovacuum Tuning Example

ALTER TABLE schema_name.table_name SET (autovacuum_vacuum_scale_factor = 0.0);

ALTER TABLE schema_name.table_name SET (autovacuum_vacuum_threshold = 10000);

ALTER TABLE schema_name.table_name SET (autovacuum_analyze_scale_factor = 0.0);

ALTER TABLE schema_name.table_name SET (autovacuum_analyze_threshold = 10000);

Find Tables Needing Aggressive Vacuum

SELECT schemaname, relname,

last_vacuum, last_analyze, vacuum_count, analyze_count,

last_autoanalyze, last_autovacuum, autovacuum_count, autoanalyze_count,

n_dead_tup

FROM pg_stat_all_tables

WHERE n_dead_tup > 10000

ORDER BY n_dead_tup DESC

LIMIT 50;

11. Check Last Analyze Time for a Table

SELECT relname, last_analyze

FROM pg_stat_all_tables

WHERE schemaname = 'your_schema'

  AND relname = 'your_table';

Conclusion

This guide provides essential queries for PostgreSQL performance tuning, monitoring sessions, identifying missing indexes, and maintaining database health. Regular monitoring combined with proper indexing and vacuum strategy ensures optimal database performance.