GTM Galaxy

Article

Scaling Beyond DLRS: Asynchronous Account Rollups with PostgreSQL Recursive CTEs

Claudine Baumbach / / Pacific

Every RevOps lead eventually hits a ceiling with account hierarchies. It usually starts when a global parent requires a roll-up of Total ARR or seat counts across four levels of subsidiaries. You install a tool like Declarative Lookup Rollup Summaries (DLRS) or write a custom Apex trigger, and it works—until it doesn’t.

The failure usually happens during a high-stakes moment: a bulk contract update on fifty child records or a data enrichment refresh. Suddenly, the CRM freezes. Integration logs fill with UNABLE_TO_LOCK_ROW errors and Apex CPU time limit exceeded exceptions. The system is trying to traverse a deep hierarchy and perform heavy math in real-time while a dozen other triggers compete for the same record locks.

Why Native Rollups Fail

Salesforce was not designed to be a recursive calculation engine. Native Roll-Up Summary fields are performant but restrictive: they require Master-Detail relationships and are capped at 40 fields per object. Most GTM teams use standard Lookups for account hierarchies, forcing them into the world of synchronous triggers.

Once you are in trigger territory, you are bound by a 10-second limit for synchronous Apex CPU time. If your account tree is deep or your transaction volume is high, the overhead of traversing that tree for every record save will eventually trip this governor limit. The result is a brittle system that blocks sales reps from saving records during peak periods.

To build a resilient revenue system, you have to move the heavy lifting out of the CRM and into an asynchronous PostgreSQL pipeline.

The Asynchronous Architecture

The strategy is simple: let the CRM handle the data entry and the database handle the math. This requires a three-stage pipeline:

  1. Ingestion: Mirror Account and Opportunity data to a PostgreSQL instance (via Fivetran, Airbyte, or a simple webhook listener).
  2. Calculation: Use Recursive Common Table Expressions (CTEs) to traverse the hierarchy and aggregate metrics.
  3. Writeback: Push the calculated totals back to the CRM using the Bulk API.

By offloading this to a database, you eliminate row-locking contention. If a massive enterprise hierarchy takes twelve seconds to calculate, no one gets an error message. The data simply updates in the background a few minutes later.

Traversing the Tree with Recursive CTEs

PostgreSQL handles hierarchical data (the "adjacency list" model) natively using WITH RECURSIVE. This allows you to walk from a parent account down through an infinite number of child tiers without writing complex loops.

Here is a robust query that sums ARR across a hierarchy while tracking the path to prevent infinite loops from circular references—a common data quality issue in large CRMs.

WITH RECURSIVE account_hierarchy AS (
    -- Anchor: Identify the root parents (accounts without parents)
    SELECT 
        id, 
        parent_id, 
        arr_local, 
        id AS root_id,
        ARRAY[id] AS path_tracked
    FROM accounts
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive step: Find children and associate them with their root_id
    SELECT 
        child.id, 
        child.parent_id, 
        child.arr_local, 
        parent.root_id,
        path_tracked || child.id
    FROM accounts child
    JOIN account_hierarchy parent ON child.parent_id = parent.id
    -- Stop if we detect a circular reference
    WHERE NOT child.id = ANY(path_tracked)
)
SELECT 
    root_id as account_id,
    SUM(arr_local) as total_hierarchy_arr
FROM account_hierarchy
GROUP BY root_id;

The path_tracked array is the safety valve. If a user accidentally sets a child account as the parent of its own grandparent, the query won't crash the database; it simply terminates that branch once it sees a duplicate ID.

Solving the Recalculation Storm

Running a full hierarchy scan for every minor field update is inefficient. To prevent a "recalculation storm," implement a debouncing layer.

When a change arrives from the CRM, flag the affected account as "dirty" in a tracking table. Instead of calculating immediately, run a worker process every 5 to 15 minutes that identifies all unique root IDs associated with those dirty records.

If a bulk update modifies 500 child records under one Global Parent, your system doesn't run 500 traversals. It waits, sees they all belong to the same tree, and performs a single calculation once the bulk load finishes.

The Writeback Strategy

Once the totals are calculated, push them back to a dedicated field like Hierarchy_ARR_Total__c.

Avoid making individual REST API calls for each account. If you are updating thousands of parents, use the Salesforce Bulk API 2.0. Because this field is only updated by your database worker, you sidestep the typical locking conflicts associated with users or other triggers modifying the same records simultaneously.

Trade-offs and Constraints

This shift moves you from real-time to eventual consistency. There will be a delay—usually between 2 and 10 minutes—between a deal closing and the global parent’s total ARR updating. For most GTM teams, this is a minor operational adjustment compared to the alternative of a locked-up CRM during a Q4 rush.

There is also the "GTM Engineering" overhead. You are now maintaining a PostgreSQL instance and a sync worker. If your account structure is flat and your transaction volume is low, native formula fields or DLRS remain the better, simpler choices.

But for organizations dealing with Global 2000 hierarchies where one parent may have six levels of subsidiaries across twenty countries, native tools are a liability. Offloading the logic to SQL provides predictability, infinite depth traversal, and the ability to audit the calculation logic without digging through thousands of lines of Apex. That visibility is worth the infrastructure every time.

— C.B.