Having worked with PostgreSQL for years, I've compiled a list of indispensable tools that significantly improve database administration and performance tuning. These tools address common pain points like query optimization, monitoring, and maintenance.

The most widely used PostgreSQL administration tool is pgAdmin, which comes bundled with PostgreSQL installations. It provides:

-- Example query execution in pgAdmin
EXPLAIN ANALYZE 
SELECT * FROM large_table 
WHERE created_at > '2023-01-01';

This helps visualize query execution plans and identify performance bottlenecks.

PostgreSQL's built-in EXPLAIN command is invaluable for query optimization:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name
ORDER BY order_count DESC;

This shows execution time, memory usage, and join strategies.

pgBadger analyzes PostgreSQL log files to generate detailed reports about:

• Slowest queries
• Most frequent queries
• Connection statistics
• Error patterns

Configuration example for logging:

# postgresql.conf settings for pgBadger
log_min_duration_statement = 100
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on

This extension tracks execution statistics for all SQL statements:

-- Installation and usage
CREATE EXTENSION pg_stat_statements;

-- Top 10 most time-consuming queries
SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

For enterprise-level PostgreSQL administration:

• pgBackRest: Robust backup and restore utility
• Barman: Disaster recovery solution
• PoWA: PostgreSQL Workload Analyzer
• Patroni: HA (High Availability) solution

Schedule routine maintenance tasks directly in PostgreSQL:

-- Create extension
CREATE EXTENSION pg_cron;

-- Schedule daily vacuum
SELECT cron.schedule('nightly-vacuum', '0 3 * * *', 'VACUUM ANALYZE');

-- Weekly statistics update
SELECT cron.schedule('weekly-stats', '0 4 * * 0', 
   'ANALYZE VERBOSE; SELECT pg_stat_reset()');

-- Example: Checking active connections in pgAdmin SQL tool
SELECT pid, usename, application_name, client_addr 
FROM pg_stat_activity 
WHERE state = 'active';

log_min_duration_statement = 1000  # ms
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on

CREATE EXTENSION pg_stat_statements;

-- Top 5 most time-consuming queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

\dv -- List views
\di -- List indexes
\dt+ -- Tables with size information
\x auto -- Toggle expanded output

CREATE EXTENSION pg_cron;

-- Weekly vacuum job
SELECT cron.schedule('weekly-vacuum', '0 3 * * 0', 
$$VACUUM (VERBOSE, ANALYZE) ALL$$);

SELECT 
  client_addr,
  state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_lag,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_lag
FROM pg_stat_replication;