Using logging parameters to capture bind variables - AWS Prescriptive Guidance

Using logging parameters to capture bind variables

A typical use case for capturing bind variables in PostgreSQL is to debug and performance-tune SQL queries. A bind variable lets you pass data to a query when you run it. By capturing the bind variables, you can see the input data that was passed to a query, which can help you identify any issues with the data or with query performance. Capturing the bind variables can also help you audit the input data and detect potential security risks or malicious activity.

There are a several ways to capture bind variables for PostgreSQL. One method is to enable the debug_print_parse and debug_print_rewritten parameters. This causes PostgreSQL to send the parsed and rewritten versions of SQL statements, along with the bound variables, to the server log.

  • debug_print_parse: When you enable this parameter, the parse tree of incoming queries is printed to the server log. This can be useful for understanding the structure of a query and the values of any bound parameters.

  • debug_print_rewritten: When you enable this parameter, the rewritten forms of incoming queries are printed to the server log. This can be useful for understanding how the query planner interprets a query and the values of any bound parameters.

You can use two additional parameters in Amazon RDS and Aurora to capture bind variables in your PostgreSQL databases:

  • log_min_duration_statement: This parameter sets the minimum duration of a statement before it is logged, in milliseconds. When a statement takes longer than the specified duration, its bind values are included in the log output.

  • log_statement: This parameter controls which SQL statements are logged. Set this parameter to all or bind to include the bound values in the log. Increasing the logging level affects performance, so we recommend that you revert changes after troubleshooting.

You can also use the pg_stat_statements extension, which provides performance statistics for all SQL statements run by a server, including the query text and the bound values. This extension allows you to use pgAdmin or similar tools to monitor and analyze query performance.

Another option is to use the pg_bind_parameter_status() function to get the values of bound parameters from a prepared statement or to use the pg_get_parameter_status (paramname) function to retrieve the status or value of a specific runtime parameter.

Additionally, you can use third-party tools such as pgBadger to analyze the PostgreSQL logs and extract the bind variables and other information for further analysis.