When optimizing application performance, monitoring every SQL statement executed against your PostgreSQL database is crucial. PostgreSQL offers several powerful logging mechanisms to capture query activity.

The most comprehensive approach involves modifying PostgreSQL's main configuration file. Here are the key parameters to set:

# Enable all statement logging
log_statement = 'all'

# Recommended additional parameters
log_min_duration_statement = 0
log_line_prefix = '%m [%p] %q%u@%d '
log_connections = on
log_disconnections = on
log_duration = on

For temporary monitoring without server restart:

-- Enable logging for current session
SET log_statement = 'all';
SET log_min_duration_statement = 0;

-- Verify settings
SHOW log_statement;

This extension provides powerful insights into query performance:

CREATE EXTENSION pg_stat_statements;

-- View top expensive queries
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

To capture execution plans of slow queries automatically:

# Add to postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_triggers = on
auto_explain.log_verbose = on

For production environments, consider these best practices:

• Rotate logs frequently using logrotate
• Use syslog for centralized logging
• Filter sensitive queries with log_statement_filter
• Analyze logs with pgBadger for visual insights

Here's how to interpret logged queries:

[2023-01-15 14:32:45 UTC] [12345] postgres@mydb LOG:  duration: 1250.123 ms  statement: SELECT * FROM large_table WHERE complex_condition = true;

This shows a query taking 1250ms - a candidate for optimization through indexing or query restructuring.

When optimizing database performance or debugging application behavior, tracking every SQL statement executed against your PostgreSQL database becomes crucial. This is particularly valuable for:

• Identifying slow-running queries
• Detecting N+1 query problems
• Analyzing application behavior during complex transactions
• Auditing database access patterns

The most effective way to log all SQL statements is through PostgreSQL's configuration file (postgresql.conf). Here are the key parameters to adjust:

# Enable general query logging
log_statement = 'all'  # Options: 'none', 'ddl', 'mod', 'all'

# Configure log output format
log_line_prefix = '%m [%p] %q%u@%d '  
log_destination = 'stderr'  # Or 'csvlog' for structured logs

# Optional: Log duration of each statement
log_duration = on

# Optional: Log query execution plan
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_min_duration = 0

For a more performance-friendly approach that doesn't log every statement to disk, consider using the pg_stat_statements extension:

-- Install the extension
CREATE EXTENSION pg_stat_statements;

-- Query the statistics
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

If you prefer a GUI approach, pgAdmin provides a dashboard for monitoring active queries:

1. Connect to your PostgreSQL server in pgAdmin
2. Navigate to Dashboard > Server Activity
3. View the "Active SQL" tab for currently executing queries

For debugging specific sessions without server-wide changes:

-- Enable logging for current session
SET log_statement = 'all';
SET client_min_messages = 'log';

Once you have logs, tools like pgBadger can help analyze them:

# Generate HTML report
pgbadger /var/log/postgresql/postgresql-*.log -o /path/to/report.html

The report will highlight frequently executed queries, slowest queries, and potential optimization opportunities.