Outlier and long running queries

Long-running queries in PostgreSQL can have several negative implications for your database and application. Here are some reasons why long-running queries are generally considered undesirable:

  • Performance impact: Long-running queries tie up database resources, including CPU, memory, and disk I/O, for an extended period.

  • Increased contention: Long-running queries can lead to increased contention for shared resources, such as locks and concurrent access to database objects.

  • Reduced throughput: When a query takes a long time to complete, it can limit the number of queries that can be executed within a given timeframe.

  • Poor user experience: If your application relies on timely query execution, long-running queries can negatively impact user experience. Users may experience delays or unresponsiveness, leading to frustration and dissatisfaction with your application.

  • Resource exhaustion: Long-running queries can consume excessive memory, leading to increased memory usage and potential out-of-memory errors. They can also generate large temporary files on disk, potentially causing disk space issues.

Outlier queries

These are the queries with the highest proportional execution time. This may include very slow but relatively infrequent queries, as well as slightly slow but extremely common queries. The queries with the highest proportional execution time are the best starting point for database query tuning at the application level or indexing.

Long running queries

This shows a view of currently executing queries which have been running for longer than the specified duration (which defaults to 5 minutes). Long running queries can cause a variety of problems.

This view includes the ability to request termination of the query if it should not be running.

Sunday refresh

The Bridge system automatically runs a pg_stat_statements_reset each week at the beginning of Sunday UTC. You'll see updated information in the outlier queries, long running queries, and any manual queries you run against pg_stat_statements each week.

You can enable/disable the Sunday refresh in the cluster Settings -- General panel.

Setting a statement timeout

Postgres allows you to set a database timeout. You can set it at multiple levels:

  • Statement
  • User
  • Database

Setting a default statement timeout for your database is a good starting point. This ensures any application or person connecting to the database by default will not have queries running longer than that. A sane default is 30 or 60 seconds:

ALTER DATABASE mydatabase SET statement_timeout = ‘60s’;