Automatic Read/Write Separation Overview
ダウンロード
フォーカスモード
フォントサイズ
This document describes the automatic read-write splitting, benefits, and read-write splitting routing rules of the database proxy feature of TencentDB for PostgreSQL.
automatic read-write splitting
Currently, many live-network users have business scenarios with far more reads than writes and unpredictable business loads. In application scenarios with a large number of read requests, a single instance may be unable to handle the read pressure, which may even impact the business. To achieve elastic scale-out of read capabilities and reduce database pressure, you can create one or more read-only instances to handle a large number of database read requests. However, this solution requires the business side to support read/write splitting modifications. The robustness of the code determines the quality of read/write splitting for the business. This solution also imposes high technical requirements on users and offers poor flexibility and scalability.
After creating read-only instances, you can activate the database proxy feature and configure an access address policy. Then, configure the database proxy address in your application so that write requests are automatically forwarded to the primary instance and read requests are automatically forwarded to the read-only instances. In addition to automatic read-write splitting, the database proxy feature provides better solutions to other business pain points, as described below:
Scenarios where the load is unpredictable or fluctuates irregularly, or where load "spikes" are prominent.
In internet business scenarios, business loads and access pressure are often unpredictable and unstable, with frequent fluctuations and "spikes". If the business side uses a large number of short connections to access the database, a large number of new connections can easily be created in these scenarios. That is, the number of connections between the database and applications tends to fluctuate with frequent changes in business access pressure.
Connection management in database proxy enables you to effectively reuse database connections to properly scale out application connection handling for unpredictable workloads. First, it allows multiple application connections to share database connections for efficient use of database resources. Second, it allows you to maintain database performance by adjusting the number of open database connections. Finally, it can remove unusable application requests to preserve the overall performance and availability of applications.
Scenarios where applications frequently establish and disconnect connections to the database
Applications built on technologies such as serverless, PHP, or Ruby on Rails may frequently open and close database connections to serve application requests.
Database proxy helps you maintain a database connection pool to avoid unnecessary pressure on database compute and memory used to establish new connections.
Scenarios where database access connections remain idle for a long time without being released
Traditional applications in the SaaS or e-commerce industry may keep database connections idle to minimize response time for user re-engagement. You can use database proxy to retain idle connections and establish database connections only as needed, instead of over-provisioning thresholds or upgrading to a higher-tier database to support mostly idle connections.
Improve the smoothness and stability of database PaaS service failover
With database proxy, you can build applications that transparently tolerate database failovers (active/passive) without writing complex failover handling code. Database proxy automatically routes read traffic to the new database instance while preserving application connections.
Feature Strengths
Self-service read/write splitting with a unified access address.
Native path support improves performance and reduces maintenance costs.
You can set weights and thresholds for flexible selection.
It provides failover capability, allowing requests to access the primary database normally even if the database proxy fails.
During primary instance failovers, configuration adjustments, or the addition or removal of read-only instances, the database proxy can dynamically hot reload configurations without causing network interruptions or restarts.
Feature Overview
The TencentDB for PostgreSQL database proxy feature supports automatic read-write splitting and can assign read weights to the primary instance and multiple read-only instances. Note that these weights are a distribution policy for read requests (non-transactional), not for transactions.
Routing Rules and Precautions
Criterion: whether a function forces routing to the primary instance depends on pg_proc.provolatile. Only volatile functions are forced to the primary instance, while stable / immutable functions are routed to read-only instances based on read requests.
Self-check method:
select proname, provolatile from pg_proc where proname = '<function name>';.When deterministic reads from the primary instance are required, use the
/* master */ hint, or switch to the direct connection address of the primary instance.Under read-write splitting, queries involving instance status, parameters, or statistics may return results from read-only instances, and these results may vary across multiple read-only instances. Therefore, they should not be used to determine the primary instance status.
List of Functions with Known Return Value Differences
For each function's provolatile value, refer to the actual pg_proc results on the target kernel version.
SELECT p.proname, p.provolatileFROM pg_proc pJOIN pg_namespace n ON n.oid = p.pronamespaceWHERE n.nspname = 'pg_catalog'AND p.provolatile IN ('s', 'i')AND (p.proname ~ '^(inet_|pg_stat_|pg_current_|pg_last_|pg_control_|pg_is_|pg_backend|pg_postmaster|pg_conf_|pg_ls_)'OR p.proname IN ('current_setting', 'version'))ORDER BY p.provolatile, p.proname;
Only items whose provolatile value is s / i are routed to read-only instances.
Category | Typical Function / View | Behavior When a Fallback to RO Is Performed |
Connection and instance identity | inet_server_addr (tested s), inet_server_port (tested s), inet_client_addr (tested s), pg_backend_pid, pg_postmaster_start_time, pg_conf_load_time | Returns the address/port/PID/startup time of the RO node itself, which differ from those of the primary instance. These values drift when multiple ROs exist. |
Parameters and configuration | current_setting('...'),SHOW <param>,pg_settings | Values differ when the primary/RO specifications or parameter templates are different (such as max_connections, work_mem, hot_standby, and default_transaction_read_only). |
Statistics and runtime views | pg_stat_activity,pg_stat_replication,pg_replication_slots,pg_stat_database,pg_locks,pg_stat_user_tables | Session replication slot statistics of the primary instance are not visible, or the statistical scope differs. As a result, conclusions are incorrect when businesses monitor connection counts, kill sessions, or monitor latency. |
Recovery/role status and WAL position | pg_is_in_recovery,pg_last_wal_replay_lsn,pg_current_wal_lsn,pg_control_checkpoint | If these functions are marked as v in the target version, they are routed to the primary instance by rule. If they are marked as s, their semantics are reversed after they fall to the RO (primary f/RO t), or the position lags behind. |
フィードバック