ApsaraDB RDS:Development and operations recommendations

Connection pooling

minimumIdle=1, maximumPoolSize=15, idleTimeout=600000 (10 minutes), maxLifetime=3600000 (60 minutes)

sqlDB.SetMaxIdleConns(1), sqlDB.SetMaxOpenConns(15), sqlDB.SetConnMaxLifetime(time.Hour)

initialSize=1, minIdle=1, maxIdle=1, maxActive=15, testOnBorrow=false, testOnReturn=false, testWhileIdle=true, minEvictableIdleTimeMillis=600000 (10 minutes), maxEvictableIdleTimeMillis=900000 (15 minutes), timeBetweenEvictionRunsMillis=60000 (1 minute), maxWait=6000 (6 seconds)

Performance and stability

• A single database corresponds to a folder in the underlying file system. Tables, partitions, and indexes correspond to files in that folder. If the number of files exceeds 20 million, the instance reports a disk space exhausted error. Split the database or merge table files based on your workload.

• Use CREATE INDEX CONCURRENTLY to create indexes for online workloads. This avoids blocking INSERT, UPDATE, and DELETE operations on the target table in other sessions.

• For instances running PostgreSQL 12 or later, use REINDEX CONCURRENTLY to rebuild indexes. For PostgreSQL 11 or earlier, use CREATE INDEX ... CONCURRENTLY to create a replacement index, then drop the original.

• Avoid frequently creating and dropping temporary tables — this increases system table overhead. Use caution with ON COMMIT DROP. In most cases, use a WITH clause instead of a temporary table.

• PostgreSQL 13 improves support for partitioned tables, HashAggregate operations for GROUP BY clauses, and parallel queries. Upgrade your instance to PostgreSQL 13 where possible. For more information, see Upgrade the major engine version of an ApsaraDB RDS for PostgreSQL instance.

• Disable the cursor feature if you are no longer using it.

• Use TRUNCATE instead of DELETE for table-level data removal — TRUNCATE is significantly faster.

• Wrap DDL statements in transactions so you can roll them back if needed. Keep transactions short: long DDL transactions block read operations on the objects they lock.

• For bulk data writes, use COPY or multi-row INSERT INTO table VALUES (),(),...(); to maximize write throughput.

Minor engine version

• To use the replication slot feature, update the minor engine version to 20201230 or later. This enables the Logical Replication Slot Failover feature and lets you configure an alert rule for the Maximum Replication Slot Latency metric, preventing logical subscriptions from falling behind or being interrupted. If logical subscriptions are delayed or interrupted, replication slots are lost and write-ahead logging (WAL) records accumulate. For more information, see Logical Replication Slot Failover and Manage the alert rules of an ApsaraDB RDS for PostgreSQL instance.

• To use the audit log feature or the Performance Insight feature, update the minor engine version to 20211031 or later.

  When log_statement is set to all, performance improves approximately four times in scenarios with more than 50 active connections, and prevents significant CPU utilization spikes.

Monitoring and alerting

• Turn on Initiative Alert to activate the default alert rules provided by the monitoring and alerting feature. For more information, see Manage the alerts.

• Set the memory usage alert threshold between 85% and 95% based on your workload characteristics.

Troubleshooting

To find the most resource-intensive SQL statements (Top SQL), see Find the most resource-intensive SQL statements (Top SQL).

To identify SQL statements with the highest resource consumption, see Locate SQL statements with the highest resource consumption.

Design

Data query

• Use COUNT(*) rather than COUNT(column_name) or COUNT(constants). COUNT(*) is the SQL-92 standard for counting rows and includes NULL values. COUNT(column_name) excludes NULL values and produces a different result.

• For COUNT(DISTINCT) across multiple columns, enclose the column list in parentheses:

  COUNT( (col1, col2, col3) )

  COUNT(DISTINCT) includes all NULL values, so it produces the same result as COUNT(*).

• Avoid SELECT * FROM t. Specify only the columns you need to prevent returning unnecessary data.

• Avoid returning large result sets to database clients, except for extract, transform, and load (ETL) operations. If a query returns an abnormally large result set, check whether the query's execution plan is optimal.

• For range queries, use Range data types with GiST indexes to improve performance.

• If your application frequently runs queries that return many rows, aggregate results into batches. For example, if a query returns 100 rows, aggregate them into a single result set. If your application accesses results by ID, aggregate by ID periodically. Smaller result sets reduce response time.

Instance management

• Enable the SQL Explorer and Audit feature to query and export SQL execution information, including the databases queried, execution status, and durations. Use this feature to diagnose SQL health, troubleshoot performance issues, and analyze traffic. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for PostgreSQL instance.

• To monitor and record activity within your Alibaba Cloud account — including console, API, and developer tool access — use ActionTrail. ActionTrail records these actions as events that you can download from the ActionTrail console or deliver to Log Service Logstores or Object Storage Service (OSS) buckets for security analysis, resource change tracking, or compliance auditing. For more information, see What is ActionTrail?.

• Review all DDL operations before running them, and schedule DDL changes during off-peak hours.

• Before committing transactions that delete or modify data, run a SELECT statement to confirm the affected rows. If your business logic requires updating exactly one row, add LIMIT 1.

• For DDL operations and other operations that acquire locks — such as VACUUM FULL and CREATE INDEX — set a lock timeout to prevent them from blocking queries indefinitely:

  BEGIN;
  SET LOCAL lock_timeout = '10s';
  -- DDL statement;
  END;

• Use EXPLAIN ANALYZE to inspect a query's execution plan. Unlike EXPLAIN, EXPLAIN ANALYZE actually executes the query. If the plan involves DML operations (UPDATE, INSERT, or DELETE), wrap the statement in a transaction and roll it back after inspection to avoid unintended data changes:

  BEGIN;
  EXPLAIN (ANALYZE) <DML (UPDATE/INSERT/DELETE) SQL>;
  ROLLBACK;

• For large-scale deletions or updates, process data in batches — each batch in its own transaction. Deleting or updating all rows in a single transaction generates large amounts of junk data.