POSTGRES-XL Introduction

first, what is POSTGRES-XL

XL means: Extensible Lattice, can be extended lattice, will be the PostgreSQL application on multi-machine distributed database visualization expression.

POSTGRES-XL is a full-acid, open-source, scalable, multi-tenant secure, PostgreSQL-based database solution.

The POSTGRES-XL is flexible enough to handle a wide range of loads, such as:

• OLAP (parallelization via MPP)
• Oltp
• OLAP & OLTP
• Operational Data storage
• Key-value storage, including JSON format

Different application scenarios:

• Support for Business intelligence applications (data Warehouse & Data mart) because PGXL supports MPP (massively Parallel processing)
• Web2.0, a solution for database expansion
• A solution to the database expansion of legacy systems
• New application, you can use PostgreSQL first, and then expand with PGXL as the database becomes larger

The PGXL bottom is PostgreSQL, which means it supports all drivers that support the Postgressql type, including: JDBC, ODBC, OLE DB, Python, Ruby, Perl DBI, Tcl, and Erlang.

second, PostgreSQL and POSTGRES-XL

1994, Postgre95 released, open source.
In 1996, PostgreSQL inherited the Postgre95, released.
2010, POSTGRES-XC released.
In 2012, the former PGXC core developer created the Stormdb company and made some improvements, including performance improvements to MPP parallelism and multi-tenancy security.
In 2013, Translattice acquired Stormdb.
In 2014, the project was open source, named POSTGRES-XL.

third, POSTGRES-XC and POSTGRES-XL

PGXL Architects and developers are a lot of the previous PGXC, PGXL part of the code is transferred from PGXC.

PGXL more emphasis on stability, correctness and performance than functionality.

PGXL adds some important performance improvements, such as the MPP and Replan avoidance on the data nodes, which are not pgxc.

PGXC is now focused on OLTP business, PGXL is more flexible and can be used in many different kinds of business, such as in the field of large data processing, in addition, in a multi-tenant environment, PGXL is also more secure.

The PGXL community is very open.

Iv. Basic knowledge of PGXL architecture

PGXL is a cluster of PostgreSQL databases, which appears to be the same as using a database at the top. Depending on the design, each table can be a form of replicated or distributed.

The PGXL has three main components, namely Gtm,coordinator and Datanode.

• The GTM (gloable Transaction Manager) is responsible for providing the ACID properties of the transaction;
• Datanode is responsible for storing the data of the table and performing the SQL tasks distributed by Coordinator locally;
• Coordinator is responsible for handling each SQL task from application, and decides which datanode to execute, then sends the task plan to the corresponding Datanode, and collects the results as needed to return to application;

The GTM is typically assumed by a separate server because the GTM needs to process transaction requests from all coordinator and Datanode. In order to aggregate the requests and responses of processes on coordinator and datanode onto a single machine, you can configure the Gtm-proxy. Gtm-proxy will reduce the load on the GTM and help handle the failure of the GTM. Even so, the GTM may have a single point of failure, and a Gtm-standby node can be configured as an alternate node for the GTM.

It is best to configure a coordinator and a datanode at the same time, so that you do not have to worry about load balancing and reduce network traffic.

Five, how to achieve high availability

You can add slave to each node, just like the streaming replication for PostgreSQL.

The GTM can have a GTM Standby.

For automatic failover, you can now use Corosync/pacemaker, although they are not yet a core project.

vi. the license of PGXL

PGXL and PostgreSQL Use the same License, as of 2015, using Mozilla public License.

POSTGRES-XL Introduction