ADB-TO-ADB connector

Based on the experience of our product development and support team, users who operate large volumes of company data often use several separate Greenplum clusters.

The motivation for such a decision can be diverse: organizational — different teams, which are the owners of business data, build their data models, process them according to their needs; technical — clusters distributed across various data centers, etc. However, sooner or later, the problem of using data from "neighboring" clusters arises. These can be either one-time scenarios for single queries or the development and deployment of more complex ETL processes. The implementation of such mechanisms can also be different, with their own advantages and disadvantages, based on existing capabilities and limitations.

This article describes the details of our connector implementation for running so-called heterogeneous queries across different Arenadata DB and/or Greenplum clusters — a task that our development team was working on in 2023. This connector allows you to combine different ADB clusters in queries, and at the same time take advantage of establishing connections between segments.

Let’s talk about everything in order.

The point of providing the ability to perform heterogeneous queries is to give a user the tools to access data in such a way that, from a data usage perspective, it appears as if it is located in the same data management system. Also, to do this as efficiently and transparently as possible.

What instruments do we have at our disposal?

The first thing that comes to mind is the PXF service, which is widespread among users. PXF is a framework for combining different data sources. Its advantages include:

There are also a number of disadvantages:

The postgres_fdw extension is a foundation or, you could say, a source of inspiration for many other existing connectors.

The connector is physically a PostgreSQL extension, which in the case of Greenplum is installed on the master host.

Initially, the extension is focused on working with remote PostgreSQL, modified within the Greenplum upstream for functioning in a distributed DBMS environment. Interaction is organized through a standard protocol of the libpq library, which allows you to transparently work with Greenplum (of course, in a "master-master" style).

In the diagram above, the local cluster (indicated in blue) is represented by a master and a pair of segments, the remote cluster is represented by a master (indicated in green).

For a SELECT query, the interaction process schematically looks like this:

A distinctive feature of this approach is the work in the "master-master" style. In this case, segments are involved only in transferring data to/from master. So, there aren’t any interactions between segments of both clusters.

In the case of INSERT queries, everything still goes through the master, but the mechanism of prepared statements is used.

What are the advantages for those who decide to use the postgres_fdw extension to organize cluster communication:

There are obvious disadvantages:

From postgres_fdw let’s move on to the big guns — to greemplum_fdw, which is included in the paid enterprise version of Greenplum. I want to note that in this case we were only able to test the beta version, and perhaps some problems inherent in beta were fixed in subsequent versions.

What’s wrong with greenplum_fdw?

The first inconvenience is the hard setting of the number of segments (workers basically). For example, a local cluster is deployed on three primary segments.

The remote Greenplum cluster does not match the number of segments — there are four of them.

If, when declaring a foreign table on the local cluster, you do not specify the required number of segments (or set it to a number that does not match the number of the remote cluster), then the query fails with an error.

Setting the required number of segments is not a big problem, although getting this information from the owners of another cluster may not always be convenient. Worse, if the cluster configuration changes and a reconfiguration is not performed, queries will stop working until ALTER SERVER is called on the local cluster and the required number of segments is configured.

A more nasty situation may arise if we want to transfer some data from a local cluster to a remote one by inserting it into a foreign table.

Let’s say there is a table on the local cluster with 1100 records, and we request an insert into the table on the remote cluster.

For such an insert request into a foreign table, the planner creates a plan that looks something like this, with a Seq Scan plan node (sequential reading a table’s data) located at the bottom, which receives data from the local table. Then, Redistribute Motion of the data between the segments is performed, the next step is inserting from each segment separately.

For experimental purposes, let’s add a notify message of the transaction commit step at the segment level (the NOTICE level message).

As we can see below, the database managed to commit transactions for two segments, but let’s imagine that some error occurred with the third transaction.

While we were doing one INSERT, which should be atomic as users of a relational DBMS with ACID support expect, we get 742 records in the remote table.

I propose to figure out what is the reason for this situation.

Let’s model it as follows. In our case, we have a local cluster of three segments and a master and a remote cluster of a master and two segments.

It is important to understand that it’s how postgres_fdw is designed and implemented. It contains an internal pool of connections where a transaction begins when a connection is established. It is committed separately, without information about the status of its neighbors. In our case, at some point, one of the transactions receives an error, and we get the result mentioned above.

As a result, the advantages of the beta version of greenplum_fdw include support for SELECT queries in MPP style, but possible problems with data consistency during insertion cast doubt on the possibility of using the connector as a bidirectional one.

At the same time, a user does not have the opportunity to control these risks: when choosing a "master to master" strategy for insertion, SELECT queries will also have a similar strategy. This will negate the benefit of using a connector that supports parallel work across segments.

Also, SELECT queries are quite strictly tied to configurations in terms of the number of segments.

To understand the internal structure of the adb_fdw connector, you need to be aware of the underlying mechanism of parallel cursors. Cursors are used to retrieve data row by row, in a procedural style. In this regard, parallel cursors are no different from their "ordinary" counterparts. They are declared on the query dispatcher (in terms of Greenplum — GP_ROLE_DISPATCH), but endpoints (more about them later) are created on segments. Their placement depends on the query, or more precisely, on the result of query planning. It is also important to add that separate backend process used to service an endpoint — a point through which data is exchanged.

Let’s look at three simple examples of parallel cursor declarations that lead to different endpoint placement configurations.

To create competitive advantages at the current stage of development, some alternative technical solutions have been implemented in our adb_fdw connector.

First, let’s look at it from a user perspective.

The entry point to the external cluster is the SERVER definition — this is the master of the remote cluster. The mpp_execute keyword specified as master does not negate the benefits of MPP for executing SELECT queries. Our connector determines the required configuration of remote segments. The master value is required for the insertion to work only in the "master-master" mode.

You can also set the number of segments or allow the connector to automatically determine the number of segments based on the query plan on a remote cluster, which is quite convenient.

Next, as usual for FDW connectors, a custom user mapping is specified, and a foreign table is declared.

What are the features of our connector?

Compared to competing solutions, the adb_fdw connector has a number of advantages that distinguish it from the considered alternatives:

Joins and aggregates push-down support in the next version of the adb_fdw connector will expand the scope of its application, reduce the amount of transmitted data, and increase the speed of interaction in the most common use cases.