Router - SPQR

The SPQR Router configuration can be specified in JSON, TOML, or YAML format. The configuration file passing as a parameter to run command:

spqr-router run --config ./examples/router.yaml

Refer to the pkg/config/router.go file for the most up-to-date configuration options.

General Settings

Setting	Description	Possible Values	Default
`log_level`	The level of logging output.	`debug`, `info`, `warning`, `error`, `fatal`	`info`
`pretty_logging`	Whether to write logs in a colorized, human-friendly format.	`true`, `false`	`false`
`daemonize`	Whether to run the router as a daemon.	`true`, `false`	`false`
`reuse_port`	Whether to create a socket with SO_REUSEPORT and SO_REUSEADDR options.	`true`, `false`	`false`
`use_systemd_notifier`	Whether to use systemd notifier.	`true`, `false`	`false`
`systemd_notifier_debug`	Whether to run systemd notifier in debug mode.	`true`, `false`	`false`
`with_coordinator`	Whether to run the router in a special coordinator mode.	`true`, `false`	`false`
`enable_role_system`	Whether to enable the role-based access control system.	`true`, `false`	`false`
`roles_file`	The file path to the roles configuration.	Any valid file path	(none)
`identity_range_size`	Size of range identifiers of sequence which router gets from coordinator.	int > 1	`0`
`use_metrics`	Enable prometheus metric exporter on port configured by `metric_port` setting.	`true`, `false`	`false`

Network Settings

Setting	Description	Possible Values	Default
`host`	The host address the router listens on.	Any valid hostname	(none)
`router_port`	The port number for the router.	Any valid port number	(none)
`router_ro_port`	The port number for the read-only queries router.	Any valid port number	(none)
`admin_console_port`	The port number for the admin console.	Any valid port number	(none)
`grpc_api_port`	The port number for the gRPC API.	Any valid port number	(none)
`metric_path`	The path for prometheus metric exporter	Any valid HTTP path	`/metric`
`metric_port`	The port for prometheus metric exporter	Any valid port number	(none)

Frontend Rules

Frontend rule is a specification of how clients connect to the router. Refer to the FrontendRule struct in the pkg/config/rules.go file for the most up-to-date configuration options.

Setting	Description	Possible Values	Default
`db`	The database name to which the rule applies	Any valid database name	(none)
`usr`	The user name for which the rule is applicable	Any valid username	(none)
`search_path`	The search path used for the database connection	Any valid search path	(none)
`auth_rule`	See General Auth Settings	Object of `AuthCfg`	(none)
`pool_mode`	The mode of connection pooling used	`SESSION`, `TRANSACTION`	(none)
`pool_discard`	Determines if `DISCARD ALL` will be issued after transaction end	`true`, `false`	`false`
`pool_rollback`	Specifies whether to execute a rollback on connections returned to the pool	`true`, `false`	`false`
`pool_prepared_statement`	Indicates if prepared statements should be pooled	`true`, `false`	`false`
`pool_default`	If set to true, uses the default pool settings defined elsewhere	`true`, `false`	`false`

Backend Rules

Backend Rule is a global setting that determines how the router connects to every shard. Refer to the BackendRule struct in the pkg/config/rules.go and pkg/config/auth_backend.go file for the most up-to-date configuration options.

Setting	Description	Possible Values	Default
`db`	The database name to connect to.	Any valid database name	(none)
`usr`	The username for database authentication.	Any valid username	(none)
`auth_rules`	A map of authentication rules for backend connections.	Map of string keys to `AuthBackendCfg` objects	(none)
`auth_rule`	The default auth rule. An object with `usr` and `password` fields	An `AuthBackendCfg` object	(none)
`pool_default`	Indicates if the connection should use the default pool settings.	`true`, `false`	`false`
`connection_limit`	The maximum number of connections allowed to the backend (host shard). `0` means use default.	Any integer value	`50`
`connection_retries`	The number of retries for a failed connection attempt. `0` means use default.	Any integer value	`10`
`connection_timeout`	The timeout duration for establishing connections to the backend. `0` means use default.	Any valid duration (e.g., `30s`, `1m`)	`1s`
`keep_alive`	The duration for keeping connections alive. `0` means use default.	Any valid duration (e.g., `30s`, `1m`)	`1s`
`tcp_user_timeout`	The TCP user timeout for backend connections. `0` means use default.	Any valid duration (e.g., `30s`, `1m`)	`9.5s`

Shards

Map of string to Shard objects. Refer to the Shard struct in the pkg/config/router.go file for the most up-to-date configuration options.

Setting	Description	Possible Values	Default
`hosts`	A list of host addresses for the shard.	Array of host addresses	(none)
`type`	Use `DATA` always	`DATA`, `WORLD`	(none)
`tls`	See auth.mdx	Object of `TLSConfig`	(none)

Statistics Settings

Collects query execution time statistics at specified percentile levels. The router tracks two independent timing metrics:

Router Time (time spent in SPQR router)
Shard Time (time waiting for backend database)

When empty, statistics collection is disabled.

Setting	Description	Possible Values	Default
`time_quantiles`	Array of time quantiles (percentiles) for query execution statistics.	Array of strings, e.g., `["0.75", "0.90", "0.999"]`	(none)
`time_quantiles_str`	Same as `time_quantiles`. This option is deprecated.	Array of strings, e.g., `["0.75", "0.90", "0.999"]`	(none)

Feature Flags

Setting	Description	Possible Values	Default
`maintain_params`	When enabled, forwards all session parameters (`application_name`, `search_path`, `lock_timeout`, etc.) to shard backends during query deployment. Can be overridden by the `__spqr__maintain_params` hint.	`true`, `false`	`false`
`world_shard_fallback`	Whether to enable fallback to world shard.	`true`, `false`	`false`

Query Routing Settings

Query routing settings are nested under the query_routing configuration key and control how queries are routed across shards.

Many of these settings can be overridden at runtime using routing hints.

Refer to the QRouter struct in the pkg/config/router.go file for the most up-to-date configuration options.

Setting	Description	Possible Values	Overridable by Hint	Default
`query_routing.default_route_behaviour`	Whether to explicitly block multishard queries. Can be overridden by the __spqr__default_route_behaviour hint.	`BLOCK`, `ALLOW`	Yes	`ALLOW`
`query_routing.default_target_session_attrs`	Default target_session_attrs for connections. Can be overridden by the __spqr__target_session_attrs hint.	`read-write`, `smart-read-write`, `read-only`, `prefer-standby`, `any`	Yes	`read-write`
`query_routing.enhanced_multishard_processing`	Enables engine V2 with enhanced multishard query processing, including automatic batch INSERT/COPY splitting across shards. Can be overridden by the __spqr__engine_v2 hint.	`true`, `false`	Yes	`false`
`query_routing.always_check_rules`	Whether to always check the routing rules for every query. This only makes sense in a single-shard deployment.	`true`, `false`	No	`false`
`query_routing.auto_route_ro_on_standby`	Whether to automatically route read-only queries to standby servers. Currently only supported for single-shard topology.	`true`, `false`	No	`false`

Transaction Settings

Setting	Description	Possible Values	Overridable by Hint	Default
`default_commit_strategy`	Default commit strategy for distributed transactions. Can be overridden per session with the __spqr__commit_strategy hint. See Distributed Transactions for details.	`best-effort`, `1pc`, `2pc`	Yes	(none)

Mode Settings

Setting	Description	Possible Values	Default
`pool_mode`	Defines the pool modes.	`SESSION`, `TRANSACTION`	(none)
`shard_type`	Defines the shard types.	`DATA`, `WORLD`	(none)
`router_mode`	TL;DR Use `PROXY` always. Defines the router modes. Local mode acts like a connection pooler, Proxy mode acts like a query router in sharded installation.	`LOCAL`, `PROXY`	(none)

Debug Settings

Setting	Description	Possible Values	Overridable by Hint	Default
`show_notice_messages`	Whether to show notice messages. Can be overridden by the __spqr__reply_notice hint.	`true`, `false`	Yes	`false`
`display_greeting`	Whether to display greeting message on client connection.	`true`, `false`	No	`true`
`notice_message_format`	Format template for notice messages. Supported placeholders: `{shard}`, `{host}`, `{hostname}`, `{port}`, `{user}`, `{db}`, `{pid}`, `{az}`, `{id}`, `{tx_status}`, `{tx_served}`. Examples: `"{shard}"`, `"{host}"`, `"{shard}@{hostname}:{port}"`, `"{shard}@{host} (user={user}, db={db})"`	Any valid template string	No	`"{shard}@{host}"`
`pid_filename`	The file name to store the process ID.	Any valid filepath	No	(none)
`log_filename`	The file name for logging output.	Any valid filepath	No	(none)
`pgproto_debug`	PostgreSQL protocol debug flag.	`true`, `false`	No	`false`
`log_min_duration_statement`	Log queries that exceed this duration. Set to `-1` to disable query logging by duration.	Duration in milliseconds (e.g., `100ms`, `1s`) or `-1` to disable	No	`-1`

DBpool Settings

Setting	Description	Possible Values	Default
`dbpool_cache_ttl`	Time-to-live for database pool cache entries	Any valid duration (e.g., `30s`, `1m`)	`5m`
`dbpool_check_interval`	Interval for checking database pool health and cleanup	Any valid duration (e.g., `30s`, `1m`)	`30s`
`dbpool_dead_check_interval`	Background health check interval for dead hosts (enables faster recovery)	Any valid duration (e.g., `15s`, `30s`) or a negative duration to disable	`15s`

QDB Settings

Setting	Description	Possible Values	Default
`use_init_sql`	Whether to execute commands from Init SQL file	`true`, `false`	`false`
`use_coordinator_init`	Whether to execute commands from Init SQL file on Router	`true`, `false`	`false`
`init_sql`	Path to SQL file that router will execute before the start	Any valid file path	(none)
`exit_on_init_sql`	Whether to exit if the parsing of the SQL file fails	`true`, `false`	`false`
`memqdb_backup_path`	MemQDB backup state path. MemQDB’s state restored if a file backup exists during the router startup. If there is no file, init.sql will be used	Any valid file path	(none)
`qdb_max_txn_ops`	Maximum number of subcommands allowed in a single qdb transaction. Default is 128. coordinator param `etcd_max_txn_ops` is stronger	Integer value in the range 0–65535

​General Settings

​Network Settings

​Frontend Rules

​Backend Rules

​Shards

​Statistics Settings

​Feature Flags

​Query Routing Settings

​Transaction Settings

​Mode Settings

​Debug Settings

​DBpool Settings

​QDB Settings

General Settings

Network Settings

Frontend Rules

Backend Rules

Shards

Statistics Settings

Feature Flags

Query Routing Settings

Transaction Settings

Mode Settings

Debug Settings

DBpool Settings

QDB Settings