PolarDB-X supports hybrid transaction/analytical processing (HTAP). PolarDB-X supports highly concurrent requests, transactional requests, and analytical queries.
Query optimizers
The optimizer of PolarDB-X is developed to handle HTAP workloads and can provide services for complex queries. In a Transaction Processing (TP) query, the number of tables used is limited to a specific number such as 3, the JOIN operations are performed based on the index, and a small amount of data is used. To handle complex queries that do not have these characteristics, the optimizer must meet higher requirements.
PolarDB-X uses a cost-based optimizer that can search for an optimal execution plan based on the volume of the queried data and the data distribution. For example, the optimizer can adjust the order in which the JOIN operations are performed, select an appropriate join or aggregation algorithm, and disassociate associated subqueries. The quality of the execution plan determines the query efficiency. Query optimization is crucial for Analytical Processing (AP) queries.
Read/write splitting
When a large amount of traffic is involved in online business, the PolarDB-X primary instance is under high pressure caused by data reading and writing. In this case, we recommend that you purchase read-only instances for the PolarDB-X primary instance to route a specified portion of TP read queries from the compute nodes of the primary instance to the data nodes of the read-only instances. This way, read queries is split from write queries and the read pressure on the primary instance is reduced.
- Strong-consistency read request: After the read request is routed to the read-only instances, the data that is updated on the primary instance before the read requests are performed can be queried. This provides external strong consistency.
- Weak-consistency read request: After the read request is routed to the read-only instances, only the latest data can be read from the read-only instances. Data reads are delayed due to asynchronous replication between your primary and read-only instances.
You can specify the read SQL statements that are executed on the primary instance by configuring hints. You can also specify a proportion of read and write SQL statements executed on the primary instance in advance. For more information, see Cluster endpoints and read-only routing endpoints.
Intelligent read/write splittingThe performance of TP queries is affected by AP queries. This is one of the main issues in the application of HTAP databases. To resolve this issue, we recommend that you deploy a PolarDB-X read-only instance whose hardware is isolated from that of the primary instance. This minimizes the impact of AP queries on TP queries.
The PolarDB-X optimizer classifies requests into TP workloads and AP workloads based on the estimated costs. AP workloads are rewritten as distributed execution plans and sent to read-only instances. This ensures that AP workloads do not affect TP workloads on the primary instance. This way, read and write queries are intelligently split.
Distributed execution
Globally consistent reads
If the read/write splitting architecture is used, the latency of data replication may cause read-after-write inconsistency. By default, globally consistent reads are supported for the queries routed to PolarDB-X read-only instances. This ensures that the expired data is not read. After data is written to the primary instance, the written data can be read only from the read-only instances.