In a previous tip, we learned that YSQL configuration parameters play an important role in optimizing and enhancing database performance.
And we saw how the YSQL SHOW command allows inspection of the current value of any parameter.
Another option to view run-time parameter settings is by querying the pg_settings system table.
Example:
yugabyte=# SELECT name, setting, short_desc FROM pg_settings WHERE name = 'max_connections';
name | setting | short_desc
-----------------+---------+----------------------------------------------------
max_connections | 300 | Sets the maximum number of concurrent connections.
(1 row)
We can also do wildcard searches! Say I want to see all of the YugabyteDB specific settings. They are prefixed with “yb_”.
yugabyte=# SELECT name, setting, short_desc FROM pg_settings WHERE name ILIKE 'yb%' ORDER BY name;
name | setting | short_desc
----------------------------------------------------------------+-------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
yb_allow_block_based_sampling_algorithm | on | Autoflag to allow YsqlSamplingAlgorithm::BLOCK_BASED_SAMPLING. Not to be touched by users.
yb_allow_dockey_bounds | on | If true, allow lower_bound/upper_bound fields of PgsqlReadRequestPB to be DocKeys. Only applicable for hash-sharded tables.
yb_allow_replication_slot_lsn_types | on | Allow specifying LSN type while creating replication slot
yb_allow_replication_slot_ordering_modes | off | Allow specifying ordering mode while creating replication slot
yb_allow_separate_requests_for_sampling_stages | on | Autoflag to allow using separate requests for block-based sampling stages. Not to be touched by users.
yb_ash_circular_buffer_size | 131072 | Size (in KiBs) of ASH circular buffer that stores the samples
yb_ash_sample_size | 500 | Number of samples captured from each component per sampling event
yb_ash_sampling_interval_ms | 1000 | Time (in milliseconds) between two consecutive sampling events
yb_binary_restore | off | Enter a special mode designed specifically for YSQL binary restore.
yb_bnl_batch_size | 1024 | Batch size of nested loop joins
yb_bnl_enable_hashing | on | Enables batched nested loop joins to use hashing to process its matches.
yb_bnl_optimize_first_batch | on | Enables batched nested loop joins to predict the size of its first batch and optimize if it's smaller than yb_bnl_batch_size.
yb_bypass_cond_recheck | on | If true then condition rechecking is bypassed at YSQL if the condition is bound to DocDB.
yb_ddl_rollback_enabled | on | If set, any DDL that involves DocDB schema changes will have those changes rolled back upon failure.
yb_ddl_transaction_block_enabled | off | If true, DDL operations in YSQL will execute within the active transaction block instead of their separate transactions.
yb_debug_log_catcache_events | off | Log details for every catalog cache event such as a cache miss or cache invalidation/refresh.
yb_debug_log_docdb_error_backtrace | off | Append stacktrace information to errors received from DocDB.
yb_debug_log_docdb_requests | off | Log the contents of all internal (protobuf) requests to DocDB.
yb_debug_log_internal_restarts | off | Log details for internal restarts such as read-restarts, cache-invalidation restarts, or txn restarts.
yb_debug_log_snapshot_mgmt | off | Log details about snapshot management such as pushing/popping a snapshot and picking a new snapshot.
yb_debug_original_backtrace_format | off | Use original Postgres functions to create and format the stacktrace
yb_default_copy_from_rows_per_transaction | 20000 | Sets the batch number of rows to copy from the source to table.
yb_default_replica_identity | CHANGE | Default replica identity at the time of table creation
yb_disable_auto_analyze | off | Run 'ALTER DATABASE <name> SET yb_disable_auto_analyze=on' to disable auto analyze on that database. Set it to off to resume auto analyze. Setting this GUC via any other method will throw a WARNING message
yb_disable_catalog_version_check | off | Disable checking that read requests from this pg backend have the latest catalog version.
yb_disable_ddl_transaction_block_for_read_committed | off | If true, DDL operations in READ COMMITTED mode will be executed in a separate DDL transaction instead of the as part of the enclosing transaction block even if ysql_yb_ddl_transaction_block_enabled is true. In other words, for Read Committed, fall back to the mode when ysql_yb_ddl_transaction_block_enabled is false.
yb_disable_transactional_writes | off | Sets the boolean flag to disable transaction writes.
yb_disable_wait_for_backends_catalog_version | off | Disable waiting for backends to have up-to-date pg_catalog. This could cause correctness issues.
yb_effective_transaction_isolation_level | read committed | [DEPRECATED - instead use the yb_get_effective_transaction_isolation_level() function]. Shows the effective YugabyteDB transaction isolation level used by the current active transaction in the session.
yb_enable_add_column_missing_default | on | Enable using the default value for existing rows after an ADD COLUMN ... DEFAULT operation.
yb_enable_advisory_locks | on | DEPRECATED - Enable advisory lock feature
yb_enable_alter_table_rewrite | on | Enable ALTER TABLE rewrite operations
yb_enable_ash | on | Enable Active Session History for sampling and instrumenting YSQL and YCQL queries, and various background activities.
yb_enable_base_scans_cost_model | on | Enables YB cost model for Sequential and Index scans. DEPRECATED: This setting is deprecated and will be removed in a future release. Use "yb_enable_cbo" instead.
yb_enable_batchednl | on | Enables the planner's use of batched nested-loop join plans.
yb_enable_bitmapscan | on | Enables the planner's use of YB bitmap-scan plans.
yb_enable_cbo | on | Enable YB cost model.
yb_enable_consistent_replication_from_hash_range | off | Enable replication slot consumption of consistent changes from a hash range of table.
yb_enable_create_with_table_oid | off | Enables the ability to set table oids when creating tables or indexes.
yb_enable_ddl_atomicity_infra | on | Used along side with yb_ddl_rollback_enabled to control whether DDL atomicity is enabled.
yb_enable_distinct_pushdown | on | Push supported DISTINCT operations to DocDB.
yb_enable_docdb_tracing | off | Enables tracing for the commands in this session.
yb_enable_docdb_vector_type | on | Autoflag to enable using the DocDB Vector type. Not to be touched by users.
yb_enable_expression_pushdown | on | Push supported expressions down to DocDB for evaluation.
yb_enable_extended_sql_codes | off | Allow to return to the client SQL status codes defined by YugabyteDB (YBxxx). Those codes are used internally to determine if transparent retry is possible. If disabled, they are replaced with similar Postgres defined codes.
yb_enable_fkey_catcache | on | Enable preloading of foreign key information into the relation cache.
yb_enable_geolocation_costing | on | Allow the optimizer to cost and choose between duplicate indexes based on locality
yb_enable_hash_batch_in | on | GUC variable that enables batching RPCs of generated for IN queries on hash keys issued to the same tablets.
yb_enable_index_aggregate_pushdown | on | Push supported index aggregate operations to DocDB.
yb_enable_inplace_index_update | on | Enables the in-place update of non-key columns of secondary indexes when key columns of the index are not updated. This is useful when updating the included columns in a covering index among others.
yb_enable_invalidate_table_cache_entry | on | Enable invalidation of individual table cache entry on catalog cache refresh.
yb_enable_invalidation_messages | on | Enable invalidation messages
yb_enable_memory_tracking | on | Enables tracking of memory consumption of the PostgreSQL process. This enhances garbage collection behaviour and memory usage observability.
yb_enable_nop_alter_role_optimization | on | Enable nop alter role statement optimization to avoid catalog version increment if the alter role statement does not involve any change.
yb_enable_optimizer_statistics | off | Enables use of the PostgreSQL selectivity estimation which utilizes table statistics collected with ANALYZE. When disabled, a simpler heuristics based selectivity estimation is used. DEPRECATED: This settting is deprecated and will be removed in a future release. Use "yb_enable_cbo" instead.
yb_enable_parallel_append | on | Enables the planner's use of parallel append plans if YB is enabled.
yb_enable_parallel_scan_colocated | off | When set, allows parallel scan of the colocated relations
yb_enable_parallel_scan_hash_sharded | off | When set, allows parallel scan of the hash sharded relations
yb_enable_parallel_scan_range_sharded | off | When set, allows parallel scan of the range sharded relations
yb_enable_parallel_scan_system | off | When set, allows parallel scan of the system relations
yb_enable_pg_locks | on | Enable the pg_locks view. This view provides information about the locks held by active postgres sessions.
yb_enable_pg_stat_statements_rpc_stats | off | If true, enable RPC execution time stats for pg_stat_statements.
yb_enable_planner_trace | off | Enables planner tracing.
yb_enable_query_diagnostics | off | Enables the collection of query diagnostics data for YSQL queries, facilitating the creation of diagnostic bundles.
yb_enable_replica_identity | on | Allow changing replica identity via ALTER TABLE command
yb_enable_replication_commands | on | Enable the replication commands for Publication and Replication Slots.
yb_enable_replication_slot_consumption | on | Enable consumption of changes via replication slots. This feature is currently in active development and should not be enabled.
yb_enable_saop_pushdown | on | Push supported scalar array operations down to DocDB for evaluation.
yb_enable_sequence_pushdown | on | Allow nextval() to fetch the value range and advance the sequence value in a single operation.
yb_enable_update_reltuples_after_create_index | on | Enables update of reltuples in pg_class for the base table and index after creating the index. When disabled, reltuples are not updated during concurrent index creation and only index reltuples are updated during non-concurrent index creation.
yb_enable_upsert_mode | off | Sets the boolean flag to enable or disable upsert mode for writes.
yb_explain_hide_non_deterministic_fields | off | If set, all fields that vary from run to run are hidden from the output of EXPLAIN
yb_explicit_row_locking_batch_size | 1024 | Batch size of explicit row locking
yb_extension_upgrade | off | Set to true when upgrading extensions during a YSQL major version upgrade.
yb_fast_path_for_colocated_copy | off | Enable fast-path transaction for copy on colocated tables. For testint now.
yb_fetch_row_limit | 1024 | Maximum number of rows to fetch per scan. 0 = No limit
yb_fetch_size_limit | 0 | Maximum size of a fetch response. 0 = No limit
yb_fk_references_cache_limit | 65535 | Sets the maximum size for the FK reference cache filled by the INSERT, SELECT ... FOR KEY SHARE or similar statmements
yb_follower_read_staleness_ms | 30000 | Sets the staleness (in ms) to be used for performing follower reads.
yb_follower_reads_behavior_before_fixing_20482 | off | Controls whether ysql follower reads that is enabled inside a transaction block should take effect in the same transaction or not. Prior to fixing #20482 the behavior was that the change does not affect the current transaction but only affects subsequent transactions. The flag is intended to be used if there is a customer who relies on the old behavior.
yb_force_catalog_update_on_next_ddl | off | Make the next DDL update the catalog in force mode which allows it to operate even during ysql major catalog upgrades. WARNING: This is a dangerous option and should be used only for DDLs on temp tables, and other transient objects.
yb_force_tablespace_locality | off | Forces use of tablespace-based locality over region locality.
yb_force_tablespace_locality_oid | 0 | Tablespace used for tablespace-based locality. Picked automatically if InvalidOid (default).
yb_format_funcs_include_yb_metadata | off | Include DocDB metadata (such as tablet splits) in formatting functions exporting system catalog information.
yb_hinted_uids | | Node UIDS to prefer in cost comparisons.
yb_ignore_freeze_with_copy | on | Ignore the FREEZE flag on COPY FROM command.
yb_ignore_pg_class_oids | on | Ignores requests to set pg_class OIDs in yb_binary_restore mode
yb_ignore_read_time_in_walsender | off | When set, walsender will fetch the publication as of current time if it encounters any failures while reading the catalog tables as of yb_read_time
yb_ignore_relfilenode_ids | on | Ignores requests to set relfilenode IDs in yb_binary_restore mode
yb_index_state_flags_update_delay | 0 | Delay in milliseconds between stages of online index build.
yb_insert_on_conflict_read_batch_size | 1024 | Maximum batch size for arbiter index reads during INSERT ON CONFLICT.
yb_invalidation_message_expiration_secs | 10 | Invalidation messages expiration time in catalog table pg_yb_invalidation_messages.
yb_is_client_ysqlconnmgr | off | Identifies that connection is created by Ysql Connection Manager.
yb_lock_pk_single_rpc | off | Use single RPC to select and lock when PK is specified.
yb_locks_max_transactions | 16 | Sets the maximum number of transactions for which to return rows in pg_locks.
yb_locks_min_txn_age | 1000 | Sets the minimum transaction age for results from pg_locks.
yb_locks_txn_locks_per_tablet | 200 | Sets the maximum number of rows per transaction per tablet to return in pg_locks.
yb_log_heap_snapshot_on_exit_threshold | -1 | When a process exits, log a peak heap snapshot showing the approximate memory usage of each malloc call stack if its peak RSS is greater than or equal to this threshold in KB. Set to -1 to disable.
yb_major_version_upgrade_compatibility | 0 | The compatibility level to use during a YSQL Major version upgrade. Allowed values are 0 and 11.
yb_make_next_ddl_statement_nonbreaking | off | When set, the next ddl statement will not cause running transactions to abort. This only affects the next ddl statement and resets automatically.
yb_make_next_ddl_statement_nonincrementing | off | DEPRECATED - When set, the next ddl statement will not cause catalog version to increment. This only affects the next ddl statement and resets automatically.
yb_max_num_invalidation_messages | 4096 | Max number of invalidation messages supported for incremental catalog cache refresh.
yb_max_query_layer_retries | 60 | Max number of internal query layer retries of a statement
yb_mixed_mode_expression_pushdown | on | Enables expression pushdown for queries in mixed mode of a YSQL Major version upgrade.
yb_mixed_mode_saop_pushdown | off | Enable pushdown of scalar array operation expressions in mixed mode of a YSQL Major version upgrade. For example, IN, ANY, ALL.
yb_neg_catcache_ids | | Comma separated list of additional sys cache ids that are allowed to be negatively cached.
yb_network_fetch_cost | 4 | Sets the planner's estimate of the fixed cost of fetching a batch of rows from a YB relation
yb_non_ddl_txn_for_sys_tables_allowed | off | Enables the use of regular transactions for operating on system catalog tables in case a DDL transaction has not been started.
yb_parallel_range_rows | 10000 | The number of rows to plan per parallel worker
yb_parallel_range_size | 1048576 | Approximate size of parallel range for DocDB relation scans
yb_pg_batch_detection_mechanism | detect_by_peeking | The drivers use message protocol to communicate with PG. The driver does not inform PG in advance about a Batch execution. We need to identify a batch because in that case the single-shard optimization should be disabled. Postgres drivers pipeline messages and we exploit this to peek the message following 'Execute' to detect a batch. This may lead to some unforeseen bugs, so this GUC provides a way to disable the single-shard optimization completely or go back to thebehavior before #16446 was fixed.
yb_pg_locks_integrate_advisory_locks | on | Enables pg_locks to integrate and display advisory locks details correctly.
yb_pg_metrics.log_accesses | off | Log each request received by the YSQL webserver
yb_pg_metrics.log_tcmalloc_stats | off | Log each request received by the YSQL webserver
yb_pg_metrics.webserver_profiler_sample_period_bytes | 1048576 | The interval at which Google TCMalloc should sample allocations in the YSQL webserver. If this is 0, sampling is disabled.
yb_planner_custom_plan_for_partition_pruning | on | If enabled, choose custom plan over generic plan for prepared statements based on the number of partition pruned.
yb_plpgsql_disable_prefetch_in_for_query | off | Disable prefetching in a PLPGSQL FOR loop over a query.
yb_prefer_bnl | on | If enabled, planner will force a preference of batched nested loop join plans over classic nested loop join plans.
yb_pushdown_is_not_null | on | If true, IS NOT NULL is pushed down.
yb_pushdown_strict_inequality | on | If true, strict inequality filters are pushed down.
yb_query_diagnostics_bg_worker_interval_ms | 1000 | Time (in milliseconds) for which the query diagnostic's background worker sleeps
yb_query_diagnostics_circular_buffer_size | 64 | Size of query diagnostics circular buffer that stores statuses of bundles
yb_query_diagnostics_disable_database_connection_bgworker | off | This disables creating extra bgworker which creates database connection for query diagnostics. If this is set to true, ASH and schema details are not dumped
yb_read_after_commit_visibility | strict | Control read-after-commit-visibility guarantee.
yb_read_from_followers | off | Allow any statement that generates a read request to go to any node.
yb_read_time | 0 | Allows querying the database as of a point in time in the past. Takes a unix timestamp in microseconds. Zero means reading data as of current time.
yb_refresh_matview_in_place | off | Refresh materialized views in place.
yb_reorderbuffer_max_changes_in_memory | 4096 | Maximum number of changes kept in memory per transaction in reorder buffer, which is used in streaming changes via logical replication. After that, changes are spooled to disk.
yb_sampling_algorithm | block_based_sampling | Which sampling algorithm to use for YSQL. full_table_scan - scan the whole table and pick random rows, block_based_sampling - sample the table for a set of blocks, then scan selected blocks to form a final rows sample.
yb_silence_advisory_locks_not_supported_error | off | Silence the advisory locks error message.
yb_skip_redundant_update_ops | on | Enables the comparison of old and new values of columns specified in the SET clause of YSQL UPDATE queries to skip redundant secondary index updates and redundant constraint checks.
yb_speculatively_execute_pl_statements | off | If enabled, procedural language statements may be speculatively executed when it is safe to do so without waiting for the successful completion of previous statements. This allows any writes produced by triggers to be batched alongside their parent data-modifying writes such that the number of storages flushes may be minimized.
yb_tcmalloc_sample_period | 1048576 | TCMalloc sample interval in bytes, i.e. approximately how many bytes between sampling allocation call stacks
yb_test_block_index_phase | | Block the given index creation phase.
yb_test_collation | off | When set, inject code to make psql output stable across linux and mac.
yb_test_delay_after_applying_inval_message_ms | 0 | When > 0, add a delay after applying invalidation messages.
yb_test_delay_next_ddl | 0 | When set, the next DDL will be delayed by this many ms prior to commit.
yb_test_delay_set_local_tserver_inval_message_ms | 0 | When > 0, add a delay before calling YBCPgSetTserverCatalogMessageList.
yb_test_fail_all_drops | off | When set, all drops will fail
yb_test_fail_index_state_change | | Fails index backfill at given stage.
yb_test_fail_next_ddl | off | When set, the next DDL will fail right before commit.
yb_test_fail_next_inc_catalog_version | off | When set, the next increment catalog version will fail right before it's done. This only works when catalog version is stored in pg_yb_catalog_version.
yb_test_fail_table_rewrite_after_creation | off | When set, DDLs that rewrite tables/indexes will fail after the new table is created.
yb_test_index_check_num_batches_per_snapshot | -1 | Used to test yb_index_check()
yb_test_inval_message_portability | off | When set, fill padding bytes with zeros when creating a shared invalidation message.
yb_test_make_all_ddl_statements_incrementing | off | When set, all DDL statements will cause the catalog version to increment. This mainly affects CREATE commands such as CREATE TABLE, CREATE VIEW, and CREATE SEQUENCE.
yb_test_planner_custom_plan_threshold | 5 | The number of times to force custom plan generation for prepared statements before considering a generic plan.
yb_test_preload_catalog_tables | off | When set, force a full catalog cache refresh before executing the next top level statement.
yb_test_reset_retry_counts | -1 | Restricts the number of retries for transaction conflicts. For testing purposes.
yb_test_slowdown_index_check | off | Slows down yb_index_check() by sleeping for 1s after processing every row. Used in tests to simulate long running yb_index_check().
yb_test_stay_in_global_catalog_version_mode | off | When set, this PG backend will stay in global catalog version mode. Used in testing to simulate a lagging PG backend during the finalization phase of cluster upgrade to a new release that has the per-database catalog version mode on by default.
yb_test_system_catalogs_creation | off | Relaxes some internal sanity checks for system catalogs to allow creating them.
yb_test_table_rewrite_keep_old_table | off | When set, DDLs that rewrite tables/indexes will not drop the old relfilenode/DocDB table.
yb_test_ybgin_disable_cost_factor | 2 | The multiplier to disable_cost to add when costing ybgin index scans that may not be supported.
yb_toast_catcache_threshold | 2048 | Size threshold in bytes for a catcache tuple to be compressed.
yb_transaction_priority | 0.000000000 (Normal priority transaction) | [DEPRECATED - instead use the yb_get_current_transaction_priority() function]. Gets the transaction priority used by the current active distributed transaction in the session. If no
distributed transaction is active, return 0
yb_transaction_priority_lower_bound | 0 | Sets lower bound for priority used by transactions of this session
yb_transaction_priority_upper_bound | 1 | Sets upper bound for priority used by transactions of this session
yb_update_max_cols_size_to_compare | 10240 | Maximum size in bytes of columns whose data is to be compared while seeking to optimize updates. If set to 0, no size limit is applied.
yb_update_num_cols_to_compare | 50 | Maximum number of columns whose data is to be compared while seeking to optimize updates. If set to 0, all applicable columns in the table will be compared.
yb_update_optimization_infra | on | Enables optimizations of YSQL UPDATE queries. This includes (but not limited to) skipping redundant secondary index updates and redundant constraint checks.
yb_upgrade_to_pg15_completed | on | Indicates the state of YSQL major upgrade to PostgreSQL version 15. Do not modify this manually.
yb_use_hash_splitting_by_default | on | Enables hash splitting as the default method for primary key and index sorting in LSM indexes
yb_use_internal_auto_analyze_service_conn | off | [Internal Only GUC] - Help a backend identify that this is a connection from the internal Auto-Analyze service
yb_use_tserver_key_auth | off | If set, the client connection will be authenticated via 'yb-tserver-key' auth
yb_user_ddls_preempt_auto_analyze | on | If object locking is off (i.e., enable_object_locking_for_table_locks=false), concurrent DDLs might face a conflict error on the catalog version increment at the end after doing all the work. Setting this flag enables a fail-fast strategy by locking the catalog version at the start of DDLs, causing conflict errors to occur before useful work is done. This flag is only applicable without object locking. If object locking is enabled, it ensures that concurrent DDLs block on each other for serialization. Also, this flag is valid only if ysql_enable_db_catalog_version_mode and yb_enable_invalidation_messages are enabled.
yb_wait_for_backends_catalog_version_timeout | 300000 | Timeout in milliseconds to wait for backends to reach desired catalog versions.
yb_walsender_poll_sleep_duration_empty_ms | 10 | Time in milliseconds for which Walsender waits before fetching the next batch of changes from the CDC service in case the last received response was empty.
yb_walsender_poll_sleep_duration_nonempty_ms | 1 | Time in milliseconds for which Walsender waits before fetching the next batch of changes from the CDC service in case the last received response was non-empty.
yb_whitelist_extra_statements_for_pl_speculative_execution | off | If enabled, additional procedural language constructs are whitelisted for use in speculative execution.
yb_xcluster_automatic_mode_target_ddl | off | Used to identify DDLs executed in Automatic xCluster mode target universe. For example, DDL operations will skip the data loading phase, including table rewrites and nonconcurrent indexes. Sequence restarts via TRUNCATE TABLE are also skipped.WARNING: Incorrect usage will result in data loss.
yb_xcluster_consistency_level | database | Controls the consistency level of xCluster replicated databases.
yb_xcluster_ddl_replication.TEST_replication_role_override | | Test override for replication role.
yb_xcluster_ddl_replication.ddl_queue_primary_key_ddl_end_time | | Internal use only: Used by HandleTargetDDLEnd function.
yb_xcluster_ddl_replication.ddl_queue_primary_key_query_id | | Internal use only: Used by HandleTargetDDLEnd function.
yb_xcluster_ddl_replication.enable_manual_ddl_replication | off | Temporarily disable automatic xCluster DDL replication - DDLs will have to be manually executed on the target.
(176 rows)
🔵 Updated for YugabyteDB 2025.2
The meta data from the
pg_settings system table above is from YugabyteDB 2025.2.Have Fun!
