Troubleshooting Render Postgres Performance

You might observe performance issues with your database as it grows in size, query complexity, and connection count. If you do, use these tips to help diagnose and resolve the underlying cause.

Identify long-running queries

Long-running queries can degrade performance by monopolizing shared compute resources and locks required by other operations. Some of these might be "runaway" queries (such as a SELECT * against a very large table without a LIMIT), while others might require more nuanced optimization.

Run the following query on your instance to fetch all of its actively running processes, ordered by age (oldest first):

Again, this query shows only active processes. To show all processes, remove the state = 'active' condition. Inactive processes include processes that are awaiting a new command from a user, either inside or outside of a transaction.

The pg_stat_activity view queried above contains several other columns, but here we're fetching the data that provides the most utility for common troubleshooting scenarios:

Example output

The following example output lists client backend processes where state = active (query strings are omitted for brevity):

Show example output

Out of the 23 listed clients:

• 9 are currently processing on the CPU (wait_event_type is NULL).
• 11 are waiting for a lock.
• 2 are waiting on the buffer manager paging in data to shared_buffers.
• 1 is feeding data back to the client.

An active query with a specified wait_event_type might be in one of several states, most commonly:

• Client, indicating that the query is expecting data to be read from or written to the client connection
• IO, indicating a blocking access to disk
• IPC, indicating communication between parallel processes
• Lock or LWLock, indicating that a table or row lock required by this query is currently held

Identify active queries that are blocking other queries

Concurrent queries running on the same tables or rows can sometimes cause lock contention. If your queries are taking longer than expected to complete, run the following to fetch all queries that are currently blocked on another query:

Example output

The following example output lists three currently blocked queries (query strings are omitted for brevity):

For each row, the blocking_ columns show details about a query that's currently holding a lock, and the blocked_ columns show details about a query that's waiting on that lock.

In this result set, a query issued by user@render.com from a psql shell is blocking two queries running from an app called sample. Additionally, one of the blocked queries running from sample is blocking the other, forming a queue.

If you notice that one particular backend process is blocking many others, you can terminate it with pg_terminate_backend as shown here. We recommend using this strategy only in emergencies.

Examine query plans

If a particular query is taking longer than expected to run, you can use the EXPLAIN command to understand how PostgreSQL is evaluating that query:

EXPLAIN returns a query plan, which is a text representation of the algorithm PostgreSQL will follow to execute the query. Details of a query plan include:

• Which tables to gather data from
• Which join order and strategy to use to combine data from multiple tables
• At which point result sets are filtered by a condition or sorted for output

In addition to running EXPLAIN, you can also run EXPLAIN ANALYZE to sample the actual execution time for each step in the query plan. Examples of both are provided below.

Query plans for the same query can differ significantly between environments.

This is because PostgreSQL takes your data's size and distribution into account when selecting a query plan that maximizes its efficiency. For the best results, run EXPLAIN in the same environment where you're experiencing performance issues.

The sections below use the following example query, executed in Render's own staging environment:

Example EXPLAIN output

Running EXPLAIN <query> with the example query above yields the following query plan:

Each line in the query plan with a listed cost represents a step in the execution process (other lines provide additional context for these steps). Steps with the largest indent level are executed first.

Based on this query plan, PostgreSQL performs the following steps in order:

1. Bitmap Index Scan: PostgreSQL first reads from the postgres_dbs_owner_id_name index (a partial index) to mark candidate pages (groups of rows) that need to be read.
2. Bitmap Heap Scan: PostgreSQL goes through each of the marked pages and returns the rows that match the Recheck Cond. This is necessary because each candidate page contains rows that belong to the index, but might also contain rows that don't (which require a post-filter).
3. Sort: PostgreSQL sorts the results from the heap scan by the created_at column in descending order.
4. Limit: PostgreSQL reads the first 200 rows from the sorted result set. The remainder of the rows are discarded.

Example EXPLAIN ANALYZE output

Because EXPLAIN ANALYZE actually executes its query, any side effects of that query also occur.

Avoid running EXPLAIN ANALYZE for INSERT, UPDATE, or DELETE queries on a production database.

Running EXPLAIN ANALYZE <query> with the example query above yields the same query plan and also executes that plan to gather exact timing and row details:

This output resembles the EXPLAIN output above, but it includes additional details:

• The actual time for each step of the query execution
• The actual number of rows returned from each step
• The total Execution Time for the query

Note that cost remains an estimate (use this value to compare the efficiency of two query plans).

Case studies

To see how these queries can be used in a real debugging scenario, read our case studies. We'll be adding to this list over time:

• A simple query with a big problem
• From slow query to fast—via stats
• Taking random samples from big tables