At the top of Navicat Monitor 3's Query Analyzer screen, there's a chart that shows queries with the longest wait times:
It's essential to identify laggard queries because they can bring everything crashing to a crawl.
Besides fixing a slow query once it's been identified, another strategy might include limiting query execution times across the board. In professional grade databases such as PostgreSQL, there are settings to cap query execution time for the entire database or even per user, via the statement_timeout variable. In this blog, we'll learn how to work with this important database variable in Navicat 16 For PostgreSQL.
Setting the statement_timeout Variable at the Database Level
Setting a default statement timeout for your database is an excellent starting point. This ensures that any application or person connecting to the database will not have queries running longer than that. A sane default would be either 30 or 60 seconds, but you can go higher if you wish. Here a statement that sets a value of 60 seconds:
ALTER DATABASE mydatabase SET statement_timeout = '60s';
In Navicat 16 For PostgreSQL we can view the statement_timeout via Tools > Server Monitor > PostgreSQL from the main menu. You'll find it on the Variables tab:
In fact, you may want to employ the Find tool in order to pinpoint the statement_timeout variable, as there are many! You can click the Highlight All toggle button to better help identify the variable once matched.
Of course the Show statement works as well:
Setting a Query Timeout for a Specific User
For even more fine grained control, we can set a query timeout value for a specific user (you know, the one who always selects the entire database!). This is achieved using the ALTER ROLE statement, which can set many database variables, including statement_timeout.
To try it out, let's create a new user role called "guest":
Now we can use the ALTER ROLE statement to limit query execution time as follows:
ALTER ROLE guest SET statement_timeout='5min';
We can query the pg_roles table to obtain information about the statement_timeout (including how it was set):
The rolconfig value is an array, so we can unnest it to get one setting per row:
Final Thoughts on Setting Query Timeouts in PostgreSQL
It's crucial to be able to identify laggard queries because they can bring your database performance down to a crawl. For that, there's Navicat Monitor 3's Long Running Queries chart at the top of the Query Analyzer screen.
Another approach is to limit how long a query can execute before it times out. As we saw in today's blog, in PostgreSQL, this can be done at the database, session, and even at the individual role level.
If you haven't already setup your statement_timeout variable(s), I would encourage you to do so ASAP. This is just one component of proper database tuning that will help to ensure your database instance stays healthy and available.
Interested in giving Navicat 16 For PostgreSQL a try? You can download the fully functioning application here to get a free 14 day trial!
