On this page 
Use the SHOW statement to display the value of one or all of the session variables. You configure session variables using SET.
Required privileges
No privileges are required to display the session variables.
Synopsis
Note:
The SHOW statement for session variables is unrelated to the other SHOW statements like SHOW CLUSTER SETTING, SHOW CREATE, and SHOW DATABASES.
Parameters
| Parameter | Description |
|---|---|
var_name |
The session variable name to show. The variable name is case-insensitive and can be enclosed in double quotes. |
Supported variables
Read-only indicates whether the variable can be changed in a session with SET. Cluster Setting identifies the cluster setting that supplies the default value, when applicable.
| Variable Name | Description | Default Value | Read-only | Cluster Setting |
|---|---|---|---|---|
allow_ordinal_column_references | Controls whether the deprecated ordinal column reference syntax (e.g., SELECT @1 FROM t) is allowed. | off | No | - |
allow_role_memberships_to_change_during_transaction | Controls whether operations consulting role membership cache retain their lease throughout the transaction. | off | No | - |
allow_unsafe_internals | Controls access to unsafe internals in the system database and the crdb_internal schema. When off, queries to those namespaces will fail. Usage of unsafe internals is audited via the SENSITIVE_ACCESS logging channel. | off | No | - |
alter_primary_region_super_region_override | Controls whether the user can modify a primary region that is part of a super region. | off | No | sql.defaults.override_alter_primary_region_in_super_region.enabled |
always_distribute_full_scans | Controls whether full table scans always force the plan to be distributed, regardless of the estimated row count. | off | No | - |
application_name | Sets the name of the application running the current session. Used for logging and per-application statistics. | - | No | - |
authentication_method | The authentication method used for the session. | - | Yes | - |
autocommit_before_ddl | Causes any DDL statement received during an explicit transaction to auto-commit before executing the schema change. This is useful because CockroachDB does not fully support multiple schema changes in a single transaction. | on | No | sql.defaults.autocommit_before_ddl.enabled |
avoid_buffering | Indicates that the returned data should not be buffered by conn executor. This is currently used by replication primitives to ensure the data is flushed to the consumer immediately. | off | No | - |
avoid_full_table_scans_in_mutations | Controls whether mutation queries that plan full table scans should be avoided. | on | No | - |
backslash_quote | Controls whether backslash can be used as a quote escape character (compatibility setting). | safe_encoding | No | - |
buffered_writes_implicit_txns_enabled | Controls whether buffered writes are enabled for implicit transactions. | off | No | - |
buffered_writes_use_locking_on_non_unique_indexes | Controls whether buffered writes use locking on non-unique indexes. | off | No | - |
bypass_pcr_reader_catalog_aost | Disables the AOST used by all user queries on the PCR reader catalog. | off | No | - |
bytea_output | Controls how to encode byte arrays when converting to string. | hex | No | - |
canary_stats_mode | Controls which table statistics are used for query planning: 'force_canary' always uses the newest stats, 'force_stable' always uses the second-newest stats, and 'auto' (default) defers to the sql.stats.canary_fraction cluster setting to decide probabilistically. | auto | No | - |
check_function_bodies | Controls whether functions are validated during function creation. | on | No | - |
client_encoding | Controls the client-side character encoding. Only UTF8 is supported. | UTF8 | No | - |
client_min_messages | Controls which message levels are sent to the client. | notice | No | - |
close_cursors_at_commit | Determines whether cursors remain open after their parent transaction closes. | on | No | - |
copy_fast_path_enabled | Controls whether the optimized copy mode is enabled. | on | No | - |
copy_from_atomic_enabled | When enabled, implicit transaction COPY FROM operations are committed atomically, matching PostgreSQL behavior. When disabled, rows are segmented into batches unless issued within an explicit transaction. | on | No | - |
copy_from_retries_enabled | Controls whether retries should be internally attempted for retriable errors in COPY FROM operations. | on | No | - |
copy_num_retries_per_batch | Determines the number of times a single batch of rows can be retried for non-atomic COPY operations. | 5 | No | - |
copy_transaction_quality_of_service | Sets the admission control priority of the transactions used to evaluate COPY commands. | background | No | - |
copy_write_pipelining_enabled | Controls whether write pipelining is enabled for implicit transactions used by COPY. | off | No | - |
cost_scans_with_default_col_size | Controls whether the optimizer should cost scans and joins using a default number of bytes per column instead of column sizes from statistics. | off | No | sql.defaults.cost_scans_with_default_col_size.enabled |
crdb_version | The version of CockroachDB. | - | Yes | - |
create_table_with_schema_locked | Controls whether CREATE TABLE acquires a schema lock on the parent schema. | on | No | sql.defaults.create_table_with_schema_locked |
database | Sets the current database for resolving names in queries. | - | No | - |
datestyle | Controls the display format for date and time values as well as the rules for interpreting ambiguous date inputs. | ISO, MDY | No | sql.defaults.datestyle |
deadlock_timeout | Sets the amount of time to wait on a lock before checking for deadlock. If set to 0, there is no timeout. | 0s | No | - |
declare_cursor_statement_timeout_enabled | Controls whether statement timeouts apply during DECLARE CURSOR operations. | on | No | - |
default_int_size | Specifies the size in bits or bytes (preferred) of how the INT type should be parsed. | 8 | No | sql.defaults.default_int_size |
default_table_access_method | Sets the default table access method (compatibility setting). | heap | No | - |
default_tablespace | Supported only for pg compatibility - CockroachDB has no notion of tablespaces. | - | No | - |
default_text_search_config | Sets the default text search configuration used for builtins like `to_tsvector` and `to_tsquery`. | pg_catalog.english | No | - |
default_transaction_isolation | Sets the transaction isolation level of new transactions. | serializable | No | - |
default_transaction_priority | Sets the default priority of newly created transactions. | normal | No | - |
default_transaction_quality_of_service | Sets the default admission control priority of newly created transactions. | regular | No | - |
default_transaction_read_only | Sets whether new transactions are read-only by default. | off | No | - |
default_transaction_use_follower_reads | When enabled, all read-only transactions use `AS OF SYSTEM TIME follower_read_timestamp()` to allow follower reads. When disabled, read-only transactions only use follower reads if an explicit `AS OF SYSTEM TIME` clause is specified. | off | No | - |
default_with_oids | Controls whether new tables are created with OIDs by default (compatibility setting). | off | No | - |
descriptor_validation | Controls whether schema object descriptors are validated at read and write time, read time only, or never. | on | No | - |
direct_columnar_scans_enabled | Controls whether the COL_BATCH_RESPONSE scan format should be used for ScanRequests and ReverseScanRequests whenever possible. | off | No | sql.distsql.direct_columnar_scans.enabled |
disable_changefeed_replication | Disables changefeed events from being emitted for data changes made in a session, applying to new transactions only. | off | No | - |
disable_hoist_projection_in_join_limitation | Disables the restrictions placed on projection hoisting during query planning in the optimizer. | off | No | - |
disable_optimizer_rules | Allows disabling specific optimizer transformation rules by name, specified as a comma-separated list. | - | No | - |
disable_partially_distributed_plans | Controls whether partially distributed plans are disabled. | off | No | - |
disable_plan_gists | Controls whether plan gists are disabled. | off | No | - |
disable_vec_union_eager_cancellation | Disables the eager cancellation that is performed by the vectorized engine when transitioning into the draining state in some cases. | off | No | - |
disable_wait_for_jobs_notice | Controls whether the notice about waiting for jobs to complete is suppressed. | off | No | - |
disallow_full_table_scans | When enabled, queries on tables with a row count greater than large_full_scan_rows will not use full table or index scans. If no other query plan is possible, queries will return an error. This setting does not apply to internal queries. | off | No | sql.defaults.disallow_full_table_scans.enabled |
distribute_group_by_row_count_threshold | Sets the minimum number of rows estimated to be processed by the GroupBy operator to distribute the plan. | 1000 | No | - |
distribute_join_row_count_threshold | Sets the minimum number of rows estimated to be processed from both inputs by the hash or merge join to distribute the plan. | 1000 | No | - |
distribute_scan_row_count_threshold | Sets the minimum number of rows estimated to be read by the Scan operator to distribute the plan. | 10000 | No | - |
distribute_sort_row_count_threshold | Sets the minimum number of rows estimated to be processed by the Sort operator to distribute the plan. | 1000 | No | - |
distsql | Controls the query distribution mode for the session. By default, CockroachDB determines which queries benefit from distributed execution. Distribution thresholds for specific operations are controlled by `distribute_group_by_row_count_threshold`, `distribute_scan_row_count_threshold`, and `distribute_sort_row_count_threshold`. | auto | No | sql.defaults.distsql |
distsql_plan_gateway_bias | Controls how many more partition spans the gateway node can be assigned compared to other nodes when distributing SQL execution. | 2 | No | - |
distsql_prevent_partitioning_soft_limited_scans | Controls whether the DistSQL planner prevents partitioning of soft-limited scans across multiple nodes. | on | No | - |
distsql_use_reduced_leaf_write_sets | Controls whether distributed SQL uses reduced write sets for leaf transactions. | on | No | - |
distsql_workmem | Determines how much RAM (in bytes) a single operation of a single query can use before it has to spill to disk. | 64 MiB | No | sql.distsql.temp_storage.workmem |
enable_auto_rehoming | Controls whether auto-rehoming is enabled for REGIONAL BY ROW tables. | off | No | sql.defaults.experimental_auto_rehoming.enabled |
enable_create_stats_using_extremes | Controls whether CREATE STATISTICS using the EXTREMES method is enabled. | on | No | - |
enable_create_stats_using_extremes_bool_enum | Controls whether CREATE STATISTICS using the EXTREMES method is enabled for boolean and enum types. | off | No | - |
enable_durable_locking_for_serializable | Controls whether FOR UPDATE and FOR SHARE locks are replicated via Raft under serializable isolation, allowing locks to be preserved across lease transfers. Enabling this adds latency to locking statements. This matches the default READ COMMITTED behavior. | off | No | - |
enable_experimental_alter_column_type_general | Controls whether ALTER TABLE ... ALTER COLUMN ... TYPE can be used for general conversions requiring online schema changes. | off | No | sql.defaults.experimental_alter_column_type.enabled |
enable_implicit_fk_locking_for_serializable | Controls whether shared locks are used to perform foreign key checks under serializable isolation. Requires enable_shared_locking_for_serializable to also be enabled. | off | No | - |
enable_implicit_select_for_update | Controls whether `FOR UPDATE` locking may be used during the row-fetch phase of mutation statements. | on | No | sql.defaults.implicit_select_for_update.enabled |
enable_implicit_transaction_for_batch_statements | Controls whether a batch of statements sent in one query is executed as an implicit transaction. | on | No | - |
enable_insert_fast_path | Controls whether the fast path for INSERT operations with VALUES input may be used. | on | No | sql.defaults.insert_fast_path.enabled |
enable_multiple_modifications_of_table | Allows statements with multiple modification subqueries for the same table, risking data corruption if rows are modified multiple times. | off | No | - |
enable_multiregion_placement_policy | Controls whether placement can be used in multi-region contexts. | off | No | sql.defaults.multiregion_placement_policy.enabled |
enable_seqscan | enable_seqscan is included for compatibility with Postgres; changing it has no effect. | on | No | - |
enable_shared_locking_for_serializable | Controls whether SELECT FOR SHARE statements acquire shared locks under serializable isolation. When off, FOR SHARE statements are still permitted but silently do not lock. | off | No | - |
enable_zigzag_join | Controls whether the optimizer should try to plan a zigzag join. | off | No | sql.defaults.zigzag_join.enabled |
enforce_home_region | When enabled, queries return an error if they cannot run entirely in the gateway's home region, such as when reading from multiple home regions in a REGIONAL BY ROW table. Only tables with ZONE survivability can be scanned without error when this is enabled. | off | No | - |
escape_string_warning | Controls whether warnings are issued for escape string syntax. | on | No | - |
expect_and_ignore_not_visible_columns_in_copy | Changes behavior for COPY FROM to expect and ignore not visible column fields. | off | No | - |
experimental_enable_implicit_column_partitioning | Controls whether implicit column partitioning can be created. | off | No | sql.defaults.experimental_implicit_column_partitioning.enabled |
experimental_enable_temp_tables | Controls whether temporary tables can be created. | off | No | sql.defaults.experimental_temporary_tables.enabled |
experimental_enable_unique_without_index_constraints | Controls whether creating unique constraints without an index is allowed. | off | No | sql.defaults.experimental_enable_unique_without_index_constraints.enabled |
experimental_hash_group_join_enabled | Controls whether the physical planner will convert a hash join followed by hash aggregator into a single hash group-join. | off | No | - |
extra_float_digits | Sets the number of digits beyond the standard number to use for float conversions. | 1 | No | - |
force_savepoint_restart | Overrides the default SAVEPOINT behavior for compatibility with certain ORMs. | off | No | - |
foreign_key_cascades_limit | Sets the maximum number of cascading operations for foreign key actions. | 10000 | No | sql.defaults.foreign_key_cascades_limit |
idle_in_transaction_session_timeout | Sets the maximum allowed duration for an idle transaction session. The session is terminated if it exceeds this limit. | 0s | No | sql.defaults.idle_in_transaction_session_timeout |
idle_session_timeout | Sets the maximum allowed duration for an idle session. The session is terminated if it exceeds this limit. | 0s | No | sql.defaults.idle_in_session_timeout |
index_join_streamer_batch_size | Sets the size limit on input rows to the ColIndexJoin operator when using the Streamer API for a single lookup KV batch. | 8.0 MiB | No | sql.distsql.index_join_streamer.batch_size |
index_recommendations_enabled | Controls whether index recommendations are enabled. | on | No | - |
initial_retry_backoff_for_read_committed | Sets the initial backoff duration for automatic retries of statements in explicit READ COMMITTED transactions that encounter retry errors. | 2ms | No | - |
inject_retry_errors_enabled | Causes statements (except SET) inside explicit transactions to return a transaction retry error for testing application retry logic. If the client retries using the cockroach_restart savepoint, the transaction proceeds normally after the 3rd retry error. | off | No | - |
inject_retry_errors_on_commit_enabled | Causes statements inside explicit transactions to return a retry error just before transaction commit for testing retry logic. | off | No | - |
integer_datetimes | Reports whether integer datetime representation is used (always on). | on | Yes | - |
intervalstyle | Controls the display format for `INTERVAL` values. | postgres | No | sql.defaults.intervalstyle |
is_superuser | Indicates whether the current user has superuser privileges. | off | Yes | - |
join_reader_index_join_strategy_batch_size | Sets the size limit on input rows to the joinReader processor when performing index joins for a single lookup KV batch. | 4.0 MiB | No | sql.distsql.join_reader_index_join_strategy.batch_size |
join_reader_no_ordering_strategy_batch_size | Sets the size limit on input rows to the joinReader processor (when ordering is not maintained) for a single lookup KV batch. | 2.0 MiB | No | sql.distsql.join_reader_no_ordering_strategy.batch_size |
join_reader_ordering_strategy_batch_size | Sets the size limit on input rows to the joinReader processor (when ordering must be maintained) for a single lookup KV batch. | 100 KiB | No | sql.distsql.join_reader_ordering_strategy.batch_size |
kv_transaction_buffered_writes_enabled | Controls whether the buffered writes KV transaction protocol is used for user queries on the current session. Changes made in an explicit transaction apply only to later transactions. | on | No | kv.transaction.write_buffering.enabled |
large_full_scan_rows | Sets the estimated row count at which a full scan is considered large and worthy of logging or disabling. | 0 | No | sql.defaults.large_full_scan_rows |
lc_collate | Reports the database collation locale. | C.UTF-8 | Yes | - |
lc_ctype | Reports the database character classification locale. | C.UTF-8 | Yes | - |
lc_messages | Sets the language in which messages are displayed (compatibility setting). | C.UTF-8 | No | - |
lc_monetary | Sets the locale for formatting monetary amounts (compatibility setting). | C.UTF-8 | No | - |
lc_numeric | Sets the locale for formatting numbers (compatibility setting). | C.UTF-8 | No | - |
lc_time | Sets the locale for formatting dates and times (compatibility setting). | C.UTF-8 | No | - |
legacy_varchar_typing | Controls the legacy behavior of allowing some invalid mix-typed comparisons with VARCHAR types. | off | No | - |
locality | The locality of the current node, as set by `cockroach start --locality`. | - | Yes | - |
locality_optimized_partitioned_index_scan | Controls whether locality-optimized partitioned index scans are enabled. | on | No | sql.defaults.locality_optimized_partitioned_index_scan.enabled |
lock_timeout | Sets the maximum amount of time a query can spend acquiring or waiting for a single row-level lock. Unlike in PostgreSQL, non-locking reads in CockroachDB also wait for conflicting locks, so this timeout applies to writes as well as locking and non-locking reads. If set to 0, queries do not time out due to lock acquisitions. | 0s | No | sql.defaults.lock_timeout |
log_timezone | The timezone used for logging (always UTC). | UTC | No | - |
max_connections | Reports the maximum number of concurrent connections. | - | Yes | - |
max_identifier_length | The maximum length allowed for identifiers. | - | Yes | - |
max_index_keys | Reports the maximum number of index keys (always 32). | 32 | Yes | - |
max_prepared_transactions | Reports the maximum number of prepared transactions (always maximum int32). | 2147483647 | Yes | - |
max_retries_for_read_committed | Sets the maximum number of automatic retries for statements in explicit READ COMMITTED transactions that encounter retry errors. | 100 | No | - |
multiple_active_portals_enabled | Determines if pgwire portal execution for certain queries can be paused, allowing interleaved execution with local plans. | off | No | - |
node_id | The ID of the current node. | - | Yes | - |
null_ordered_last | Controls whether NULL values are ordered last. When true, NULL values appear after non-NULL values in ordered results. | off | No | - |
on_update_rehome_row_enabled | Controls whether the ON UPDATE rehome_row() will actually trigger on row updates. | on | No | sql.defaults.on_update_rehome_row.enabled |
opt_split_scan_limit | Sets the maximum number of UNION ALL statements a Scan may be split into during query optimization to avoid a sort. | 2048 | No | - |
optimizer | Controls whether the cost-based optimizer is enabled. | on | No | - |
optimizer_always_use_histograms | Ensures that the optimizer always uses histograms to calculate statistics if available. | on | No | - |
optimizer_check_input_min_row_count | Sets a lower bound on row count estimates for the buffer scan of foreign key and uniqueness checks. | 1 | No | - |
optimizer_clamp_inequality_selectivity | Controls whether the optimizer clamps selectivity estimates for inequality predicates to improve cardinality estimation accuracy. | on | No | - |
optimizer_clamp_low_histogram_selectivity | Controls whether the optimizer clamps low selectivity estimates from histogram statistics to prevent overly optimistic cardinality estimates. | on | No | - |
optimizer_disable_cross_region_cascade_fast_path_for_rbr_tables | Controls whether the optimizer disables the cross-region cascade fast path optimization for REGIONAL BY ROW tables. | on | No | - |
optimizer_enable_lock_elision | Controls whether the optimizer can eliminate unnecessary locking operations. | on | No | - |
optimizer_hoist_uncorrelated_equality_subqueries | Controls whether the optimizer hoists uncorrelated subqueries in equality expressions with columns, potentially producing more efficient plans. | on | No | - |
optimizer_inline_any_unnest_subquery | Controls whether the optimizer inlines subqueries containing ANY expressions combined with UNNEST. | on | No | - |
optimizer_merge_joins_enabled | Controls whether the optimizer should explore query plans with merge joins. | on | No | - |
optimizer_min_row_count | Sets a lower bound on row count estimates during query planning, except for expressions with zero cardinality. | 1 | No | - |
optimizer_plan_lookup_joins_with_reverse_scans | Controls whether the optimizer can plan lookup joins using reverse index scans. | on | No | - |
optimizer_prefer_bounded_cardinality | Instructs the optimizer to prefer query plans where every expression has a bounded cardinality over plans with unbounded cardinality expressions. | on | No | - |
optimizer_prove_implication_with_virtual_computed_columns | Controls whether the optimizer should use virtual computed columns to prove partial index implication. | on | No | - |
optimizer_push_limit_into_project_filtered_scan | Controls whether the optimizer should push limit expressions into projects of filtered scans. | on | No | - |
optimizer_push_offset_into_index_join | Controls whether the optimizer should push offset expressions into index joins. | on | No | - |
optimizer_span_limit | Sets the maximum number of constraint spans allowed in a scan during query optimization. 0 means no limit. | 0 | No | - |
optimizer_use_conditional_hoist_fix | Prevents the optimizer from hoisting volatile expressions that are conditionally executed by CASE, COALESCE, or IFERR expressions. | on | No | - |
optimizer_use_delete_range_fast_path | Controls whether the optimizer uses the fast path for DELETE operations using range deletions. | on | No | - |
optimizer_use_exists_filter_hoist_rule | Controls whether the optimizer hoists filters out of EXISTS subqueries. | on | No | - |
optimizer_use_forecasts | Controls whether the optimizer should use statistics forecasts for cardinality estimation. | on | No | - |
optimizer_use_histograms | Controls whether the optimizer should use histogram statistics for cardinality estimation. | on | No | sql.defaults.optimizer_use_histograms.enabled |
optimizer_use_improved_computed_column_filters_derivation | Enables the optimizer to derive filters on computed columns in more cases beyond simple single-column equations. | on | No | - |
optimizer_use_improved_disjunction_stats | Controls whether the optimizer should use improved statistics calculations for disjunctive filters. | on | No | - |
optimizer_use_improved_distinct_on_limit_hint_costing | Controls whether the optimizer should use an improved costing estimate for DistinctOn operators with limit hints. | on | No | - |
optimizer_use_improved_hoist_join_project | Controls whether the optimizer uses an improved rule for hoisting projections out of joins. | on | No | - |
optimizer_use_improved_join_elimination | Allows the optimizer to eliminate joins in more cases by remapping columns from eliminated joins to equivalent columns. | on | No | - |
optimizer_use_improved_multi_column_selectivity_estimate | Controls whether the optimizer should use an improved selectivity estimate for multi-column predicates. | on | No | - |
optimizer_use_improved_split_disjunction_for_joins | Enables the optimizer to split more disjunctions (OR expressions) in join conditions by building a UNION of join expressions. | on | No | - |
optimizer_use_improved_trigram_similarity_selectivity | Controls whether the optimizer should use an improved selectivity estimate for trigram similarity filters. | on | No | - |
optimizer_use_improved_zigzag_join_costing | Controls whether the optimizer should use improved logic in the cost model for zigzag joins. | on | No | - |
optimizer_use_limit_ordering_for_streaming_group_by | Enables optimization for 'SELECT ... GROUP BY ... ORDER BY ... LIMIT n' queries by using the limit ordering to inform group-by requirements. | on | No | - |
optimizer_use_lock_elision_multiple_families | Controls whether the optimizer enables lock elision for operations involving multiple column families. | off | No | - |
optimizer_use_lock_op_for_serializable | Controls whether the optimizer implements SELECT FOR UPDATE and FOR SHARE using the Lock operator under serializable isolation. | off | No | - |
optimizer_use_max_frequency_selectivity | Controls whether the optimizer uses the maximum frequency value for selectivity estimation. | on | No | - |
optimizer_use_merged_partial_statistics | Controls whether the optimizer should use statistics merged from partial and full statistics for cardinality estimation. | on | No | - |
optimizer_use_min_row_count_anti_join_fix | Controls whether the optimizer uses a fix for minimum row count estimation in anti-join operations. | on | No | - |
optimizer_use_multicol_stats | Controls whether the optimizer should use multi-column statistics for cardinality estimation. | on | No | sql.defaults.optimizer_use_multicol_stats.enabled |
optimizer_use_not_visible_indexes | Controls whether the optimizer can still choose to use not visible indexes for query plans. | off | No | - |
optimizer_use_polymorphic_parameter_fix | Controls whether the optimizer validates routine polymorphic parameters during overload resolution and type-checking. | on | No | - |
optimizer_use_provided_ordering_fix | Controls whether the optimizer reconciles provided orderings with required ordering choices to prevent internal errors. | on | No | - |
optimizer_use_trigram_similarity_optimization | Controls whether the optimizer should generate improved plans for queries with trigram similarity filters. | on | No | - |
optimizer_use_virtual_computed_column_stats | Controls whether the optimizer should use statistics on virtual computed columns for cardinality estimation. | on | No | - |
override_multi_region_zone_config | Controls whether zone configurations can be modified for multi-region databases and their objects. | off | No | sql.defaults.override_multi_region_zone_config.enabled |
parallelize_multi_key_lookup_joins_avg_lookup_ratio | Sets the average lookup ratio threshold for parallelizing multi-key lookup joins. | 10 | No | - |
parallelize_multi_key_lookup_joins_avg_lookup_row_size | Sets the average lookup row size threshold for parallelizing multi-key lookup joins. | 100 KiB | No | - |
parallelize_multi_key_lookup_joins_enabled | Controls whether the join reader should parallelize lookup batches. When enabled, this increases the speed of lookup joins with multiple looked up rows at the cost of increased memory usage. | off | No | sql.distsql.parallelize_multi_key_lookup_joins.enabled |
parallelize_multi_key_lookup_joins_max_lookup_ratio | Sets the maximum lookup ratio threshold for parallelizing multi-key lookup joins. | 10000 | No | - |
parallelize_multi_key_lookup_joins_only_on_mr_mutations | Controls whether parallelization of multi-key lookup joins is restricted to multi-row mutations only. | on | No | - |
password_encryption | The encryption method used for passwords. | scram-sha-256 | No | - |
pg_trgm.similarity_threshold | Sets the value used to compare trigram similarities for the string % string overload. | 0.3 | No | - |
plan_cache_mode | Controls the method that the optimizer should use to choose between a custom and generic query plan. | auto | No | sql.defaults.plan_cache_mode |
plpgsql_use_strict_into | Causes PL/pgSQL "SELECT ... INTO" and "RETURNING INTO" syntax to always behave as if specified with STRICT option. | off | No | - |
prefer_lookup_joins_for_fks | Causes foreign key operations to prefer lookup joins. | off | No | sql.defaults.prefer_lookup_joins_for_fks.enabled |
prepared_statements_cache_size | Causes the LRU prepared statements in a session to be automatically deallocated when total prepared statement memory usage exceeds this value. | 0 B | No | - |
prevent_update_set_column_drop | Controls whether columns referenced in an UPDATE SET clause are prevented from being dropped. | on | No | - |
propagate_admission_header_to_leaf_transactions | Controls whether admission control headers are propagated to leaf transactions in distributed queries. | on | No | - |
propagate_input_ordering | Controls whether to propagate inner ordering to the outer scope when planning subqueries or CTEs if the outer scope is unordered. | off | No | sql.defaults.propagate_input_ordering.enabled |
recursion_depth_limit | Sets the maximum depth that nested trigger function calls can reach. | 1000 | No | - |
register_latch_wait_contention_events | Controls whether contention events are registered for latch wait operations. | off | No | - |
reorder_joins_limit | Sets the number of joins at which the optimizer should stop attempting to reorder. | 8 | No | sql.defaults.reorder_joins_limit |
require_explicit_primary_keys | Controls whether CREATE TABLE statements should error out if no primary key is provided. | off | No | sql.defaults.require_explicit_primary_keys.enabled |
results_buffer_size | Specifies the size at which the pgwire results buffer will self-flush. | - | Yes | - |
role | The current role for the session. | none | No | - |
row_security | Controls whether row level security is enabled. | on | No | - |
search_path | Sets the list of namespaces to search when resolving unqualified names. | "$user", public | No | - |
serial_normalization | Controls how `SERIAL` columns are normalized. | rowid | No | sql.defaults.serial_normalization |
server_encoding | Reports the database encoding (always UTF8). This cannot be changed. | UTF8 | Yes | - |
server_version | Reports the server version string. | 13.0.0 | Yes | - |
server_version_num | Reports the server version as an integer. | 130000 | Yes | - |
session_id | The unique identifier for the current session. | - | Yes | - |
show_primary_key_constraint_on_not_visible_columns | Controls whether SHOW CONSTRAINTS and pg_catalog.pg_constraint include primary key constraints with only hidden columns. | on | No | - |
sql_safe_updates | When enabled, disallows potentially unsafe SQL statements: `DROP DATABASE` of a non-empty database, `DELETE` and `UPDATE` without a `WHERE` clause (unless a `LIMIT` clause is included), `SELECT ... FOR UPDATE`/`FOR SHARE` without a `WHERE` or `LIMIT` clause, `ALTER TABLE ... DROP COLUMN`, and converting an existing table to `REGIONAL BY ROW` unless a region column has already been added. | off | No | - |
ssl | Controls whether SSL is enabled. | - | Yes | - |
standard_conforming_strings | Controls whether strings conform to SQL standard. | on | No | - |
statement_timeout | Sets the maximum allowed duration for a statement. The query is cancelled if it exceeds this limit. | 0s | No | sql.defaults.statement_timeout |
streamer_always_maintain_ordering | Controls whether the SQL users of the DistSQL Streamer should always maintain ordering, even when not strictly necessary. | off | No | - |
streamer_enabled | Controls whether the DistSQL Streamer API can be used. | on | No | sql.distsql.use_streamer.enabled |
streamer_head_of_line_only_fraction | Controls the fraction of the DistSQL streamer's memory budget used for the head-of-the-line request when the eager memory usage limit has been exceeded. | 0.8 | No | - |
streamer_in_order_eager_memory_usage_fraction | Controls the fraction of the DistSQL streamer's memory budget that might be used for issuing requests eagerly in the InOrder mode. | 0.5 | No | - |
streamer_out_of_order_eager_memory_usage_fraction | Controls the fraction of the DistSQL streamer's memory budget that might be used for issuing requests eagerly in the OutOfOrder mode. | 0.8 | No | - |
strict_ddl_atomicity | When enabled, causes errors when DDL operations inside an explicit transaction cannot be guaranteed to be performed atomically, preventing partial transaction commits. | off | No | - |
stub_catalog_tables | Controls whether catalog tables are stubbed out. | on | No | sql.defaults.stub_catalog_tables.enabled |
synchronize_seqscans | Controls whether synchronized sequential scans are enabled (compatibility setting). | on | No | - |
synchronous_commit | synchronous_commit is included for compatibility with Postgres; changing it has no effect. | on | No | - |
system_identity | Indicates the original name of the client presented to pgwire before it was mapped to a SQL identifier. | - | Yes | - |
tcp_keepalives_count | Sets the number of TCP keepalive probes to send before giving up on the connection. | 0 | No | - |
tcp_keepalives_idle | Sets the time interval in seconds before TCP keepalive probes are sent on idle connections. | 0 | No | - |
tcp_keepalives_interval | Sets the time interval in seconds between TCP keepalive probes. | 0 | No | - |
tcp_user_timeout | Sets the time in milliseconds before a connection is forcibly closed if data is not acknowledged. | 0 | No | - |
testing_optimizer_cost_perturbation | Controls the random cost perturbation factor for producing non-optimal query plans during testing. | 0 | No | - |
testing_optimizer_disable_rule_probability | Sets the probability of randomly disabling non-essential optimizer transformation rules for testing. | 0 | No | - |
testing_optimizer_inject_panics | Controls whether random panics are injected during optimization to test error-propagation. | off | No | - |
testing_optimizer_random_seed | Sets a random seed for the optimizer for testing by initializing an RNG with the given integer. | 0 | No | - |
testing_vectorize_inject_panics | Controls whether random panics are injected into vectorized execution to test error propagation. | off | No | - |
timezone | Sets the timezone used for parsing timestamps. | UTC | No | - |
tracing | Controls whether tracing is enabled for the session. | - | Yes | - |
transaction_isolation | The isolation level of the current transaction. Possible values are `SERIALIZABLE` and `READ COMMITTED`. Also allows the isolation level to change as long as queries have not been executed yet. | serializable | No | - |
transaction_priority | The priority level of the current transaction. Possible values are `LOW`, `NORMAL`, and `HIGH`. | - | Yes | - |
transaction_read_only | Controls whether the current transaction is read-only. | off | No | - |
transaction_rows_read_err | Sets the limit for the number of rows read by a SQL transaction which - once exceeded - will fail the transaction (0 means disabled). | 0 | No | sql.defaults.transaction_rows_read_err |
transaction_rows_read_log | Sets the threshold for the number of rows read by a SQL transaction which - once exceeded - will trigger a logging event. | 0 | No | sql.defaults.transaction_rows_read_log |
transaction_rows_written_err | The limit for the number of rows written by a SQL transaction which - once exceeded - will fail the transaction (0 means disabled). | 0 | No | sql.defaults.transaction_rows_written_err |
transaction_rows_written_log | Sets the threshold for the number of rows written by a SQL transaction which - once exceeded - will trigger a logging event. | 0 | No | sql.defaults.transaction_rows_written_log |
transaction_status | Reports the current transaction status. | - | Yes | - |
transaction_timeout | Sets the maximum duration a transaction is permitted to run before cancellation. | 0s | No | - |
troubleshooting_mode | When enabled, avoids performing additional work on queries such as collecting and emitting telemetry data. This is particularly useful when the cluster is experiencing issues, unavailability, or failure. | off | No | - |
unbounded_parallel_scans | Controls whether the TableReader DistSQL processors should parallelize scans across ranges. | off | No | - |
unconstrained_non_covering_index_scan_enabled | Controls whether unconstrained non-covering index scan access paths are explored by the optimizer. | off | No | - |
unsafe_allow_triggers_modifying_cascades | When enabled, allows row-level BEFORE triggers to modify or filter rows that are being updated or deleted as part of a cascading foreign key action. This is unsafe because it can lead to constraint violations. | off | No | - |
use_backups_with_ids | Controls whether backup operations use backup IDs. | off | No | - |
use_cputs_on_non_unique_indexes | Controls whether conditional puts (CPuts) at the KV layer are used on non-unique indexes. | off | No | - |
use_declarative_schema_changer | Controls whether the declarative schema changer is used. | on | No | sql.defaults.use_declarative_schema_changer |
use_improved_routine_dependency_tracking | Controls whether improved dependency tracking is used for stored procedures and routines. | on | No | - |
use_improved_routine_deps_triggers_and_computed_cols | Controls whether improved dependency tracking is used for routines involving triggers and computed columns. | on | No | - |
use_pre_25_2_variadic_builtins | Controls whether pre-25.2 variadic built-in function behavior is used. | off | No | - |
use_proc_txn_control_extended_protocol_fix | Controls whether a fix for procedure transaction control with the extended protocol is enabled. | on | No | - |
use_soft_limit_for_distribute_scan | Controls whether soft limits are used for distributing scans across nodes. | on | No | - |
use_swap_mutations | Controls whether swap mutations are used as an alternative mutation strategy. | off | No | - |
variable_inequality_lookup_join_enabled | Controls whether the optimizer should consider lookup joins with inequality conditions. | on | No | - |
vector_search_beam_size | Sets the beam width for vector search operations. | 32 | No | - |
vector_search_rerank_multiplier | Sets the multiplier for re-ranking candidates in vector search results. | 50 | No | - |
vectorize | Controls if and when the Executor executes queries using the columnar execution engine. Can be 'off', 'on', or 'experimental_always'. | on | No | sql.defaults.vectorize |
xmloption | Sets how XML data is to be implicitly parsed (compatibility setting). | content | No | - |
For session variables on features in preview, see Features in Preview.
Special syntax cases supported for compatibility:
| Syntax | Equivalent to |
|---|---|
SHOW TRANSACTION PRIORITY |
SHOW "transaction priority" |
SHOW TRANSACTION ISOLATION LEVEL |
SHOW "transaction isolation level" |
SHOW TIME ZONE |
SHOW "timezone" |
SHOW TRANSACTION STATUS |
SHOW "transaction status" |
Examples
Showing the value of a single session variable
> SHOW DATABASE;
database
------------
movr
(1 row)
Showing the value of all session variables
> SHOW ALL;
variable | value
----------------------+-------------------
application_name | $ cockroach demo
bytea_output | hex
client_encoding | UTF8
client_min_messages | notice
...
