Build distributed database solutions based on PostgreSQL/openGauss

As MySQL ShardingSphere-Proxy has become more and more mature and widely adopted, the ShardingSphere team has also been working on PostgreSQL Shardingsphere-Proxy. Compared to alpha and Beta, 5.0.0 has a lot of improvements in PostgreSQL protocol implementation, SQL support, permission control, and other aspects, paving the way for the full integration of PostgreSQL ecosystem. The ecological docking between ShardingSphere-Proxy and PostgreSQL enables users to obtain incremental transparent capabilities such as data sharding, read/write separation, shadow library, data encryption/desensitization, and distributed governance based on the PostgreSQL database.

In addition to PostgreSQL, openGauss, an open-source Chinese database developed by Huawei, continues to gain popularity. OpenGauss has excellent stand-alone performance, combined with the capability and ecology of ShardingSphere, can create a domestic distributed database solution covering more scenes.

ShardingSphere PostgreSQL/openGauss Proxy currently supports data sharding, read/write separation, shadow library, data encryption/desensitization, distributed governance and most of the capabilities in the Apache ShardingSphere ecosystem. Gradually align ShardingSphere MySQL Proxy in the degree of perfection.

This article will introduce shardingSphere-Proxy 5.0.0 on PostgreSQL and the ecological docking with openGauss.

The authors introduce

Benedict goh

Apache ShardingSphere Committer, SphereEx Middleware Engineer. Currently focused on Apache ShardingSphere and its sub-project ElasticJob.

ShardingSphere – Proxy is introduced

Shardingsphere-proxy is an access end in the ShardingSphere ecosystem. It is positioned as a database Proxy transparent to clients. ShardingSphere Proxy is not limited to Java. It implements MySQL and PostgreSQL database protocols, and can connect and manipulate data using various clients compatible with MySQL/PostgreSQL protocols.

In the case of sub-database sub-table or other rules, data will be scattered to multiple database instances, which will inevitably have some inconvenience in management. Or developers using non-Java languages who need the capabilities provided by ShardingSphere… These situations are exactly what Shardingsphere-Proxy can do.

Shardingsphere-proxy hides the actual back-end database. For the client, it is just using a database. There is no need to care about how ShardingSphere coordinates the database behind, and it is more friendly to developers or DBAs who use non-Java languages.

In terms of protocol, ShardingSphere PostgreSQL Proxy implements Simple Query and most Extended Query protocols, and supports heterogeneous languages to connect to Proxy through PostgreSQL/openGauss drivers. ShardingSphere openGauss Proxy not only uses the PostgreSQL protocol, but also supports the opengAuss-specific bulk insert protocol.

However, because ShardingSphere-Proxy adds a layer of network interaction compared with ShardingSphere-JDBC, the delay of SQL execution will be increased and the loss will be slightly higher than shardingSphere-JDBC.

Ecological interconnection between ShardingSphere-Proxy and PostgreSQL

Compatible with PostgreSQL Simple Query and Extended Query

Simple Query and Extended Query are the protocols most users use when using PostgreSQL.

For example, when the following command line tool PSQL is used to connect to a PostgreSQL database for CRUD operations, the Simple Query protocol is used to interact with the database.

$ psql -h 127.0. 01. -U postgres
psql (14.0 (Debian 14.0-1.pgdg110+1))
Type "help" for help.
postgres=# select id, name from person where age < 35;
 id | name 
----+------
  1 | Foo
(1 row)
Copy the code

The Simple Query protocol interaction diagram is as follows:

When using a Driver such as the PostgreSQL JDBC Driver, the following code may be PreparedStatement, which by default corresponds to the Extended Query protocol.

String sql = "select id, name from person where age > ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setInt(1.35);
ResultSet resultSet = ps.executeQuery();
Copy the code

Extended Query’s protocol interaction is shown as follows:

Currently, ShardingSphere PostgreSQL Proxy implements Simple Query and most Extended Query protocols. However, since database clients and drivers have encapsulated apis for users to use, The average user doesn’t need to care about the database protocol level.

Shardingsphere-proxy’s compatibility with PostgreSQL’s Simple Query and Extended Query means: Users can use a common PostgreSQL client or driver to connect to ShardingSphere-Proxy for CRUD operations, taking advantage of the incremental capabilities provided by ShardingSphere at the top of the database.

Ecological connection between ShardingSphere-Proxy and openGauss

Supports openGauss JDBC Driver

The openGauss database has a JDBC driver. The JDBC URL prefix is JDBC: openGauss. Although the PostgreSQL JDBC driver can connect to openGauss databases, it does not take full advantage of openGauss ‘unique features such as bulk inserts. ShardingSphere adds openGauss database type and can identify openGauss JDBC Driver. Developers can directly use openGauss JDBC Driver when using ShardingSphere.

Supports openGauss batch insert protocol

As an example, when we prepare an insert statement below

insert into person (id, name, age) values (?, ?, ?)
Copy the code

Using JDBC as an example, we might perform bulk inserts using the following methods:

String sql = "insert into person (id, name, age) values (? ,? ,?) ";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setLong(1.1);
ps.setString(2."Foo");
ps.setInt(3.18);
ps.addBatch();
ps.setLong(1.2);
ps.setString(2."Bar");
ps.setInt(3.36);
ps.addBatch();
ps.setLong(1.3);
ps.setString(2."Tom");
ps.setInt(3.54);
ps.addBatch();
ps.executeBatch();
Copy the code

On the PostgreSQL protocol level, Bind messages can transmit a set of parameters to form a Portal, and Execute can Execute a Portal at a time. Performing bulk inserts can be done by repeatedly executing Bind and Execute. Protocol interaction diagram is as follows:

Batch BindThis is an openGauss specific message type, compared to the originalBind.Batch BindCan pass multiple groups of parameters at a time, usingBatch BindThe protocol interaction for performing batch inserts is as follows:

Shardingsphere-proxy openGauss implements support for the Batch Bind protocol, that is,Clients can directly use openGauss clients or drivers to perform bulk inserts to ShardingSphere Proxy.

What shardingSphere-proxy will do next

ShardingSphere PostgreSQL Proxy logical MetaData query is supported

Shardingsphere-proxy serves as a transparent database Proxy, and users do not need to care about how the Proxy coordinates the database behind it.

In the following figure, logical library Sharding_DB and logical table Person are configured in Shardingsphere-proxy. There are actually 4 tables in 2 databases behind Proxy.

Currently executed separately in ShardingSphere MySQL Proxyshow schemas,show tablesStatement, the result of the query can list the logical library normallysharding_dbAnd the logical tableperson.

When PSQL is used to connect to PostgreSQL, you can run the \l and \d commands to query libraries and tables. However, unlike MySQL, show tables is supported by MySQL. In PSQL, \d corresponds to a more complex SQL statement. Currently, the Logical library or logical table cannot be queried using ShardingSphere PostgreSQL Proxy.

Describe Prepared Statement that supports Extended Query

There are two variants of Describe messages for the PostgreSQL protocol, Describe Portal and Describe Prepared Statement. Currently, ShardingSphere Proxy only supports Describe Portal and does not support Describe Prepared Statement.

Obtain MetaData for a result set before the PreparedStatement is executed.

PreparedStatement preparedStatement = connection.prepareStatement("select * from t_order limit ?");
ResultSetMetaData metaData = preparedStatement.getMetaData();
Copy the code

The docking process between The ShardingSphere and the PostgreSQL/openGauss ecosystem is still in progress, and there is still much to be done. If you are interested in what we do, you are welcome to join the ShardingSphere community via GitHub or the mailing list.

Making: github.com/apache/shar…

The resources

• www.postgresql.org/docs/curren…
• Gitee.com/opengauss/o…

Welcome to add community Manager wechat (SS_assistant_1), enter the wechat communication group and communicate with more ShardingSphere enthusiasts