> ## Documentation Index > Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt > Use this file to discover all available pages before exploring further. # View and configure runtime parameters ## What are runtime parameters? Runtime parameters are variables that can be set at runtime to configure the behavior of RisingWave. They are also known as session variables. ## How to view runtime parameters? You can use the `SHOW ALL` command to view the runtime parameters, their current settings, and some notes about these parameters. ```bash theme={null} SHOW ALL; ``` For example, you may see a table similar to this: ```bsh Runtime Parameters theme={null} Name | Setting | Description --------------------------------+-----------------+-------------------------------------- implicit_flush | false | If set to `true`, every INSERT/UPDATE/DELETE statement will block until the entire dataflow is refreshed. create_compaction_group_for_mv | false | If set to `true`, RisingWave will create dedicated compaction groups when creating these materialized views. query_mode | auto | A temporary config variable to force query running in either local or distributed mode. If the value is auto, the system will decide for you automatically. ... ``` Below is the detailed information about the parameters you may see after using the `SHOW ALL` command: | Name | Values or examples | Description | | :----------------------------------------------------- | :--------------------------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | implicit\_flush | true/false | If set to `true`, every INSERT/UPDATE/DELETE statement will block until the entire dataflow is refreshed. In other words, every related table & MV will be able to see the write. | | create\_compaction\_group\_for\_mv | true/false | If set to `true`, RisingWave will create dedicated compaction groups when creating these materialized views. | | query\_mode | auto | A temporary config variable to force query running in either local or distributed mode. The default value is auto which means let the system decide to run batch queries in local or distributed mode automatically. | | extra\_float\_digits | 1 | Set the number of digits displayed for floating-point values. See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#:~:text=for%20more%20information.-,extra%5Ffloat%5Fdigits,-%28integer%29) for details. | | application\_name | psql | Set the application name to be reported in statistics and logs. See [here](https://www.postgresql.org/docs/14/runtime-config-logging.html#:~:text=What%20to%20Log-,application%5Fname,-%28string%29) for details. | | datestyle | DMY | It is typically set by an application upon connection to the server. See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DATESTYLE) for details. | | batch\_enable\_lookup\_join | true/false | Force the use of lookup join instead of hash join when possible for local batch execution. | | batch\_enable\_sort\_agg | true/false | Enable usage of sortAgg instead of hash agg when order property is satisfied in batch execution. | | batch\_enable\_distributed\_dml | true/false | Enable distributed DML, allowing INSERT/UPDATE/DELETE statements to be executed in a distributed way, such as running on multiple Compute Nodes. Defaults to false. | | batch\_expr\_strict\_mode | true/false | Control whether to let the entire query fail or fill `NULL` values for expression evaluation failures. | | batch\_hnsw\_ef\_search | 40 | Set the `ef_search` parameter for [HNSW](/processing/vector-indexes#index-types) vector index queries. | | max\_split\_range\_gap | 8 | The max gap allowed to transform small range scan into multi point lookup. | | search\_path | "\$user", public | Set the order in which schemas are searched when an object (table, data type, function, etc.) is referenced by a simple name with no schema specified. See [here](https://www.postgresql.org/docs/14/runtime-config-client.html#GUC-SEARCH-PATH) for details. | | visibility\_mode | default | If `VISIBILITY_MODE` is `all`, we will support querying the latest uncommitted data, and consistency is not guaranteed between the tables. | | transaction\_isolation | read committed | See [here](https://www.postgresql.org/docs/current/transaction-iso.html) for details. | | query\_epoch | 0 | Select as of specific epoch. Sets the historical epoch for querying data. If 0, querying latest data. | | timezone | UTC | Session timezone. Defaults to UTC. | | streaming\_parallelism | default/adaptive/0/``/bounded(``)/ratio(``) | Streaming parallelism policy for `CREATE MATERIALIZED VIEW/TABLE/INDEX/SOURCE/SINK`. Accepts a fixed integer ``, `adaptive` (equivalent to `0`) for full adaptive scheduling, `bounded()` or `ratio()` for capped adaptive scheduling, or `default` to use the built-in default, which resolves to `bounded(64)`. The effective parallelism is capped at `streaming_max_parallelism`. See [Adaptive parallelism strategies](#adaptive-parallelism-strategies) for details. | | streaming\_parallelism\_for\_backfill | default/1,2,3,... | Specific parallelism for backfill operations in streaming jobs. If set to `default`, it will fall back to the global `streaming_parallelism`. The job automatically switches to normal streaming parallelism after backfill completes. | | streaming\_parallelism\_for\_table | default/adaptive/0/``/bounded(``)/ratio(``) | Per-job-type policy for tables. Accepts the same value forms as `streaming_parallelism`. `default` resolves to `bounded(4)` when `streaming_parallelism` is also `default`; otherwise it follows the resolved global value. | | streaming\_parallelism\_for\_materialized\_view | default/adaptive/0/``/bounded(``)/ratio(``) | Per-job-type policy for materialized views. Accepts the same value forms as `streaming_parallelism`. `default` follows the resolved global value. | | streaming\_parallelism\_for\_index | default/adaptive/0/``/bounded(``)/ratio(``) | Per-job-type policy for indexes. Accepts the same value forms as `streaming_parallelism`. `default` follows the resolved global value. | | streaming\_parallelism\_for\_sink | default/adaptive/0/``/bounded(``)/ratio(``) | Per-job-type policy for sinks. Accepts the same value forms as `streaming_parallelism`. `default` follows the resolved global value. | | streaming\_parallelism\_for\_source | default/adaptive/0/``/bounded(``)/ratio(``) | Per-job-type policy for sources. Accepts the same value forms as `streaming_parallelism`. `default` resolves to `bounded(4)` when `streaming_parallelism` is also `default`; otherwise it follows the resolved global value. | | streaming\_max\_parallelism | 256 | The maximum parallelism allowed for streaming queries. For more information, see [Configuring maximum parallelism](/deploy/k8s-cluster-scaling#configuring-maximum-parallelism). | | streaming\_enable\_delta\_join | true/false | Enable delta join for streaming queries. Defaults to false. | | streaming\_enable\_bushy\_join | true/false | Enable bushy join for streaming queries. Defaults to true. | | streaming\_enable\_materialized\_expressions | true/false | Enable materialized expressions for impure functions (typically UDF). Defaults to true. | | streaming\_enable\_unaligned\_join | true/false | Control whether the streaming join should be unaligned or not. See [Isolating high-amplification joins](/processing/sql/joins#isolating-high-amplification-joins) for more details. | | streaming\_force\_filter\_inside\_join | true/false | Force filter to be pushed down into inner join. Defaults to false. See [inner join](/processing/sql/joins#inner-joins) for more details. | | streaming\_unsafe\_allow\_unmaterialized\_impure\_expr | true/false | Unsafe: allow impure expressions on retract/upsert streams without planner-inserted materialization. This also applies to impure select-list expressions in projections. Defaults to false. Enable it only when you accept the risk of inconsistent results or execution panics caused by re-evaluation on updates or retracts. | | streaming\_unsafe\_allow\_upsert\_sink\_pk\_mismatch | true/false | Unsafe: allow an upsert sink created with `snapshot=false` to use downstream primary-key columns that are not part of the upstream stream key. Defaults to false. Enable it only for compatibility cases where the sink input still contains complete old and new rows; otherwise downstream primary-key changes may leave stale rows. | | streaming\_join\_encoding | memory\_optimized /cpu\_optimized | Control which encoding strategy of rows is used in the join operator.
  • `memory_optimized`: Uses more CPU but significantly reduces memory per cached row, allowing more rows to be stored. Recommended when state size exceeds memory or disk cache, often indicated by high cache misses or remote I/O in Grafana.
  • `cpu_optimized`: Uses less CPU but more memory per row. Recommended when state fits in memory or cache. Can improve performance by up to 50%, depending on query complexity.
| | streaming\_separate\_consecutive\_join | true/false | Separate consecutive StreamHashJoin by no-shuffle StreamExchange. Defaults to false. | | streaming\_separate\_sink | true/false | Separate StreamSink by no-shuffle StreamExchange. Defaults to false. | | streaming\_use\_arrangement\_backfill | true/false | Enable arrangement backfill for streaming queries. Defaults to true. | | streaming\_use\_snapshot\_backfill | true/false | Enable snapshot backfill for streaming queries. Provides better isolation between the backfill and streaming phases by preventing resource contention when creating materialized views, indexes, or sinks. Defaults to true. | | enable\_index\_selection | true/false | Enable index selection for streaming and batch queries. Defaults to true. For details, see [Index selection](/performance/best-practices#index-selection). | | enable\_join\_ordering | true/false | Enable join ordering for streaming and batch queries. Defaults to true. | | enable\_locality\_backfill | true/false | Enable locality backfill for streaming queries. Defaults to false. For details, see [Locality backfill](/performance/best-practices#locality-backfill). | | enable\_serverless\_backfill | true/false | Enable serverless backfilling for materialized view, sink, and index creation in the current session. Defaults to false. Serverless backfilling is not supported for databases in non-default resource groups. In RisingWave Cloud, enable serverless backfilling in the Cloud console first. For details, see [Serverless backfilling](/processing/serverless-backfill). | | enable\_two\_phase\_agg | true/false | Enable two phase agg optimization. Defaults to true. Setting this to true will always set `FORCE_TWO_PHASE_AGG` to false. | | force\_two\_phase\_agg | true/false | Force two phase agg optimization whenever there's a choice between optimizations. Defaults to false. Setting this to true will always set `ENABLE_TWO_PHASE_AGG` to false. | | enable\_share\_plan | true/false | Enable sharing of common sub-plans. This means that DAG structured query plans can be constructed, rather than only tree structured query plans. | | force\_split\_distinct\_agg | true/false | Enable split distinct agg. | | intervalstyle | postgres | Set the display format for interval values. It is typically set by an application upon connection to the server. See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-INTERVALSTYLE) for details. | | batch\_parallelism | 0 | If `BATCH_PARALLELISM` is non-zero, batch queries will use this parallelism. | | server\_version | 9.5.0 | The version of PostgreSQL that Risingwave claims to be. | | server\_version\_num | 90500 | The version of PostgreSQL that Risingwave claims to be. | | client\_min\_messages | notice | See [here](https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES) for details. | | client\_encoding | UTF8 | See [here](https://www.postgresql.org/docs/15/runtime-config-client.html#GUC-CLIENT-ENCODING) for details. | | sink\_decouple | default | Enable decoupling sink and internal streaming graph or not. | | synchronize\_seqscans | true/false | See [here](https://www.postgresql.org/docs/current/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION) for details. Unused in RisingWave, support for compatibility. | | statement\_timeout | 1h/2s/500ms | Abort query statements that take more than the specified amount of time. Values must include an explicit time unit, such as `ms`, `s`, `min`, or `h`. If `log_min_error_statement` is set to ERROR or lower, the statement that timed out will also be logged. The default value is `1h`. | | lock\_timeout | 0 | See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT) for details. Unused in RisingWave, support for compatibility. | | cdc\_source\_wait\_streaming\_start\_timeout | 30 | For limiting the startup time of a shareable CDC streaming source when the source is being created. Unit: seconds. | | row\_security | true/false | See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-ROW-SECURITY) for details. Unused in RisingWave, support for compatibility. | | standard\_conforming\_strings | on | See [here](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STANDARD-CONFORMING-STRINGS) for details. | | source\_rate\_limit | default/positive integer/0 | Set the maximum number of records per second per source, for each parallelism. This parameter is applied when creating new sources and tables with sources.

The value can be default, 0, or a positive integer.
  • Set it to 0 will pause the source read for sources.
  • Set it to default will remove the rate limit.
Setting this variable will only affect new DDLs within the session, but not change the rate limits of existing jobs. Use `ALTER` to change the rate limits in existing [sources](/sql/commands/sql-alter-source/#set-source-rate-limit) and [tables that have source](/sql/commands/sql-alter-table/#set-source-rate-limit).

Note that the total throughput of a streaming job is determined by multiplying the parallelism with the throttle rate. To obtain the parallelism value for a streaming job, you can refer to the `streaming_parallelism` runtime parameter in this table. | | backfill\_rate\_limit | default/positive integer/0 | Set the maximum number of records per second per parallelism for the backfill process of materialized views, sinks, and indexes. This parameter is applied when creating new jobs, and throttles the backfill from upstream materialized views and sources.

The value can be default, 0, or a positive integer.
  • Set it to 0 will pause the backfill.
  • Set it to default will remove the backfill rate limit.
Setting this variable will only affect new DDLs within the session, but not change the rate limits of existing jobs. Use `ALTER` to change the backfill rate limits in existing [materialized views](/sql/commands/sql-alter-materialized-view#set-backfill-rate-limit) and [CDC tables](/sql/commands/sql-alter-table/#set-backfill%5Frate%5Flimit).

Note that the total throughput of a streaming job is determined by multiplying the parallelism with the throttle rate. To obtain the parallelism value for a streaming job, you can refer to the `streaming_parallelism` runtime parameter in this table. | | dml\_rate\_limit | positive integer/0 | Set streaming rate limit (rows per second) for each parallelism for table DML.
  • Set it to -1 will disable rate limit.
  • Set it to 0 will pause the DML.
| | sink\_rate\_limit | positive integer/0 | Set sink rate limit (rows per second) for each parallelism for external sink.
  • Set it to -1 will disable rate limit.
  • Set it to 0 will pause the sink.
| | streaming\_over\_window\_cache\_policy | full | Cache policy for partition cache in streaming over window. Can be `full`, `recent`, `recent_first_n` or `recent_last_n`. | | background\_ddl | true/false | Run DDL statements in background. | | server\_encoding | UTF8 | Show the server-side character set encoding. At present, this parameter can be shown but not set, because the encoding is determined at database creation time. | | bytea\_output | hex | Set the output format for values of type bytea. Valid values are hex (the default) and escape (the traditional PostgreSQL format). The bytea type always accepts both formats on input, regardless of this setting. | | iceberg\_engine\_connection | string | Specifies the connection information for the Iceberg engine tables. Create an Iceberg connection and set this variable before creating Iceberg engine tables. The format should be `schema_name.connection_name`. | | iceberg\_query\_storage\_mode | `iceberg` (default) / `hummock` / `auto` | Controls which storage backend is used for batch `SELECT` queries on Iceberg engine tables. `iceberg` always reads from Iceberg columnar storage. `hummock` always reads from Hummock row storage. `auto` lets the optimizer choose based on heuristics (currently prefers Hummock for point lookups on primary keys). Only affects batch `SELECT` on tables with `ENGINE = ICEBERG`. | If you just want to view a specific parameter's value, you can also use the `SHOW` command. ```sql theme={null} SHOW parameter_name; ``` ## How to configure runtime parameters? You can use `SET` command or the `set_config()` function to change the setting of a runtime parameter. The syntax of the `SET` command is: ```sql theme={null} SET parameter_name { TO | = } { value | 'value' | DEFAULT}; ``` Where `parameter_name` is the name of the parameter, and `value` or `'value'` is the new value of the parameter. `DEFAULT` can be written to specify resetting the parameter to its default value. For details about the `set_config()` function, see [System administration functions](/sql/functions/sys-admin#set-config), and for details about the `SET` command, see [SET](/sql/commands/sql-set). `SET` applies only to the current session. You can also use the [ALTER SYSTEM SET](/sql/commands/sql-alter-system) command to set a system-wide default value for a runtime parameter. This configuration will then be applied to every new session. `ALTER SYSTEM SET` takes effect in new sessions. The value of the runtime parameter remains unchanged in the current session. ```sql Syntax theme={null} ALTER SYSTEM SET session_param_name TO session_param_value; ``` ## Usage examples ### Configuring backfill parallelism The `streaming_parallelism_for_backfill` parameter allows you to specify different parallelism for backfill operations versus normal streaming. This is useful for optimizing resource utilization during the initial data loading phase. ```sql Example theme={null} SET STREAMING_PARALLELISM = 3; SET STREAMING_PARALLELISM_FOR_BACKFILL = 2; SET BACKFILL_RATE_LIMIT = 1; SET BACKGROUND_DDL = true; CREATE TABLE t(v int); INSERT INTO t SELECT * FROM generate_series(1, 10); CREATE MATERIALIZED VIEW m AS SELECT * FROM t; -- While backfill is running, check parallelism SHOW JOBS; SELECT name, parallelism FROM rw_fragment_parallelism WHERE name = 'm'; -- Output: parallelism = 2 (using streaming_parallelism_for_backfill) -- After backfill completes, check parallelism again SHOW JOBS; SELECT name, parallelism FROM rw_fragment_parallelism WHERE name = 'm'; -- Output: parallelism = 3 (switched to streaming_parallelism) ``` The materialized view uses parallelism of 2 during the backfill phase, then automatically switches to parallelism of 3 after backfill completes. ## Adaptive parallelism strategies Streaming parallelism is configured through a single family of unified parameters: * `streaming_parallelism` — the global policy. * `streaming_parallelism_for_` — per-job-type overrides, where `` is one of `table`, `materialized_view`, `index`, `source`, or `sink`. Each parameter accepts a value that either pins a fixed parallelism or selects an adaptive strategy, giving you fine-grained control over how streaming jobs utilize available worker parallelism in the resource group. ### Accepted values Each unified parameter accepts one of the following forms: * **``**: A positive integer, for example `4`. Pins the job to a fixed parallelism. The effective parallelism is still capped by `streaming_max_parallelism`. * **`adaptive`** (or **`0`**): Uses all available worker parallelism in the resource group. Maximizes resource utilization for high-throughput workloads. * **`bounded()`**: Adaptive scheduling with an upper bound of ``, for example `bounded(4)`. The job scales with the resource group but never exceeds `` parallel workers. * **`ratio()`**: Adaptive scheduling using a fraction of the available worker parallelism, where `0 < r ≤ 1`, for example `ratio(0.5)`. * **`default`**: Falls back to the built-in default. * For `streaming_parallelism`, `default` resolves to `bounded(64)`. * For `streaming_parallelism_for_table` and `streaming_parallelism_for_source`, `default` resolves to `bounded(4)` when `streaming_parallelism` is also `default`; otherwise it follows the resolved global value. * For the other `streaming_parallelism_for_` parameters, `default` follows the resolved global value. ### Examples Set a session-level policy for all streaming jobs: ```sql theme={null} SET streaming_parallelism = 'ratio(0.5)'; ``` Pin a fixed parallelism: ```sql theme={null} SET streaming_parallelism = 8; ``` Cap adaptive parallelism at an upper bound: ```sql theme={null} SET streaming_parallelism = 'bounded(16)'; ``` Override parallelism per job type within the same session: ```sql theme={null} SET streaming_parallelism_for_table = 'adaptive'; SET streaming_parallelism_for_materialized_view = 'bounded(8)'; SET streaming_parallelism_for_sink = 'ratio(0.3)'; ``` Create a table with a specific policy: ```sql theme={null} SET streaming_parallelism_for_table = 'bounded(2)'; CREATE TABLE my_table (id INT, value VARCHAR); ``` Restore a parameter to its default: ```sql theme={null} SET streaming_parallelism_for_materialized_view = DEFAULT; -- or equivalently SET streaming_parallelism_for_materialized_view = 'default'; ``` Set a system-wide default that applies to new sessions: ```sql theme={null} ALTER SYSTEM SET streaming_parallelism TO 'bounded(4)'; ``` ### Important notes * Per-job-type parameters (`streaming_parallelism_for_`) take precedence over the global `streaming_parallelism`. When a per-type parameter is `default`, it inherits from the resolved global value, with the exceptions for `table` and `source` noted above. * The resolved policy is persisted with the streaming job at creation time and remains effective even if session or system-level settings change later. * The concrete policy for each job is visible in system catalogs and diagnostics. Fixed parallelism displays as an integer (for example, `4`); adaptive policies display as `adaptive`, `bounded()`, or `ratio()`. This applies to `pg_catalog.pg_settings`, `rw_streaming_parallelism`, `rw_streaming_jobs`, `rw_table_fragments`, and diagnose-style output. * `ALTER TABLE|SOURCE|SINK|INDEX|MATERIALIZED VIEW ... SET PARALLELISM` accepts the same value forms (`adaptive`, `0`, a fixed integer, `bounded()`, `ratio()`). Fragment-level `ALTER ... SET PARALLELISM` remains limited to `adaptive` and fixed integers. Older releases exposed the deprecated parameters `adaptive_parallelism_strategy` (system-level) and `streaming_parallelism_strategy` / `streaming_parallelism_strategy_for_` (session-level). On upgrade, meta migrates their values into `streaming_parallelism` and `streaming_parallelism_for_` once, then removes the deprecated entries. New clusters only expose the unified parameters, and `pg_catalog.pg_settings` no longer lists `adaptive_parallelism_strategy`.