ALTER SYSTEM SET

ALTER SYSTEM SET globally modifies the value of a configuration parameter.

To see the current value of a configuration parameter, use SHOW.

Syntax

ALTER SYSTEM SET name TO = value DEFAULT
Field Use
name The name of the configuration parameter to modify.
value The value to assign to the configuration parameter.
DEFAULT Reset the configuration parameter’s default value. Equivalent to ALTER SYSTEM RESET.

Key configuration parameters

Name Default value Description Modifiable?
cluster quickstart The current cluster. Yes
cluster_replica The target cluster replica for SELECT queries. Yes
database materialize The current database. Yes
search_path public The schema search order for names that are not schema-qualified. Yes
transaction_isolation strict serializable The transaction isolation level. For more information, see Consistency guarantees.

Accepts values: serializable, strict serializable.
Yes

Other configuration parameters

Name Default value Description Modifiable?
allowed_cluster_replica_sizes Varies The allowed sizes when creating a new cluster replica. Contact support
application_name The application name to be reported in statistics and logs. This parameter is typically set by an application upon connection to Materialize (e.g. psql). Yes
auto_route_catalog_queries true Boolean flag indicating whether to force queries that depend only on system tables to run on the mz_catalog_server cluster for improved performance. Yes
client_encoding UTF8 The client’s character set encoding. The only supported value is UTF-8. Yes
client_min_messages notice The message levels that are sent to the client.

Accepts values: debug5, debug4, debug3, debug2, debug1, log, notice, warning, error. Each level includes all the levels that follow it.
Yes
datestyle ISO, MDY The display format for date and time values. The only supported value is ISO, MDY. Yes
emit_introspection_query_notice true Whether to print a notice when querying replica introspection relations. Yes
emit_timestamp_notice false Boolean flag indicating whether to send a notice specifying query timestamps. Yes
emit_trace_id_notice false Boolean flag indicating whether to send a notice specifying the trace ID, when available. Yes
enable_rbac_checks true Boolean flag indicating whether to apply RBAC checks before executing statements. Yes
enable_session_rbac_checks false Boolean flag indicating whether RBAC is enabled for the current session. No
extra_float_digits 3 Boolean flag indicating whether to adjust the number of digits displayed for floating-point values. Yes
failpoints Allows failpoints to be dynamically activated. No
idle_in_transaction_session_timeout 120 seconds The maximum allowed duration that a session can sit idle in a transaction before being terminated. If this value is specified without units, it is taken as milliseconds. A value of zero disables the timeout. Yes
integer_datetimes true Boolean flag indicating whether the server uses 64-bit-integer dates and times. No
intervalstyle postgres The display format for interval values. The only supported value is postgres. Yes
is_superuser Reports whether the current session is a superuser with admin privileges. No
max_aws_privatelink_connections 0 The maximum number of AWS PrivateLink connections in the region, across all schemas. Contact support
max_clusters 10 The maximum number of clusters in the region Contact support
max_connections 5000 The maximum number of concurrent connections in the region Contact support
max_credit_consumption_rate 1024 The maximum rate of credit consumption in a region. Credits are consumed based on the size of cluster replicas in use. Contact support
max_databases 1000 The maximum number of databases in the region. Contact support
max_identifier_length 255 The maximum length in bytes of object identifiers. No
max_kafka_connections 1000 The maximum number of Kafka connections in the region, across all schemas. Contact support
max_mysql_connections 1000 The maximum number of MySQL connections in the region, across all schemas. Contact support
max_objects_per_schema 1000 The maximum number of objects in a schema. Contact support
max_postgres_connections 1000 The maximum number of PostgreSQL connections in the region, across all schemas. Contact support
max_query_result_size 1073741824 The maximum size in bytes for a single query’s result. Yes
max_replicas_per_cluster 5 The maximum number of replicas of a single cluster Contact support
max_result_size 1 GiB The maximum size in bytes for a single query’s result. Contact support
max_roles 1000 The maximum number of roles in the region. Contact support
max_schemas_per_database 1000 The maximum number of schemas in a database. Contact support
max_secrets 100 The maximum number of secrets in the region, across all schemas. Contact support
max_sinks 25 The maximum number of sinks in the region, across all schemas. Contact support
max_sources 25 The maximum number of sources in the region, across all schemas. Contact support
max_tables 200 The maximum number of tables in the region, across all schemas Contact support
mz_version Version-dependent Shows the Materialize server version. No
real_time_recency false Boolean flag indicating whether real-time recency is enabled for the current session. Contact support
real_time_recency_timeout 10 seconds Sets the maximum allowed duration of SELECT statements that actively use real-time recency. Yes
server_version_num Version-dependent The PostgreSQL compatible server version as an integer. No
server_version Version-dependent The PostgreSQL compatible server version. No
sql_safe_updates false Boolean flag indicating whether to prohibit SQL statements that may be overly destructive. Yes
standard_conforming_strings true Boolean flag indicating whether ordinary string literals ('...') should treat backslashes literally. The only supported value is true. Yes
statement_timeout 10 seconds The maximum allowed duration of INSERT, UPDATE, and DELETE operations. If this value is specified without units, it is taken as milliseconds. Yes
timezone UTC The time zone for displaying and interpreting timestamps. The only supported value is UTC. Yes

Privileges

Superuser privileges are required to execute this statement.

Back to top ↑