> ## Documentation Index > Fetch the complete documentation index at: https://docs.pg-sharding.tech/llms.txt > Use this file to discover all available pages before exploring further. # Routing hints ## Routing hints aka virtual parameters The SPQR router supports runtime hints and behavior configuration parameters. They behave differently depending on how users apply them in their work. There are two main ways to work with virtual parameters: * **Comment in the SQL statement**: ```sql theme={null} SELECT count(*) FROM articles /* __spqr__execute_on: shard2 */; ``` * **Via SET-RESET interface**: ```sql theme={null} SET __spqr__default_route_behaviour TO 'ALLOW'; SHOW __spqr__default_route_behaviour; default route behaviour ------------------------- ALLOW (1 row) RESET __spqr__default_route_behaviour; ``` ### Scope and priority Many routing hints can override corresponding configuration settings from the router config file. When multiple sources define the same parameter, SPQR uses the following priority order (highest to lowest): 1. **Transaction-level SET** - SET statements executed inside an explicit transaction (BEGIN...COMMIT) 2. **SQL comment hints** - Hints specified as SQL comments in the query (e.g., `/* __spqr__execute_on: shard2 */`) 3. **Session-level SET** - SET statements executed outside a transaction 4. **Router configuration** - Default values from the router config file (e.g., `query_routing.default_route_behaviour`) ## Example ```sql theme={null} db1=# SHOW __spqr__commit_strategy; __spqr__commit_strategy ------------------------- best-effort (1 row) db1=# SET __spqr__commit_strategy TO '2pc'; SET db1=# SHOW __spqr__commit_strategy; __spqr__commit_strategy ------------------------- 2pc (1 row) db1=# BEGIN; BEGIN db1=*# SET __spqr__commit_strategy TO '1pc'; SET db1=*# show __spqr__commit_strategy; __spqr__commit_strategy ------------------------- 1pc (1 row) db1=*# ROLLBACK; ROLLBACK db1=# SHOW __spqr__commit_strategy; __spqr__commit_strategy ------------------------- 2pc (1 row) ``` ## List of hints ### \_\_spqr\_\_engine\_v2 Enables v2 routing engine functionality. This hint is currently under development and may be removed in future updates. This hint overrides the [`query_routing.enhanced_multishard_processing`](/configuration/router#query-routing-settings) router configuration setting. ```sql theme={null} SET __spqr__engine_v2 TO on; ``` **Features enabled by engine V2:** | Feature | Description | | ------------------------- | --------------------------------------------------------------------------------------------------- | | Multi-shard SELECT | `WHERE id = X OR id = Y` routes only to needed shards | | Set operations | `UNION`, `EXCEPT`, `INTERSECT` across shards | | CTEs | `WITH` clauses spanning multiple shards | | Batch INSERT | Multi-value INSERTs auto-split by shard ([details](/sharding/bulk#batch-insert)) | | Multi-shard UPDATE/DELETE | Modify data across all shards | | Reference table writes | INSERT/UPDATE/DELETE on [reference tables](/features/reference_tables) | | Transaction expansion | Transactions can span multiple shards | | Two-phase commit | Atomic commits across shards ([details](/sharding/distributed_transactions#2-two-phase-commit-2pc)) | **Examples:** ```sql theme={null} -- Multi-shard SELECT (routes to sh1 and sh2 only, not all shards) SELECT * FROM users WHERE id = 1 OR id = 100 /* __spqr__engine_v2: true */; -- UNION across shards SELECT id FROM users WHERE id = 1 UNION ALL SELECT id FROM users WHERE id = 300; -- Multi-shard UPDATE UPDATE users SET status = 'active' /* __spqr__engine_v2: true */; -- Multi-shard DELETE within transaction BEGIN; DELETE FROM users WHERE id IN (1, 100, 300) /* __spqr__engine_v2: true */; COMMIT; -- Reference table modification INSERT INTO ref_table VALUES (1, 'data') /* __spqr__engine_v2: true */; ``` Batch INSERT rewriting only works with simple protocol. Parameterized queries (extended protocol) are not supported for cross-shard batch inserts. ### \_\_spqr\_\_target\_session\_attrs The `target-session-attrs` parameter in PostgreSQL is used to specify the desired session state when connecting to a database. It is particularly useful in scenarios with multiple servers and failover configurations, where you want to ensure your session connects to the server with the desired role or state. This hint overrides the [`query_routing.default_target_session_attrs`](/configuration/router#query-routing-settings) router configuration setting. `target_session_attrs` and `target-session-attrs` are both aliases for `__spqr__target_session_attrs`. Possible values for `target-session-attrs`: * `read-write`: Connects to a server in read-write mode, and fails if the primary is unavailable. * `smart-read-write`: Connects to a server in read-write mode, and fails if the primary is unavailable. Automatically routes read-only queries to standbys (with prefer-standby semantics), if query\_routing: auto\_route\_ro\_on\_standby is set to true in configure file. * `read-only`: Connects only to read-only (in recovery) servers. Fails if none available. * `prefer-standby` or `prefer-replica`: Prefers connecting to standby servers. If none are available, it connects to primary. * `any`: Connect to any available server, regardless of state. ```sql theme={null} SELECT pg_is_in_recovery() /* target-session-attrs: read-write */; pg_is_in_recovery ------------------- f (1 row) SELECT pg_is_in_recovery() /* target-session-attrs: read-only */; pg_is_in_recovery ------------------- t (1 row) ``` ### \_\_spqr\_\_default\_route\_behaviour Controls whether multi-shard queries are allowed or blocked. This hint overrides the [`query_routing.default_route_behaviour`](/configuration/router#query-routing-settings) router configuration setting. Possible values: * `BLOCK` - Rejects queries that cannot be routed to a single shard (default in most configurations) * `ALLOW` - Permits multi-shard query execution ```sql theme={null} DROP DATABASE prod; ERROR: query is blocked due to the default_route_behavior. DROP DATABASE prod /* __spqr__default_route_behaviour: allow */; NOTICE: send query to shard(s) : shard1,shard2,shard3,shard4 ``` This parameter is particularly useful for: * DDL commands that need to run on all shards * Administrative queries * Development and testing scenarios For more details on transaction control, see [Distributed Transactions](/sharding/distributed_transactions). ### \_\_spqr\_\_maintain\_params Controls whether SPQR forwards session parameters (such as `application_name`, `lock_timeout`, `search_path`, `statement_timeout`, etc.) to shard backends when deploying queries. Without this hint, parameters set via `SET` are only stored in the router's session state and never reach the shards. This is particularly important when using `__spqr__execute_on` inside a transaction with session-level settings. This hint overrides the [`maintain_params`](/configuration/router#feature-flags) router configuration setting. Possible values: * `true` - Forward all session parameters to shard backends * `false` - Do not forward session parameters (default) When enabled, SPQR sends a `RESET ALL` followed by `SET` statements for each non-internal session parameter to all allocated shard connections. Internal parameters (`user`, `database`, `options`, `password`) and `__spqr__*` virtual parameters are excluded. **Examples:** ```sql theme={null} -- Enable parameter forwarding SET __spqr__maintain_params TO true; -- These will now be forwarded to the shard SET application_name = 'maintainer'; SET lock_timeout = '1800s'; BEGIN; SET __spqr__execute_on = 'shard1'; SELECT run_maintenance('my_table'); COMMIT; ``` ### \_\_spqr\_\_execute\_on The `__spqr__execute_on` parameter is used to specify the shard on which a query should be executed. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. ```sql theme={null} SELECT col1 FROM xx ORDER BY col1 /* __spqr__execute_on: shard2 */; ``` ### \_\_spqr\_\_reply\_notice Whether to show notice messages. This hint overrides the [`show_notice_messages`](/configuration/router#debug-settings) router configuration setting. ```sql theme={null} SET __spqr__reply_notice TO true; INSERT INTO xxtest_sw (id) VALUES(1), (2), (3); NOTICE: send query to shard(s) : sh1 ``` ### \_\_spqr\_\_sharding\_key The `__spqr__sharding_key` explicitly pass the sharding key to the SPQR Router. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. ```sql theme={null} INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 30*/; ``` ### \_\_spqr\_\_distribution The `__spqr__distribution` explicitly specify which distribution the SPQR router should use for query routing. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. ```sql theme={null} DELETE FROM users WHERE name = 'denchick' /* __spqr__distribution: ds1,__spqr__sharding_key: 123 */; ``` ### \_\_spqr\_\_distributed\_relation The `__spqr__distributed_relation` is name of distributed relation in context of which query meant to be executed. Is takes effect when distribution contains both CITY and MURMUR hash based sharding This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. ```sql theme={null} DELETE FROM users WHERE name = 'denchick' /* __spqr__distribution: ds1,__spqr__distributed_relation: users,__spqr__sharding_key: 123 */; ``` ### \_\_spqr\_\_auto\_distribution The `__spqr__auto_distribution` parameter is used to automatically adding new table in SPQR metadata without going into the spqr-console. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. Distributions with a single key column are only supported. The following key types are supported for the automatic creation of distributed tables: `integer`, `uuid`, and `varchar`. Hash distributions are currently not supported for auto-creating tables. ```sql theme={null} CREATE TABLE tax_rates ( country_code VARCHAR(2), state_code VARCHAR(2) ); ERROR: error processing query 'CREATE TABLE zz(i int, j int, k int);': distribution for relation "zz" not found CREATE TABLE tax_rates ( country_code VARCHAR(2), state_code VARCHAR(2) /* __spqr__auto_distribution: REPLICATED */; ); NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4 ``` ### \_\_spqr\_\_distribution\_key The `__spqr__distribution_key` parameter is used to automatically add a new table to the SPQR metadata without having to go into the spqr console. This parameter can only be used with the `__spqr__auto_distribution` hint. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. ```sql theme={null} CREATE TABLE orders (id int, item int) /* __spqr__auto_distribution: ds1, __spqr__distribution_key: id */; ``` ### \_\_spqr\_\_scatter\_query Forces a query to execute on all shards in the cluster, regardless of the sharding key. This is a runtime-only hint with no corresponding router configuration setting. It can only be specified via SQL comments or SET statements. Possible values: * `true` - Execute query on all shards * `false` - Normal routing behavior (default) ```sql theme={null} SELECT * FROM users /* __spqr__scatter_query: true */; -- Can also be set at session level SET __spqr__scatter_query TO true; ``` Scatter queries do not provide consistent cross-shard snapshots and can have significant performance impact. Use primarily for administrative or analytical purposes. For more details, see [Distributed Transactions](/sharding/distributed_transactions). ### \_\_spqr\_\_commit\_strategy Specifies the commit strategy for distributed transactions. This parameter is critical for ensuring data consistency when transactions span multiple shards. This hint overrides the [`default_commit_strategy`](/configuration/router#transaction-settings) router configuration setting. Possible values: * `best-effort` (default) - One-phase commit with no coordination between shards * `1pc` - Alias for `best-effort` * `2pc` - Two-phase commit with atomicity guarantees across shards Requirements for 2PC: * Enable `__spqr__engine_v2` hint * Set [max\_prepared\_transactions](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS) > 0 on all shards Examples: ```sql theme={null} -- Session-level setting SET __spqr__commit_strategy TO '2pc'; -- Statement-level hint BEGIN; INSERT INTO users (id, name) VALUES (1, 'Alice') /* __spqr__commit_strategy: 2pc */; COMMIT; ``` How it works: * 1pc/best-effort: Commits each shard independently. Fast but no atomicity across shards. * 2pc: Uses PostgreSQL's prepared transactions to ensure all-or-nothing commits across shards. For comprehensive information about transaction strategies, including when to use each mode, see [Distributed Transactions](/sharding/distributed_transactions). ### \_\_spqr\_\_preferred\_engine This is developer-only option. Only possible value currently is 'v2' This is a runtime-only hint with no corresponding router configuration setting. It is intended for internal development use only.