Router

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)

Availability Zone Settings

Setting	Description	Possible Values	Default
availability_zone	The router’s availability zone, matched against zone labels in shard host entries.	Any zone label	(none)
prefer_same_availability_zone	Prefer hosts in the router’s zone. Takes effect when availability_zone is non-empty.	true, false	false

When prefer_same_availability_zone is false or availability_zone is empty, zone labels do not affect host selection.

See Shards for host zone labels and a configuration example.

gRPC TLS Settings

client_tls configures outgoing internal gRPC connections, server_tls configures the internal gRPC API:

coordinator.yaml

client_tls:
  mode: tls # disabled, tls, or mtls
  root_ca_file: /path/to/ca.crt
  server_name: spqr.internal # optional certificate name override
  # cert_file and key_file are required for mtls
server_tls:
  mode: tls # disabled, tls, or mtls
  cert_file: /path/to/server.crt
  key_file: /path/to/server.key
  # client_ca_file is required for mtls

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 with optional availability zone labels and a connection priority.	Array of host:port[:availability_zone] [PRIORITY n] strings	(none)
type	Use DATA always	DATA, WORLD	(none)
tls	See auth.mdx	Object of TLSConfig	(none)

Hosts can be assigned a connection priority with the PRIORITY <n> suffix. A priority of -1 disables the host entirely (it is treated as dead); any other value is reserved for future use.

Example with availability zone preference enabled:

availability_zone: "us-east-1a"
prefer_same_availability_zone: true

shards:
  shard1:
    hosts:
      - "db1.example.com:5432:us-east-1a"
      - "db2.example.com:5432:us-east-1b"

SPQR prefers db1.example.com and can fall back to db2.example.com if no suitable host is available in us-east-1a. The selected host must satisfy target-session-attrs.

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, prefer-replica, any, dc-local	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
query_routing.use_spqrguard	Maintains spqrguard metadata when SPQR executes DDL.	true, false	No	false
query_routing.forbid_direct_shard_queries	Deprecated alias for query_routing.use_spqrguard.	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. 0 uses the QDB default of 128; a positive coordinator etcd_max_txn_ops value overrides this setting.	Integer value in the range 0–65535	0