# Auth settings Source: https://docs.pg-sharding.tech/configuration/auth auth, tls, frontend_tls, etc. ## General Settings Refer to the [pkg/config/auth.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/auth.go) file for the most up-to-date configuration options. | Setting | Description | Possible Values | | ------------- | ------------------------------------------------------------------------------------------------------------------------------------ | ---------------------------------------------------------- | | `auth_method` | Specifies the authentication method to be used. | `ok`, `notok`, `clear_text`, `md5`, `scram`, `ldap`, `gss` | | `password` | The password used for authentication methods that require one. This is applicable for methods like `clear_text`, `md5`, and `scram`. | Any string value | | `ldap_config` | This is required if `auth_method` is set to `ldap`. | See `GSS Auth Settings` | | `gss_config` | This is required if `auth_method` is set to `gss`. | See `LDAP Auth Settings` | ## Admin Console Auth Settings In router and coordinator `frontend_tls` field. Refer to the [pkg/config/tls.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/tls.go) file for the most up-to-date configuration options. | Setting Name | Description | Possible Values | | ---------------- | ------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------ | | `sslmode` | Determines the level of SSL/TLS protection to use for connections. | `disable`, `require`, `verify-ca`, `verify-full` | | `key_file` | Path to the private key file to be used for SSL/TLS connections. | Any valid filepath | | `cert_file` | Path to the certificate file to be used for SSL/TLS connections. | Any valid filepath | | `root_cert_file` | Path to the root certificate file. This is used to verify the server's certificate if sslmode is set to `verify-ca` or `verify-full`. | Any valid filepath | ## GSS Auth Settings Refer to the [pkg/config/gss.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/gss.go) file for the most up-to-date configuration options. | Setting Name | Description | Possible Values | | ----------------- | ------------------------------------------------------------------------------------------------------------------------- | ------------------ | | `krb_keytab_file` | Path to the Kerberos keytab file. This file is used for authenticating against a Kerberos server. | Any valid filepath | | `krb_realm` | Specifies the Kerberos realm. A realm is a network that is defined for authentication purposes. | Any valid realm | | `include_realm` | Determines if the realm should be included in the authentication process. This can affect how user names are interpreted. | `true`, `false` | ## LDAP Auth Settings Refer to the [pkg/config/ldap.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/ldap.go) file for the most up-to-date configuration options. | Setting Name | Description | Possible Values | | ------------------ | ----------------------------------------------------------------------------- | ----------------------- | | `ldap_auth_mode` | Specifies the LDAP authentication mode. | `UnencryptedMode` | | `ldap_conn_config` | Configuration for LDAP connection, including server URLs and connection mode. | `LDAPConnCfg` | | `servers` | List of LDAP server URLs. | array of strings (URLs) | | `prefix` | Prefix to be added to the username during authentication. | string | | `suffix` | Suffix to be added to the username during authentication. | string | | `bind_dn` | Distinguished Name (DN) to bind to the LDAP server. | string | | `bind_password` | Password for the bind DN. | string | | `base_dn` | Base DN under which to search for users. | string | | `search_attribute` | LDAP attribute to search against when looking up users. | string | | `search_filter` | Additional LDAP search filters. | string | # Balancer Source: https://docs.pg-sharding.tech/configuration/balancer The SPQR Balancer configuration can be specified in JSON, TOML, or YAML format. The configuration file passing as a parameter to run command: ``` spqr-balancer run --config ./examples/balancer.yaml ``` Work in progress. Refer to the [pkg/config/balancer.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/balancer.go) file for the most up-to-date configuration options. # Coordinator Source: https://docs.pg-sharding.tech/configuration/coordinator The SPQR Coordinator configuration can be specified in JSON, TOML, or YAML format. The configuration file passing as a parameter to run command: ``` spqr-coordinator run --config ./examples/coordinator.yaml ``` Refer to the [pkg/config/coordinator.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/coordinator.go) file for the most up-to-date configuration options. ## Coordinator 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` | | `qdb_addr` | Deprecated: single QDB server address. Use `qdb_addrs` instead. | Any valid address | (none) | | `host` | The host address the coordinator listens on. | Any valid hostname | (none) | | `coordinator_port` | The port number for the coordinator. | Any valid port number | (none) | | `grpc_api_port` | The port number for the gRPC API. | Any valid port number | (none) | | `frontend_tls` | See [auth.mdx](./auth). | Object of `TLSConfig` | (none) | | `frontend_rules` | The rules for frontend connections. | List of `FrontendRule` | (none) | | `shard_data` | Path to shard metadata used for data moves and distribution. | Any valid file path | (none) | | `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` | | `iteration_timeout` | Sleep duration between watchRouters iterations. Controls how frequently the coordinator checks router status and syncs metadata. | Duration string (e.g., `1s`, `5m`, `10m`) | `1s` | | `lock_iteration_timeout` | Sleep duration between attempts to acquire the coordinator lock when starting up. | Duration string (e.g., `500ms`, `1s`, `5s`) | `1s` | | `router_keepalive_time` | Interval for sending gRPC keepalive pings to routers. Prevents idle connection closure by network intermediaries. | Duration string (e.g., `15s`, `30s`, `1m`) | `30s` | | `router_keepalive_timeout` | Time to wait for keepalive ping response before considering connection dead. | Duration string (e.g., `10s`, `20s`) | `20s` | | `enable_role_system` | Whether to enable the [role-based access control system](./roles). | `true`, `false` | `false` | | `roles_file` | The file path to the [roles](./roles) configuration. | Any valid file path | (none) | | `etcd_max_send_bytes` | Maximum request size in bytes that the etcd client (QDB implementation) is allowed to send. | Integer (bytes), use `0` for the etcd default | `0` | | `etcd_max_txn_ops` | Maximum number of subcommands allowed in a single etcd transaction. Default is 128. | Integer value in the range 0–65535 | | | `data_move_disable_triggers` | Disable triggers during data move operations to speed up copying/deleting data. | `true`, `false` | `false` | | `data_move_bound_batch_size` | Maximum number of rows fetched per batch when bounded data moves are executed. | Positive integer | `10000` | | `data_move_query_log_level` | Log level for data move operations. | `debug`, `info`, `warning`, `error`, `fatal` | `"debug"` | | `data_move_await_pid_exception` | Whether to await PID exception during data moves. | `"true"`, `"false"` (as strings) | `"true"` | ## Coordinator Timing Settings ### Iteration Timeout The `iteration_timeout` setting controls how frequently the coordinator's watchRouters loop runs to monitor and manage router instances. This is one of the most important performance tuning parameters. #### What watchRouters Does On each iteration, the coordinator: 1. Queries QDB for the list of active routers 2. Connects to each router via gRPC (using cached connections) 3. Calls `GetRouterStatus()` to check router health 4. Syncs coordinator address and metadata if needed 5. Opens/closes routers in QDB based on their status 6. Cleans up connections for removed routers 7. Sleeps for `iteration_timeout` before the next cycle When using high `iteration_timeout` values (e.g., 5m+), ensure `router_keepalive_time` is configured appropriately to prevent cached connections from being closed by network devices. See [gRPC Keepalive Settings](#grpc-keepalive-settings). #### Impact on Operations * **Router Failover**: Time to detect and mark failed routers as closed * **Topology Changes**: Time to recognize new routers added to the cluster * **Metadata Sync**: Frequency of coordinator address updates to routers * **Resource Usage**: CPU and network bandwidth for health checks Start with the default `1s` for development. In production, increase to `10s` or higher once your topology is stable to reduce overhead. ### Lock Iteration Timeout The `lock_iteration_timeout` setting controls the retry interval when multiple coordinator instances compete for leadership during startup. #### How Coordinator Locking Works SPQR supports running multiple coordinator instances for high availability, but only one can be active (hold the lock) at a time: 1. On startup, each coordinator tries to acquire a distributed lock in QDB (etcd) 2. If the lock is already held, the coordinator waits `lock_iteration_timeout` 3. After the timeout, it tries again 4. This continues until it acquires the lock or the process is stopped In high-availability setups with multiple coordinator instances, a longer `lock_iteration_timeout` reduces load on QDB/etcd during leadership elections. ## gRPC Keepalive Settings The coordinator maintains persistent gRPC connections to routers using connection caching. To prevent these connections from being closed by network intermediaries (load balancers, firewalls, NAT gateways) during idle periods, gRPC keepalive is configured. ### Why Keepalive is Important Network devices typically close idle TCP connections after 60 seconds to 5 minutes. When `iteration_timeout` is set to several minutes, cached connections may be closed by the network before they're reused, causing connection failures and unnecessary reconnection overhead. Keepalive sends periodic "ping" messages to keep connections alive and detect dead connections early. If you experience frequent connection errors when `iteration_timeout` is high, reduce `router_keepalive_time` to match your network environment's idle timeout characteristics. ## Frontend Rules Frontend rule is a specification of how clients connect to the admin console. Refer to the `FrontendRule` struct in the [pkg/config/rules.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/rules.go) file for the most up-to-date configuration options. | Setting | Description | Possible Values | | ------------------------- | ------------------------------------------------------------------------------------------------- | ------------------------ | | `db` | The database name to which the rule applies. | Any valid database name | | `usr` | The user name for which the rule is applicable. | Any valid username | | `auth_rule` | See [General Auth Settings](./auth). | Object of `AuthCfg` | | `search_path` | Search path sent to the backend. | String | | `pool_mode` | Pooling mode value (ignored by coordinator but kept for compatibility with router configuration). | See router pooling modes | | `pool_discard` | Whether to discard pooled connections after use (ignored by coordinator). | `true`, `false` | | `pool_rollback` | Whether to issue `ROLLBACK` on pooled connections (ignored by coordinator). | `true`, `false` | | `pool_prepared_statement` | Whether to reuse prepared statements in the pool (ignored by coordinator). | `true`, `false` | | `pool_default` | Whether the rule should be used as the default pool configuration for incoming connections. | `true`, `false` | # Roles settings Source: https://docs.pg-sharding.tech/configuration/roles Specify readers, writes, admins ## Roles Roles represents a collection of table groups. It is used to define and manage groups of tables in the configuration. The structure supports JSON, TOML, and YAML serialization formats. By default, the role system is disabled. To enable it, you have to specify `enable_role_system: true` and `roles_file` settings in both your [router's](./router) and [coordinator's](./coordinator) config. Refer to the [pkg/config/roles.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/roles.go) file for the most up-to-date configuration options. ## Table Group TableGroup represents a group of tables with associated roles and permissions. | Setting | Description | Possible Values | | --------- | --------------------------------------------------- | ---------------------- | | `id` | Unique identifier for the table group. | Any string value | | `readers` | List of users with read access to the table group. | Array of string values | | `writers` | List of users with write access to the table group. | Array of string values | | `admins` | List of users with admin access to the table group. | Array of string values | ## Example For example, let's assume we have the following roles configuration: ```yaml theme={null} table_groups: - id: "example_table_group" readers: - "user1" - "user2" - "user3" writers: - "prod_user" admins: - "admin_user" ``` When we try to connect to (any) database as user user1 and run a modify query, we get something like this: ```sql theme={null} CREATE KEY RANGE krid2 FROM 11 ROUTE TO sh2 FOR DISTRIBUTION ds1; ERROR: permission denied for user=user1 dbname=prod ``` # Router Source: https://docs.pg-sharding.tech/configuration/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](https://github.com/pg-sharding/spqr/blob/master/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](./roles). | `true`, `false` | `false` | | `roles_file` | The file path to the [roles](./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](https://github.com/pg-sharding/spqr/blob/master/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](./auth) | 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](https://github.com/pg-sharding/spqr/blob/master/pkg/config/rules.go) and [pkg/config/auth\_backend.go](https://github.com/pg-sharding/spqr/blob/master/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](https://github.com/pg-sharding/spqr/blob/master/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](./auth) | 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`](/routing/hints#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](/routing/hints). Refer to the `QRouter` struct in the [pkg/config/router.go](https://github.com/pg-sharding/spqr/blob/master/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](/routing/hints#spqr-default-route-behaviour) hint. | `BLOCK`, `ALLOW` | Yes | `ALLOW` | | `query_routing.default_target_session_attrs` | Default [target\_session\_attrs](https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-TARGET-SESSION-ATTRS) for connections. Can be overridden by the [\_\_spqr\_\_target\_session\_attrs](/routing/hints#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](/sharding/bulk) splitting across shards. Can be overridden by the [\_\_spqr\_\_engine\_v2](/routing/hints#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](/routing/hints#spqr-commit-strategy) hint. See [Distributed Transactions](/sharding/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](/routing/hints#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 | | # Router with Init SQL Source: https://docs.pg-sharding.tech/deployment/bare_router Simple SPQR deployment using init_sql for static sharding configuration ## Overview The bare router deployment is the **simplest** way to run SPQR. It's ideal for testing, development, and scenarios where your sharding rules don't need to be updated dynamically. In this deployment mode: * You run `spqr-router` with an `init_sql` file * The router reads the sharding configuration from this local file on startup * No external coordinator or etcd cluster is required * You can run multiple router instances simultaneously * Each router instance operates independently ## Setup ### 1. Create Router Configuration File Create a YAML configuration file (e.g., `router.yaml`): ```yaml router.yaml theme={null} host: 'localhost' router_port: '6432' admin_console_port: '7432' grpc_api_port: '7001' # Enable init SQL mode use_init_sql: true init_sql: "/path/to/init.sql" exit_on_init_sql: true # Exit if init SQL parsing fails router_mode: PROXY log_level: info frontend_rules: - db: mydb usr: myuser pool_mode: TRANSACTION auth_rule: auth_method: ok backend_rules: - db: mydb usr: myuser connection_limit: 100 pool_discard: false pool_rollback: true shards: shard1: db: mydb usr: myuser pwd: password type: DATA hosts: - 'shard1-host:5432' shard2: db: mydb usr: myuser pwd: password type: DATA hosts: - 'shard2-host:5432' ``` ### 2. Create Init SQL File Create an SQL file with your sharding configuration (e.g., `init.sql`): ```sql init.sql theme={null} -- Create a distribution for your sharding key CREATE DISTRIBUTION ds1 COLUMN TYPES integer; -- Attach tables to the distribution ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id; ALTER DISTRIBUTION ds1 ATTACH RELATION order_items DISTRIBUTION KEY order_id; -- Define key ranges CREATE KEY RANGE krid1 FROM 0 ROUTE TO shard1 FOR DISTRIBUTION ds1; CREATE KEY RANGE krid2 FROM 10000 ROUTE TO shard2 FOR DISTRIBUTION ds1; ``` ### 3. Start the Router ```bash theme={null} spqr-router run --config router.yaml ``` Connect to the admin console to verify the configuration: ```bash theme={null} psql "host=localhost port=6432 dbname=spqr-console user=spqr-console sslmode=disable" ``` Check the configured shards and key ranges: ```sql theme={null} -- List all shards SHOW shards; -- List distributions SHOW distributions; -- List key ranges SHOW key_ranges; ``` ### 4. Connect Clients can now connect to the router: ```bash theme={null} psql "host=localhost port=6432 dbname=mydb user=myuser sslmode=disable" ``` The router will automatically route queries to the appropriate shard based on the sharding key. ## Considerations * Configuration changes require updating the `init.sql` file and restarting the router * No dynamic updates are possible while the router is running * For persistence you may set `memqdb_backup_path`. This feature is deprecated and may be removed in a future release — use at your own risk. For configuration reference, see the [Router Configuration](/configuration/router) reference. # Overview Source: https://docs.pg-sharding.tech/deployment/overview Understanding SPQR deployment modes and choosing the right one for your use case SPQR can be deployed in several different configurations depending on your requirements, infrastructure constraints, and operational needs. Simple deployment for testing and static configurations Production-ready setup with dynamic configuration Combined router + coordinator for resource efficiency # Router + Coordinator Source: https://docs.pg-sharding.tech/deployment/router_coordinator Production-ready SPQR deployment with dynamic configuration management ## Overview The Router + Coordinator deployment is the **recommended** setup for production environments. It provides centralized metadata management, dynamic configuration updates, and support for multiple router instances. In this deployment mode: * A **Coordinator** manages the sharding metadata and configuration * **QDB (etcd cluster)** stores the cluster metadata persistently * Multiple **Routers** connect to the Coordinator and share the same configuration * Routers must be registered and unregistered with the Coordinator * Configuration can be updated dynamically without router restarts ## Setup ### 1. Deploy etcd Cluster Set up an etcd cluster to serve as the QDB. For production, we recommend using a 3-node cluster. **Example: Single-node etcd (for testing)** ```bash theme={null} etcd --listen-client-urls http://0.0.0.0:2379 \ --advertise-client-urls http://localhost:2379 ``` For production, follow the [etcd clustering guide](https://etcd.io/docs/latest/op-guide/clustering/). ### 2. Configure the Coordinator Create a coordinator configuration file: ```yaml coordinator.yaml theme={null} host: 'localhost' coordinator_port: '7002' grpc_api_port: '7003' qdb_addr: 'localhost:2379' log_level: info frontend_rules: - db: spqr_console usr: admin auth_rule: auth_method: ok password: your_admin_password ``` ### 3. Start the Coordinator ```bash theme={null} spqr-coordinator --config coordinator.yaml ``` ### 4. Configure Sharding Rules Connect to the Coordinator's admin console: ```bash theme={null} psql "host=localhost port=7002 dbname=spqr_console user=spqr-console sslmode=disable" ``` Configure your sharding setup: ```sql theme={null} CREATE SHARD shard1 OPTIONS (HOST 'shard1-host:5432'); CREATE SHARD shard2 OPTIONS (HOST 'shard2-host:5432'); CREATE DISTRIBUTION ds1 COLUMN TYPES integer; ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id; ALTER DISTRIBUTION ds1 ATTACH RELATION order_items DISTRIBUTION KEY order_id; CREATE KEY RANGE krid1 FROM 0 ROUTE TO shard1 FOR DISTRIBUTION ds1; CREATE KEY RANGE krid2 FROM 10000 ROUTE TO shard2 FOR DISTRIBUTION ds1; ``` ### 5. Configure the Router Create a router configuration file: ```yaml router.yaml theme={null} host: 'localhost' router_port: '6432' admin_console_port: '7432' grpc_api_port: '7010' with_coordinator: true router_mode: PROXY log_level: info frontend_rules: - db: mydb usr: myuser pool_mode: TRANSACTION auth_rule: auth_method: ok backend_rules: - db: mydb usr: myuser connection_limit: 100 pool_discard: false pool_rollback: true auth_rule: auth_method: clear_text password: backend_password ``` ### 6. Start the Router ```bash theme={null} spqr-router run --config router.yaml ``` ### 7. Register the Router Connect to the coordinator’s admin console and register the router: ```bash theme={null} psql "host=localhost port=7002 dbname=spqr-console user=spqr-console sslmode=disable" REGISTER ROUTER router1 ADDRESS '[localhost]:7010'; ``` ### 8. Connect Clients can now connect to the router: ```bash theme={null} psql "host=localhost port=6432 dbname=mydb user=myuser sslmode=disable" ``` ## Managing the Deployment ### Adding More Routers Create a new router configuration with different ports, start it, and register: ```sql theme={null} REGISTER ROUTER router2 ADDRESS '[localhost]:7011'; ``` ### Updating Configuration Dynamically All configuration changes are made through the Coordinator's admin console: ```sql theme={null} CREATE SHARD shard3 OPTIONS (HOST 'shard3-host:5432'); CREATE KEY RANGE krid3 FROM 20000 ROUTE TO shard3 FOR DISTRIBUTION ds1; ``` Changes are automatically propagated to all registered routers. ### Unregistering a Router Before shutting down a router: ```sql theme={null} UNREGISTER ROUTER router1; ``` Or unregister all routers: ```sql theme={null} UNREGISTER ROUTER ALL; ``` ## Monitoring ### Check Router Status On the Coordinator's admin console: ```sql theme={null} SHOW routers; SHOW shards; SHOW distributions; SHOW key_ranges; ``` ## Configuration Reference For complete options: * [Router Configuration](/configuration/router) * [Coordinator Configuration](/configuration/coordinator) # SPQR Infra Mode Source: https://docs.pg-sharding.tech/deployment/spqrinfra Combined router and coordinator deployment for resource efficiency 🚧 **This section is under construction** ## Overview SPQR Infra mode is a special deployment where each `spqr-router` instance acts as **both a router and a coordinator** in a single binary. This mode is ideal when you want to save CPU and memory resources or simplify your deployment architecture. In SPQR Infra mode: * A single `spqr-router` binary provides both routing and coordination functions * Multiple instances form a distributed consensus group * An etcd cluster is required for distributed state * You should run an odd number of instances (1, 3, or 5) * Reduced operational overhead compared to separate router and coordinator deployments ## Setup ### 1. Deploy etcd Cluster Set up an etcd cluster to serve as the QDB. For production, we recommend using a 3-node cluster. **Example: Single-node etcd (for testing)** ```bash theme={null} etcd --listen-client-urls http://0.0.0.0:2379 \ --advertise-client-urls http://localhost:2379 ``` For production, follow the [etcd clustering guide](https://etcd.io/docs/latest/op-guide/clustering/). 🚧 **This section is under construction** # Various authentication types Source: https://docs.pg-sharding.tech/features/authentication SPQR provides various methods for authenticating users: * `trust`, see [Trust Authentication](https://www.postgresql.org/docs/15/auth-trust.html). * `password`, `md5`, `scram` and `scram-sha-256`, see [Password Authentication](https://www.postgresql.org/docs/15/auth-password.html). * `ldap`, see [LDAP Authentication](https://www.postgresql.org/docs/current/auth-ldap.html) * `gss`, using Kerberos. See [GSSAPI Authentication](https://www.postgresql.org/docs/current/gssapi-auth.html). For more information about authentication config, see [pkg/config/auth.go](https://github.com/pg-sharding/spqr/blob/master/pkg/config/auth.go) # Live configuration reloading Source: https://docs.pg-sharding.tech/features/config_reloading You can send a `SIGHUP` signal to the router's process. This will trigger the router to reload its configuration file and apply any changes without interrupting its operation. For more information see router's [config documentation](/configuration/router). # Cross-shard queries Source: https://docs.pg-sharding.tech/features/cross_shard_queries SPQR is optimized for single-shard OLTP queries but supports limited cross-shard operations: * `SELECT * FROM` without `WHERE` clause and DDL commands (best-effort with no snapshot isolation, see [Distributed Transactions](/sharding/distributed_transactions)) * [Bulk operations](/sharding/bulk) (COPY, batch INSERT) Disable with `query_routing.default_route_behaviour: BLOCK` in router config. # Data Balancing Source: https://docs.pg-sharding.tech/features/data_balancing Data migration between shards aims to balance the workload across shards proportionally. The main idea is to minimize any locking impact during these migrations, which is accomplished by reducing the size of the data ranges being transferred. ## Core concept We considered different options for moving data. The most popular way is to make a copy via logical replication, then delete half of the data on one node and delete another half of the data on the other node. We decided that logical replication does not work well enough yet. Instead, the coordinator makes ε-split - cut off a small part of the data. Since it is small, it all works very quickly. ![ε-split](https://github.com/pg-sharding/spqr/blob/master/docs/images/balancing_e-split.png?raw=true "ε-split") # Default shard Source: https://docs.pg-sharding.tech/features/default_shard A distribution can have a default shard that acts as a catch-all fallback. When the router matches a query to a distribution with a default shard, it is guaranteed to find a route — even if the sharding key doesn't fall within any explicitly defined key range. The default shard is implemented as a special key range with the minimum possible value as its lower bound. This key range has a reserved ID: `{DistributionId}.DEFAULT`. For example, if the distribution has column types `[varchar, integer, uuid]`, the default key range lower bound is `["", -9223372036854775808, "00000000-0000-0000-0000-000000000000"]`. ```sql theme={null} -- Setup: key ranges kr1: [-20, -10) → sh2, kr2: [-10, 0) → sh3, kr3: [0, ∞) → sh4 -- Default shard: sh1 (covers MinInt64 to -20) INSERT INTO t(id) VALUES (-30); -- → sh1 (default, below all explicit ranges) INSERT INTO t(id) VALUES (-20); -- → sh2 (kr1) INSERT INTO t(id) VALUES (-10); -- → sh3 (kr2) INSERT INTO t(id) VALUES (0); -- → sh4 (kr3) ``` See [Default Shard](/sharding/default_shard) for more details. # Distributed Transactions Source: https://docs.pg-sharding.tech/features/distributed_transactions SPQR supports distributed transactions across multiple shards with different commit strategies. The `__spqr__commit_strategy` parameter controls how transactions are committed: * `best-effort` or `1pc` - One-phase commit with no coordination between shards * `2pc` - Two-phase commit with atomicity guarantees across shards For atomic cross-shard transactions, use two-phase commit (2PC). Set `__spqr__commit_strategy` to `2pc` and ensure `max_prepared_transactions` > 0 on all shards. ```sql theme={null} SET __spqr__commit_strategy TO '2pc'; BEGIN; INSERT INTO shard1_table (id, data) VALUES (1, 'data1'); INSERT INTO shard2_table (id, data) VALUES (2, 'data2'); COMMIT; -- Atomically commits across shards ``` See [Distributed Transactions](/sharding/distributed_transactions) for detailed information on commit strategies, requirements, and best practices. # Fault Tolerance Source: https://docs.pg-sharding.tech/features/fault_tolerance The router stores the sharding rules only for cache purposes. Information about the entire installation is stored inside the QDB service, so the number of routers running simultaneously is unlimited. In the router configuration, it is possible to specify multiple servers for one shard. # Multiple servers and failover Source: https://docs.pg-sharding.tech/features/multiple_servers_and_failover In the router configuration, it is possible to specify multiple servers for one shard. Then the router will distribute read-only queries among the replicas. However, in addition to the automatic routing, you also have the option to explicitly define the destination for a specific query by using the `target-session-attr` parameter within the query. See [Routing hints](/routing/hints#target-session-attrs) for more information. ```sql theme={null} -- you could specify target-session-attrs anywhere in your query SELECT pg_is_in_recovery(), id FROM tsa_test WHERE id = 22 /* target-session-attrs: read-write */; NOTICE: send query to shard(s) : sh1 pg_is_in_recovery | id -------------------+---- f | 22 (1 row) ``` # Performance Source: https://docs.pg-sharding.tech/features/performance Minor overhead for query execution. See benchmarks [here](https://github.com/pg-sharding/spqr/blob/master/benchmarks/README.md) and [here](https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/30). # Transaction and session pooling Source: https://docs.pg-sharding.tech/features/pooling Just as in your favorite connection pooler (Odyssey or PgBouncer). # Works over PostgreSQL protocol Source: https://docs.pg-sharding.tech/features/protocol The application communicates with the router using the PostgreSQL protocol. In other words, the application doesn't even know that it's communicating with a Golang application and not a regular PostgreSQL cluster. Moreover, you can connect to the router and the coordinator via psql to perform administrative tasks. SPQR has an administrative console. You can find the console port in your config file. ```sql theme={null} ➜ psql "host=localhost sslmode=verify-full user=demo dbname=demo port=7432" SPQR router admin console Here you can configure your routing rules ------------------------------------------------ You can find documentation here https://github.com/pg-sharding/spqr/tree/master/docs SHOW shards; listing data shards --------------------- shard id -> shard1 shard id -> shard2 (2 rows) ``` # Dedicated read-only mode Source: https://docs.pg-sharding.tech/features/read_only Once enabled, the router will respond to a `SHOW transaction_read_only` command with "true" and handle only read-only queries, similar to a standard PostgreSQL replica. # Reference tables Source: https://docs.pg-sharding.tech/features/reference_tables Sometimes, it can be useful to have one or more tables on each shard with the same data. SPQR supports what are known as [reference table](/sharding/reference_tables). A reference table is replicated across all shards and is not split into separate partitions. Every shard has **a local copy of the table**, which reduces network overhead and allows for fast access to frequently used data. ```sql theme={null} \c spqr-console CREATE REFERENCE TABLE tax_rates; create reference table ------------------------ table -> tax_rates shard id -> sh1 (2 rows) ``` # Sharding Source: https://docs.pg-sharding.tech/features/sharding The router knows that some tables have been [split into shards](/sharding/sharded_tables). If possible, the router tries to determine on the first transaction statement to which shard this transaction should be sent. SPQR supports both single-column and [composite (multi-column) sharding keys](/sharding/composite_keys). ## Query Routing When a query arrives, the router extracts the sharding key from the query and determines which key range (and thus which shard) should handle it. ```sql theme={null} -- This query works with properly configured sharding rules -- The router extracts id=10 and routes to the appropriate shard INSERT INTO test(id, age) VALUES (10, 16); ``` ### Explicit Sharding Key You can explicitly specify a sharding key in a SQL comment when the router cannot determine it automatically: ```sql theme={null} -- Override automatic routing by specifying the sharding key INSERT INTO test(id, age) VALUES (10, 16) /*__spqr__sharding_key: 30*/; ``` For composite sharding keys, specify all key values separated by commas: ```sql theme={null} -- Explicit composite sharding key INSERT INTO users(tenant_id, user_id, name) VALUES (1, 100, 'Alice') /*__spqr__sharding_key: 1, 100*/; ``` ### Explicit Shard Selection You can also force a query to execute on a specific shard: ```sql theme={null} -- Execute on a specific shard regardless of key SELECT * FROM test /*__spqr__execute_on: sh1*/; ``` # Statistics Source: https://docs.pg-sharding.tech/features/statistics You can get access to statistics in router's administrative console via [SHOW command](https://github.com/pg-sharding/spqr/blob/master/yacc/console/gram.y#L319). ```sql theme={null} SHOW clients WHERE dbname = testdb; client_id | user | dbname | server_id | router_address --------------+----------+--------+-----------------------+---------------- 824660987840 | test_app | testdb | no backend connection | local 824686914368 | test_app | testdb | no backend connection | local 824656201120 | test_app | testdb | no backend connection | local 824657328608 | test_app | testdb | example.com:6432 | local 824656165344 | test_app | testdb | example.com:6432 | local 824655861952 | test_app | testdb | no backend connection | local (6 rows) ``` For more information, see [SHOW command](/sharding/console/sql_commands#show) reference. # Routing hints Source: https://docs.pg-sharding.tech/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. # Details Source: https://docs.pg-sharding.tech/sharding/balancing/details ## Algorithm This is a brief summary of what stages balancing consists of: 1. **Collecting statistics**: The load balancer collects statistics on the workload on the shards using [pg\_comment\_stats](#pg_comment_stats) to measure CPU and disk usage. 2. **Finding the most heavily loaded shard**: Based on the collected statistics, the load balancer identifies the shard with the highest workload. 3. **Selecting the most significant load criterion**: Among all the workload criteria, the one with the greatest impact on the overall workload is chosen. 4. **Checking out the need for data migration**: The workload on the key range is compared to a threshold value. If it exceeds the threshold, it's time to migrate the data. 5. **Finding the key range with the heaviest load**: On the identified shard, the key range with the highest workload is determined. 6. **Choosing a destination**: It is decided which shard and key range the data will be migrated to. 7. **Data movement**: A data movement operation is initiated, which may involve splitting the data into smaller chunks, if necessary, and transferring them to the destination shard. For more details see \[data movement internals]\(#Data movement internals) 8. **Synchronization**: The changes are synchronized with the etcd cluster to ensure data consistency. ## pg\_comment\_stats We fork pg\_stat\_statements and modified it a little bit. The original version of the extension records stats for each SQL statement, while [pg\_comment\_stats](https://github.com/munakoiso/pg_comment_stats) keeps track of queries that have specific keys mentioned in the statement comments. ```sql theme={null} > /* a: 1 c: hmm*/ select 1; > select comment_keys, query_count, user_time from pgcs_get_stats() limit 1; -[ RECORD 1 ]+---------------------- comment_keys | {"a": "1"} query_count | 1 user_time | 6.000000000000363e-06 ``` ## Data movement internals Balancer is a separate binary that executes the algorithm. It executes the algorithm exactly once without cyclic repetition, and its running time is on the order of seconds. If the queue is not empty, the balancer performs a task from the queue. A data transport task is actually a group of tasks that can have many actions, and between all actions, the task state is synchronized with etcd. After completion, the task is removed from the task group. For clarity, here is how it is defined [in the code](https://github.com/pg-sharding/spqr/blob/master/pkg/models/tasks/tasks.go): ```golang theme={null} type MoveTask struct { Bound [][]byte KrIdTemp string State TaskState // Planned, Split, Moved } type MoveTaskGroup struct { ShardToId string KrIdFrom string KrIdTo string Tasks []*MoveTask Type SplitType // SplitLeft, SplitRight } type BalancerTask struct { Type JoinType // JoinLeft, JoinRight KrIdFrom string KrIdTo string KrIdTemp string ShardIdTo string KeyCount int64 State BalancerTaskState // Planned, Moved } ``` # Overview Source: https://docs.pg-sharding.tech/sharding/balancing/overview Ideally, all the shards within the system should be evenly loaded. However, at times, one of the shards may become overloaded based on certain criteria, such as CPU usage, free space, or I/O. In such cases, it may be beneficial to move some data from the overloaded shard to another shard with more available resources. We considered different options for moving data. The most popular way is to make a copy via logical replication, then delete half of the data on one node and delete another half of the data on the other node. We decided that logical replication does not work well enough yet. Instead, the coordinator makes **ε-split** - cut off a small portion of the data. Since it is small, it all works relatively quickly. ε-split # Bulk Operations Source: https://docs.pg-sharding.tech/sharding/bulk Efficiently load data across multiple shards using COPY and batch INSERT SPQR supports bulk data loading operations that automatically distribute data across shards according to your sharding setup. This page covers two main approaches: `COPY FROM` for file-based loading and batch `INSERT` for multi-value statements. Be careful with bulk operations when not using two-phase commit. There is no concept of snapshot isolation across shards in SPQR. This means: * A `SELECT` that runs on multiple shards at the same time may see partial results * If an error occurs on one shard, the transaction is rolled back only on that shard * Use [two-phase commit](/sharding/distributed_transactions#2-two-phase-commit-2pc) for atomic operations across shards ## COPY FROM The `COPY` command allows for efficient bulk copying of data from files or stdin into a sharded table. For more information on the PostgreSQL `COPY` command, see the [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-copy.html). **From stdin:** ```sql theme={null} COPY xx (i, j) FROM STDIN WITH DELIMITER '|'; 1|1 2|2 3|3 ... \. NOTICE: send query to shard(s) : shard1,shard2,shard3,shard4 ``` **From file using psql:** ```bash theme={null} dbname=> \copy "spqr-test" (uid, data) from 'data_test.csv' WITH DELIMITER ';'; NOTICE: send query to shard(s) : shard1,shard2,shard3,shard4 ``` ## Batch INSERT Batch INSERT rewriting only works with **simple protocol**. Parameterized queries using extended protocol (prepared statements with placeholders) are not supported for cross-shard batch inserts. When [engine V2](/routing/hints#spqr-engine-v2) is enabled, SPQR supports batch INSERT statements that target multiple shards. The router automatically analyzes each row in the VALUES clause, determines the target shard based on the sharding key, and rewrites the query into separate INSERT statements per shard. ```sql theme={null} -- Insert multiple rows that target different shards INSERT INTO users (id, name) VALUES (1, 'Alice'), -- routes to sh1 (100, 'Bob'), -- routes to sh2 (2, 'Charlie') -- routes to sh1 /* __spqr__engine_v2: true */; -- NOTICE: send query to shard(s) : sh1,sh2 ``` SPQR rewrites this into: * `INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Charlie');` → sh1 * `INSERT INTO users (id, name) VALUES (100, 'Bob');` → sh2 For atomic batch inserts across shards, combine engine V2 with two-phase commit: ```sql theme={null} SET __spqr__engine_v2 TO on; SET __spqr__commit_strategy TO '2pc'; BEGIN; INSERT INTO users (id, name) VALUES (1, 'Alice'), (100, 'Bob'); COMMIT; -- Uses 2PC for atomicity across shards ``` # Coordinator Source: https://docs.pg-sharding.tech/sharding/cluster_components/coordinator The coordinator consists of two parts: the coordinator itself and the QDB. * The **SPQR Coordinator** provides an interface for managing a sharded cluster configuration and routers. * **QDB** stores the metadata for a sharded cluster, which includes the distributions, key ranges, tables, distributed locks, and so on. It's a regular etcd cluster ([https://etcd.io](https://etcd.io)) # Overview Source: https://docs.pg-sharding.tech/sharding/cluster_components/overview ## TL;DR A SPQR cluster consists of the following components: * **Shard**: each shard contains a subset of the sharded data. This is a typical PostgreSQL cluster with a master and replicas. * **Router**: it acts as a query router, providing an interface between client applications and shards. * **Coordinator**: it stores metadata and manages the SPQR cluster. ## Cluster Components What does a typical application look like? Backends connect to a PostgreSQL cluster in some way. We assume that replication is configured in the cluster and backups are performed regularly. Writing requests come mainly to replicas, while reading and writing is done to the master. We had the idea that we could put a proxy between the backends and the PostgreSQL clusters. The proxy would receive requests, determine which shard the request should be sent to, wait for a response from that shard, and then return the result to the backend. typical Actually, this is what we did. This thing is called the **SPQR Router**. typical The router works using the PostgreSQL protocol, so the application doesn't even realize that it's communicating with a Golang application, not a regular PostgreSQL cluster. Due to this, we achieve a relatively high performance per one router. Clearly, in this configuration, the router is a bottleneck and a single point of failure. Therefore, we have designed a system where **many routers can work in parallel**. In fact, thousands of routers operating in parallel should not be a problem. typical But in order for routers to have the same metadata, we need to store this information somewhere. We store it in QDB, which is an regular [etcd cluster](https://etcd.io). Metadata changes are performed by the **SPQR Coordinator**, so the coordinator makes sure the routers have the same information. typical # Router Source: https://docs.pg-sharding.tech/sharding/cluster_components/router The **SPQR Router** routes queries to shards and provides the interface for clients to a sharded cluster. Applications should never communicate directly with shards, but rather use the Router to route their queries. The **Router** caches metadata from the Coordinator and guesses which shard a transaction should be routed to. A **Router** is stateless, meaning it has no persistent state, and is typically run on dedicated resources. Or can be run anywhere else, including on the same host as the application. # Shard Source: https://docs.pg-sharding.tech/sharding/cluster_components/shard A **shard** is a portion of data stored within a cluster. A cluster is made up of multiple shards, each containing a unique subset of the overall dataset. Together, these shards form the complete data store. **Shard management** includes tasks like: * Replication * Backup * Failover/switchover configuration * Monitoring These tasks are the responsibility of the **user**, not SPQR. SPQR was originally designed to enable PostgreSQL sharding on top of managed cloud database services. While it simplifies query routing across shards, it does not aim to be a complete shard management system. There are **no current plans** to release a fully open-source shard management solution as part of SPQR. Instead, users are encouraged to build PostgreSQL clusters using established high-availability (HA) tools such as: * **Patroni** * **PgConsul** * **Stolon** * **Managed PostgreSQL offerings** These tools rely on physical quorum-based HA mechanisms, which are proven to be reliable and compatible with SPQR. Importantly, **SPQR does not manage or control the individual shards**. It only requires: * Connection information for each shard * The data range assigned to each shard That's all SPQR needs to route queries correctly. # Composite Sharding Keys Source: https://docs.pg-sharding.tech/sharding/composite_keys Learn how to use multiple columns as sharding keys in SPQR SPQR supports composite sharding keys, which are multiple columns used as sharding keys. This is useful for: * When your data has a natural multi-column partition key, such as `tenant_id + user_id`. * Geographic data. * Implementing time-series sharding strategies. When using composite sharding keys, SPQR creates key ranges based on the combined values of all specified columns. The columns are compared in order (left to right) when determining which shard to route a query to, similar to how [composite indexes work in PostgreSQL](https://www.postgresql.org/docs/current/indexes-multicolumn.html). ## Example To set up composite sharding keys, specify multiple column types when creating the distribution: ```sql theme={null} -- Two integer columns CREATE DISTRIBUTION ds1 COLUMN TYPES integer, varchar; -- With hash on second column CREATE DISTRIBUTION ds2 COLUMN TYPES integer, varchar hash; ``` Create key ranges with comma-separated values: ```sql theme={null} CREATE KEY RANGE FROM 0, 0 ROUTE TO shard1 FOR DISTRIBUTION ds1; CREATE KEY RANGE FROM 0, 100 ROUTE TO shard2 FOR DISTRIBUTION ds1; CREATE KEY RANGE FROM 100, 0 ROUTE TO shard3 FOR DISTRIBUTION ds1; ``` Attach tables with multiple columns in the `DISTRIBUTION KEY` clause: ```sql theme={null} ALTER DISTRIBUTION ds1 ATTACH RELATION users DISTRIBUTION KEY region_id, user_id; ``` You can also use composite keys with hash-based sharding: ```sql theme={null} -- Create distribution with hash type for second column CREATE DISTRIBUTION ds1 COLUMN TYPES INT, VARCHAR hash; -- Create key ranges CREATE KEY RANGE FROM 0, 0 ROUTE TO shard1 FOR DISTRIBUTION ds1; CREATE KEY RANGE FROM 0, 2147483648 ROUTE TO shard2 FOR DISTRIBUTION ds1; CREATE KEY RANGE FROM 1, 0 ROUTE TO shard3 FOR DISTRIBUTION ds1; CREATE KEY RANGE FROM 1, 2147483648 ROUTE TO shard4 FOR DISTRIBUTION ds1; -- Attach table with hash function ALTER DISTRIBUTION ds1 ATTACH RELATION events DISTRIBUTION KEY tenant_id, event_id HASH FUNCTION MURMUR; ``` ## Best Practices 1. **Column Order Matters**: Choose the order of columns carefully. The first column should be the one most frequently used in your queries. 2. **Cardinality Considerations**: For [direct key sharding](/sharding/ranged), consider using a lower-cardinality column first (e.g., tenant ID) followed by a higher-cardinality column (e.g., user ID or timestamp). 3. **Hash Functions**: When using hash functions with composite keys, they are applied individually to each column. 4. **Query Patterns**: Ensure your application queries include all composite key columns in WHERE clauses. Queries missing one or more key columns may be rejected. # Concepts Source: https://docs.pg-sharding.tech/sharding/console/concepts Some SPQR Admin Console terms ## Distribution Distribution is a sort of container for all the settings. With distribution, you can shard a part of your database with one logic, and other part is another logic. You may think of distribution like a virtual database. ## Key Range A key range refers to a range of keys that are used to partition and distribute data across multiple shards. Each key in the range is associated with a specific shard. You can lookup registered key ranges using command "show key\_ranges". ```sql theme={null} show key_ranges; key_range_id | shard_id | distribution_id | lower_bound | locked --------------+----------+-----------------+-------------+-------- kr1 | sh1 | ds1 | 10 | true kr2 | sh1 | ds1 | 5 | false (2 rows) ``` # How to connect Source: https://docs.pg-sharding.tech/sharding/console/how_to_connect SPQR router and coordinator admin console Router and coordinator have administrative console. This is an app that works by PostgreSQL protocol and you can connect to it by usual psql: ```sql theme={null} ➜ psql "host=localhost sslmode=verify-full user=demo dbname=demo port=7432" SPQR router admin console Here you can configure your routing rules ------------------------------------------------ You can find documentation here https://github.com/pg-sharding/spqr/tree/master/docs SHOW shards; listing data shards --------------------- shard id -> shard1 shard id -> shard2 (2 rows) ``` Administrative Console is the place where you configure the sharding logic. The router will decide which of the shards to send each request to according to the settings. # SQL commands Source: https://docs.pg-sharding.tech/sharding/console/sql_commands Create distributions, key ranges and tables and see cluster info ### CREATE DISTRIBUTION Creates a sharding distribution with a distribution identifier and key column types. A distribution defines the sharding key type layout used by attached relations and key ranges. Optionally, specify a default shard to route records that do not match any key range. For composite sharding keys (multiple columns), specify multiple types separated by commas. You can append "hash" after a column type to apply hash-based distribution to that column. ```sql theme={null} CREATE DISTRIBUTION [COLUMN TYPES | ()] [DEFAULT SHARD ] where types is a comma-separated list of column types for the distribution key, and each type is one of: INTEGER, INT, VARCHAR, UUID (VARCHAR/INTEGER/UUID may be followed by HASH) where is a default shard identifier. The must exist. ``` **Examples:** ```sql theme={null} -- Single integer key distribution CREATE DISTRIBUTION ds1 COLUMN TYPES integer; -- Single varchar key distribution with hash CREATE DISTRIBUTION ds2 COLUMN TYPES varchar hash; -- Single uuid key distribution with hash CREATE DISTRIBUTION ds_uuid COLUMN TYPES uuid hash; -- Composite key distribution with two columns CREATE DISTRIBUTION ds3 COLUMN TYPES integer, integer; -- Composite key with mixed types and hash on second column CREATE DISTRIBUTION ds4 COLUMN TYPES integer, varchar hash; -- Parenthesized type syntax with default shard CREATE DISTRIBUTION ds5 (integer, varchar hash) DEFAULT SHARD sh1; ``` ### DROP DISTRIBUTION Removes a distribution from cluster metadata. Use CASCADE to remove dependent metadata objects (key ranges, attached relations) when required. Dropping a distribution removes routing metadata and may impact attached relations. ```sql theme={null} DROP DISTRIBUTION [CASCADE] ``` **Examples:** ```sql theme={null} -- Drop one distribution DROP DISTRIBUTION ds1; -- Drop distribution with dependent metadata DROP DISTRIBUTION ds1 CASCADE; ``` ### ALTER DISTRIBUTION ATTACH TABLE Attaches one or more [tables](/sharding/sharded_tables) to an existing distribution using the ATTACH TABLE clause. For composite sharding keys, specify multiple column names separated by commas in the DISTRIBUTION KEY clause. ```sql theme={null} ALTER DISTRIBUTION ATTACH TABLE [, ...] DISTRIBUTION KEY [HASH FUNCTION ] where keys is a comma-separated list of column names (e.g., "col1, col2" for composite keys) where hashFunction is one of: IDENTITY, MURMUR, CITY ``` **Examples:** ```sql theme={null} -- Single column sharding key ALTER DISTRIBUTION ds1 ATTACH TABLE orders DISTRIBUTION KEY order_id; -- Composite sharding key with two columns ALTER DISTRIBUTION ds2 ATTACH TABLE users DISTRIBUTION KEY tenant_id, user_id; -- Composite key with hash function ALTER DISTRIBUTION ds3 ATTACH TABLE events DISTRIBUTION KEY region_id, event_id HASH FUNCTION MURMUR; ``` ### CREATE TABLE This command is a shorthand for [ALTER DISTRIBUTION ATTACH TABLE](#alter-distribution-attach-table). It creates a table and attaches it to a distribution in a single statement. The `DISTRIBUTED` keyword is optional and has no effect on the behavior. There are two syntax forms for specifying distribution keys: **Parenthesized syntax** — column names and optional hash functions are listed inside parentheses: **DISTRIBUTION KEY syntax** — uses explicit `DISTRIBUTION KEY` clause: `CREATE [DISTRIBUTED] TABLE [schema.] DISTRIBUTION KEY [IN | FOR DISTRIBUTION ]` ```sql theme={null} CREATE [DISTRIBUTED] TABLE [schema.] () [IN | FOR DISTRIBUTION ] ``` **Examples:** ```sql theme={null} -- Simple column with no hash function (uses default distribution) CREATE TABLE r (i); -- Column with hash function CREATE TABLE r (i HASH MURMUR); -- Attach to a specific distribution CREATE TABLE t (id) IN ds1; -- Using FOR DISTRIBUTION syntax CREATE DISTRIBUTED TABLE table1 (i HASH MURMUR) FOR DISTRIBUTION ds1; -- Composite key with multiple columns CREATE TABLE t3 (id HASH MURMUR, id2 HASH MURMUR) IN ds3; -- Routing expression syntax for composite keys with typed columns CREATE TABLE tr (MURMUR [id1 INT HASH, id2 VARCHAR HASH]); -- Schema-qualified table name CREATE DISTRIBUTED TABLE sh1.r (i) IN d; -- DISTRIBUTION KEY syntax CREATE DISTRIBUTED TABLE xx DISTRIBUTION KEY col1, col2 HASH FUNCTION MURMUR IN ds1; -- Composite key without hash using DISTRIBUTION KEY syntax CREATE DISTRIBUTED TABLE copy_test (id1, id2) IN ds2; ``` ### ALTER DISTRIBUTION DETACH TABLE Detaches a table from the distribution using the DETACH TABLE clause. ```sql theme={null} ALTER DISTRIBUTION DETACH TABLE ``` ### ALTER DISTRIBUTION ALTER TABLE DISTRIBUTION KEY Replaces the entire distribution key of an already attached table. This overwrites all column names and hash functions in one shot — it does not patch individual columns. Use this when you need to change the key structure entirely, or to fix expression-based routing entries (which `RENAME DISTRIBUTION COLUMN` does not support). The new key must have the same number of entries as the distribution's column types. Duplicate column names in the new key are rejected. This command does not have well-defined behavior with concurrent read-write operations. Use it during setup or maintenance windows only. ```sql theme={null} ALTER DISTRIBUTION ALTER TABLE DISTRIBUTION KEY [HASH FUNCTION ] where keys is a comma-separated list of column names or expression entries where hashFunction is one of: IDENTITY, MURMUR, CITY ``` **Examples:** ```sql theme={null} -- Replace the distribution key with a new column name ALTER DISTRIBUTION ds1 ALTER TABLE orders DISTRIBUTION KEY customer_id; -- Replace with a composite key ALTER DISTRIBUTION ds1 ALTER TABLE events DISTRIBUTION KEY region_id, event_id; -- Fix an expression-routing entry (RENAME DISTRIBUTION COLUMN cannot do this) ALTER DISTRIBUTION ds1 ALTER TABLE tr DISTRIBUTION KEY MURMUR [correct_id INT HASH, id2 VARCHAR HASH]; ``` ### ALTER DISTRIBUTION ALTER TABLE SCHEMA Changes the schema associated with an attached table in the distribution metadata. ```sql theme={null} ALTER DISTRIBUTION ALTER TABLE SCHEMA SCHEMA ``` **Examples:** ```sql theme={null} -- Change table schema ALTER DISTRIBUTION ds1 ALTER TABLE orders SCHEMA my_schema; ``` ### ALTER DISTRIBUTION RENAME DISTRIBUTION COLUMN A metadata-only fix that renames a column in the distribution key of an attached table. This does **not** execute `ALTER TABLE ... RENAME COLUMN` on PostgreSQL — it only updates the column name stored in SPQR's distribution metadata. The primary use case is fixing a case mismatch between the column name in SPQR metadata and the actual PostgreSQL column. Because PostgreSQL normalizes unquoted identifiers to lowercase, attaching a table with `W_ID` when the table column is `w_id` breaks routing: INSERTs fail and SELECTs scatter to all shards. For composite (multi-column) distribution keys, call this command once per column that needs renaming. This command does not have well-defined behavior with concurrent read-write operations. Use it during setup or maintenance windows only. ```sql theme={null} ALTER DISTRIBUTION ALTER TABLE RENAME DISTRIBUTION COLUMN TO ``` **Examples:** ```sql theme={null} -- Fix column name casing in metadata ALTER DISTRIBUTION ds1 ALTER TABLE rc_test RENAME DISTRIBUTION COLUMN W_ID TO w_id; ``` ### ALTER DISTRIBUTION ADD DEFAULT SHARD Adds a default shard to an existing distribution. Records that do not match any key range will be routed to this shard. ```sql theme={null} ALTER DISTRIBUTION ADD DEFAULT SHARD where is a default shard identifier. The must exist. ``` ### ALTER DISTRIBUTION DROP DEFAULT SHARD Removes the default shard from an existing distribution. ```sql theme={null} ALTER DISTRIBUTION DROP DEFAULT SHARD ``` ### CREATE REFERENCE TABLE Creates a reference table — a table replicated across all or specific shards. The optional AUTO INCREMENT clause creates a sequence for the specified columns. If no shards are specified with ON, the table is created on all shards. You may specify initial sequence value with the START keyword. ```sql theme={null} CREATE REFERENCE TABLE [AUTO INCREMENT [START ], ...] [ON , ...] ``` **Examples:** ```sql theme={null} -- Create a reference table on all shards CREATE REFERENCE TABLE tax_rates; -- Create on specific shards CREATE REFERENCE TABLE tax_rates ON sh1, sh2; -- With auto-increment column CREATE REFERENCE TABLE orders AUTO INCREMENT id; -- With auto-increment starting at a specific value CREATE REFERENCE TABLE orders AUTO INCREMENT id START 1000; ``` ### DROP REFERENCE TABLE Removes a reference table from SPQR metadata. This does not drop the actual table from the shards. ```sql theme={null} DROP REFERENCE TABLE ``` **Examples:** ```sql theme={null} -- Drop a reference table DROP REFERENCE TABLE tax_rates; ``` ### ALTER REFERENCE TABLE STORAGE Changes the set of shards where a reference table is stored. ```sql theme={null} ALTER REFERENCE TABLE STORAGE TO (, ...) ``` **Examples:** ```sql theme={null} -- Change storage to three shards ALTER REFERENCE TABLE tax_rates STORAGE TO (sh1, sh2, sh3); ``` ### DROP SEQUENCE Drops a sequence used for auto-increment columns in reference tables. The optional CASCADE keyword drops dependent objects as well. ```sql theme={null} DROP SEQUENCE [CASCADE] ``` **Examples:** ```sql theme={null} -- Drop a sequence DROP SEQUENCE orders_id_seq; -- Drop a sequence and its dependents DROP SEQUENCE orders_id_seq CASCADE; ``` ### SYNC REFERENCE TABLE Synchronizes a reference table to a target shard by copying data from an existing shard that already has the table. This is useful when adding a new shard to the cluster that needs reference table data, or recovering reference table data on a shard after a failure. This command is not available for installations without SPQR coordinator. The table structure must already exist on the destination shard before running this command. ```sql theme={null} SYNC REFERENCE TABLE ON ``` **Examples:** ```sql theme={null} -- Sync a reference table to a new shard SYNC REFERENCE TABLE tax_rates ON sh3; ``` ### CREATE KEY RANGE This command is used to create a new key range. Since the key space is an ordered set, it is enough to specify only one end of the range. For composite sharding keys, specify multiple values separated by commas that correspond to each column in the distribution. ```sql theme={null} CREATE KEY RANGE [ ] FROM ROUTE TO FOR DISTRIBUTION where lowerBound is a single value or comma-separated values for composite keys (e.g., "100, 'value'" for a two-column key) ``` **Examples:** ```sql theme={null} -- Single column key range CREATE KEY RANGE krid1 FROM 1000 ROUTE TO shard1 FOR DISTRIBUTION ds1; -- Composite key range with two integers CREATE KEY RANGE krid2 FROM 0, 100 ROUTE TO shard2 FOR DISTRIBUTION ds2; -- Composite key range with integer and varchar CREATE KEY RANGE krid3 FROM 100, 'aaaaa' ROUTE TO shard3 FOR DISTRIBUTION ds3; ``` ### CREATE KEY RANGES FOR DISTRIBUTION Automatically creates a set of uniformly distributed key ranges for a distribution. Instead of defining each key range manually, this command inspects the distribution's column type, splits the whole key space (or an explicitly supplied range) into equal parts, and creates one key range per target shard. The key space is divided evenly across the selected shards, and each resulting key range is assigned to one shard in order. Generated key ranges are named `-`, where index starts at 0. By default the command uses the full value range of the distribution column type. Use the optional BETWEEN clause to bound the generated key ranges to a specific interval. When no shard selection is given, key ranges are created for all shards. This command only supports distributions with a single, hashable (numeric) sharding column. Composite keys and non-hashable types such as varchar or uuid are not supported; use CREATE KEY RANGE to define those ranges manually. ```sql theme={null} CREATE KEY RANGES FOR DISTRIBUTION [ BETWEEN AND ] [ USING ALL SHARDS | USING SHARDS [, ...] ] ``` **Examples:** ```sql theme={null} -- Create key ranges for all shards (default) CREATE KEY RANGES FOR DISTRIBUTION ds1; -- Create key ranges explicitly for all shards CREATE KEY RANGES FOR DISTRIBUTION ds1 USING ALL SHARDS; -- Create key ranges for a subset of shards CREATE KEY RANGES FOR DISTRIBUTION ds1 USING SHARDS sh1, sh2; -- Split a bounded interval across all shards CREATE KEY RANGES FOR DISTRIBUTION ds1 BETWEEN 0 AND 1000 USING ALL SHARDS; -- Split a bounded interval across selected shards CREATE KEY RANGES FOR DISTRIBUTION ds1 BETWEEN 0 AND 1000 USING SHARDS sh1, sh2; ``` ### DROP KEY RANGE Removes a key range from the cluster. This deletes the key range metadata but does not affect the actual data stored on the shard. Use DROP KEY RANGE ALL to remove all key ranges at once. ```sql theme={null} DROP KEY RANGE DROP KEY RANGE ALL ``` **Examples:** ```sql theme={null} -- Drop a specific key range DROP KEY RANGE krid1; -- Drop all key ranges DROP KEY RANGE ALL; ``` ### LOCK KEY RANGE Locks a key range to prevent concurrent modifications. This is typically used before performing operations like SPLIT or UNITE that require exclusive access to the key range. While a key range is locked, queries routed to it will receive a `key range is locked` error. ```sql theme={null} LOCK KEY RANGE ``` ### MOVE KEY RANGE Moves the data belonging to a key range to the specified shard. If META ONLY key word is given, then the command only updates the metadata i.e. changes query routing, NOT the data placement. MOVE KEY RANGE ... META ONLY requires the key range to be locked prior. Non-META ONLY version locks all the data for update & lacks pre-run checks, so it's discouraged to use in favour of REDISTRIBUTE KEY RANGE. This version is only available in installations with SPQR-coordinator. ```sql theme={null} MOVE KEY RANGE TO [META ONLY] ``` **Examples:** ```sql theme={null} -- Move a key range to a different shard MOVE KEY RANGE krid1 TO sh2; ``` ### REDISTRIBUTE KEY RANGE Moves a key range to a different shard, including the actual data migration. The operation can be run in check-only mode to validate before applying, and supports batch processing to control the migration pace. This command is not available for installations without SPQR coordinator. ```sql theme={null} REDISTRIBUTE KEY RANGE TO [BATCH SIZE ] [TASK GROUP ] [CHECK | APPLY | NOWAIT] ``` **Examples:** ```sql theme={null} -- Redistribute with default settings REDISTRIBUTE KEY RANGE krid1 TO sh2; -- Redistribute with custom batch size REDISTRIBUTE KEY RANGE krid1 TO sh2 BATCH SIZE 100; -- Check redistribution without applying REDISTRIBUTE KEY RANGE krid1 TO sh2 CHECK; -- Non-blocking redistribution REDISTRIBUTE KEY RANGE krid1 TO sh2 BATCH SIZE 500 NOWAIT; -- Redistribute with task group for tracking REDISTRIBUTE KEY RANGE krid1 TO sh2 TASK GROUP taskgroup1; ``` ### SPLIT KEY RANGE Splits an existing key range into two key ranges at the specified boundary value. The new key range takes the upper portion of the original range (from the split point to the original upper bound), while the source range keeps the lower portion. Both resulting key ranges remain on the same shard. Use REDISTRIBUTE KEY RANGE afterwards to relocate one of them if needed. ```sql theme={null} SPLIT KEY RANGE FROM BY where splitBound is a single value or comma-separated values for composite keys ``` **Examples:** ```sql theme={null} -- Split a key range at integer boundary SPLIT KEY RANGE krid2 FROM krid1 BY 100; -- Split a key range at UUID boundary SPLIT KEY RANGE krid4 FROM krid3 BY 'FF000000-0000-0000-0000-000000000000'; ``` ### UNITE KEY RANGE Merges two adjacent key ranges into a single key range. The left key range absorbs the right key range, extending its upper bound to cover both ranges. The two key ranges must be adjacent (share a boundary) and must route to the same shard. After the operation, the right key range is removed. ```sql theme={null} UNITE KEY RANGE WITH ``` **Examples:** ```sql theme={null} -- Unite two adjacent key ranges UNITE KEY RANGE krid1 WITH krid2; ``` ### UNLOCK KEY RANGE Releases the lock on a key range that was previously locked with LOCK KEY RANGE. After unlocking, queries routed to this key range will resume normal processing. ```sql theme={null} UNLOCK KEY RANGE ``` ### SHOW Displays cluster metadata, topology, configuration, and runtime status. Valid targets: `databases`, `routers`, `shards`, `shards_extended`, `distributions`, `key_ranges`, `key_ranges_extended`, `relations`, `reference_relations`, `pools`, `clients`, `backend_connections`, `hosts`, `version`, `status`, `instance`, `sequences`, `users`, `task_group`, `task_groups`, `task_group_ext`, `task_groups_ext`, `move_task`, `move_tasks`, `redistribute_tasks`, `prepared_statements`, `time_quantiles`, `unique_indexes`, `coordinator_address`, `is_read_only`, `move_stats`, `errors`, `startup_finished`, `two_phase_tx`, `dcs_storage`, `file_settings`, `tsa_cache`. ```sql theme={null} SHOW [()] [WHERE ] [GROUP BY ] [ORDER BY [ASC|DESC]] ``` **Examples:** ```sql theme={null} -- Show all shards SHOW shards; -- Show key ranges with specific columns SHOW key_ranges (key_range_id, shard_id); -- Show backend connections filtered by hostname SHOW backend_connections WHERE hostname = 'localhost'; -- Show clients ordered by user SHOW clients ORDER BY user DESC; -- Show backend connections grouped by shard SHOW backend_connections GROUP BY shard_key_name; ``` ### KILL CLIENT Terminates a specific client connection by its numeric ID. Use SHOW CLIENTS to find client IDs. ```sql theme={null} KILL CLIENT ``` **Examples:** ```sql theme={null} -- Kill a client connection KILL CLIENT 42; ``` ### KILL BACKEND Cancels a backend (shard) connection by its numeric ID. Use SHOW BACKEND\_CONNECTIONS to find backend IDs. ```sql theme={null} KILL BACKEND ``` **Examples:** ```sql theme={null} -- Cancel a backend connection KILL BACKEND 17; ``` ### INVALIDATE CACHE Invalidates the router's schema cache, forcing it to be rebuilt on the next query. ```sql theme={null} INVALIDATE CACHE INVALIDATE SCHEMA CACHE ``` **Examples:** ```sql theme={null} -- Invalidate the schema cache INVALIDATE CACHE; ``` ### INVALIDATE BACKENDS Marks all backend connections as stale, causing them to be re-established. ```sql theme={null} INVALIDATE BACKENDS ``` **Examples:** ```sql theme={null} -- Invalidate all backend connections INVALIDATE BACKENDS; ``` ### INVALIDATE STALE CLIENTS Identifies clients with dead TCP connections and signals them to close. ```sql theme={null} INVALIDATE STALE CLIENTS ``` **Examples:** ```sql theme={null} -- Clean up stale client connections INVALIDATE STALE CLIENTS; ``` ### REGISTER ROUTER Registers a router with the SPQR coordinator. When a router is registered, the coordinator becomes aware of it and can manage it as part of the cluster. After registration, the coordinator automatically synchronizes metadata (shards, key ranges, distributions) to the router. This command is not available for installations without SPQR coordinator. The router ID must be unique across all registered routers. The address must be unique across all registered routers. The router must be reachable at the specified address (a ping check is performed). After registration, metadata is automatically synchronized to the router via gRPC. ```sql theme={null} REGISTER ROUTER ADDRESS
where is a unique identifier for the router (unquoted or quoted string) where
is the network address in the format '[host]:port' ``` **Examples:** ```sql theme={null} -- Register a router on localhost REGISTER ROUTER r1 ADDRESS '[localhost]:6432'; -- Register a router with a custom address REGISTER ROUTER r2 ADDRESS '[router-host]:7432'; ``` ### UNREGISTER ROUTER Unregisters a router from the SPQR coordinator. This removes the router from the coordinator's management. Use ALL to unregister all routers at once. This command is not available for installations without SPQR coordinator. ```sql theme={null} UNREGISTER ROUTER UNREGISTER ROUTER ALL where is the identifier of the router to unregister ``` **Examples:** ```sql theme={null} -- Unregister a specific router UNREGISTER ROUTER r1; -- Unregister all routers UNREGISTER ROUTER ALL; ``` # Default shard Source: https://docs.pg-sharding.tech/sharding/default_shard A distribution can have a default shard that acts as a catch-all fallback. When the router matches a query to a distribution with a default shard, it is guaranteed to find a route — even if the sharding key doesn't fall within any explicitly defined key range. The default shard is implemented as a special key range with the minimum possible value as its lower bound. This key range has a reserved ID: `{DistributionId}.DEFAULT`. For example, if the distribution has column types `[varchar, integer, uuid]`, the default key range lower bound is `["", -9223372036854775808, "00000000-0000-0000-0000-000000000000"]`. ## How to Use It Given a distribution with key range `kr1` starting "From 0", you can add a default shard: ```sql theme={null} ALTER DISTRIBUTION testWithDefault ADD DEFAULT SHARD sh2; -- Add default shard create default shard ------------------------------------ distribution id -> testWithDefault shard id -> sh2 (2 rows) SHOW key_ranges; -- Let's see the key ranges now key_range_id | shard_id | distribution_id | lower_bound | locked -------------------------+----------+-----------------+----------------------+-------- kr18b713215 | sh1 | ds1 | 0 | true testWithDefault.DEFAULT | sh2 | testWithDefault | -9223372036854775808 | false (2 rows) ``` ## How It Works The default shard is implemented as a special key range with ID `{distribution_id}.DEFAULT` that covers the minimum possible value for your column types: | Column Type | Default Lower Bound | | -------------- | ---------------------------------------- | | `integer` | `-9223372036854775808` (MinInt64) | | `uinteger` | `0` | | `varchar` | `""` (empty string) | | `varchar hash` | `0` | | `uuid` | `"00000000-0000-0000-0000-000000000000"` | `uuid` column type does not support default shard. See [issue #1666](https://github.com/pg-sharding/spqr/issues/1666). ## Routing Example ```sql theme={null} -- Setup: key ranges kr1: [-20, -10) → sh2, kr2: [-10, 0) → sh3, kr3: [0, ∞) → sh4 -- Default shard: sh1 (covers MinInt64 to -20) INSERT INTO t(id) VALUES (-30); -- → sh1 (default, below all explicit ranges) INSERT INTO t(id) VALUES (-20); -- → sh2 (kr1) INSERT INTO t(id) VALUES (-10); -- → sh3 (kr2) INSERT INTO t(id) VALUES (0); -- → sh4 (kr3) ``` ## Configuration Set a default shard during distribution creation: ```sql theme={null} CREATE DISTRIBUTION ds1 COLUMN TYPES integer DEFAULT SHARD sh1; ``` Or add it to an existing distribution: ```sql theme={null} ALTER DISTRIBUTION ds1 ADD DEFAULT SHARD sh1; ``` Remove the default shard: ```sql theme={null} ALTER DISTRIBUTION ds1 DROP DEFAULT SHARD; ``` # Distributed Transactions Source: https://docs.pg-sharding.tech/sharding/distributed_transactions How to use two-phase commit and understand transaction commit strategies ## Transaction Behavior ### Default Route Behaviour [query\_routing.default\_route\_behaviour](/configuration/router#query-routing-settings) parameter determines what happens when SPQR cannot route a query to a single shard: * `BLOCK`: Rejects queries that would need to execute on multiple shards (safer for consistency) * `ALLOW`: Permits multi-shard queries to execute (useful for DDL and administrative queries) ```sql theme={null} -- Default behavior blocks multi-shard queries DROP DATABASE prod; -- ERROR: query is blocked due to the default_route_behavior. -- Allow multi-shard execution DROP DATABASE prod /* __spqr__default_route_behaviour: allow */; -- NOTICE: send query to shard(s) : shard1,shard2,shard3,shard4 ``` Use Cases: * `BLOCK`: Production environments where you want strict control over shard access * `ALLOW`: Development, testing, or when executing DDL commands across all shards ### Scatter Query `__spqr__scatter_query` routing hint is useful when you need to run a query across all shards, regardless of sharding key: ```sql theme={null} -- Execute on all shards SELECT COUNT(*) FROM users /* __spqr__scatter_query: true */; -- Can also be set at session level SET __spqr__scatter_query TO true; SELECT * FROM users; -- Executes on all shards RESET __spqr__scatter_query; ``` Scatter queries do not provide consistent cross-shard snapshots, can have significant performance impact on large clusters, and should be used primarily for administrative or analytical purposes. ### Bulk Operations SPQR supports bulk data loading across multiple shards, including COPY FROM and batch INSERT statements. When engine V2 is enabled, multi-value INSERT statements are automatically split and routed to the correct shards. For detailed information, examples, and limitations, see [Bulk Operations](/sharding/bulk). ## Commit Strategies SPQR provides three commit strategies for distributed transactions, controlled by the [default\_commit\_strategy](/configuration/router#transaction-settings) parameter. ### 1. One-Phase Commit Value: `1pc` (alias: `best-effort`) This is the default commit strategy. In this mode transactions are committed on each shard independently with no coordination between shards and is best used for **single-shard** transactions **or** when **eventual consistency** is acceptable. If a transaction spans multiple shards, partial commits are possible if one shard fails. ```sql theme={null} -- You may explicitly set if needed -- SET __spqr__commit_strategy TO '1pc'; BEGIN; INSERT INTO users (id, name) VALUES (1, 'Alice'); INSERT INTO orders (id, user_id) VALUES (100, 1); COMMIT; ``` ### 2. Two-Phase Commit (2PC) To use 2PC, you need to ensure the following: * Set [max\_prepared\_transactions](https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS) on each shard. * Enable [enhanced\_multishard\_processing](/configuration/router#query-routing-settings). Two-phase commit ensures atomicity across multiple shards using [PostgreSQL's prepared transaction](https://www.postgresql.org/docs/current/sql-prepare-transaction.html) feature. How 2PC Works: 1. Prepare Phase: SPQR sends `PREPARE TRANSACTION` to all involved shards with a unique transaction ID 2. Commit Phase: If all shards successfully prepare, SPQR sends `COMMIT PREPARED` to each shard 3. Rollback on Failure: If any shard fails to prepare, SPQR rolls back the entire transaction Example: ```sql theme={null} -- Session-level setting SET __spqr__commit_strategy TO '2pc'; BEGIN; -- Multi-shard transaction INSERT INTO shard1_table (id, data) VALUES (1, 'data1'); INSERT INTO shard2_table (id, data) VALUES (2, 'data2'); COMMIT; -- Uses 2PC automatically ``` Or use it for a specific transaction: ```sql theme={null} BEGIN; SET __spqr__commit_strategy TO '2pc'; INSERT INTO shard1_table (id, data) VALUES (1, 'data1'); INSERT INTO shard2_table (id, data) VALUES (2, 'data2'); COMMIT; ``` 2PC provides atomicity but has performance overhead. Prepared transactions consume resources on shards until committed or rolled back. Monitor prepared transactions with `pg_prepared_xacts` on each shard and configure `max_prepared_transactions` based on your expected concurrent transaction volume. 2PC requires a durable transaction coordinator that issues prepare/commit decisions and can recover them after failures; without a durable coordinator, prepared transactions may become orphaned and require manual intervention. SPQR's router currently acts as the ephemeral coordinator for multi-shard transactions but there is no separate persistent coordinator service implemented yet. ## Virtual Queries When not connected to a shard, SPQR buffers transaction commands locally: ```sql theme={null} BEGIN; SET __spqr__commit_strategy TO '2pc'; -- No shard connection yet, parameters buffered SET client_encoding = 'UTF8'; -- Not yet SET standard_conforming_strings = on; -- Wait.. SET DateStyle = ISO, MDY; -- Wait... SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- No shard connection yet, parameters buffered INSERT INTO users (id, name) VALUES (1, 'Alice'); -- Connection established here COMMIT; ``` # Hash-based sharding Source: https://docs.pg-sharding.tech/sharding/hashed Hash-based sharding is a method of distributing data across multiple database shards by applying a hash function to a specific key. The hash function generates a hash value, which is then used to determine the shard where the data should be stored. This approach ensures a more uniform distribution of data compared to [direct key sharding](/sharding/ranged). Hash-based sharding **still uses key ranges**, just like direct key sharding. The difference is that your key is first transformed by a hash function (e.g., MURMUR or CITY), and the resulting hash value is then routed using key ranges defined over the hash space. For example, if you are sharding based on user IDs, the hash function will take a user ID as input and produce a hash value. This hash value is then mapped to one of the available shards using key ranges. The same hash function is used consistently to ensure that the same key always maps to the same shard. Hash-based sharding helps to evenly distribute the data and load across all shards, reducing the risk of hotspots and ensuring better performance and scalability. However, it can make range queries more complex, as the data for a given range of keys may be spread across multiple shards. SPQR also supports [composite (multi-column) sharding keys](/sharding/composite_keys) with hash functions, which is particularly useful when you have a low-cardinality first column combined with a high-cardinality second column. hashed ## How to use it? To use a hash function as your distribution(sharding) key, you need to specify it in the administrative router or coordination console using the `HASH FUNCTION` keyword: ```sql theme={null} ALTER DISTRIBUTION ds1 ATTACH RELATION r4 DISTRIBUTION KEY col1 HASH FUNCTION CITY; attach table ------------------------ relation name -> r4 distribution id -> ds1 (2 rows) ``` Then, you create key ranges over the hash space. For hash functions like MURMUR, the output is a 32-bit unsigned integer, so the hash space is `0` to `4294967295`: ```sql theme={null} -- Split hash space between two shards -- First half: 0 to 2147483647 -> shard01 -- Second half: 2147483648 to 4294967295 -> shard02 CREATE KEY RANGE krid1 FROM 0 ROUTE TO shard01 FOR DISTRIBUTION ds1; add key range --------------- bound -> 0 (1 row) CREATE KEY RANGE krid2 FROM 2147483648 ROUTE TO shard02 FOR DISTRIBUTION ds1; add key range --------------- bound -> 2147483648 (1 row) ``` When creating multiple key ranges, create them in descending order by lower bound (highest first). This ensures proper range boundary handling. For more examples, see [this test](https://github.com/pg-sharding/spqr/blob/master/test/regress/tests/console/expected/hash.out). # Manual shard rebalancing Source: https://docs.pg-sharding.tech/sharding/manual_rebalancing Move data between shards online using the coordinator console This guide covers manual rebalancing. It may be useful when you need full control over which key ranges move and when. For automated redistribution of UUID-based distributions, you may take a look at [redmon](https://github.com/pg-sharding/redmon). Built-in auto-rebalancing is tracked in [#1960](https://github.com/pg-sharding/spqr/issues/1960). ## When to use it Use this flow when you need explicit control over what moves and when: * Add a new empty shard and fill it with data from existing shards. * Remove a shard — first move all its data elsewhere, then delete it. * Quickly fix a hotspot (overloaded data range) without waiting for the balancer. * Move a specific tenant’s data to a separate shard. ## Prerequisites Before starting, make sure that: * The cluster is deployed with a **coordinator** (or via [spqrinfra](/deployment/spqrinfra)). Data rebalancing is not available in [bare-router](/deployment/bare_router) deployments that run without a coordinator. * The destination shard is registered in the cluster and visible via `SHOW shards`. See [Deployment overview](/deployment/overview) and [Coordinator](/sharding/cluster_components/coordinator) for how shards are added. * Both [`spqrguard`](https://github.com/pg-sharding/spqrguard) and `postgres_fdw` extensions are installed on the source and destination shards. * `spqrguard` is added to `shared_preload_libraries` in `postgresql.conf`. `spqrguard` is optional but strongly recommended. It prevents direct inserts into shards that bypass SPQR, protecting against data corruption. ## 1. Connect to the coordinator Rebalancing commands are executed against the coordinator's administrative console, not the router. Use `psql` over the PostgreSQL protocol as described in [How to connect](/sharding/console/how_to_connect). In high-availability setups, SPQR runs multiple coordinator instances, but only one of them is active at a time (see [Coordinator configuration](/configuration/coordinator)). ## 2. Inspect the current topology Check the shards and the current key range layout: ```sql theme={null} SHOW shards; shard | options -------+--------------------------------------------------------- sh1 | {host=spqr_shard_1:6432,host=spqr_shard_1_replica:6432} sh2 | {host=spqr_shard_2:6432,host=spqr_shard_2_replica:6432} sh3 | {host=spqr_shard_3:6432,host=spqr_shard_3_replica:6432} (3 rows) SHOW key_ranges; key_range_id | shard_id | distribution_id | lower_bound | locked --------------+----------+-----------------+-------------+-------- krid1 | sh1 | ds1 | 0 | false krid2 | sh2 | ds1 | 335000 | false krid3 | sh3 | ds1 | 670000 | false ``` ## 3. Plan the splits You can redistribute an entire key range in one go — there is no technical limitation preventing it. In practice, however, it is common to split the source key range into several smaller pieces first and move them one by one. This gives you more control: you can monitor each piece independently, stop at any point, and limit the impact on the running application. When in doubt, prefer smaller pieces — you can always [UNITE](/sharding/console/sql_commands#unite-key-range) them later. ## 4. Split the source key range [`SPLIT KEY RANGE`](/sharding/console/sql_commands#split-key-range) turns one key range into two, splitting it at the given bound. Both resulting ranges continue to point to the same shard until you explicitly redistribute one of them. Example — splitting `krid1` (covering `0..335000` on `sh1`) into four pieces: ```sql theme={null} SPLIT KEY RANGE krid1_b FROM krid1 BY 100000; -- krid1 = 0..100000, krid1_b = 100000..335000 SPLIT KEY RANGE krid1_c FROM krid1_b BY 200000; -- krid1_b = 100000..200000, krid1_c = 200000..335000 SPLIT KEY RANGE krid1_d FROM krid1_c BY 300000; -- krid1_c = 200000..300000, krid1_d = 300000..335000 ``` Verify with `SHOW key_ranges`. ## 5. Redistribute key ranges to the destination shard Move the new key ranges with [`REDISTRIBUTE KEY RANGE`](/sharding/console/sql_commands#redistribute-key-range). It migrates both the metadata and the actual data. Internally the coordinator splits the source key range into batches of `BATCH SIZE` rows and moves them one by one as separate *move tasks*, all tracked under a single *task group*. While a batch is in flight, the rows it covers are briefly unavailable for writes. ```sql theme={null} REDISTRIBUTE KEY RANGE krid1_b TO sh2 BATCH SIZE 1000 NOWAIT; REDISTRIBUTE KEY RANGE krid1_c TO sh2 BATCH SIZE 1000 NOWAIT; REDISTRIBUTE KEY RANGE krid1_d TO sh2 BATCH SIZE 1000 NOWAIT; ``` `NOWAIT` returns control immediately and lets you observe the task group asynchronously; without it, the session blocks until the task group finishes. Use the `CHECK` modifier if you want to validate the operation without performing the move. Run the redistribute on a single key range first and wait for it to complete before launching the rest. This validates the path end-to-end at production load before you commit to moving everything. ### Choosing a batch size `BATCH SIZE` controls how many rows each move task transfers at a time. It is a tradeoff: * **Smaller batches** — shorter per-batch unavailability, smoother impact on the application, more total batches and therefore longer total wall-clock time. * **Larger batches** — fewer round trips and faster overall, but each batch locks a larger slice of the key range for longer. There is no universal formula — the right batch size can only be found empirically. Start with `500`–`1000` and adjust from there; depending on your workload and data, you may end up anywhere up to `1000000`. Monitor the impact on the application (see the next section) and increase the batch size as long as it remains acceptable. ## 6. Monitor the move While a redistribute is running, watch: * **`SHOW task_group`** on the coordinator — current state of every task group and its move tasks. Possible values for `state` are `PLANNED`, `RUNNING` and `ERROR`; a task group disappears from the listing once it has finished successfully. * **Application error rate and latency** — in particular client-visible 5xx errors and write latency on the affected key range. * **Shard load** — CPU, disk and replication lag on both source and destination shards. ## 7. React to problems If something goes wrong during the rebalancing, follow this escalation order. ### Stop the problematic task group If one redistribute starts misbehaving (for example, replication lag on the destination shard is growing), stop just that task group and leave the others running: ```sql theme={null} STOP TASK GROUP ; ``` To stop all task groups at once: ```sql theme={null} STOP TASK GROUP ALL; ``` ### Retry if the issue was transient If a task group ended up in an error state due to a transient issue (network blip, restarted shard, etc.), retry it: ```sql theme={null} RETRY TASK GROUP ; -- or, asynchronously RETRY TASK GROUP NOWAIT; ``` ### Recovering from partial failure A failed task group can leave affected key ranges in a **locked** state. While a key range is locked, any application query that touches it will receive a `key range is locked` error. **Do not manually unlock key ranges** — this is unsafe and can lead to data loss. If a task group fails mid-flight: 1. Run `SHOW task_group` to identify the failed task group and its error. 2. Run `SHOW key_ranges` to see which key ranges are locked and where the data currently lives. 3. Investigate **why** the task failed (check coordinator and shard logs). 4. Fix the underlying issue and `RETRY TASK GROUP` to let it finish — this is the safest way to unlock the affected key ranges. Every partial failure is different. There is no universal recovery recipe — you need to understand the specific error and determine where the most recent data resides before taking action. ## 8. Post-rebalancing cleanup Once all redistributes have finished, verify with `SHOW key_ranges` that the layout matches what you expect and spot-check that data actually lives on the right shards. If two adjacent key ranges now point to the same shard, you can merge them with [`UNITE KEY RANGE`](/sharding/console/sql_commands#unite-key-range). # Direct key sharding Source: https://docs.pg-sharding.tech/sharding/ranged Direct key sharding (also known as range-based sharding) is a method of distributing data across multiple database shards where each shard holds a contiguous range of data. The sharding key value is used directly to determine which key range (and therefore which shard) the data belongs to. For example, if you are sharding a database of user records by user ID, you might have one shard that holds users with IDs from 1 to 1000, another shard that holds users with IDs from 1001 to 2000, and so on. This method allows for efficient range queries, as all the data within a specific range is located on the same shard. However, it can lead to uneven distribution of data and load between key ranges. If you need more uniform distribution, consider using [hash-based sharding](/sharding/hashed), which also uses key ranges but applies a hash function to the key first. SPQR also supports [composite (multi-column) sharding keys](/sharding/composite_keys) for more complex range-based sharding scenarios, such as sharding by tenant ID and user ID together. ranged ## How to use it? In administrative router or coordinator console it looks like this: ```sql theme={null} CREATE KEY RANGE krid1 FROM 1 ROUTE TO shard01 FOR DISTRIBUTION ds1; add key range --------------- bound -> 1 (1 row) ``` For clarity, we recommend you take a look [get started](/welcome/get_started) guide. # Reference Tables Source: https://docs.pg-sharding.tech/sharding/reference_tables A reference table is a table that is fully replicated across all shards instead of being divided into separate partitions. This allows **every shard to have a local copy of the table**, reducing network overhead and ensuring fast access to frequently used data. Reference tables are useful when you need: * Consistent access to shared data across all shards. * Fast lookups without cross-shard queries. * Integrity constraints on small tables used across different parts of an application. ## Examples Some typical use cases for reference tables include: * **eCommerce**: Sales tax rates apply to all stores. * **Common codes**: A table with country codes and region names. * **Roles**: A system-wide list of roles and permissions. * **Finance**: Exchange rates. * **Global settings**: Settings or feature flags. * **SaaS**: A table defining different subscription tiers, their pricing, and feature sets. ## How to use it? Creating a reference table in SPQR is just a metadata operation. You will need to create this table on the shards as well, either through the SPQR router or any other method you prefer. To create a reference table, use the SPQR administrative console: ```sql theme={null} \c spqr-console CREATE REFERENCE TABLE tax_rates; attach table ------------------------------- relation name -> tax_rates distribution id -> REPLICATED (2 rows) ``` Then, create the table and insert data: ```sql theme={null} \c ordersdb CREATE TABLE tax_rates ( country_code VARCHAR(2), state_code VARCHAR(2), tax_rate NUMERIC(5, 2), tax_category VARCHAR(50) ); NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4 COPY tax_rates FROM 'test_tax_rates.csv' DELIMITER '\t'; NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4 ``` Verify replication: ```sql theme={null} SELECT * FROM tax_rates /* __spqr__execute_on: sh1 */; NOTICE: send query to shard(s) : sh1 country_code | state_code | tax_rate | tax_category --------------+------------+----------+-------------- US | CA | 7.25 | Sales US | NY | 8.875 | Sales GB | | 20.00 | VAT CA | | 5.00 | GST AU | | 10.00 | GST IN | | 18.00 | GST (6 rows) SELECT * FROM tax_rates /* __spqr__execute_on: sh2 */; NOTICE: send query to shard(s) : sh2 country_code | state_code | tax_rate | tax_category --------------+------------+----------+-------------- US | CA | 7.25 | Sales US | NY | 8.875 | Sales GB | | 20.00 | VAT CA | | 5.00 | GST AU | | 10.00 | GST IN | | 18.00 | GST (6 rows) ``` ## Sequences In some cases, it may be beneficial to avoid manually specifying a unique identifier (commonly the `id` column) when creating new records. Instead, you can rely on the [AUTO INCREMENT](https://www.postgresql.org/docs/11/sql-createsequence.html) feature provided by the database. For reference tables, this can be challenging because the `id` values must remain synchronized across all shards. Fortunately, SPQR supports this functionality through a feature called **sequences**. Here’s an example of how you can automatically generate `id` values. To create a reference table with country codes and region names, follow these steps: ```sql theme={null} \c spqr-console CREATE REFERENCE TABLE country_regions AUTO INCREMENT id; -- you may specify initial sequence value via START keyword attach table ------------------------------- relation name -> country_regions distribution id -> REPLICATED (2 rows) ``` Then, create the table and insert data: ```sql theme={null} \c ordersdb CREATE TABLE country_regions ( id INT, country_code VARCHAR(2), region_name VARCHAR(100) ); NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4 INSERT INTO country_regions (country_code, region_name) VALUES ('US', 'United States'), ('CA', 'Canada'), ('GB', 'United Kingdom'), ('AU', 'Australia'), ('IN', 'India'); NOTICE: send query to shard(s) : sh1,sh2,sh3,sh4 ``` Verify replication: ```sql theme={null} SELECT * FROM country_regions /* __spqr__execute_on: sh2 */; NOTICE: send query to shard(s) : sh2 id | country_code | region_name ----+--------------+------------------- 1 | US | United States 2 | CA | Canada 3 | GB | United Kingdom 4 | AU | Australia 5 | IN | India ``` Whenever router need new autoincrement value it ask it from coordinator. To improve performance generating autoincrement values you can set `identity_range_size` in router config file. This parameter set size of range identifiers which router get from coordinator in a time. To remove a sequence, use the [DROP SEQUENCE](/sharding/console/sql_commands#drop-sequence) command in the SPQR console: ```sql theme={null} DROP SEQUENCE country_regions_id CASCADE; ``` # Sharded Tables Source: https://docs.pg-sharding.tech/sharding/sharded_tables Sharded tables, also known as **replicated tables** or **distributed tables**, are tables that are distributed across multiple shards, allowing each shard to store a portion of the data. Sharded tables are useful when you need or have: * Distributed data across multiple shards for performance and scalability. * Large datasets that need to be partitioned. * High availability and fault tolerance by spreading data across multiple servers. * Certain types of queries isolated to specific shards, reducing the query load on other parts of the system. ## Examples Some typical use cases for sharded tables include: * **eCommerce**: Product catalogs, where each product has information about categories, descriptions, and prices, which can be sharded based on product IDs. * **Social Media**: User data and posts, sharded by user ID. * **Finance**: Transaction records, sharded by account number or transaction ID. * **IoT**: Data from devices, sharded by device ID or geographical region. * **Logs and Analytics**: Event logs and analytics data, sharded by date or region. ## How to use it? See the [Get Started](/welcome/get_started) guide. # Design Goals Source: https://docs.pg-sharding.tech/welcome/design We followed the following core principles during the development of SPQR: 1. Use highly available clusters as building blocks for sharding installations. These clusters can be based on Patroni, Stolon, Managed PostgreSQL, or any other HA solution over vanilla Postgres. Physical quorum-based PostgreSQL HA solutions are battle-proven, and we reuse all their benefits. 2. Zero downtime for conversion from monolith to sharded cluster and vice versa. The existing database (monolith) is the initial shard for horizontal scaling. Then we add new nodes and move data to them without downtime, eventually having multiple shards. When needed, we repeat the process, adding more shards – again, without downtime. And if at some point we want to go back to the monolith, we use the same tooling for that, without downtime and without the need to restore from backups. 3. SPQR dev/test environments should be installable on a developer's laptop or Raspberry Pi in minutes, not hours in a datacenter. 4. SPQR is optimized for single-shard OLTP queries. We aim to have a very low latency overhead (1-2 ms) introduced by SPQR for such queries. 5. Data migration between shards aims to balance the workload across shards proportionally. Migrations must cause as little locking impact as possible. One of the ways to achieve that is to reduce range size. Liquid migrations should allow transferring between clouds. However, in this case, temporary high latencies are inevitable. ## Why Stateless? There are some good sharding solutions relying on the Postgres codebase for routing. This is a reliable and maintainable design decision. One of the obvious benefits of this approach is that the SQL grammar is always compatible between the same versions of Postgres. Postgres, as any DBMS, solves the hard problem of state management. And the most important state is system catalog - metadata, data about your data. Postgres allows you to see a snapshot of the structure of your data in the past. To make the performance of the system catalog acceptable, it employs a sophisticated system of caches with rather tricky invalidations. At the beginning of our journey to sharding solutions, we tried to implement FDW-based sharding and custom node based sharding. Eventually, we concluded that both catalog and caches are excessive elements for the task of query routing. At query routing time, Postgres core checks that column data types pose necessary casts, support functions, operators, etc. The "analyze" and "rewrite" phases of the query routing made latencies go unreasonably high. That's why we decided to build a query routing component that knows about data structure as little as possible. SPQR does not preserve any data besides routing rules. ## Why Router? It is a good idea to hack your favourite database driver and implement all the routing logic there. This will be fast, relatively cheap, and maintainable. However, as soon as you come to migrating data between shards or fully resharding, you will face serious problems. By the way, it is not always possible to modify the source code of an application. In such a situation, only a proxy can be used between the application and the database. # FAQ Source: https://docs.pg-sharding.tech/welcome/faq Frequently Asked Questions ## General Questions ### When should SPQR be used, and when is it not a good fit? **Use SPQR when:** * Your data no longer fits on a single PostgreSQL instance (> several TB). * You experience performance degradation in reads/writes, and vertical scaling has reached its limits. * You need to “cool down” part of your data without losing availability. * Your database is already sharded and you need automated re-sharding. * Recommended to start sharding when you have >40 CPU cores, >4 hosts, or >600 GB of data. **SPQR is not suitable when:** * You have complex queries involving multiple shards (e.g., cross-shard JOINs). * Transactions modify data across multiple shards. * You need OLAP workloads — use **Greenplum** instead. ### Are JSONB and large objects supported? Yes. SPQR is fully compatible with PostgreSQL data types, including JSONB. Keep in mind that large objects can impact network performance. ### How does SPQR differ from other solutions (Citus, ShardingSphere, Neon, Vitess, YDB)? * **License:** SPQR uses the open PostgreSQL license. * **Simplicity:** SPQR is built to solve a single, focused problem — routing application queries to shards. * **Performance:** SPQR is optimized for minimal overhead. ### What technologies is SPQR built on, and how is it configured? SPQR is written in **Go** and works directly at the **PostgreSQL protocol** level. Sharding rules are stored in **etcd**, a distributed key-value store providing consistent, low-latency coordination. Rules are configured via an **administrative console** (PostgreSQL-compatible protocol). ### Can SPQR be used with other systems and cloud databases? Yes! SPQR is compatible with managed PostgreSQL databases (Yandex Managed PostgreSQL, AWS RDS, etc.). You can even use **Neon** as a shard for data storage while SPQR routes queries between them. ### How to ensure SPQR high availability? * Deploy at least **3 routers** in different availability zones. * Set up alerts for metrics (CPU, connections, errors). * Use HA-managed services for shards (Odyssey/PgBouncer as connection poolers, PgConsul/Patroni for HA management). ### What happens under high load? It depends on the workload type. Example: 95% writes, 5% reads. If the router’s configuration parameter [`default_target_session_attrs`](https://pg-sharding.tech/configuration/router#query-routing-settings) = `read-only`, read queries are distributed among replicas. If a replica becomes unresponsive (`SELECT pg_is_in_recovery();` timeout), SPQR stops sending queries to it and periodically checks its status in the background. Once it’s healthy again, traffic resumes automatically. ### Are there risks of duplicated queries when using a load balancer in front of SPQR? Yes. If a client disconnects and the load balancer retries the query, SPQR treats it as a new one — potentially causing duplication (e.g., `INSERT`). Use **idempotent operations** or implement deduplication at the application layer. ### How to set up backups? Full **PITR** (Point-In-Time Recovery) for the SPQR cluster consists of shard PITRs plus a backup of SPQR metadata stored in etcd. We recommend using **WAL-G** for this purpose. ### How to diagnose issues and collect statistics? Enable debug logging in the router config and use `/* __spqr__reply_notice = true */` for detailed error messages. The router provides a PostgreSQL-compatible admin console with `SHOW` commands for metrics (e.g., `SHOW clients WHERE dbname = testdb;` or `SHOW shards;`). ### How to configure logging and tracing? Use the following router parameters: * `log_level` — debug, info, warning, error, fatal * `pretty_logging` — enables structured output * `pgproto_debug` — detailed PostgreSQL protocol logging * `notice_message_format` — configures NOTICE message formatting Latency metrics are available via `time_quantiles`. ## Transactions ### How does SPQR process SQL queries? SPQR handles queries differently depending on their type and context: 1. **Regular queries:** SPQR parses the query, identifies the target table, column, and key value, then matches it to pre-defined sharding rules (range or hash). It routes the query to the corresponding shard. 2. **Queries with routing hints:** Query routing can be influenced by special hints (provided as SQL comments or router settings). 3. **Transactions:** When `BEGIN TRANSACTION` is received, SPQR buffers subsequent statements (like `SET` commands) in memory. The entire transaction is sent to a specific shard only when the target shard can be determined unambiguously. ### How are multi-shard transactions handled? For atomic cross-shard transactions, use **two-phase commit (2PC):** 1. At session start: `SET __spqr__commit_strategy TO '2pc'`. 2. Or add `/* __spqr__commit_strategy: 2pc */` to your `COMMIT`. 3. Ensure `max_prepared_transactions` > 0 on all shards. *Note:* Without 2PC, changes may be partially applied. ### Can queries spanning multiple shards be executed? Yes, but with limitations: * For cross-shard operations, use `/* __spqr__engine_v2: true */`. * **Batch INSERT**: Multi-value INSERT statements targeting different shards are automatically split and routed (engine V2 required). * **COPY FROM**: Works automatically, no hint required. Data is routed to correct shards based on sharding key. * For atomic multi-shard transactions, use [two-phase commit](/sharding/distributed_transactions#2-two-phase-commit-2pc). ### How to explicitly route a query to a specific shard? SPQR supports virtual parameters (hints), set via SQL comments or `SET` statements: * `__spqr__execute_on` — target specific shard * `__spqr__distribution` — choose routing distribution * `__spqr__scatter_query` — broadcast query to all shards ### What commit strategies are supported? The `__spqr__commit_strategy` hint defines the transaction commit mode: * `best-effort` — default mode, same as `1pc` * `1pc` — one-phase commit (commits each shard independently, no atomicity guarantees across shards) * `2pc` — two-phase commit (atomic commits across shards, requires `/* __spqr__engine_v2: true */` and PostgreSQL `max_prepared_transactions` > 0). For comprehensive information, see [Distributed Transactions](/sharding/distributed_transactions). ### How to use reference tables? Reference tables are replicated across all shards. Create them with: ```sql theme={null} CREATE REFERENCE TABLE table_name (...); ``` or via: ```sql theme={null} CREATE DISTRIBUTION ds_repl COLUMN TYPES ... REPLICATED; ALTER DISTRIBUTION ds_repl ATTACH RELATION table_name ...; ``` Data automatically replicates to all shards. Use `/* __spqr__engine_v2: true */` for writes. ### Can related tables be sharded by the same key? Yes — SPQR allows colocating related data on the same shard, enabling efficient intra-shard JOINs. ### Does SPQR support distributed sequences? Yes — via `CREATE REFERENCE TABLE ... AUTO INCREMENT`. SPQR guarantees globally unique IDs. ### How are queries without a sharding key handled? By default, multi-shard queries (without a key) are **forbidden**. You can enable them with `/* __spqr__scatter_query: true */`. Results from all shards are merged, but consistency is not guaranteed. ## Connections and Authentication ### How to connect to SPQR? SPQR uses the standard PostgreSQL protocol — applications see it as a regular PostgreSQL cluster. You can connect via `psql` or any PostgreSQL driver using the router’s host and port. The admin console runs on a separate port (defined by `admin_console_port`), e.g.: ```bash theme={null} psql host=localhost sslmode=require user=demo dbname=demo port=6432 ``` ### How do session and transaction pooling work? SPQR supports pooling modes similar to Odyssey/PgBouncer. Configured via `pool_mode` in frontend rules: * `SESSION` — a client session is bound to one backend connection * `TRANSACTION` — connection is released to the pool after each transaction ### What options exist for connection routing control? See [Routing hints](https://pg-sharding.tech/routing/hints). ### Are external drivers (pgx, JDBC, etc.) supported? Yes. SPQR speaks the PostgreSQL wire protocol, so most drivers are compatible. However, some versions of **pgx** may hang during connection initialization — test your driver setup and report issues to SPQR developers. ## Performance ### How does SPQR performance compare to vanilla PostgreSQL? * SPQR shows **\~10–30% performance drop** depending on workload type compared to vanilla PostgreSQL. * Optimization work is ongoing. ### What hardware resources are required for routers and coordinators? It depends on workload. For high RPS (100K+), use strong CPUs (e.g., Intel Ice Lake) and >=16 GB RAM. Example configurations for \~20,000 RPS: **Minimal:** * 3 routers × 4 vCPU, 16 GB RAM * 3 coordinators × 1 vCPU, 4 GB RAM * **Total:** 15 vCPU, 60 GB RAM **Optimal:** * 3 routers × 4 vCPU, 16 GB RAM * 3 coordinators × 2 vCPU, 8 GB RAM * **Total:** 18 vCPU, 72 GB RAM **With headroom:** * 5 routers × 4 vCPU, 16 GB RAM * 3 coordinators × 2 vCPU, 8 GB RAM * **Total:** 26 vCPU, 104 GB RAM ## Data Migration and Rebalancing ### How to shard an existing database? Steps: 1. Connect your current cluster as the first shard. 2. Define sharding keys for your tables. 3. Create key ranges covering the entire key space. 4. Add new shards to the config and move data using `REDISTRIBUTE KEY RANGE`. ### How to configure application connections to SPQR? * Use standard PostgreSQL drivers (e.g., `pgx`). * Specify all routers in your configuration for load balancing. ### How to shard by a composite key (multiple columns)? ```sql theme={null} CREATE DISTRIBUTION ds1 COLUMN TYPES integer, varchar; ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY user_id, order_date; ``` ### How to create a default shard for unassigned keys? ```sql theme={null} ALTER DISTRIBUTION {distribution_id} ADD DEFAULT SHARD {shard}; ``` ### How to add a new shard and rebalance data? 1. Add a new shard in `router.yaml` / `coordinator.yaml` or via `CREATE SHARD`. 2. Reassign key ranges using: * `SPLIT KEY RANGE` — split range * `MOVE KEY RANGE` — move range (locks data) * `REDISTRIBUTE KEY RANGE` — automatic migration 3. Use `SYNC REFERENCE TABLES` to copy reference tables. Rebalancing can be **automatic** (based on CPU/disk load, ε-split) or **manual**. ### What data types are supported for sharding? * Integer (`int`, `bigint`) * String (`varchar`) * UUID * Hash functions: `CITY`, `MURMUR` (for integers only) — **custom hash functions are not supported** * Composite keys ### Are JOINs between shards supported? No. JOINs are only allowed within a single shard. To handle related data, use **colocation** (store related tables on the same shard via a shared key). ### How does SPQR manage connection limits? Connection-level limits (rate limits, user quotas) are planned but not yet implemented. ### Does SPQR provide query deduplication or retry policy? Not yet. If a client disconnects and retries, SPQR treats it as a new query. Configurable retry strategies are planned. ### When I see `error processing query ... : syntax error`, what should I do? This usually indicates an internal SPQR parser issue rather than an actual SQL syntax error. **1. Report it to SPQR developers:** * Include the full query text * SPQR and PostgreSQL versions * Sharding configuration **2. Temporary workaround:** * Check your query against SPQR documentation * Simplify the query (split it, remove complex constructs) **Why does this happen?** SPQR uses its own SQL parser, which may not fully support: * Rare PostgreSQL operators * Nonstandard syntax * Custom functions # Get Started Source: https://docs.pg-sharding.tech/welcome/get_started Start sharding your PostgreSQL cluster in 5 minutes Let's set up the simplest possible installation scenario - when you have two shards and one router. For the fastest experience, use our pre-built Docker image: **Available tags:** * `latest` - Latest stable release (recommended for production) * `stable` - Same as latest * `nightly` - Built from master branch (latest features) * `v1.2.3` - Specific version **Ports:** * `6432` - Router port (your app connects here) * `7432` - Admin console (configure sharding) * `7000` - gRPC API You'll need to set up PostgreSQL shards and configure the router. See the [sharding setup](#setting-up-sharding) section below for complete instructions. For local development, you need [the latest Go version](https://go.dev/dl/). How to build: ```bash theme={null} make make build ``` How to run: ```bash theme={null} spqr-router run --config path-to-router-config.yaml ``` ## Setting up sharding First, you need to configure the rules by which the router will decide which of the shards to send each request to. For this purpose, SPQR has an **administrative console**. This is an app that works by PostgreSQL protocol and you can connect to it by usual psql. You can find the console port in your config file. ```sql theme={null} ➜ psql "host=localhost sslmode=disable user=demo dbname=demo port=7432" SPQR router admin console Here you can set up your own routing rules ------------------------------------------------ You can find the documentation here https://github.com/pg-sharding/spqr/tree/master/docs psql (14.5 (Homebrew), server console) Type "help" to get help. demo=> SHOW shards; listing data shards ----------------------- datashard with ID shard1 datashard with ID shard2 (2 rows) ``` To make all things work, the router needs to know the following: * What tables do you query * Which columns in each table should the router search for * Types of these columns * Mapping from \[range of values] to \[shard number] Let's create a distribution first: ```sql theme={null} ➜ psql "host=localhost sslmode=disable user=demo dbname=demo port=7432" demo=> CREATE DISTRIBUTION ds1 COLUMN TYPES integer; add distribution ---------------------------------- created distribution with id ds1 (1 row) ``` The next step is to specify a list of tables and columns. ```sql theme={null} demo=> ALTER DISTRIBUTION ds1 ATTACH RELATION orders DISTRIBUTION KEY id; attach table -------------------------------------------- attached relation orders to distribution ds1 (1 row) demo=> ALTER DISTRIBUTION ds1 ATTACH RELATION items DISTRIBUTION KEY order_id; attach table ---------------------------------------------- attached relation items to distribution ds1 (1 row) ``` And at the end specify a list of ranges: which values to route to which shard. Note: The right bound is infinity if there are no key ranges. ```sql theme={null} CREATE KEY RANGES FOR DISTRIBUTION ds1 BETWEEN 0 AND 2000; ``` Order is important. If you reverse the order, you will get an error "key range krid2 intersects with key range krid1 in QDB". Here we go! You can play with some SELECTs or INSERTs. ## Connect to SPQR router Now we can connect to proxy a.k.a. router and play with it. Please use psql again, but this time connect to a different port. ```bash theme={null} ➜ psql "host=localhost sslmode=disable user=demo dbname=demo port=6432" psql (13.3, server 9.6.22) Type "help" for help. demo=> CREATE TABLE orders ( id SERIAL NOT NULL PRIMARY KEY, customer_id INT, order_date DATE ); NOTICE: send query to shard(s) : shard01,shard02 CREATE TABLE demo=> CREATE TABLE items ( id SERIAL NOT NULL PRIMARY KEY, order_id SERIAL NOT NULL, name VARCHAR ); NOTICE: send query to shard(s) : shard01,shard02 CREATE TABLE ``` Then, populate it with an order: ```bash theme={null} demo=> BEGIN; BEGIN demo=> INSERT INTO orders (id, customer_id, order_date) VALUES (777, 123456, '2024-01-08'); NOTICE: send query to shard(s) : shard01 INSERT 0 1 demo=> INSERT INTO items (id, order_id, name) VALUES (1, 777, 'elephant'); INSERT 0 1 demo=> COMMIT; COMMIT ``` > NOTICE messages are disabled by default, specify `show_notice_messages` setting in the router config to enable them The order can be found on the first shard: ```bash theme={null} demo=> SELECT * FROM orders WHERE id = 777; NOTICE: send query to shard(s) : shard01 id | customer_id | order_date ------+-------------+-------------- 777 | 123456 | '2024-01-08' (1 row) ``` SPQR can handle such queries as `SELECT * FROM table` but we don't recommend using it. This feature is implemented in a non-transactional way. ```bash theme={null} demo=> SELECT * FROM orders; NOTICE: send query to shard(s) : shard01,shard02 id | customer_id | order_date ------+-------------+-------------- 777 | 123456 | '2024-01-08' (1 row) ``` # Welcome to SPQR Source: https://docs.pg-sharding.tech/welcome/index ## What is SPQR SPQR (Stateless Postgres Query Router) is a production-ready system for horizontal scaling of PostgreSQL via sharding. A real open source distributed under the PostgreSQL Global Development Group License. ```bash theme={null} # Pull the latest stable SPQR router image docker pull pgsharding/spqr-router:latest # Run with your configuration docker run -d \ --name spqr-router \ -v /path/to/router.yaml:/etc/spqr/router.yaml:ro \ -p 6432:6432 \ -p 7432:7432 \ -p 7000:7000 \ pgsharding/spqr-router:latest ``` ## Why SPQR PostgreSQL is awesome, but it's hard to manage a single database with some terabytes of data and 105+ queries per second. Existing sharding solutions focus on analytical and hybrid workloads (OLAP, HTAP). Moreover, most of those solutions do not provide a simple, painless path for the monolith \<-> sharded transitions. That's why the
[Data Platform team of Yandex.Cloud](https://cloud.yandex.com/en-ru/services#data-platform) designed SPQR. Our team has been exploring different approaches to sharding a PostgreSQL cluster for a long time. We have considered FDW-based sharding, CustomNode-based sharding, C-implemented lightweight query routing, and finally came to the SPQR design. SPQR is a good fit for: * Developers who are comfortable with Postgres and prefer not to use other database engines * Developers who cannot or do not wish to implement sharding logic on the application level * A database that is well splitted by a sharding key * Single-shard OLTP queries Start sharding your PostgreSQL cluster in 5 minutes Learn more how SPQR works under the hood Main SPQR features The complete Configuration reference # Talks Source: https://docs.pg-sharding.tech/welcome/talks Public Talks About SPQR Here are some public talks about SPQR that you might find interesting: | Title | Speaker(s) | Conference / Channel | Date | | --------------------------------------------------------------------------------------- | --------------------------- | ----------------------------------------------------------------------- | ------------ | | [How to easily balance Sharded PostgreSQL](https://www.youtube.com/watch?v=HIyXcpbsXTw) | Denis Volkov | [Saint Highload++ 2024](https://highload.ru/spb/2024/abstracts/11942) | Jun 25, 2024 | | [How to easily shard PostgreSQL](https://www.youtube.com/watch?v=2xbZ8nOI2lM) | Denis Volkov, Kirill Reshke | [Golang Conf 2023](https://golangconf.ru/2023/abstracts/11317) | Nov 27, 2023 | | [SPQR: horizontal scaling of PostgreSQL](https://www.youtube.com/watch?v=WMmaOx41bAk) | Denis Volkov, Kirill Reshke | [Saint Highload++ 2022](https://highload.ru/moscow/2022/abstracts/9662) | Nov 24, 2022 | | [SPQR (Postgres sharding)](https://www.youtube.com/watch?v=PPuN1KFS9ts) | Andrey Borodin | [PostgresTV](https://www.youtube.com/@PostgresTV) | Aug 11, 2022 |