OneSparse PostgreSQL: GraphBLAS Integration in DBOS

• OneSparse PostgreSQL is a PostgreSQL extension that implements GraphBLAS hypersparse matrix operations for traffic-matrix processing in DBOS.
• It leverages SQL-callable functions to construct, aggregate, and reduce matrices, seamlessly integrating network sensing into transactional web workflows.
• The design ensures data durability and security with in-place updates, multithreaded processing, and trigger-driven aggregation for high-performance analytics.

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

3. GraphBLAS execution model in SQL

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

$O(\log n + N_v)$6

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 (Lockton et al., 11 Sep 2025).

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

$O(\log n + N_v)$7

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

$O(\log n + N_v)$8

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 (Lockton et al., 11 Sep 2025).

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_v$0, a new row is opened for the next cycle (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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$1 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 (Lockton et al., 11 Sep 2025).

The source paper provides the central pattern in PL/pgSQL. The trigger on dbos.workflow_status runs AFTER INSERT [FOR](https://www.emergentmind.com/topics/feasible-operating-region-for) EACH ROW, and the helper rotate_working_aggregate() checks whether the working aggregate has reached $N_v$2 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 (Lockton et al., 11 Sep 2025).

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

$O(\log n + N_v)$9

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;</code>, and transmits the serialized matrix to the coordinator. The coordinator, designated rank 0, collects worker aggregates using <code>ProbeMsg/RecvMsg</code> and invokes a PL/pgSQL function <code>merge_into_global(mat)</code> that performs the same in-place <code>onesparse_matrix_eWiseAdd</code> logic on <code>dbos.global_matrices</code> (<a href="/papers/2509.09898" title="" rel="nofollow" data-turbo="false" class="assistant-link" x-data x-tooltip.raw="">Lockton et al., 11 Sep 2025</a>).</p> <p>The paper also describes an HTTP-based variant aligned with cloud-native deployment. Once local analytics are computed, they are stored in <code>dbos.analytics</code> 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 (<a href="/papers/2509.09898" title="" rel="nofollow" data-turbo="false" class="assistant-link" x-data x-tooltip.raw="">Lockton et al., 11 Sep 2025</a>).</p> <h2 class='paper-heading' id='throughput-scalability-and-measured-overheads'>5. Throughput, scalability, and measured overheads</h2> <p>On a single worker, the reported throughput figures separate matrix processing from end-to-end insert performance. Base matrix build is given as approximately$$N_v$3 requests/sec, local aggregation with in-place eWiseAdd as approximately $N_v$4 requests/sec, and analytics based on reduce operations as approximately $N_v$$5 requests/sec. The end-to-end sustained insertion rate into <code>dbos.workflow_status</code> is limited by DBOS’s per-insert transactional overhead, but base-matrix construction itself, bypassing full inserts, reaches$$N_v$6 RPS (Lockton et al., 11 Sep 2025).

At the DBOS service level, the paper states that the sustained web request rate for a single DBOS instance was $N_v$7, 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 (Lockton et al., 11 Sep 2025).

For multi-node execution, the implementations diverge. The Python-GraphBLAS workflow scales linearly up to 64 workers, with coordinator throughput approximately $N_v$8 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 $N_v$9 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 $O(\log n + N_v)$0 RPS each (Lockton et al., 11 Sep 2025).

The overhead comparison is explicit. PostgreSQL adds approximately $O(\log n + N_v)$1–$O(\log n + N_v)$2 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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 (Lockton et al., 11 Sep 2025).

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 $O(\log n + N_v)$3 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 (Lockton et al., 11 Sep 2025).

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 $O(\log n + N_v)$4 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 $O(\log n + N_v)$5 throughput overhead versus pure Python, scales to dozens of nodes with negligible incremental resource demands (Lockton et al., 11 Sep 2025).