PostgreSQL is considered to be the primary open-source database choice when migrating from commercial databases such as Oracle. AWS provides two managed PostgreSQL options: Amazon Relational Database Service (Amazon RDS) for PostgreSQL and Amazon Aurora PostgreSQL-Compatible Edition. Identifying slow queries and tuning for better performance is an important task for developers and database administrators managing Amazon RDS and Aurora PostgreSQL environments. To identify slow queries, multiple options are available. You can use the pgBadger tool to find the queries from the PostgreSQL log files by generating a report, or you can use Amazon RDS Performance Insights, which provides an easy-to-understand dashboard for detecting performance problems. For more information on finding slow queries, see Optimizing and tuning queries in Amazon RDS PostgreSQL based on native and external tools.

If database slowness is caused by SQL queries, you can generate query plans using the EXPLAIN command and optimize the SQLs accordingly. However, if the slowness is due to PL/pgSQL functions or procedures, the EXPLAIN plan doesn’t help much because it can’t generate plans for the SQLs inside the function or procedure. The SQL queries inside are optimized just like normal SQL queries, however separately and one by one. Or you can use the additional module auto_explain to get more details. Statements inside PL/pgSQL functions are considered nested statements, so you need to turn on the auto_explain.log_nested_statements parameter. For more details, refer to How can I log execution plans of queries for Amazon RDS PostgreSQL or Aurora PostgreSQL to tune query performance.

Generating a plan for SQLs (manually or through auto_explain) is a time-consuming process. To overcome this, you can use the plprofiler extension, which creates performance profiles of UDFs (user-defined PL/pgSQL functions) and stored procedures. You can use this extension to figure out the issue in the PL/pgSQL functions and procedures along with their runtimes. As part of profiling, we can generate HTML reports that contain details of the queries along with their respective runtimes.

The plprofiler tool is available in Amazon RDS for PostgreSQL and Aurora PostgreSQL (supported from version 11.6 and later).

In this post, we discuss how to install and configure the plprofiler extension. We also generate a report for a sample function to troubleshoot and find the queries causing the slowdown.

Solution overview

To use plprofiler, you need to load the extension on the backend (create the extension in the database) and install the plprofiler command line utility to generate reports.

Complete the following steps as part of installation:

1. Configure and enable plprofiler on Aurora PostgreSQL or Amazon RDS for PostgreSQL.
2. Install plprofiler on Amazon Elastic Compute Cloud (Amazon EC2) so that the command line utility can be invoked.

Prerequisites

To install and configure plprofiler, you need to complete the following steps as prerequisites:

1. Create an Aurora PostgreSQL cluster or RDS for PostgreSQL instance if you don’t already have one.
2. Create an EC2 instance (Ubuntu 16 or Amazon Linux) to install plprofiler and the PostgreSQL client to access the Aurora or RDS for PostgreSQL instance.
3. Install the PostgreSQL client.

To install PostgreSQL on Ubuntu, complete the following steps:

1. Create the file repository configuration

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

2. Import the repository signing key

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | 
  sudo apt-key add -

3. Update the package lists

4. Install the latest version of PostgreSQL. If you want a specific version, use postgresql-12 or similar instead of postgresql

sudo apt-get -y install postgresql-client-13

To install PostgreSQL on Amazon Linux, complete the following steps:

1. Check the available PostgreSQL and Python version and then enable the required versions

sudo amazon-linux-extras | egrep "postgres|python" 
sudo amazon-linux-extras enable python3.8 postgresql13

2. Update yum and then install PostgreSQL and Python

sudo yum update -y 
sudo yum install postgresql* python38* -y

Configure and enable plprofiler on Aurora PostgreSQL

Complete the following steps to configure and enable plprofiler on Aurora for PostgreSQL:

1. Set the shared_preload_libraries parameter to plprofiler in the instance parameter group, as shown in the following screenshot.

This parameter change requires an Aurora PostgreSQL instance reboot because this is a static parameter.

2. When the reboot is complete, connect to the database using pgadmin4 or psql client and create the plprofiler extension in the database.

psql -h endpoint -U dbuser -d dbname -c ”create extension plprofiler;”

Install plprofiler on an EC2 instance

After the plprofiler is enabled and configured on the database, we can proceed w