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

This article introduces the knowledge of "how to solve the distributed database based on PostgreSQL/openGauss". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

ShardingSphere-Proxy introduction

ShardingSphere-Proxy is an access side in the ShardingSphere ecosystem and is positioned as a database agent transparent to the client. ShardingSphere Proxy is not limited to Java, it implements MySQL and PostgreSQL database protocols, and can use various clients compatible with MySQL / PostgreSQL protocols to connect and manipulate data.

ShardingSphere-JDBCShardingSphere-Proxy

Database any MySQL / PostgreSQL protocol based database connection consumption high low heterogeneous language support Java and other JVM-based language arbitrary performance loss low loss slightly high centralization whether there is no static entry

In the case of sub-database and sub-table or other rules, the data will be distributed to multiple database instances, which will inevitably have some inconvenience in management, or developers who use non-Java languages need the capabilities provided by ShardingSphere. All these circumstances are within the reach of ShardingSphere-Proxy.

ShardingSphere-Proxy hides the actual back-end database, which means using a database for the client. It doesn't need to care about how ShardingSphere coordinates the database behind it. It is more friendly to developers using non-Java languages or DBA.

In terms of protocols, ShardingSphere PostgreSQL Proxy implements Simple Query and most Extended Query protocols, and supports heterogeneous languages to connect Proxy through PostgreSQL/openGauss drivers. ShardingSphere openGauss Proxy not only reuses the PostgreSQL protocol, but also supports the batch insertion protocol unique to openGauss.

However, because ShardingSphere-Proxy adds a layer of network interaction compared to ShardingSphere-JDBC, the latency of SQL execution is increased, and the loss is slightly higher than ShardingSphere-JDBC.

Ecological docking of ShardingSphere-Proxy and PostgreSQL

Compatible with PostgreSQL Simple Query and Extended Query

Simple Query and Extended Query are the most commonly used protocols for most users when using PostgreSQL.

For example, when using the following command line tool psql to connect to the PostgreSQL database for CRUD operations, you mainly use the Simple Query protocol to interact with the database.

Psql-h 127.0.0.1-U postgrespsql (14.0 (Debian 14.0-1.pgdg110+1)) Type "help" for help.postgres=# select id, name from person where age

When users use drivers such as PostgreSQL JDBC Driver, the following code may use PreparedStatement, which corresponds to the Extended Query protocol by default.

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

The protocol interaction diagram of Extended Query is as follows:

At present, ShardingSphere PostgreSQL Proxy implements Simple Query and most Extended Query protocols, but because database clients and drivers have encapsulated API for users to use, ordinary users do not need to care about the database protocol level.

ShardingSphere-Proxy compatible with PostgreSQL Simple Query and Extended Query means that users can use common PostgreSQL clients or drivers to connect ShardingSphere-Proxy for CRUD operations, taking advantage of the incremental capabilities provided by ShardingSphere at the upper layer of the database.

Ecological docking of ShardingSphere-Proxy and openGauss

Support for openGauss JDBC Driver

The openGauss database has a corresponding JDBC driver, the prefix jdbc:opengauss of JDBC URL. Although the JDBC driver of PostgreSQL can also be used to connect to the openGauss database, it is impossible to make full use of the unique features of openGauss, such as batch insertion. ShardingSphere adds the openGauss database type, which can recognize openGauss JDBC Driver, and developers can use openGauss's JDBC driver directly when using ShardingSphere.

Support for openGauss bulk insertion protocol

For example, when we prepare an insert statement as follows

Insert into person (id, name, age) values (?

Taking 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 ()

At the PostgreSQL protocol level, Bind messages can pass a set of parameters at a time so that Portal,Execute can execute one Portal at a time. Batch inserts can be performed by repeatedly executing Bind and Execute. The schematic diagram of the protocol interaction is as follows:

Batch Bind is a unique message type of openGauss. Compared with the original Bind,Batch Bind, which can pass multiple groups of parameters at a time, the protocol interaction for batch insertion using Batch Bind is shown below:

ShardingSphere-Proxy openGauss implements support for the Batch Bind protocol, that is, clients can perform batch inserts into ShardingSphere Proxy directly using openGauss clients or drivers.

What ShardingSphere-Proxy will do in the future

Support for ShardingSphere PostgreSQL Proxy logical MetaData queries

ShardingSphere-Proxy as a transparent database agent, users do not need to care about how Proxy coordinates the database behind.

The following figure shows that there are actually two databases and four tables behind the configuration of the logical library sharding_db and the logical table person,Proxy in ShardingSphere-Proxy.

At present, show schemas and show tables statements are executed in ShardingSphere MySQL Proxy, and the query results can normally list logical library sharding_db and logical table person.

When using psql to connect to PostgreSQL, you can query libraries and tables through commands such as\ l,\ d, and so on. But unlike MySQL, show tables is a statement supported by MySQL, and the\ d used in psql actually corresponds to a more complex SQL. Currently, it is impossible to query logical libraries or logical tables using ShardingSphere PostgreSQL Proxy.

Describe Prepared Statement that supports Extended Query

There are two variations of Describe messages for the PostgreSQL protocol, which are Describe Portal and Describe Prepared Statement. Currently, ShardingSphere Proxy only supports Describe Portal, but not Describe Prepared Statement.

An example of a practical application of Describe PreparedStatement is to get the MetaData of the result set before PreparedStatement execution.

PreparedStatement preparedStatement = connection.prepareStatement ("select * from t_order limit?"); ResultSetMetaData metaData = preparedStatement.getMetaData (); "how to solve distributed databases based on PostgreSQL/openGauss" ends here. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.