Why are external DWH orchestrators a "cargo cult"? An introduction to the in-database framework DBLiner

Whenever a company faces the task of building a corporate data warehouse (DWH) based on PostgreSQL or the massively parallel processing (MPP) database Greenplum, architects instinctively reach for the usual stack: Apache Airflow, Dagster, or Prefect.

The setup follows a classic pattern: a heavy external infrastructure is provisioned, and Python-based workers are deployed. On a set schedule, these workers wake up, establish a session with the database, and send a single line of code:

INSERT INTO marts.customer_360 SELECT ... FROM staging.events;

Does the process end there? No. An external Python worker remains in memory, waiting for hours while the MPP cluster churns through terabytes of data on its disks. If the network link blips, the session drops, Airflow logs an error, and engineers are forced to manually restart the failed DAG.

But why go to the trouble of deploying, containerizing, and maintaining a complex external system when the database itself—especially one on the scale of Greenplum—is already a powerful computational engine?

We decided to break with this "cargo cult" and move the control plane to where it truly belongs: right inside the database. Meet DBLiner—a lightweight data orchestration framework written entirely in PL/pgSQL. Notably, this framework is being developed by the same independent developer (raoote) who created CerebroSQL, a free tool for database management. Together, they form a seamless ecosystem: you write and debug the SQL logic for your data marts in CerebroSQL, while DBLiner handles the orchestration—directly within the database management system itself.

What is the fundamental problem with external workers in DWH?

In the modern ELT (Extract-Load-Transform) paradigm, the data transformation phase takes place entirely within the database. In this setup, an external orchestrator acts merely as an "alarm clock" and a dependency manager. Attempting to apply web-oriented tools to heavy-duty databases gives rise to three systemic problems:

1. Network overhead and session fragility: External orchestrators keep TCP connections open for hours. Any network timeout kills a long-running process, even though the computation might have successfully completed on the database side.

2. Infrastructure overhead: Just to run a sequence of SQL scripts, you have to manage Kubernetes pods for Airflow, ensure that Python DAG files stay synchronized, set up CI/CD, and monitor Celery/Redis.

3. A "blind spot" for DBAs: Database administrators see hundreds of disparate, resource-intensive queries originating from a single Airflow service user. It is extremely difficult for them to map a specific `SELECT` statement to a high-level business objective or a data mart.

DBLiner Architecture: Complete Isolation Within the DBMS

All DBLiner components are deployed within the database under a system schema named `dbliner` using a single script, `Install.sql`. The framework does not interfere with your business logic—you continue to write standard stored functions or SQL steps as usual. It handles the routine tasks: scheduling, dependency management, logging, alerting, and error handling.

A key distinction between DBLiner and other in-database "schedulers" is the clear separation between the scheduled workflow and ad-hoc recalculation queues:

[ BUSINESS LOGIC (SQL Steps) in dm$sql ]

│

┌──────────────────┴──────────────────┐

▼ ▼

[ REGULATIONS: dm$scheduler registry ] [ AD-HOC: Recalculation queue dm$queues ]

│ │

▼ ▼

dbliner.run_scheduler(worker_id) dbliner.run_task()

│ │

└──────────────────┬──────────────────┘

▼

[ Managing locks: pg_advisory_lock ]

│

▼

[ Isolation via session context ]

│

▼

[ Logging to dm$operation_log ]

1. Scheduled task pipeline (dm$scheduler)

This entity is responsible for the scheduled execution—organized by hour, day, or month—of ETL processes and the construction of data marts. The scheduler’s core is triggered by the `dbliner.run_scheduler(worker_id)` function. It automatically scans the dependency map (the `dm$mapping` table), determines the correct execution sequence, and ensures that the final data mart is not processed until the raw staging data has been updated.

Parallel recalculation queue (dm$queues)

In a real-world production environment, analysts often need to recalculate historical data from a previous quarter using new coefficients, or re-run a failed data segment with custom parameters.

To handle this, DBLiner features an autonomous queue called `dm$queues`, managed by the `dbliner.run_task()` function. You can submit any ad-hoc tasks—complete with custom session parameters—to this queue; they will then execute in parallel without interfering with your primary, scheduled production workflows.

How does this solve the problem of lock contention? Taming queues using `pg_advisory_lock`

Critics of in-database orchestrators often argue: "If you launch parallel threads, they’ll start blocking queue tables and competing for rows during UPDATE/SELECT operations, eventually trapping the entire DBMS in an endless cycle of deadlocks."

The developers of DBLiner addressed this classic problem at the core design level. Instead of relying on persistent, heavy-duty transactional row locks (such as SELECT ... FOR UPDATE), the system handles task distribution and manages concurrency between parallel processes entirely through PostgreSQL’s built-in advisory locks—`pg_advisory_lock`.

How distributed coordination works:

* Locking without tables: The `pg_advisory_lock` mechanism operates using abstract numeric keys within the database's shared memory, without interacting with table rows at all. Workers can reach an agreement instantaneously by atomically acquiring the required task ID.

* Contextual session isolation: Once a lock has been successfully acquired via `pg_advisory_lock`, the framework isolates execution threads using custom database session contexts (via `current_setting` / `set_config`), assigning each thread its own isolated pool of variables (RUNNER1, RUNNER2).

This made it possible to reduce physical competition for resources to absolute zero: parallel workers process tasks atomically, safely, and at the speed of RAM.

dbt vs. Airflow vs. DBLiner: What’s the difference?

Data engineers often ask: "If we want to perform in-database transformations, we already have dbt (Data Build Tool). Why would we need another tool?"

There is an important conceptual distinction here. dbt is not an orchestrator. While it can compile Jinja templates into SQL and construct a directed acyclic graph (DAG) of model dependencies, it cannot function as a persistent daemon, poll queues, execute tasks based on complex schedules (cron), or handle ad-hoc, real-time recalculation requests. To run dbt in a production environment, you still have to wrap it in an external tool—such as Apache Airflow, Prefect, or dbt Cloud.

DBLiner, by contrast, is a full-fledged control plane (comprising an orchestrator, a scheduler, and a queue system) that resides within the database management system itself.

Criterion	Apache Airflow	Apache Airflow	DBLiner
Where does management logic reside?	On an external server / K8s cluster (Python)	On an external server (CLI / Python)	Within the DBMS itself (dbliner schema, PL/pgSQL)
Where are the calculations performed?	DBMS (but the session is maintained by an external worker)	Entirely within the DBMS	Entirely within the DBMS (True MPP ELT with pushdown)
Schedule Management (Scheduler)	Yes (built-in powerful timeline engine)	No (requires an external trigger: cron, Airflow, or dbt Cloud)	Yes (dm$scheduller table + built-in engine)
Parallel ad-hoc queue	Limited (Trigger DAG Run with parameter passing)	No (manually launching the entire sequence of commands from the terminal)	Yes (The dm$queues entity for parallel recalculation of history)
Infrastructure overhead	High (requires Python, Celery, Redis, web server)	Intermediate (requires a Python environment, git, and CLI runners)	Zero-level (deployed in a single run via Install.sql)
Integrated Development Environment (IDE)	Any text editor (VS Code, PyCharm)	VS Code / dbt Cloud	CerebroSQL (the native free IDE from the same author) or any other IDE

In what ways does DBLiner outperform dbt in practice?

1. Dynamic parameters "on the fly" without recompilation: In dbt, to change execution parameters (for example, to recompute data for a specific date), you must pass `--vars` strings via the CLI of an external runner. In DBLiner, you simply perform an `INSERT` into the `dm$queues` queue table with the required session parameters, and the system picks them up automatically.

2. Fewer intermediate layers: Running dbt on a server requires setting up Python, configuring connection profiles (profiles.yml), and managing adapter versions (e.g., dbt-greenplum). DBLiner operates using pure SQL, directly within your database console or the CerebroSQL interface.

Integration with the Big Data ecosystem (PXF & Greenplum)

If your data warehouse is built on Greenplum, DBLiner becomes the ultimate tool for managing a Data Lakehouse.

Thanks to native support for the PXF (Platform Extension Framework), moving data between Hadoop (HDFS/Hive), object storage (S3/MinIO), and your DWH becomes a seamless SQL operation defined within the `dm$sql` table.

In this setup, DBLiner functions exclusively as a control plane (coordinator):

* Performs pushdown computations directly on the Greenplum cluster.

* Eliminates intermediate proxy servers. Data is streamed directly from external sources into the DBMS segment nodes in parallel.

* Controls the limits on concurrent PXF streams, protecting the NameNode of your Hadoop cluster from overload.

Native development tool: CerebroSQL + DBLiner

Creating in-database pipelines manually via the `psql` console is a rather unpleasant experience. That is precisely why this framework truly shines when used in conjunction with CerebroSQL.

Since both products were created by the same author, the CerebroSQL interface is perfectly tailored for working with DBLiner metadata. Directly from the graphical interface of this free IDE, you can:

1. Quickly write, test, and debug SQL code for future ETL steps.

2. Register task spaces (Spaces) and assign them to departments.

3. Monitor execution logs (dm$operation_log) in real time and track failed steps, down to the specific line of code.

What’s next?

We have ended up with a lightweight, fully automated data management system that resides entirely within the DBMS itself. No Python, no infrastructure overhead—just pure SQL, the raw speed of the DBMS, secure `pg_advisory_lock` mechanisms, and complete control.

In the next article, we’ll dive into the code: we’ll examine the inner workings of the worker pool, explore the source code behind the `pg_advisory_lock` mechanisms, take a look under the hood of the `RUNNER` context variables, and learn how to trigger parallel historical recalculations using the `dm$queues` ad-hoc queue. Stay tuned so you don’t miss it!

The framework’s source code is open-source under the MIT license and is available on GitHub: DBLiner