---
title: 'OneSparse PostgreSQL: GraphBLAS Integration in DBOS'
url: https://www.emergentmind.com/topics/onesparse-postgresql
type: topic
---

# OneSparse PostgreSQL: GraphBLAS Integration in DBOS

OneSparse PostgreSQL is a PostgreSQL-based realization of GraphBLAS hypersparse traffic-matrix processing within DBOS (DataBase Operating System), used to add network sensing to DBOS web services while preserving DBMS-centered deployment, durability, and security properties. In the reported design, network sensing is implemented with the `onesparse` PostgreSQL extension, which introduces a `onesparse.matrix_t` type and SQL-callable GraphBLAS operators for constructing, aggregating, multiplying, and reducing hypersparse matrices stored as BLOBs. Within DBOS, this mechanism is integrated directly into the HTTP request path, so that incoming requests populate relational logs, trigger matrix construction, and feed both local and global collaborative-awareness workflows [2509.09898].

## 1. Architectural role within DBOS

DBOS is described as a capability that integrates web services, operating system functions, and database features to reduce web-deployment effort while increasing resilience. In that setting, network sensing is added using GraphBLAS hypersparse traffic matrices via two approaches: Python-GraphBLAS and OneSparse PostgreSQL. The OneSparse PostgreSQL approach is the database-native path, in which each DBOS web-service instance runs its own PostgreSQL with the `onesparse` extension installed, and the standard HTTP request pipeline automatically inserts each incoming request into `dbos.workflow_status` [2509.09898].

The same stored procedures that implement DBOS system calls also implement the network sensing workflow, so developers do not need to write any additional code. This places OneSparse PostgreSQL at the boundary between transactional web-service execution and sparse linear-algebraic network analytics. A plausible implication is that the design treats sensing not as a separate telemetry subsystem but as a first-class DBOS workflow.

The paper situates this implementation in a collaborative-awareness architecture. Many DBOS instances can be connected to a single DBOS aggregator, and local analytics are both stored and transmitted onward. The aggregator is itself another DBOS service that loads incoming summaries into its own PostgreSQL, uses the same `onesparse` functions to build a rolling global view, and publishes alerts or visualization data via DBOS’s built-in web dashboard [2509.09898].

## 2. Relational schema and hypersparse matrix representation

The OneSparse PostgreSQL workflow uses a small number of explicitly defined tables to bridge web-request logs and GraphBLAS matrices.

| Table | Stored content | Role |
|---|---|---|
| `dbos.workflow_status` | Time-ordered log of incoming web requests with `request_id`, `src_ip`, `dst_ip`, `created_at` | Source stream for matrix construction |
| `dbos.matrices` | Base and intermediate GraphBLAS matrices with request-window metadata, `nnz`, and `mat onesparse.matrix_t` | Local aggregation state |
| `dbos.analytics` | Nine summary quantities computed on each completed local aggregate | Local summary output |
| `dbos.global_matrices` | Same schema as `dbos.matrices` | Global aggregation across workers |

`dbos.workflow_status` is a time-ordered log of every incoming web request. Each row contains at least `request_id` (serial), `src_ip` (IPv4 as 32-bit integer), `dst_ip` (IPv4 as 32-bit integer), and `created_at` (timestamp). `dbos.matrices` stores base and intermediate GraphBLAS matrices and includes `matrix_id`, `start_request`, `end_request`, `nnz`, `mat onesparse.matrix_t`, and `created_at timestamptz default now()`. `dbos.global_matrices` has the same schema as `dbos.matrices`, but is used for the global aggregate across workers [2509.09898].

The underlying sparsity model is central. The paper states that the space of possible IP pairs is in the billions, but only $N_v \simeq 2^{17}$ are present per base matrix. That hypersparse property is handled inside the OneSparse type: only nonzeros `(src_ip, dst_ip, weight=1)` are stored, in compressed “triplet + CSC” form. The `mat` field is therefore not an ordinary relational structure but a BLOB embedding a hypersparse SuiteSparse:GraphBLAS matrix [2509.09898].

Indexing is used to align relational access paths with request-windowed matrix workloads. A GiST index on `int8range(start_request,end_request)` allows the system to quickly find all matrices covering a given request window, while a regular btree index on `created_at` supports time-range queries. The paper also reports a partial index on `workflow_status(request_id)` to fetch only the last $N_v$ rows in $O(\log n + N_v)$ time [2509.09898].

## 3. GraphBLAS execution model in SQL

The `onesparse` extension is loaded into PostgreSQL at install time with:

```sql
CREATE EXTENSION IF NOT EXISTS onesparse;
```

This adds the `onesparse.matrix_t` domain and a set of SQL-callable functions for GraphBLAS operations, including construct, add, multiply, reduce, and extract. The resulting execution model presents GraphBLAS kernels as SQL UDFs while keeping the actual matrix representation inside the extension [2509.09898].

The key algebraic operations are explicitly stated. Element-wise addition is issued as:

```sql
SELECT onesparse_matrix_eWiseAdd(m1.mat, m2.mat) AS mat_sum
```

and is written in GraphBLAS notation as $C \leftarrow A \oplus B$, where $\oplus$ is integer addition. Matrix-matrix multiplication is issued as:

```sql
SELECT onesparse_matrix_mxm(A.mat, B.mat) AS C
```

and is written as $C = A \oplus.\otimes B$, meaning
$$
C(i,j) = \bigoplus_k \left(A(i,k) \otimes B(k,j)\right),
$$
with $\oplus = \text{plus}$, $\otimes = \text{times}$, or any other semiring [2509.09898].

A distinguishing feature of the PostgreSQL workflow is incremental in-place aggregation. Rather than storing $N_b$ base matrices and summing them in one batch, the workflow keeps a working aggregate row in `dbos.matrices`. Each time a new base matrix is created, a trigger updates that row by replacing `mat` with `onesparse_matrix_eWiseAdd(mat, NEW.mat)`, incrementing `nnz`, and extending `end_request`. Once `nnz` reaches $N_a = N_v \times N_b$, a new row is opened for the next cycle [2509.09898].

The performance-critical detail is that the heavy work is not done by PL/pgSQL itself. The paper states that the `onesparse` C library is multithreaded; when `onesparse_matrix_eWiseAdd` or `onesparse_matrix_mxm` is called, control passes into SuiteSparse:GraphBLAS where OpenMP threads do the work, bypassing PostgreSQL’s single-threaded executor. This is paired with `pg_prewarm` on the `matrices` table to keep the working aggregate in memory [2509.09898].

## 4. Trigger-driven local workflow and cross-node messaging

The local workflow is triggered directly by inserts into the request log. A counter sequence is created with `CREATE SEQUENCE workflow_counter;`, and the `build_base_matrix()` trigger function advances that counter on each new row in `dbos.workflow_status`. When `cnt % :N_v = 0`, it gathers the last $N_v$ requests, constructs a base matrix with `onesparse_matrix_build`, inserts that matrix into `dbos.matrices`, and then invokes logic to manage the current working aggregate [2509.09898].

The source paper provides the central pattern in PL/pgSQL. The trigger on `dbos.workflow_status` runs `AFTER INSERT FOR EACH ROW`, and the helper `rotate_working_aggregate()` checks whether the working aggregate has reached $N_a$ rows, computes analytics through SQL UDFs such as `onesparse_reduce_sum` and `onesparse_reduce_max`, writes the results into `dbos.analytics`, and begins a fresh working aggregate. The nine summary quantities are described as being computed on each completed local aggregate and include examples such as number of valid web requests, unique links, and max fan-out [2509.09898].

Distributed coordination is externalized rather than embedded in PL/pgSQL. Because PostgreSQL cannot directly call pPython’s `SendMsg`, each `matrix_insert` trigger issues:

```sql
NOTIFY matrix_ready, working_id::text;
```

A small external Python daemon on each worker node listens for these notifications, fetches the matrix BLOB via `SELECT mat FROM dbos.matrices WHERE matrix_id = $1;`, and transmits the serialized matrix to the coordinator. The coordinator, designated rank 0, collects worker aggregates using `ProbeMsg/RecvMsg` and invokes a PL/pgSQL function `merge_into_global(mat)` that performs the same in-place `onesparse_matrix_eWiseAdd` logic on `dbos.global_matrices` [2509.09898].

The paper also describes an HTTP-based variant aligned with cloud-native deployment. Once local analytics are computed, they are stored in `dbos.analytics` and sent as HTTP POSTs through a simple REST endpoint to a DBOS aggregator service. This parallels the pPython message-passing approach but uses HTTPS-compatible service interfaces [2509.09898].

## 5. Throughput, scalability, and measured overheads

On a single worker, the reported throughput figures separate matrix processing from end-to-end insert performance. Base matrix build is given as approximately $10^{6.2}$ requests/sec, local aggregation with in-place `eWiseAdd` as approximately $10^{7}$ requests/sec, and analytics based on reduce operations as approximately $10^{6.4}$ requests/sec. The end-to-end sustained insertion rate into `dbos.workflow_status` is limited by DBOS’s per-insert transactional overhead, but base-matrix construction itself, bypassing full inserts, reaches $\gg 10^5$ RPS [2509.09898].

At the DBOS service level, the paper states that the sustained web request rate for a single DBOS instance was $>10^5$, well above the required maximum, indicating that network sensing can be added to DBOS with negligible overhead. This claim is specific to the service-level workload, whereas the component-level figures above describe matrix construction, aggregation, and analytics throughput [2509.09898].

For multi-node execution, the implementations diverge. The Python-GraphBLAS workflow scales linearly up to 64 workers, with coordinator throughput approximately $10^{7.2}$ RPS. The OneSparse PostgreSQL workflow scales linearly up to 32 workers; beyond 32, single-threaded PostgreSQL connection overhead and Python listener contention cap global aggregation at approximately $10^{6.35}$ RPS. Measured coordinator aggregation latency per global batch is 12.9 s for PostgreSQL and 1.9 s for Python, corresponding to theoretical maxima of approximately 25 PostgreSQL workers versus approximately 175 Python workers at $10^5$ RPS each [2509.09898].

The overhead comparison is explicit. PostgreSQL adds approximately $2\times$–$4\times$ overhead versus Python-GraphBLAS for raw matrix construction and analytics, due primarily to transaction serialization, BLOB compression/decompression, and trigger machinery. The paper identifies durability and transactional consistency as the associated trade-offs [2509.09898].

## 6. Resilience, security, and operational interpretation

The OneSparse PostgreSQL approach is embedded in DBOS’s security and persistence model. All tables use row-level security policies: only the DBOS middleware user role can `INSERT` into `workflow_status`; only the `onesparse` UDFs can `UPDATE` the working aggregate; and only the aggregator role can `SELECT` from `dbos.global_matrices`. PostgreSQL’s WAL ensures that no data is lost if a DBOS instance crashes mid-aggregation, and on restart the triggers pick up from the last committed `request_id` [2509.09898].

Retention and replay semantics are also part of the design. The `workflow_status` table can be truncated or archived without affecting the durable aggregate in `dbos.matrices`, supporting both retention policies and “rolling buffer” operation. This means that durable sensing state is associated with the aggregate matrices rather than with indefinite retention of the full request log [2509.09898].

The paper’s reported best practices emphasize how OneSparse PostgreSQL should be used. It recommends keeping the working aggregate in a single row and doing in-place updates to avoid writing $N_b$ separate base matrices; pushing as much work as possible into the `onesparse` C code (`eWiseAdd`, `mxm`, `reduce`) to benefit from multithreading and avoid row-by-row PL/pgSQL loops; using `LISTEN/NOTIFY` plus an external lightweight Python daemon to connect PostgreSQL to MPI- or HTTP-based coordination; and tuning `work_mem` and `maintenance_work_mem` so that in-memory hypersparse matrix expansions do not spill to disk. For extreme scale, it suggests considering sharding of `global_matrices` by time window or IP-prefix to enable parallel global aggregation in multiple PostgreSQL instances [2509.09898].

Two common misconceptions are addressed by the reported results. One is that placing GraphBLAS inside PostgreSQL makes the entire system natively parallel; the paper states instead that PostgreSQL’s SQL executor remains single-threaded and that heavy linear algebra must be offloaded to the extension’s C backend. The other is that the database-native path is simply a slower version of the Python path; the reported “lessons learned” characterize the trade-off more precisely: OneSparse lets PostgreSQL act as a full GraphBLAS engine, but transactional writes and per-row triggers incur overhead, batch grouping by $N_v$ is critical, and the durability, security, and built-in Web APIs of DBOS/OneSparse provide a “network sensing as a service” model that, despite approximately $2\times$ throughput overhead versus pure Python, scales to dozens of nodes with negligible incremental resource demands [2509.09898].

Source: https://www.emergentmind.com/topics/onesparse-postgresql