Sharded PostgreSQL settings

For a Managed Service for Sharded PostgreSQL cluster, you can set Sharded PostgreSQL settings.

Sharded PostgreSQL settings are configured at the Managed Service for Sharded PostgreSQL cluster level. You can specify them when creating or updating a cluster.

Router settings (Router → Config):

• Show Notice Messages

  It determines whether to include additional information, e.g., the shard name, host address, username, transaction status, etc., in the query response.

  This setting is disabled by default, i.e., the response includes no additional information.

  For more on setting up show_notice_messages, see this Sharded PostgreSQL guide.

• Time Quantiles

  A list of time quantiles for displaying query execution time statistics. Time quantile values are floating-point numbers. If the list is empty, statistics are not collected.

  The default set is [0.5, 0.75, 0.9, 0.95, 0.99, 0.999, 0.9999].

  For more on setting up time_quantiles, see this Sharded PostgreSQL guide.

• Default Route Behavior

  It determines whether multishard queries are allowed:

  • block: Rejects multishard queries to safeguard data consistency. This is the default option.
  • allow: Allows multishard queries. This is useful for DDL and administrative queries.

  For more on setting up query_routing.default_route_behaviour, see this Sharded PostgreSQL guide.

• Prefer Same Availability Zone

  If this setting is enabled, read-only queries will go to replica hosts in the router’s availability zone, whenever possible.

  This setting is enabled by default.

• Enhanced Multishard Processing

  It determines whether to enable enhanced processing for multishard queries.

  For more on setting up query_routing.enhanced_multishard_processing, see this Sharded PostgreSQL guide.

  This setting is disabled by default, i.e., enhanced processing is not perfromed.

• Default Target Session Attrs

  It defines the default value for the target_session_attrs PostgreSQL parameter indicating the preferred session state when connecting to the database.

  The possible values are:

  • any: Connect to any available host regardless of its state.
  • prefer standby: Prefer replica hosts for connection. If none of them is available, connect to the master host.
  • read only: Connect only to replica hosts. If none of them is available, the connection fails.
  • read write (default): Connect only to the master host. If it is not available, the connection fails.
  • smart read write: Connect to the master host. If it is not available, the connection fails. If the configuration file specifies true for the query_routing.auto_route_ro_on_standby setting, read-only queries are automatically redirected to replica hosts (with the prefer standby semantics).

  For detailed descriptions of the query_routing.default_target_session_attrs and query_routing.auto_route_ro_on_standby settings, see this Sharded PostgreSQL guide.

• Default Commit Strategy

  It defines the commit strategy for distributed transactions:

  • best effort: One-stage commit without coordination between shards.
  • one pc (default): Alias for best-effort.
  • two pc: Two-stage commit with guaranteed automicity between shards.

  For more on setting up default_commit_strategy, see this Sharded PostgreSQL guide.