Configuring database flags

This page describes how to configure database flags for Cloud SQL, and lists which flags you can set for your instance. You use database flags to adjust PostgreSQL parameters and options, to configure and tune your instance.

In some cases, setting one flag may require that you set another flag to fully enable the desired functionality.

When you set, remove, or modify a flag for an instance, the instance might be restarted. The flag value is then persisted for the instance until you remove it.

Configuring database flags

Setting a database flag

Console

  1. In the Google Cloud Platform Console, create a new GCP Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Scroll down to the Flags section.
  4. To set a flag that has not been set on the instance before, click Add item, choose the flag from the drop-down menu, and set its value.
  5. Click Save to save your changes.
  6. Confirm your changes under Flags on the Overview page.

gcloud

Edit the instance:

gcloud sql instances patch [INSTANCE_NAME] --database-flags [FLAG1=VALUE1,FLAG2=VALUE2]

For flags that do not take a value, specify the flag name followed by an equals sign ("="). Include the values for all flags you want set on the instance; any flag not included is set back to its default value.

cURL

To set a flag for an existing instance:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"settings" : {"databaseFlags" :
         [{ "name": "<FLAG-NAME>",  "value": "<FLAG-VALUE>" }]}}' \
     -X PATCH \
     https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>

If there are existing flags configured for the instance, modify the previous command to include them. The PATCH command overwrites the existing flags with the ones specified in the request.

Clearing all flags to their default value

Console

  1. In the Google Cloud Platform Console, create a new GCP Console project, or open an existing project by selecting the project name.
  2. Open the instance and click Edit.
  3. Open the Database flags section.
  4. Click the X next to all of the flags shown.
  5. Click Save to save your changes.

gcloud

Clear all flags to their default values on an instance:

gcloud sql instances patch [INSTANCE_NAME] --clear-database-flags

You are prompted to confirm that the instance will be restarted.

cURL

To clear all flags for an existing instance:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 

       --header 'Content-Type: application/json' 

       --data '{"settings" : {"databaseFlags" : []}}' 

       -X PATCH 

       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>

Viewing current values of database flags

To view all current values of the PostgreSQL settings, log into your instance with the psql client and enter the following statement:

 SELECT name, setting FROM pg_settings;

Note that you can change the value only for supported flags (as listed below).

Determining what database flags have been set for an instance

To see what flags have been set for a Cloud SQL instance:

Console

  1. In the Google Cloud Platform Console, create a new GCP Console project, or open an existing project by selecting the project name.
  2. Select the instance to open its Instance Overview page.

    The database flags that have been set are listed under the Database flags section.

gcloud

Get the instance state:

gcloud sql instances describe [INSTANCE_NAME]

In the output, database flags are listed under the settings as the collection databaseFlags. For more information about the representation of the flags in the output, see Instances Resource Representation.

cURL

To list flags configured for an instance:

gcloud auth application-default login
ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" 

       --header 'Content-Type: application/json' 

       -X GET 

       https://www.googleapis.com/sql/v1beta4/projects/<PROJECT-ID>/instances/<INSTANCE_NAME>?fields=settings

In the output, look for the databaseFlags field.

Supported flags

For a given flag, Cloud SQL might support a different range than the corresponding PostgreSQL parameter or option.

A | C | D | E | F | G | H | I | J | L | M | O | P | R | S | T | V | W

Cloud SQL Flag Type
Acceptable Values and Notes		 Restart
Required?		 In Beta?
autovacuum boolean
on | off		 No No
autovacuum_analyze_scale_factor float
0 ... 100		 No No
autovacuum_analyze_threshold integer
0 ... 2147483647		 No No
autovacuum_freeze_max_age integer
100000 ... 2000000000		 Yes Yes
autovacuum_max_workers integer
1 ... 262143		 Yes No
autovacuum_multixact_freeze_max_age integer
10000 ... 2000000000		 Yes Yes
autovacuum_naptime integer
1 ... 2147483 s		 No No
autovacuum_vacuum_cost_delay integer
0 ... 100 ms, or -1 to use the vacuum_cost_delay value		 No No
autovacuum_vacuum_cost_limit integer
0 ... 10000, or -1 to use the vacuum_cost_limit value		 No No
autovacuum_vacuum_scale_factor float
0 ... 100		 No No
autovacuum_vacuum_threshold integer
0 ... 2147483647		 No No
autovacuum_work_mem integer
0 ... 2147483647 KB, or -1 to use maintenance_work_mem value		 No Yes
checkpoint_completion_target float
0.0 ... 1.0		 No No
checkpoint_timeout integer
30 ... 86400 s		 No Yes
checkpoint_warning integer
0 ... 2147483647 s		 No No
commit_delay integer
0 ... 100000		 No No
commit_siblings integer
0 ... 1000		 No No
constraint_exclusion enumeration
partition | on | off		 No No
cpu_index_tuple_cost float
0.0 ... inf		 No No
cpu_operator_cost float
0.0 ... inf		 No No
cpu_tuple_cost float
0.0 ... inf		 No No
cursor_tuple_fraction float
0.0 ... 1.0		 No No
deadlock_timeout integer
1 ... 2147483647 ms		 No No
default_statistics_target integer
1 ... 10000		 No No
default_tablespace string No No
default_transaction_deferrable boolean
on | off		 No No
default_transaction_isolation enumeration
serializable | 'repeatable read' | 'read committed' | 'read uncommitted'		 No No
enable_bitmapscan boolean
on | off		 No No
enable_hashagg boolean
on | off		 No No
enable_hashjoin boolean
on | off		 No No
enable_indexonlyscan boolean
on | off		 No No
enable_indexscan boolean
on | off		 No No
enable_material boolean
on | off		 No No
enable_mergejoin boolean
on | off		 No No
enable_nestloop boolean
on | off		 No No
enable_seqscan boolean
on | off		 No No
enable_sort boolean
on | off		 No No
enable_tidscan boolean
on | off		 No No
force_parallel_mode enumeration
off | on | regress		 No No
from_collapse_limit integer
1 ... 2147483647		 No No
geqo boolean
on | off		 No No
geqo_effort integer
1 ... 10		 No No
geqo_generations integer
0 ... 2147483647		 No No
geqo_pool_size integer
0 ... 2147483647		 No No
geqo_seed float
0.0 ... 1.0		 No No
geqo_selection_bias float
1.5 ... 2.0		 No No
geqo_threshold integer
2 ... 2147483647		 No No
gin_fuzzy_search_limit integer
0 ... 2147483647		 No No
gin_pending_list_limit integer
64 ... 2147483647 KB		 No No
hot_standby_feedback boolean
on | off		 No No
idle_in_transaction_session_timeout integer
0 ... 2147483647 ms		 No No
join_collapse_limit integer
1 ... 2147483647		 No No
lock_timeout integer
0 ... 2147483647 ms		 No No
log_autovacuum_min_duration integer
0 ... 2147483647 ms, or -1 to disable		 No No
log_checkpoints boolean
on | off		 No No
log_connections boolean
on | off		 No No
log_disconnections boolean
on | off		 No No
log_duration boolean
on | off		 No No
log_error_verbosity enumeration
terse | default | verbose		 No No
log_executor_stats boolean
on | off		 No No
log_hostname boolean
on | off		 No No
log_lock_waits boolean
on | off		 No No
log_min_duration_statement integer
-1 ... 2147483647 ms		 No No
log_min_error_statement enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | info | notice | warning | error | log | fatal | panic		 No No
log_min_messages enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | info | notice | warning | error | log | fatal | panic		 No No
log_parser_stats boolean
on | off		 No No
log_planner_stats boolean
on | off		 No No
log_replication_commands boolean
on | off		 No No
log_statement enumeration
none | ddl | mod | all		 No No
log_statement_stats boolean
May not be enabled together with log_parser_stats, log_planner_stats, or log_executor_stats.		 No No
log_temp_files integer
0 ... 2147483647 KB, or -1 to disable		 No No
maintenance_work_mem integer
1024 ... 2147483647 KB		 No Yes
max_connections integer
14 ... 262143

The value on replicas must be >= the value on the primary. Changes on the primary propagate to replicas that have a value that is lower than the new value on the primary, or that have not been changed from the default value.

Yes Yes
max_locks_per_transaction integer
10 ... 2147483647

The value on replicas must be >= the value on the primary. Changes on the primary propagate to replicas that have a value that is lower than the new value on the primary, or that have not been changed from the default value.

Yes Yes
max_prepared_transactions integer
0 ... 262143

The value on replicas must be >= the value on the primary. Changes on the primary propagate to replicas that have a value that is lower than the new value on the primary, or that have not been changed from the default value.

Yes Yes
max_standby_archive_delay integer
0 ... 2147483647 ms, or -1 to wait forever		 No No
max_standby_streaming_delay integer
0 ... 2147483647 ms, or -1 to wait forever		 No No
max_wal_size integer
2 ... 2147483647

Unit is 16 MB for Postgres 9.6; unit default size is 1 MB for Postgres 11.1

 No Yes
min_parallel_relation_size integer
0 ... 715827882
Unit is 8 KB		 No No
old_snapshot_threshold integer
0 ... 86400 min, or -1 to disable		 Yes No
parallel_setup_cost float
0.0 ... inf		 No No
parallel_tuple_cost float
0.0 ... inf		 No No
pg_stat_statements.max integer
100 ... 2147483647		 Yes No
pg_stat_statements.save boolean
on | off		 No No
pg_stat_statements.track enumeration
none | top | all		 No No
pg_stat_statements.track_utility boolean
on | off		 No No
random_page_cost float
0.0 ... inf		 No No
replacement_sort_tuples integer
0 ... 2147483647		 No No
standard_conforming_strings boolean
on | off		 No No
synchronize_seqscans boolean
on | off		 No No
temp_buffers integer
100 ... 1073741823
Unit is 8 KB		 No Yes
temp_file_limit integer
1048576 ... 2147483647 KB		 No No
trace_notify boolean
on | off		 No No
trace_recovery_messages enumeration
debug5 | debug4 | debug3 | debug2 | debug1 | log | notice | warning | error		 No No
trace_sort boolean
on | off		 No No
track_activities boolean
on | off		 No No
track_activity_query_size integer
100 ... 102400		 Yes No
track_commit_timestamp boolean
on | off		 Yes No
track_counts boolean
on | off		 No No
track_functions enumeration
none | pl | all		 No No
track_io_timing boolean
on | off		 No No
vacuum_cost_delay integer
0 ... 100 ms		 No No
vacuum_cost_limit integer
1 ... 10000		 No No
vacuum_freeze_min_age integer
0 ... 1000000000		 No No
vacuum_freeze_table_age integer
0 ... 2000000000		 No No
vacuum_multixact_freeze_min_age integer
0 ... 1000000000		 No No
vacuum_multixact_freeze_table_age integer
0 ... 2000000000		 No No
work_mem integer
64 ... 2147483647 KB		 No Yes

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

This page
Documentation feedback
Cloud SQL for PostgreSQL
Product feedback