[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for Sharded PostgreSQL](../index.md) > FAQ > All questions on one page

# FAQ about Managed Service for Sharded PostgreSQL {#general}

### General questions about Managed Service for Sharded PostgreSQL {#toc-general}

* [What is Managed Service for Sharded PostgreSQL?](#what-is-mspqr)

* [What are the benefits offered by Managed Service for Sharded PostgreSQL?](#mspqr-benefits)

* [When should I use Managed Service for Sharded PostgreSQL?](#use-cases)

* [When is Managed Service for Sharded PostgreSQL not a good fit?](#not-suitable-use-cases)

* [Are JSONB and large objects supported?](#jsonb-support)

* [When should I choose Managed Service for Sharded PostgreSQL over Yandex Managed Service for YDB?](#mspqr-vs-ydb)

* [Can a multi-host Managed Service for PostgreSQL cluster serve as a shard in Managed Service for Sharded PostgreSQL?](#multi-host-pg-cluster)

* [Does it make sense to migrate high-performance PostgreSQL instances (e.g., 96 vCPUs) to Sharded PostgreSQL?](#powerful-pg-instances)

* [How is Managed Service for Sharded PostgreSQL different from Neon?](#mspqr-vs-neon)

* [How is Managed Service for Sharded PostgreSQL different from Citus/Vitess?](#mspqr-vs-citus-vs-vitess)

* [How do I get started with Managed Service for Sharded PostgreSQL?](#how-to-start)

### Safety and operation {#toc-security}

* [How is data security ensured?](#data-security)

* [Can my credentials leak through the router?](#router-security)

* [How do I set up backups?](#how-to-set-up-backups)

* [How do I ensure high availability?](#high-availability)

* [What happens in case of overloads?](#overload-behaviour)

* [What happens if a query is canceled?](#cancel-query-processing)

* [Are there risks of query duplication is using a load balancer upstream of the routers?](#query-duplication)

* [How do I restrict access to the administrative console?](#restrict-access-to-admin-console)

### Distributed queries {#toc-distributed-queries-and-transactions}

* [How does Sharded PostgreSQL process SQL queries?](#sql-queries-parcing)

* [How to perform transactions across multiple shards?](#multi-shard-transactions)

* [How to explicitly specify which shard to run the query on?](#how-to-specify-shard)

* [What transaction commit strategies are supported?](#commit-strategy)

* [How do reference tables work?](#reference-tables)

* [How to create reference tables?](#how-to-create-reference-table)

* [Does Sharded PostgreSQL support distributed sequences?](#distributed-sequences)

* [Is it possible to shard related tables based on a single key?](#single-key-sharding-for-connected-tables)

* [How are queries executed without an explicit sharding key?](#queries-with-no-explicit-key)

### Connection {#toc-connection}

* [How do I connect to a router?](#how-to-connect-to-router)

* [How do I connect to the Sharded PostgreSQL administrator console?](#how-to-connect-to-admin-console)

* [What are the session and transaction modes?](#session-vs-transaction-modes)

* [How do I get the statistics and statuses of connections?](#connections-monitoring)

* [How do I set up an app connection to Sharded PostgreSQL?](#how-to-connect-app-to-spqr)

* [What options are there to manage connection routing?](#connection-routing-management)

### Performance {#toc-performance}

* [How to improve performance?](#how-to-improve-performance)

* [How to reduce reading latency?](#how-to-decrease-read-latency)

* [How to limit the load caused by data uploads?](#how-to-improve-data-upload-performance)

* [How to set up reading from replicas?](#how-to-read-from-replicas)

* [What resources are required for routers and coordinators?](#router-and-coordinator-resources)

### Data migration {#toc-data-migration}

* [Can I shard by a composite key?](#composite-sharding-key)

* [Can I create a default shard for unbound keys?](#default-shard-for-unbound-keys)

* [How do I add a new shard and rebalance the data?](#how-to-add-new-shard)

* [How does data rebalancing work when adding a new shard?](#data-rebalancing-for-new-shard)

* [How do I load big data volumes?](#how-to-upload-big-chunks-of-data)

* [How can I speed up migration of big data volumes?](#how-to-improve-migration-of-big-chunks-of-data)

* [What happens if there is a failure during data migration?](#data-migration-failure)

* [How do I rename a table while keeping it available?](#how-to-rename-table-and-keep-it-available)

### Limits {#toc-limitations}

* [What types of data are available for sharding?](#available-data-types)

* [What are the limits for a Managed Service for Sharded PostgreSQL cluster?](#cluster-limits)

* [Are cross-shard JOINs supported?](#cross-shard-join-queries)

* [Are cross-shard queries supported?](#cross-shard-queries)

* [What retry policy is used by Sharded PostgreSQL?](#retry-policy)

* [How does Sharded PostgreSQL manage connection limits?](#connection-limits)

* [Is there query deduplication?](#query-deduplication)

* [Are DDL operations (e.g., `ALTER TABLE` and `RENAME`) allowed in transactions?](#ddl-operations)

### Troubleshooting {#toc-troubleshooting}

* [Transaction does not apply to all shards](#cross-shard-transaction-failure)

* [Requests are not routed to a new shard](#queries-routing-fails-for-new-shard)

* [`failed to get connection to any shard host within` error when connecting to cluster hosts](#failed-to-get-connection)

* [`error processing query ... : syntax error` error when executing the query](#error-processing-query)

* [`permission denied for schema` error](#permission-denied-for-schema)

* [`failed to find primary within host` error](#failed-to-find-primary)

* [`failed to match any datashard` error or blocked request](#failed-to-match-any-datashard)

## General questions about Managed Service for Sharded PostgreSQL {#general}

#### What is Managed Service for Sharded PostgreSQL? {#what-is-mspqr}

Managed Service for Sharded PostgreSQL (Sharded PostgreSQL) is a managed service for horizontal PostgreSQL scaling through automatic [sharding](../concepts/sharding.md). It functions as an intelligent proxy router that processes SQL queries and distributes them across shards based on defined rules called [sharding keys](../concepts/sharding-keys.md). In addition, Managed Service for Sharded PostgreSQL:

- Uses high-availability clusters as the foundation for sharding to maximize reliability.
- Maintains cluster availability during migrations from monolithic to sharded architectures.
- Optimized for OLTP queries with minimal overhead.

Key features

* Sharding by value range or by hash: the [router](../concepts/index.md#router) selects the target [shard](../concepts/index.md#shard) for each query.
* Compatibility with the PostgreSQL extended protocol enables out-of-the-box support for prepared statements and client libraries.
* Support for the session and transaction modes.
* Unlimited number of routers.
* [Rebalancing](../concepts/sharding-method.md#data-rebalancing), i.e., cross-shard data migration for even load distribution.
* Support for assigning multiple servers to a single shard, which enables the router to distribute read-only queries among replicas and automatically locate the master.

#### What are the benefits offered by Managed Service for Sharded PostgreSQL? {#mspqr-benefits}

With Managed Service for Sharded PostgreSQL, you get the following benefits:

* Managed service: Out-of-the-box support for automated updates, monitoring, and backups.
* High availability: Automatic failover to replicas in case of failures.
* Dynamic rebalancing: `REDISTRIBUTE KEY RANGE` command for data redistribution across shards.
* Transaction support: `SESSION` and `TRANSACTION` connection management modes.
* Resource monitoring, where the user retains control over load.
* Consulting services.

#### When should I use Managed Service for Sharded PostgreSQL? {#use-cases}

Managed Service for Sharded PostgreSQL is ideal for scenarios that meet one or more of these criteria:
* Your data size exceeds 1 TB with no remaining options for vertical scaling.
* Your workload exceeds 20,000 queries per second, causing visible performance degradation.
* Data cooling is required (archiving old data while keeping it accessible).
* You need to automate an existing sharded infrastructure.

We recommend implementing sharding if your cluster has more than four hosts, more than 40 CPU cores, or disk size exceeding 600 GB. Early migration to Managed Service for Sharded PostgreSQL removes the added complexity of handling multi-terabyte datasets.

#### When is Managed Service for Sharded PostgreSQL not a good fit? {#not-suitable-use-cases}

Managed Service for Sharded PostgreSQL is not suitable for:

* OLAP workloads (use [Yandex MPP Analytics for PostgreSQL instead](../../managed-greenplum/index.md)).
* Complex queries involving data from multiple shards, e.g., cross-shard JOIN operations.

#### Are JSONB and large objects supported? {#jsonb-support}
Yes. Managed Service for Sharded PostgreSQL supports all native PostgreSQL data types, including JSONB. Note that large objects may affect network performance.

#### When should I choose Managed Service for Sharded PostgreSQL over Yandex Managed Service for YDB? {#mspqr-vs-ydb}
Managed Service for Sharded PostgreSQL enables you to solve the PostgreSQL scaling problem without changing the DBMS type.

#### Can a multi-host Managed Service for PostgreSQL cluster serve as a shard in Managed Service for Sharded PostgreSQL? {#multi-host-pg-cluster}
Yes. In Managed Service for Sharded PostgreSQL, a shard can be either a single-host or a multi-host Managed Service for PostgreSQL cluster.

#### Does it make sense to migrate high-performance PostgreSQL instances (e.g., 96 vCPUs) to Sharded PostgreSQL? {#powerful-pg-instances}
Yes, if your application is ready for sharding. Sharded PostgreSQL replaces a single high-spec host with multiple smaller instances, allowing you to scale resources horizontally and balance the load.

#### How is Managed Service for Sharded PostgreSQL different from Neon? {#mspqr-vs-neon}

Neon decouples the compute and storage layers (similar to Amazon Aurora), but it is not a sharded solution and does not support horizontal scaling of clusters. Sharded PostgreSQL achieves true horizontal scalability through data and query sharding across independent PostgreSQL nodes.

#### How is Managed Service for Sharded PostgreSQL different from Citus/Vitess? {#mspqr-vs-citus-vs-vitess}

| **Feature** | **Managed Service for Sharded PostgreSQL** | **Citus** | **Vitess** |
|--------------|--------------------------------|-----------|------------|
| Performance compared to PostgreSQL | 10–30% lower | 15–40% lower | 20–50% lower |
| Protocol | Native PostgreSQL | PostgreSQL extensions | Proprietary |
| Rebalancing | Via `REDISTRIBUTE KEY RANGE`; cluster remains available for reads and writes | Requires downtime | Via VReplication |
| Management | Full integration with managed databases | Manual administration | End-to-end setup |
| Reading from replicas | Automated | Via master only | Via VTGate |
| License | Open PostgreSQL license | GNU AGPLv3 with limitations | Apache License 2.0 |

#### How do I get started with Managed Service for Sharded PostgreSQL? {#how-to-start}

Create your first Managed Service for Sharded PostgreSQL cluster. For step-by-step instructions, see [Getting started with Managed Service for Sharded PostgreSQL](../quickstart.md).

Before you begin, define your cluster specifications:
* [Sharding type](../concepts/sharding.md#shard-management).
* Your cluster [network](../../vpc/concepts/network.md#network).
* [Availability zone](../../overview/concepts/geo-scope.md) for your cluster hosts.
* Number and [class of hosts](../concepts/instance-types.md).
* [Storage](../concepts/storage.md) size (reserved in full during cluster creation).

## Safety and operation {#security}

#### How is data security ensured? {#data-security}

Sharded PostgreSQL stores only data location metadata. Data security is ensured by [Managed Service for PostgreSQL](../../managed-postgresql/index.md) by providing:

* TLS 1.3 traffic encryption for all connections (client ↔ router ↔ shard).
* Audit, with [access logs](../at-ref.md) stored in [Yandex Audit Trails](../../audit-trails/index.md) for 30 days. [Learn more about viewing logs in a Managed Service for Sharded PostgreSQL cluster](../operations/cluster-logs.md).

#### Can my credentials leak through the router? {#router-security}

No, your credentials are secure. The risks are comparable to using a connection pooler, e.g., [Odyssey](https://yandex.ru/dev/odyssey/). Your data does go through Sharded PostgreSQL, but the security practices are as per [Yandex Cloud standards](../../security/standarts.md).

#### How do I set up backups? {#how-to-set-up-backups}

Backups are run automatically for all deployed clusters. Optionally, you can specify the start time and select the retention period for your backups. [Learn more about setting up backups in Managed Service for Sharded PostgreSQL](../operations/cluster-backups.md).

#### How do I ensure high availability? {#high-availability}

To ensure high availability of your Managed Service for Sharded PostgreSQL cluster:

* Create shards so that each shard, i.e., Managed Service for PostgreSQL cluster, includes at least three hosts (master and replicas) located in different availability zones.
* For Managed Service for Sharded PostgreSQL clusters with standard sharding, create at least three `INFRA` hosts in different availability zones.
* For Managed Service for Sharded PostgreSQL clusters with advanced sharding, create at least three `COORDINATOR` hosts and three `ROUTER` hosts in different availability zones.

#### What happens in case of overloads? {#overload-behaviour}

An overloaded replica becomes unavailable, and the cluster stops sending requests to it until it recovers. 

Let's assume your cluster receives 95% of write requests and 5% of read requests. If your router configuration is `default_target_session_attrs = read-only`, read requests are evenly distributed among replicas. If a replica becomes unavailable, e.g., `SELECT pg_is_in_recovery();` times out, the service stops sending requests to that replica and proceeds to check its health. As soon as the replica responds, requests to it are resumed.

#### What happens if a query is canceled? {#cancel-query-processing}

The application terminates the current router connection, establishes a new one, and sends a cancel message with the query ID to the router. The router receives the cancel message and forwards it to the shard the query is addressed to.

{% note tip %}

Canceling a query results in reconnections and increases TLS handshake overhead, so we do not recommend using timeouts under 100 ms.

{% endnote %}

#### Are there risks of query duplication is using a load balancer upstream of the routers?

Yes. If a client terminates the connection and the load balancer retries the request, Sharded PostgreSQL will treat it as a new request, which may result in duplication, e.g., for `INSERT`. We recommend using idempotent operations or implementing deduplication logic at the application layer.

#### How do I restrict access to the administrative console? {#restrict-access-to-admin-console}

Access to the administrative console is restricted by default. You can [connect](../operations/connect.md) to the console only via TLS using a password.

You set a password for access to the administrative console when [creating a cluster](../operations/cluster-create.md). If you need to, you can [change the password](../operations/cluster-update.md) on an active cluster.

## Distributed queries {#distributed-queries-and-transactions}

#### How does Sharded PostgreSQL process SQL queries? {#sql-queries-parcing}

Sharded PostgreSQL processes SQL queries depending on their type and context:

* Regular queries: Sharded PostgreSQL processes the query to determine the table, column, and the value being accessed. This data is compared against pre-defined sharding rules (e.g., by key or range). Based on these rules, the system determines the target shard the request should be sent to.

* Requests with routing settings: Routing of such a request can be affected by virtual parameters that are specified as comments in the SQL query or in the router configuration.

* Transactions: When receiving the `BEGIN TRANSACTION` command, Sharded PostgreSQL does not execute queries immediately. Instead, it stores all subsequent queries in memory (e.g., `SET` commands). The entire transaction is sent to a specific shard only when a query is received for which the target shard can be clearly identified. This allows for execution of the entire transaction on a single shard.

#### How to perform transactions across multiple shards? {#multi-shard-transactions}

For atomic cross-shard transactions, use two-phase commit (2PC):

1. At the beginning of the session: `SET __spqr__commit_strategy TO '2pc'`.
1. In a separate `COMMIT` operation: Add the `/* __spqr__commit_strategy: 2pc */` virtual parameter.
1. Make sure the shards are set to `max_prepared_transactions > 0`.

  {% note warning %}

  Without 2PC, changes may be applied partially.

  {% endnote %}

`COPY` operations are supported with the `/* __spqr__allow_multishard: true */` virtual parameter.

You can set virtual parameters with comments in SQL or via `SET`.

#### How to explicitly specify which shard to run the query on? {#how-to-specify-shard}

To specify the shard to run a query on, use virtual parameters:

* `/* __spqr__execute_on: <shard_name> */`: Specifies a specific shard to run the query on.

  To find out the shard name, run the `SHOW shards;` SQL query.

* `/* __spqr__auto_distribution: ... */`: Selects a sharding rule for routing.
* `/* __spqr__scatter_query: true */`: Enables sending a query to all shards.

You can set virtual parameters with comments in SQL or via `SET`.

For more information on the query execution settings, see [this SPQR guide](https://docs.pg-sharding.tech/routing/hints#__spqr__target_session_attrs).

#### What transaction commit strategies are supported? {#commit-strategy}

Sharded PostgreSQL supports single-phase and two-phase commits.

The commit method in a distributed transaction is specified by the `__spqr__commit_strategy` virtual parameter. The possible values are:

* `1pc`: One-stage commit (best-effort commit).
* `2pc`: Two-stage commit.

  For a two-stage commit, use the `/* __spqr__engine_v2: true */` virtual parameter and set the `max_prepared_transactions` PostgreSQL parameter on all shards.

You can set virtual parameters with comments in SQL or via `SET`.

#### How do reference tables work? {#reference-tables}

Data in such tables is replicated across all shards. Queries to them are automatically sent to all nodes using two-stage commit.

#### How to create reference tables? {#how-to-create-reference-table}

Tables that are identical on all shards are created through the coordinator:

```sql
CREATE REFERENCE TABLE table_name (...);
```

Data is automatically replicated to all shards. Queries to them are executed without specifying sharding.

For more information on creating reference tables, see [this SPQR guide](https://docs.pg-sharding.tech/sharding/console/sql_commands#create-reference-table).

#### Does Sharded PostgreSQL support distributed sequences? {#distributed-sequences}

Yes, Sharded PostgreSQL guarantees the uniqueness of the autoincrement at the cluster level by using the `CREATE REFERENCE TABLE ... AUTO INCREMENT` command.

#### Is it possible to shard related tables based on a single key? {#single-key-sharding-for-connected-tables}

Yes, Sharded PostgreSQL allows you to store related data from different tables on a single shard, which simplifies JOIN operations within a shard.

#### How are queries executed without an explicit sharding key? {#queries-with-no-explicit-key}

By default, queries without a sharding key (multishard requests) are prohibited. You can enable them using the `/* __spqr__scatter_query: true */` virtual parameter. The results from each shard are merged, but there is no guarantee of consistency.

You can set virtual parameters with comments in SQL or via `SET`.

## Connection {#connection}

#### How do I connect to a router? {#how-to-connect-to-router}

You can connect to a router in a Managed Service for Sharded PostgreSQL cluster over PostgreSQL. Do it by running this command:

```bash
psql "host=<host_FQDN> \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username> \
      target_session_attrs=read-write"
```

Where `target_session_attrs` defines the type of request to the host. For example, `read-write` enables both reading and writing. For more information, see [this SPQR guide](https://docs.pg-sharding.tech/routing/hints#__spqr__target_session_attrs).

After runing this command, enter the user password to complete your connection.

#### How do I connect to the Sharded PostgreSQL administrator console? {#how-to-connect-to-admin-console}

Use port `6432`, the `spqr-console` user, and the `spqr-console` database. Here is an example:

```bash
psql "host=<router_FQDN> port=6432 user=spqr-console dbname=spqr-console sslmode=verify-full"
```

#### What are the session and transaction modes? {#session-vs-transaction-modes}

In the session mode, the client connection is established upon the initial database query and maintained until the client terminates the session. This connection can then be used by that or any other client. This approach effectively handles large volumes of client connections to the DBMS, e.g., during application startup, but it is less performant than the transaction mode.

In the transaction mode, the client connection is established upon the initial database query and maintained until the transaction ends. This connection can then be used by that or any other client. This approach allows maintaining few server connections between the connection pooler and PostgreSQL hosts when there are many client connections.

The transaction mode provides high performance and optimizes DBMS resource usage, but it has no support for the following features:

* [Temporary tables](https://www.postgresql.org/docs/current/sql-createtable.html), [cursors](https://www.postgresql.org/docs/current/plpgsql-cursors.html), and [advisory locks](https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS) that persist beyond a single transaction.

* [Prepared statements](https://www.postgresql.org/docs/current/sql-prepare.html).

To configure this mode, use the `pool_mode` parameter in the router configuration. The transaction mode is enabled by default.

#### How do I get the statistics and statuses of connections? {#connections-monitoring}
The router provides an administrative console over the PostgreSQL protocol. You can use the console to run the `SHOW` commands to get statistics, for example:

* `SHOW clients WHERE dbname = <database_name>;`: Displays a list of clients, route, router address, and connection state.
* `SHOW shards`: Outputs a list of shards.
* `SHOW backend_connections`: Outputs a list of connections to shard hosts.

#### How do I set up an app connection to Sharded PostgreSQL? {#how-to-connect-app-to-spqr}

To connect from applications, use the standard PostgreSQL drivers, e.g., pgx. Specify all cluster routers in the configuration. Make sure the cluster security groups allow connections to the cluster.

For complex queries, e.g., with CTEs, use the `/* __spqr__engine_v2: true */` virtual parameter. You can set virtual parameters with comments in SQL or via `SET`. For more information on virtual parameters, see [this SPQR guide](https://docs.pg-sharding.tech/routing/hints#__spqr__target_session_attrs).

#### What options are there to manage connection routing? {#connection-routing-management}

You can only manage the type of query to a host. To do this, use the `/*__spqr__target_session_attrs */` virtual parameter or the `target_session_attrs` parameter to specify the query type: `read-write`, `smart-read-write`, `read-only`, `prefer-standby`, or `any`. 

The query type affects the cluster's behavior when processing the query. For example, `read-only` allows connections to replicas only, while `prefer-standby` selects a replica but switches to the master if no replicas are available. This is useful in environments with multiple servers and automatic master failover. For more about the request types, see [this SPQR guide](https://docs.pg-sharding.tech/routing/hints#__spqr__target_session_attrs).

You can set virtual parameters with comments in SQL or via `SET`.

## Performance {#performance}

#### How to improve performance? {#how-to-improve-performance}

* Increase the resources (CPU, RAM) of your existing routers.

* Add new routers.

* Disable debug logging for routers to reduce load on your computing resources.

* In the router configuration, disable `show_notice_messages` because NOTICE messages increase load on Sharded PostgreSQL.

* Avoid frequent reconnections: set up connection pooling in your application.

* Enable reading from replicas. Do it by providing this virtual parameter in an SQL query:

  ```sql
  SELECT * FROM orders /* target-session-attrs: read-only */;
  ```
* Limit execution time for long queries:

  ```sql
  SET session_duration_timeout = '5min';
  ```

#### How to reduce reading latency? {#how-to-decrease-read-latency}

* Enable reading from replicas:

  ```sql
  SELECT * FROM table /* target-session-attrs: read-only */;
  ```

* Increase `max_connections` for the user.

#### How to limit the load caused by data uploads? {#how-to-improve-data-upload-performance}

* Create a separate user and limit the number of connections for them (`conn_limit` setting).
* Use a dedicated router for ETL operations.
* Set `session_duration_timeout` to automatically end long sessions.

#### How to set up reading from replicas? {#how-to-read-from-replicas}

You can specify multiple servers for one shard in the configuration. The router will automatically distribute read-only queries across replicas. For a specific query, you can explicitly set the `target-session-attrs` parameter:

* `read-write` (default): Queries only to the master.
* `smart-read-write`: Queries only to the master, but with read-only queries redirected to replicas.
* `read-only`: Queries only to replicas (if available).
* `prefer-standby` or `prefer-replica`: Queries to replicas. If none are available, queries are forwarded to the master.
* `any`: Queries to any available node (preferably local). To reduce latency, we recommend to use this value in conjunction with closest host selection.

#### What resources are required for routers and coordinators? {#router-and-coordinator-resources}

We recommend selecting the computing resource configuration for the router and coordinator based on the expected load.

Recommended configurations for a load of 20,000 read requests per second:

#|
|| Level of requirements | Router configuration | Coordinator configuration | General configuration ||

|| Minimum 
| 
* Three routers. Each router's host class must include four vCPUs with a vCPU guarantee of 100% and 16 GB of RAM.
* `local-ssd` disk, 10 GB
| 
* Three coordinators. Each coordinator's host class must include two vCPUs with a guaranteed vCPU share of 100% and 4 GB of RAM.
* `local-ssd` disk, 10 GB
|
* 18 vCPUs with a vCPU guarantee of 100%.
* 60 GB RAM.
* `local-ssd` disks, 60 GB
||
|| Optimal
|
* Three routers. Each router's host class must include four vCPUs with a vCPU guarantee of 100% and 16 GB of RAM.
* `local-ssd` disk, 10 GB
|
* Three coordinators. Each coordinator's host class must include two vCPUs with a vCPU guarantee of 100% and 8 GB of RAM.
* `local-ssd` disk, 10 GB
|
* 18 vCPUs with a vCPU guarantee of 100%.
* 72 GB RAM.
* `local-ssd` disks, 60 GB
||
|| With a margin
|
* Five routers. Each router's host class must include four vCPUs with a vCPU guarantee of 100% and 16 GB of RAM.
* `local-ssd` disk, 10 GB
|
* Three coordinators. Each coordinator's host class must include two vCPUs with a vCPU guarantee of 100% and 8 GB of RAM.
* `local-ssd` disk, 10 GB
|
* 26 vCPUs with a vCPU guarantee of 100%.
* 104 GB RAM.
* `local-ssd` disks, 80 GB
||
|#

To calculate the cost of a Managed Service for Sharded PostgreSQL cluster, [use this calculator](https://yandex.cloud/en/services/managed-spqr#calculator).

## Data migration {#data-migration}

#### Can I shard by a composite key? {#composite-sharding-key}

Yes. Do it by creating a composite key:

```sql
CREATE DISTRIBUTION <sharding_rule_name> COLUMN TYPES integer, varchar;
ALTER DISTRIBUTION <sharding_rule_name> ATTACH RELATION orders DISTRIBUTION KEY user_id, order_date;
```

Learn more about composite sharding keys in [this SPQR guide](https://docs.pg-sharding.tech/sharding/composite_keys).

#### Can I create a default shard for unbound keys? {#default-shard-for-unbound-keys}

Yes. Do it by running this command:

```sql
ALTER DISTRIBUTION <sharding_rule_name> ADD DEFAULT SHARD <shard_name>;
```

Learn more about default shards in [this SPQR guide](https://docs.pg-sharding.tech/sharding/default_shard).

#### How do I add a new shard and rebalance the data? {#how-to-add-new-shard}

1. [Create a new shard](../operations/shards.md#create-shard).
1. Use the `SYNC REFERENCE TABLES` command to copy reference tables.
1. Redistribute the key ranges. Use these commands:
    * `SPLIT KEY RANGE` to split the key range.
    * `REDISTRIBUTE KEY RANGE` to automatically migrate data.

#### How does data rebalancing work when adding a new shard? {#data-rebalancing-for-new-shard}

When adding a new shard, you need to start manual data migration using the `REDISTRIBUTE KEY RANGE` command. In which case Sharded PostgreSQL moves small data ranges to minimize the cluster being unavailable for writes.

#### How do I load big data volumes? {#how-to-upload-big-chunks-of-data}

There are two options for loading big data volumes:

* `COPY` with the `/* __spqr__allow_multishard: true */` virtual parameter.

    For example, for loading from a CSV file:

    ```sql
    COPY <table_name> FROM 'data.csv' WITH DELIMITER ',' /* __spqr__allow_multishard: true */;
    ```

    {% note warning %}

    The use of `COPY` can cause a high load on the router. If you are using `COPY` on a regular basis, we recommend allocating a separate router for the task.

    {% endnote %}

* Batch insert of several table rows using the `/* __spqr__engine_v2: true */` virtual parameter. In this scenario, the router analyzes each row, determines the target shard based on the sharding key, and converts the query into separate `INSERT` commands for each shard.

    For example, the following command:

    ```sql
    INSERT INTO users (id, name) VALUES
      (1, 'Alice'),      -- send to shard sh1
      (100, 'Bob'),      -- send to shard sh2
      (2, 'Charlie')     -- send to shard sh1
    /* __spqr__engine_v2: true */;
    -- NOTICE: send query to shard(s) : sh1,sh2
    ```

    will be converted to:

    * `INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Charlie');` for shard `sh1`.
    * `INSERT INTO users (id, name) VALUES (100, 'Bob');` for shard `sh2`.

You can set virtual parameters with comments in SQL or via `SET`.

Learn more about inserting big data volumes in [this SPQR guide](https://docs.pg-sharding.tech/sharding/bulk).

#### How can I speed up migration of big data volumes? {#how-to-improve-migration-of-big-chunks-of-data}

* Increase chunk size.
* Make sure each shard has an index based on the sharding key.
* Avoid running parallel writes during migration.

#### What happens if there is a failure during data migration? {#data-migration-failure}
Sharded PostgreSQL ensures range-level atomicity. If there is a failure, data can temporarily exist on both shards. The operation will be either canceled or resumed following recovery.

#### How do I rename a table while keeping it available? {#how-to-rename-table-and-keep-it-available}

Run the `ALTER TABLE` sequence within a single transaction. To enable this feature, use the `/* __spqr__multishard_ddl: true */` virtual parameter.

```sql
ALTER TABLE ... /* __spqr__multishard_ddl: true */;
```

{% note warning}

The operation is non-transactional. Be careful when renaming tables.

{% endnote %}

You can set virtual parameters with comments in SQL or via `SET`.

## Limits {#limitations}

#### What types of data are available for sharding? {#available-data-types}

* Integers (`INT`, `BIGINT`).
* Strings `VARCHAR`.
* `UUID`.
* Composite keys.
* Hash functions: `CITY`, `MURMUR` (for integers only).

  {% note warning %}

  Custom hash functions are not supported.

  {% endnote %}

If you are missing any type of data, you can create an issue in the [project's Github repository](https://github.com/pg-sharding/spqr/issues).

#### What are the limits for a Managed Service for Sharded PostgreSQL cluster? {#cluster-limits}

The number of routers and shards in a Managed Service for Sharded PostgreSQL cluster is unlimited.

Learn more about [quotas and limits in Managed Service for Sharded PostgreSQL](../concepts/limits.md).

#### Are cross-shard JOINs supported?{#cross-shard-join-queries}

No. JOIN is only possible within a single shard. When working with linked data, use the same sharding keys for linked tables to keep the data on the same shard.

If you need cross-shard JOINs, we recommend using [Yandex MPP Analytics for PostgreSQL](../../managed-greenplum/index.md).

#### Are cross-shard queries supported? {#cross-shard-queries}

They are only supported for the following cases:

* Reference tables with the `/* __spqr__engine_v2: true */` virtual parameter.
* `COPY` with the `/* __spqr__allow_multishard: true */` virtual parameter.
* Transactions with DDL and the `/* __spqr__default_route_behaviour: ALLOW */` virtual parameter provided.
* Queries for which the `/* __spqr__scatter_query: true */` virtual parameter is explicitly specified.

You can set virtual parameters with comments in SQL or via `SET`.

#### What retry policy does Sharded PostgreSQL use? {#retry-policy}
The router does not retry user queries. The user must implement the retry policy on their own based on their business logic.

#### How does Sharded PostgreSQL manage connection limits? {#connection-limits}
The connection limit is set separately for each user in the `conn_limit` parameter.

#### Is there query deduplication? {#query-deduplication}

No. If the client disconnects and repeats the query, the router will process it as a new one.

#### Are DDL operations (e.g. ALTER TABLE, RENAME) allowed in transactions? {#ddl-operations}

Yes, if `/* __spqr__default_route_behaviour: ALLOW */` is enabled.

Depending on your needs, we recommend to use the following virtual parameter:

* With single-phase fixation: `/* __spqr__commit_strategy: 1pc */`.
* With two-phase fixation: `/* __spqr__commit_strategy: 2pc */`.

You can set virtual parameters with comments in SQL or via `SET`.

## Troubleshooting {#troubleshooting}

#### Transaction does not apply to all shards {#cross-shard-transaction-failure}

**Cause:** Two-phase fixation not enabled for cross-shard operations.
**Solution:** Enable two-phase fixation:

```sql
BEGIN;
SET __spqr__commit_strategy TO '2pc';
INSERT INTO orders ...; /* affects several shards */
COMMIT;
```

{% note warning %}

`max_prepared_transactions` must be greater than zero on all shards.

{% endnote %}

#### Queries are not routed to new shard {#queries-routing-fails-for-new-shard}

When adding a new shard, you may hit a situation where queries are not routed to it. To track query routing, you can:

* Enable `show_notice_message`.
* Use the `/* __spqr__reply_notice: true */` virtual parameter.

  You can set virtual parameters with comments in SQL or via `SET`.

In either case, the router will message to your app the shard the query was routed to.

**Solution:**

* Check whether the new shard is displayed in Sharded PostgreSQL (`SHOW shards`).
* If you are using a sharded table, make sure the data needs to be on this particular shard (`SHOW key_ranges`).
* If you are using a reference table, make sure the table was created on this particular shard (`SHOW reference_relations`).

#### _failed to get connection to any shard host within_ error when connecting to cluster hosts {#failed-to-get-connection}

Error example:

```bash
failed to get connection to any shard host within: host {rc1d-cofs7cre********.mdb.yandexcloud.net:6432 rc1d}: dial tcp 10.151.25.35:6432: i/o timeout, host {rc1b-49796b52********.mdb.yandexcloud.net:6432 rc1b}: dial tcp 10.149.25.23:6432: i/o timeout, host {rc1a-kdm7v4qm********.mdb.yandexcloud.net:6432 rc1a}: dial tcp 10.148.25.15:6432: i/o timeout
```

This error occurs if the [router](../concepts/index.md#router) cannot connect to the [shard](../concepts/index.md#shard) hosts.

**Solution:**

1. Make sure that the Managed Service for Sharded PostgreSQL cluster and shards are in the same network and [security group](../../vpc/concepts/security-groups.md).
1. [Add](../../vpc/operations/security-group-add-rule.md) to the security group the inbound and outbound traffic rules allowing TCP connection to port `6432`:

    * **Port range**: `6432`.
    * **Protocol**: `TCP`.
    * **Destination name**: `CIDR`.
    * **CIDR blocks**: Specify the cluster CIDR, e.g., `10.96.0.0/16`.

#### _error processing query ... : syntax error_ when running a query {#error-processing-query}

This error occurs due to internal Sharded PostgreSQL problem, not because of syntax errors in your SQL query. Sharded PostgreSQL uses a proprietary SQL parser which may not support some of the nuances:

* PostgreSQL-specific operators.
* Rare syntax variants.
* Non-standard functions.

**Solution**: Report the problem to the Sharded PostgreSQL vendor by creating an issue in the [project’s Github repository](https://github.com/pg-sharding/spqr/issues) and attaching the full text of your query.

#### _permission denied for schema_ error {#permission-denied-for-schema}

This error occurs if the user does not have enough permissions to use a schema.

**Solution:** Use the `GRANT ALL ON SCHEMA <schema_name> TO <username>;` command to grant the user permissions for the schema on the relevant shard or all shards.

#### _failed to find primary within host_ error {#failed-to-find-primary}

This error means that your router fails to connect to the shard master within the specified time.

**Possible causes**:

* Network issues between the router and the shard.
* Shard overload (e.g., high _CPU wait_).
* Incorrect `target-session-attrs` settings (e.g., `read-only` in case of a write query).

**Solution:**

* Make sure the networking between the router and the shard is undisturbed.
* Increase computing resources in the PostgreSQL cluster which is the overloaded shard.
* Make sure the `target-session-attrs` settings match your query.

{% note info %}

This problem was supposed to be fixed in [release 2.9.0](https://github.com/pg-sharding/spqr/releases/tag/2.9.0). If you see this error in logs, create an issue in the [project’s Github repository](https://github.com/pg-sharding/spqr/issues).

{% endnote %}

#### _failed to match any datashard_ error or blocked query {#failed-to-match-any-datashard}

This error occurs when your router cannot match the query with a specific sharding key range. For example, if `default_route_behaviour` in the router configuration is set to `BLOCK`, queries without a sharding key are blocked.

**Solution:**

* Change the router's request matching behavior:

  * Permanently: Set `default_route_behaviour` to `ALLOW` in the router configuration.
  * Temporarily: Use the `/* __spqr__default_route_behaviour: allow */` virtual parameter.
* Check that:
  * Sharding key is correct in the query: its name in the query must match the key name in Sharded PostgreSQL metadata.
  * Sharding rules are in place (`SHOW distributions`).
  * Tables are in place (`SHOW relations`).
  * Ranges are in place (`SHOW key_ranges`).
* For multi-shard queries, activate engine_v2 via the `/* __spqr__engine_v2: true */` virtual parameter.

You can set virtual parameters with comments in SQL or via `SET`.