Comprehensive Guide to Query History and Performance Analysis in PostgreSQL

Methods for Obtaining Query History in PostgreSQL

In PostgreSQL database management, acquiring query history is crucial for performance analysis and problem diagnosis. While PostgreSQL does not maintain complete query history within the database itself, it provides multiple mechanisms to record and monitor SQL statement execution.

Logging Configuration Approach

By configuring PostgreSQL's logging parameters, comprehensive recording of SQL queries can be achieved. In the postgresql.conf configuration file, the log_statement parameter controls the statement logging level. When set to 'all', the system records all executed SQL statements. For performance analysis scenarios, the log_min_duration_statement parameter is more practical - it sets a time threshold (in milliseconds), and only queries exceeding this threshold are logged. Setting it to 0 will log all queries along with their execution times.

Configuration example:

log_statement = 'all'
log_min_duration_statement = 0
logging_collector = on
log_destination = 'csvlog'

After configuration, restart the PostgreSQL service for the settings to take effect. Using csvlog format facilitates subsequent log content queries through foreign tables.

psql Tool Usage Techniques

The psql command-line tool provides convenient query timing functionality. By executing the \timing command, statement execution time display can be enabled. Thereafter, each executed SQL statement will show its execution duration.

Usage example:

\timing
SELECT * FROM users WHERE id = 1;
-- Output includes execution time information

This method is particularly suitable for quickly testing query performance in development environments, requiring no database superuser privileges and offering simple operation.

System View Queries

PostgreSQL provides the pg_stat_activity system view for viewing currently executing query information. However, it's important to note that this view only displays currently active queries and does not provide historical records.

Query example:

SELECT query, state, query_start 
FROM pg_stat_activity 
WHERE datname = 'your_database_name';

pg_stat_statements Extension

For more advanced query monitoring requirements, the pg_stat_statements extension can be installed. This extension can statistics all executed SQL statements, including execution count, total execution time, average execution time, and other detailed information.

Installation and configuration steps:

-- Add to postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

-- Create extension after restart
CREATE EXTENSION pg_stat_statements;

-- Query statistical information
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC;

Performance Analysis and Optimization

After obtaining slow query information through the above methods, the EXPLAIN ANALYZE command can be used to deeply analyze query execution plans and identify performance bottlenecks. Combined with techniques like index optimization and query rewriting, database performance can be effectively improved.

In actual production environments, it's recommended to set log_min_duration_statement to an appropriate threshold (such as 100ms) to avoid excessively large log files while effectively capturing performance issues.