Google Cloud SQL for PostgreSQL `work_mem`

Question

there. I want to tune Google Cloud SQL for PostgreSQL instance. Currently, I'm trying to eliminate sorting speed degradation:

Sort Method: external merge Disk: 39592kB

Right now work_mem is set to 4MB, and it seems that is too small. After reading docs, I didn't find the way how to change this setting. It's impossible via Web GUI and via command line:

$ gcloud sql instances patch reporting-dev --database-flags work_mem=128MB
The following message will be used for the patch API method.
{"project": "xxx-153410", "name": "reporting-dev", "settings": {"databaseFlags": [{"name": "work_mem", "value": "128MB"}]}}
WARNING: This patch modifies a value that requires your instance to be
 restarted. Submitting this patch will immediately restart your
instance if it's running.

Do you want to continue (Y/n)?  Y

ERROR: (gcloud.sql.instances.patch) HTTPError 404: Flag requested cannot be set.

Any thoughts on that?

According to this post you can set it in your session but that's it. — bma

Scott Marlowe Scott Marlowe · Accepted Answer · 2018-01-09T20:50:43

You can change it by user or by database.

alter database db1 set work_mem='64MB';
alter user stan set work_mem='32MB';

User overides db, db overrides postgresql.conf / cluster settings. Both override alter system set ... which you might not be able to use due to security settings.

Google Cloud SQL for PostgreSQL `work_mem`

1 Answers