Managed Service for Sharded PostgreSQL cluster performance

• How to improve performance?

• How to reduce reading latency?

• How to limit the load caused by data uploads?

• How to set up reading from replicas?

• What resources are required for routers and coordinators?

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:

  SELECT * FROM orders /* target-session-attrs: read-only */;
  

• Limit execution time for long queries:

  SET session_duration_timeout = '5min';
  

How to reduce reading latency?How to reduce reading latency?

• Enable reading from replicas:

  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 limit the load caused by data uploads?

• 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 set up reading 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?What resources are required for routers and coordinators?

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:

To calculate the cost of a Managed Service for Sharded PostgreSQL cluster, use this calculator.