Shulou(Shulou.com)06/02 Report--

"PostgreSQL UDB is used for big data analysis, and query efficiency is higher. Compared with self-built, its reliability is higher and it is convenient for operation and maintenance. "

- 31 Conference Operations Manager Tang Lei

How to use PostgreSQL?

PostgreSQL is a very popular open source database in the industry, tied for the first echelon with MySQL and MongoDB.

PostgreSQL supports a variety of table association algorithms, has rich statistical functions and syntax, and performs well in multi-dimensional complex queries and analysis scenarios. MySQL and MongoDB also have their own features and fans.

Objectively speaking, there is no need to simply compare the advantages and disadvantages of these databases, but we should fully understand their respective characteristics, make reasonable decisions and skillfully match them according to business needs, so as to maximize the effect.

A practical example from the UCloud User 31 conference can help illustrate how PostgreSQL can be successfully applied to solve fast queries with large data volumes.

user scenarios

31 Conference is China's leading scenario marketing technology service provider, affiliated to Shanghai Bayantu Information Technology Co., Ltd. Through the application of Internet, Internet of Things, AI, big data and cloud computing technologies, combined with face-to-face marketing scenarios such as conferences, exhibitions and events, it has successively launched three product systems of conference cloud, exhibition cloud and marketing cloud as well as 10 industry scenario schemes. Among them, 31 Conference Cloud and 31 Exhibition Cloud, as one-stop digital conference SaaS cloud platforms, realize the wisdom of the whole process of exhibition through componentization, integration and process.

Note: Photo from Conference 31

It can be imagined that SaaS conference platform has many internal modules and close association, and the demand for database is diversified and refined, so the first thing is to investigate and understand the mainstream database types available in the market. The UDB subtypes that UCloud can provide are as follows:

Due to the business characteristics of users, they have important demands for OLTP and OLAP, and so far, the accumulated service volume of more than 300,000 customers and more than 1.3 million exhibitions means that the storage capacity of the database is also considerable.

After careful consideration, the user selects three databases at the same time to meet different goals.

Among them, PostgreSQL is faster and more efficient than MySQL in OLAP, which is also an important weight for users to choose PostgreSQL. In actual business, users use PostgreSQL to process large data queries with a single table size of 500w records, and quickly and smoothly transfer the results to the next business process.

Self-built cluster or PostgreSQL UDB?

The choice now is whether to build your own PostgreSQL cluster using cloud hosting or use UCloud's off-the-shelf PostgreSQL UDB product.

PostgreSQL UDB provides high security and availability, backup creation, automatic rollback, and more. In terms of data level, high-availability active and standby and underlying data storage have data redundancy to ensure zero data loss.

Based on these elements available at the product level, 31 chose to use PostgreSQL UDB products. We are pleased that in the user's use of PostgreSQL for more than a year, its instance has not experienced a failure, the reliability design mechanism in the background has withstood various unexpected situations, has not affected the normal use of users, and helped them avoid the trouble of emergency troubleshooting.

How to ensure high reliability?

In order to fully guarantee reliability, PostgreSQL UDB has done a lot of work in terms of commercialization, such as:

1. automatic rollback

This function means that when a user deletes or loses data due to human error, as long as the backup of the previous 7 days exists, the "second-level rollback" function can be used to restore the data to any second in the past 7 days, which can be said to provide a "reassurance pill" for users to use PostgreSQL products.

In addition to rollback, users can also create more copies of the database through the "Create Slave Library" feature to further increase data security.

2. High availability deployment, automatic disaster recovery

PostgreSQL UDB uses a master-slave replication architecture to ensure high availability of services. While the primary database provides services, another set of database services keeps synchronizing data and waiting at any time. The automatic disaster recovery module in the UDB background can automatically detect and recover when there is a problem with the PostgreSQL instance service, ensuring the stability and reliability of the database service.

During instance switching, the disaster recovery module promotes the standby PostgreSQL service to the master and falls back to the slave after the original master service is started. In the whole process, users do not need any manual intervention and configuration modification, and truly achieve automatic disaster recovery.

Figure: PostgreSQL UDB automatic disaster recovery diagram

3. Hot upgrade, continuous service online expansion

PostgreSQL UDB dynamically scales database resources on demand, based on business needs. With just a few clicks on the console, users can dynamically adjust the memory and disk size of the instance to meet the elastic requirements of database performance and storage space in different business phases.

PostgreSQL UDB in the process of resource expansion, database services can be basically non-stop service, only second-level flash. This greatly reduces the impact time of database expansion on business and achieves real "hot upgrade."

Currently, PostgreSQL UDB is available in Beijing, Shanghai, Guangzhou and Hong Kong. The specific deployment is as follows:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.