postgresqlgoogle-cloud-sql

How to configure pg_trgm similarity threshold parameters in a Google Cloud SQL PostgreSQL instance

I have a Postgres database in Cloud SQL and would like to utilize the pg_trgm extension to do similarity searches. However, the default threshold parameters are not sensitive enough in my use case so they should be altered.

The problem is that Cloud SQL does not provide database flags for either pg_trgm.similarity_threshold or pg_trgm.word_similarity_threshold and the database users do not have the privileges to alter the user or database:

postgres=> ALTER ROLE postgres SET pg_trgm.similarity_threshold = 0.1;
ERROR:  permission denied to set parameter "pg_trgm.similarity_threshold"

postgres=> ALTER DATABASE postgres SET pg_trgm.similarity_threshold = 0.1;
ERROR:  permission denied to set parameter "pg_trgm.similarity_threshold"

Is there any other way to alter these parameters?

Solution

  • Can you do the straightforward SET pg_trgm.similarity_threshold = 0.1;? You would have to repeat it in every session, so maybe incorporate it into your connection sub.

    For similarity_threshold, you can use the function set_limit unless Google has also disabled that. You would also have to set it this way in each session, though. There is no alternative way to set word_similarity_threshold, though, unless Google has invented one.