Styjun

The company I work for uses Google Cloud SQL to manage their SQL databases in production.

We're having performance issues and I thought it'd be a good idea (among other things) to see/monitor all queries above a specific threshold (e.g. 250ms).

By looking at the PostgreSQL documentation I think log_min_duration_statement seems like the flag I need.

log_min_duration_statement (integer)

Causes the duration of each completed statement to be logged if the statement ran for at least the specified number of milliseconds. Setting this to zero prints all statement durations.

But judging from the Cloud SQL documentation I see that is only possible to set a narrow set of database flags (as in for each DB instance) but as you can see from here log_min_duration_statement is not among those supported flags.

So here comes the question. How do I log/monitor my slow PostgreSQL queries with Google Cloud SQL? If not possible then what kind of tool/methodologies do you suggest I use to achieve a similar result?

The possibility of monitoring slow PostgreSQL queries for Cloud SQL instances is currently not available. As you comment, the log_min_duration_statement flag is currently not supported by Cloud SQL.

Right now, work is being made on adding this feature to Cloud SQL, and you can keep track on the progress made through this link. You can click on the star icon on the top left corner to get email notifications whenever any significant progress has been achieved.

Not ideal by any measure, but what we do is run something like this on a cron once a minute and log out the result:

SELECT EXTRACT(EPOCH FROM now() - query_start) AS seconds, query
 FROM pg_stat_activity 
 WHERE state = 'active' AND now() - query_start > interval '1 seconds' AND query NOT LIKE '%pg_stat_activity%'
 ORDER BY seconds DESC LIMIT 20

You'd need to fiddle with the query to get millisecond granularity, and even then it'll only catch queries that overlap with your cron frequency, but probably better than nothing?

There is a way to log slow queries through the pg_stat_statements extension which is supported by Cloud SQL.

Since Cloud SQL doesn't grant superuser right to any of the users you need to use some workaround.
First, you need to enable the extension with

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

then you can check slow queries with a query like

SELECT pd.datname,
 us.usename,
 pss.userid,
 pss.query AS SQLQuery,
 pss.rows AS TotalRowCount,
 (pss.total_time / 1000) AS TotalSecond,
 ((pss.total_time / 1000) / calls) as TotalAverageSecond
FROM pg_stat_statements AS pss
 INNER JOIN pg_database AS pd
 ON pss.dbid = pd.oid
 INNER JOIN pg_user AS us
 ON pss.userid = us.usesysid
ORDER BY TotalAverageSecond DESC
LIMIT 10;

As postgres user you can have a look on all slow queries, but since the user is not superuser you will see <insufficient privilege> on all other users' queries.
To get around this limitation you can install the extension on other databases too (normally only postgres user has rigths to install extensions) and you can check the query texts with the owner of the db.