How Can We Combine PostgreSQL and MySQL to Make the Whole Greater than the Sum of the Parts? (Part 1)

Image for post
Image for post

Digoal, a senior product expert in the Database Business Group of Alibaba Cloud Intelligence, introduced ways to combine ApsaraDB for PostgreSQL and ApsaraDB for MySQL.

MySQL is currently the most popular open-source database product, and PostgreSQL is the most advanced open-source database product. The combination of the two can make the whole greater than the sum of the parts and solve problems that could not have been solved by individual applications. This two-part article details how to combine both the PostgreSQL and MySQL versions of ApsaraDB RDS to enhance the application value. It also explains five business scenarios and joint applications.

1. What Is ApsaraDB for RDS?

ApsaraDB RDS is a relational database service from Alibaba Cloud. It supports MySQL, PostgreSQL, and MS SQL. It is an out-of-the-box centralized management platform that can manage the full database lifecycle.

Users of ApsaraDB for RDS usually do not care about underlying database operating principles, such as how the database product implements high availability, backup, and security audit. All of these are managed by ApsaraDB for RDS so that it becomes a product that covers the full database lifecycle.

Image for post
Image for post

2. Product Forms of ApsaraDB for RDS

The output forms of ApsaraDB for RDS include public cloud, virtual provider, big account, and software output. The last form is the edition provided by Alibaba Cloud instead of the open-source edition. Some new features were added to the Alibaba Cloud edition. Some users may want to use certain special features and therefore need to use the software output. The Apsara stack and agility editions are also available.

Image for post
Image for post

3. Technical Architecture of ApsaraDB RDS for PostgreSQL

ApsaraDB RDS for PostgreSQL from Alibaba Cloud not only has the features of native PostgreSQL but also inherits the excellent features of ApsaraDB for RDS. The following figure shows its technical architecture.

Image for post
Image for post

4. Features of ApsaraDB RDS for PostgreSQL

As shown in the following figure, ApsaraDB RDS for PostgreSQL provides high availability, large capacity, high performance, backup and recovery, robust security and reliability, and read-only disaster recovery.

Image for post
Image for post

For more information about performance insights, explore the console. If you have enabled this feature, it will intermittently collect active sessions in the database. An active session contains SQL statements that are being executed, the database where each user is executing the SQL statements, the source host, and the waiting event during the execution, such as waiting for CPUs. All of the waiting information will be recorded by this feature. The horizontal axis indicates the timeline, and the vertical axis indicates the number of waiting sessions. For a point in time, the larger the area of the polygon enclosed by the point and both axes, the more waiting sessions at this point of time. The visualized graph provided by performance insights tells us whether to improve I/O throughput or increase the number of CPUs to solve the performance problem.

Image for post
Image for post

So far, we have achieved a qualitative leap in parallel computing.

In 32-thread and 64-thread editions, we tested 39 scenarios, for which the speed increased by 20 times on average. These scenarios cover almost all complex query conditions. In other words, parallel computing is available for any query. In addition, the parallelism is completely automatic.

Also, you can manually set the parallelism for SQL statements. For example, you need to generate a report in the middle of the night, or your analysts must respond to the boss’s need during the daytime to generate a report for some factories. In this case, you can manually set the parallelism so the database can run as fast as expected.

Image for post
Image for post

We have some modules to support multi-modal application enhancements for certain application scenarios. The first type of application scenarios, including the processing of spatio-temporal, GIS, and time series data, are supported by the proprietary GIS module, open-source GIS module, and time series module. The second type of application scenarios, including graph generation, real-time marketing, and user profiling, are supported by the tree module, bitmap module, and advanced bitmap module. The third type of application scenarios, including image similarity analysis, multidimensional similarity analysis, and vector searching, are supported by the image similarity module, text similarity module, and high-dimensional vector module. The fourth type of application scenarios, including text analysis, searching, and the separation of cold and hot data, are supported by the fuzzy query module, Chinese word segmentation module, module of the separation of cold and hot data, and document type module. With these enhancements to support the relevant application scenarios, the product becomes more developer-friendly.

Image for post
Image for post

Continue reading Part 2 of this article series to learn more about the benefits of combining ApsaraDB for PostgreSQL and ApsaraDB for MySQL.

Original Source:

Written by

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store