Introduction to PostgreSQL-related open source software and architecture

1, Pgbouncer
A lightweight connection pooling tool for the PG database, features and features are as follows:
1) Cache the connection of the back-end PG database, when the current-side application request, allocate connection pool connection to the application, thus make full use of system resources.
2) allows the application to create more connections than the connection pool, does not apply the connection to provide queued waiting function, so that the full use of resources, but also ensure high concurrency on the application side.
3) The application connection can be limited, so that the system can play a stable and security role.
4) The communication efficiency of pgbouncer is very high.
5) Pgbouncer is implemented by C language, short and short, and consumes very little system resources.
6) Pgbouncer supports three connection pooling modes: Session level, transaction level, statement level.
2, Slony-i
PG database of a logical replication tool, features and features are as follows:
1) SLONY-I supports cascade replication, a node is a subscriber at the same time, but also as the next level of data providers, the data of the original producers of data modification, will be distributed among subscribers at all levels.
2) The Slon daemon is required on each node in the Slony-i cluster to handle events in replication, such as configuring events and synchronizing events.
3) Slony-i can be managed and configured through Slonik tools with the ability to process scripts.
4) Slony-i has a strong logical replication capabilities, but also some limitations, such as: Large object replication, DDL replication, User rights replication, and so on.
3, Bucardo
PG database of a two-way synchronization function of the tool, features and features are as follows:
1) A dual master architecture can be implemented for PG, only dual master synchronization is possible before version 5.0, and multi-master synchronization can be achieved after version 5.0.
2) The tool is asynchronous synchronous, which is similar to MySQL's m-s synchronization.
3) through the trigger record changes, using the PG Message Event notification mechanism to achieve synchronization.
4) Synchronization is flexible, you can synchronize only a subset of the data in the database.
5) daemon is implemented in Perl language, and is synchronized by Pl/pgsql and Pl/perlu functions.
4, Pl/proxy
The software, features and features of the PG database for horizontal splitting are as follows:
1) The application sends the request to the horizontal split middleware, which sends SQL to the underlying data node based on the hash value of the split key.
2) in the Pl/proxy architecture, some PG databases are split horizontally as middleware, but they do not store actual data. In addition to the PG libraries for these middleware roles, there are some PG libraries that actually store data in the bottom.
3) Pl/proxy is not a standalone program, but a PL language installed in the PG library that can route requests to the underlying database.
4) The route to the application request is implemented by invoking the same name function in the middleware and the underlying library.
5, Pgpool-ii
Middleware located between the PG server and the client, features and features as follows:
1) With connection pooling function.
2) replication can be performed between multiple PG servers.
3) Load balancing can be performed across multiple PG servers.
4) Limit the connection exceeding the limit.
5) Parallel query between multiple PG servers.
6) For multi-process architecture, including: PCP process, pgpool-ii parent process, pgpool-ii child process, worker process.
6, POSTGRES-XC
The real data-level split distributed database based on PG library features and features are as follows:
1) compared with other horizontal splitting schemes, POSTGRES-XC is implemented more thoroughly, and for users, the POSTGRES-XC cluster is like a stand-alone database.
2) The cluster based on PG is implemented by modifying the PG source code, rather than the middleware on the PG database.
3) PG client and driver can be connected to the POSTGRES-XC without distinction.
4) realize the global transaction and achieve strong data consistency.
5) symmetric cluster, no center node, application can read and write any node.
6) Linear extended read and write, by adding nodes, not only can expand read performance, but also can extend the write performance.
7) as the node increases, the POSTGRES-XC performance increases nearly linearly.

Introduction to PostgreSQL-related open source software and architecture