Separating Orca into a Postgres extension: purpose and implementation details

Greengage Database is our fork of Greenplum Database. The main idea is to stay open-sourced and keep the development and improvements of the database. We are going to move Greengage Database to a newer Postgres release, delivering richer feature set to all community users and our customers.

But this task is harder than it might sound. Previously, in order to implement massive parallel features of Greenplum Database, the core Postgres functionality was reworked tremendously. And it introduced a huge difficulty when we speak about bumping the Postgres version. For instance, moving from Postgres 9 to Postgres 12 had demanded enormous efforts — it is almost 5 years of development between major releases.

In order to make the process of moving a new Postgres release smoother and more painless, we have committed ourselves to reduce the internal coupling of Postgres core and MPP features inside Greengage Database.

Postgres itself allows extending its functionality by a powerful and mature mechanism of extensions that are capable to modify standard Postgres behavior via a set of hooks. So, if the majority of MPP features could be organized as external extensions to a pure Postgres core, it would greatly simplify updating the core part.

With that in mind, we have started a large refactoring of Greengage Database core. In scope of it, we are going to separate Postgres core and Greengage Database specific features, using standard existing Postgres means for extensions.

Arenadata’s aim to decouple Greengage’s core from PostgreSQL will enable smoother upgrades to newer PostgreSQL versions. This will allow Greengage Database to benefit from the latest features and security enhancements of PostgreSQL while maintaining its MPP capabilities.

As the first step in functionality decoupling, we’ve started with ORCA.

Orca is a powerful query planner within Greengage Database, designed to significantly enhance query performance and efficiency. It achieves this through a top-down optimization approach, cost-based optimization, advanced join optimization techniques, and extensibility.

By leveraging Orca, Greengage Database delivers exceptional performance and scalability for complex analytical workloads. Orca is the brain behind Greengage’s performance, responsible for optimizing query execution plans.

Before refactoring, Orca was deeply embedded in the Greengage Database core code.

Originally, Orca was developed as a standalone module, which was plugged into Greenplum Database. But at some point, it was placed into the Greenplum Database core. Orca sources resided in the Greenplum Database source code tree as a part of its backend. And as time passed, more and more bindings grew between Orca and the rest of Greenplum Database code. Before we could move Orca into an extension, we had to reorganize all the places where the core interacted directly with Orca.

After extensive analysis, we’ve come up with the following plan of actions:

With the sequence of steps above, during the overall feature development, we could stay in a rather stable state (meaning no major functionality is broken) and deliberately move step by step to our final goal.

All Greengage-specific extensions are located in the gpcontrib folder under the root of the project. We have added a new orca extension there. The extension is built only if the build is configured without the --disable-orca key. The extension produces a shared library. As a part of installation steps, when the extension is installed, it also modifies the postgresql.conf.sample file by setting:

At this point, the extension contains the shared library init and deinit functions:

The init function registers the function PlannedStmt * orca_planner(Query *parse, int cursorOptions, ParamListInfo boundParams) to planner_hook, which is called from the planner() Postgres function. orca_planner() is the point of Orca planner invocation. In case Orca could create a plan, the plan is used further for query execution. In case Orca didn’t manage to produce a valid plan, orca_planner() calls standard_planner(). It is done only for a coordinator instance, as the planning stage is done on the coordinator. (Note: Strictly speaking, segments can also perform planning of local queries dispatched by the coordinator. But Orca was never used to plan these auxiliary queries, only Postgres planner was used. So, nothing changed from the segment’s point of view.)

And here comes the first challenge. To work with Orca, we first need to do some initialization of it by calling the InitGPOPT() function. Previously, it was done in the InitPostgres() function. This function is called for each backend at its start. But now we can rely only on _PG_init() inside the extension, which is called during the init of Postmaster. InitGPOPT() does memory allocations for internal structures. And memory protection is enabled only in InitPostgres(). So, we couldn’t call InitGPOPT() before memory protection is enabled, and we were not able to place it into _PG_init(), which could be seen as the most obvious approach.

Another interesting thing is related to de-init. The TerminateGPOPT() de-init function of Orca was called from ShutdownPostgres(), which in turn registered as process-exit callback. We could create our own callback and register it with the help of before_shmem_exit() in _PG_init()…​ But, as already mentioned, _PG_init() is called by Postmaster. And when Postmaster forks a new backend instance, all of its callbacks are cleared. So, no callbacks registered in _PG_init() are called on the backends exit.

To resolve these limitations, we’ve moved to lazy initialization of Orca from the planner hook. That is a valid place, as the planner can be invoked only by an already initialized coordinator backend when memory protection is properly configured and enabled. And, once the initialization is done, the de-initialization callback is also registered.

So, overall simplified orca_planner() logic is described in the diagram below.

After the steps above, we’d obtained a basis that allowed us to get rid of other couplings between Orca and Greengage Database core code.

Once all the steps above had been done, we were able to move the major part of Orca source files into the gpcontrib/orca extension. This included:

Besides moving the source files, we’ve updated linter and code format tools (src/tools/fmt and src/tools/tidy) and moved them into gpcontrib/orca/tools, as they are used specifically for Orca.

Apart from the optimizer GUC already mentioned above, there is a long list of GUCs used internally in Orca. So, once all Orca sources had been moved to the extension, these GUCs were moved there as well.

As it is seen in the chapters above, the planner hook defined in Orca extension passes control to a previously registered hook only if Orca didn’t manage to create a plan. That is because the other hook may call a standard planner, which we do not want. If Orca has created a plan that is our final station, we do not need to plan more.

But other extensions may want to register a planner hook not for the purpose of a new plan creation, but, for example, to monitor the planning or to assist the planner. One of such examples is the pg_hint_plan extension.

The same is true for the explain hook. If DXL output format is requested, all previously registered hooks will not take control.

The only viable solution, for now, is to invoke all such hooks before Orca is invoked. In consequence, Orca (or any other similar external planner) should be the first shared library to be loaded. So it should appear at the first place in shared_preload_libraries.

After we’d performed all the steps described above, we’ve come up with a version of Greengage where Orca can be plugged in and out dynamically by adding/removing the orca string to the shared_preload_libraries GUC and reloading the cluster. This change has significantly reduced code coupling and increased code cohesion in the product, which is always a good thing to do in terms of software quality.

The key advantages of Orca separation:

After the refactoring, it is possible to easily add any other external planner, extending standard functionality of Greengage Database. Thus, it can give a strong boost to all Greengage community efforts.