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:

  SELECT count(*) FROM articles /* __spqr__execute_on: shard2 */;
  

• Via SET-RESET interface:

  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

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 router configuration setting.

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)
Multi-shard UPDATE/DELETE	Modify data across all shards
Reference table writes	INSERT/UPDATE/DELETE on reference tables
Transaction expansion	Transactions can span multiple shards
Two-phase commit	Atomic commits across shards (details)

Examples:

-- 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 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.

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 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

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.

__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 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:

-- 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.

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 router configuration setting.

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.

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.

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.

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.

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.

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)

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.

__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 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 > 0 on all shards

Examples:

-- 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.

__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.