PolarDB:SQL throttling

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL cluster with SQL throttling enabled (contact Alibaba Cloud support)

• Access to the PolarDB console

Steps

1. Log on to the PolarDB console. In the left navigation pane, click Cluster List. Select the region of your cluster and click the cluster ID.

2. In the left navigation pane, choose Settings and Management > Security.

3. On the SQL Throttling tab, click Add.

   

4. In the Create SQL Throttling Rule dialog box, configure the parameters below and click OK.

Basic information

Configurations

Template matching vs. full-text matching

When you create a rule, the proxy preprocesses your SQL template based on the matching mode and generates a unique identifier from the result. When a business SQL statement arrives, the proxy applies the same preprocessing and compares the identifier against configured rules.

Given this SQL template:

SELECT * FROM tbl WHERE id < 1;

For the business SQL SELECT * FROM tbl WHERE id < 100, only a template matching rule hits.

Parameterization support

SQL templates accept PostgreSQL's standard parameter binding syntax ($1, $2, and so on):

SELECT * FROM tbl WHERE id < $1 AND name = $2 LIMIT 1;

The proxy normalizes parameters to ? in both modes:

-- Template matching result
SELECT * FROM tbl WHERE id < ? AND name = ? limit ?

-- Full-text matching result
SELECT * FROM tbl WHERE id < ? AND name = ? limit 1

For the business SQL SELECT * FROM tbl WHERE id < $1 AND name = 2 LIMIT 100, a template matching rule hits; a full-text matching rule does not.

Do not use ? directly in an SQL template—it does not follow PostgreSQL syntax and will not match any statement. Use $1, $2, $3, and so on instead:

-- Invalid
SELECT ?, ?, ?;
-- Valid
SELECT $1, $2, $3;

Prepared statements

For prepared statements, only the EXECUTE statement triggers throttling—not PREPARE. The proxy matches the SQL part of the corresponding PREPARE statement against the rule.

The following two SQL templates are equivalent in a throttling rule:

-- Template 1
PREPARE s1 AS SELECT * FROM tbl WHERE id < $1 AND name > $2;

-- Template 2
SELECT * FROM tbl WHERE id < $1 AND name > $2;

Example: Using template matching with the rule template SELECT * FROM tbl WHERE id < $1 AND name > $2:

-- PREPARE does not trigger throttling
PREPARE s1 AS SELECT * FROM tbl WHERE id < $1 AND name > 100;

-- Each EXECUTE hits the rule and is throttled
EXECUTE s1;
EXECUTE s1;
EXECUTE s1;

For more information on prepared statements, see PREPARE.

Extended protocol support

When an application driver uses the PostgreSQL extended query protocol, only Execute messages trigger throttling. The proxy finds the corresponding Parse message to calculate the SQL template for matching. No special configuration is needed—SQL throttling handles both simple and extended protocol connections automatically.

For more information, see the PostgreSQL extended query protocol documentation.

High availability considerations

For high availability (HA), the database proxy runs on two or more nodes. Client connections are distributed across these nodes, and each node tracks concurrency and queue length independently. For a deployment with N nodes, where C is the per-node Maximum Active Concurrent Statements and Q is the per-node Maximum Waiting Queue Length:

• Client concurrency range: [C+Q, N × (C+Q)]

• Database active concurrency range: [C, N × C]

The actual total concurrency across the cluster is not precisely controlled. Plan your limits with this range in mind.

The waiting queue has a minimum retry interval. If the configured maximum QPS is high, the actual throughput may be slightly lower than the configured value.