Unlocking PostgreSQL 19 Performance: How New Aggregate Support Functions Eliminate Redundancy and Speed Up Queries

Main Facts: The Evolution of PostgreSQL Optimization

Database optimization has always been a delicate balancing act between high-level declarative querying and low-level computational execution. For years, one of PostgreSQL’s persistent architectural quirks has been the relative inefficiency of its built-in aggregate functions when processing variable-length or high-precision data types. Scenarios involving massive data streams where aggregation acts as a preparatory filtering step—producing a nearly identical number of groups as input rows—routinely suffered performance penalties.

The issue is especially pronounced with types like SUM(numeric). Because built-in aggregates must handle values in their most generalized form to preserve semantic safety, they cannot make assumptions about constrained real-world data schemas. In enterprise environments such as ERP systems (including Microsoft Dynamics or NetSuite), monetary columns of type numeric are almost universally declared with a fixed scale. Yet, the core engine treats them with full flexibility, costing valuable CPU cycles.

This computational bottleneck is now directly addressed in PostgreSQL 19 (currently in beta). Developer David Rowley has introduced a crucial new extension hook to the core engine: SupportRequestSimplifyAggref (implemented via commit 42473b3b31). This mechanism seamlessly passes custom aggregate-transformation logic to the query planner through the pre-existing planner support functions (prosupport) framework.

While the prosupport mechanism has existed since PostgreSQL 12, its extension to aggregates represents a paradigm shift. Although core PostgreSQL utilizes this new capability modestly—replacing COUNT(1) and COUNT(col) over NOT NULL columns with COUNT(*)—it unlocks vast possibilities for extensions. Developers can now intercept aggregate expressions at planning time, rewrite their syntax tree nodes, strip away redundancies, and optimize execution profiles without altering the underlying application queries or patching the database core.

Chronology: From Fork Limitations to Core Inclusion

To understand the significance of PostgreSQL 19’s new aggregate support hooks, it helps to examine how the database management system has historically handled query-time transformations.

• Pre-PostgreSQL 12 Era: Query optimization logic was heavily hardcoded into the core planner. Extensions had virtually no way to inject custom transformation rules into how expressions were evaluated or restructured during the planning phase.
• PostgreSQL 12 Release: The introduction of prosupport functions provided a vital breakthrough, allowing extensions to attach C-based helper routines to functions and operators. However, these capabilities were limited to standard functions and operators, leaving aggregate expressions (Aggrefs) out of reach.
• The Fork Era: Advanced query optimizations tuned to specific execution constraints were previously restricted to proprietary or customized PostgreSQL forks, keeping sophisticated aggregate refactoring out of mainstream reach.
• PostgreSQL 19 Development Cycle: David Rowley bridged this gap by introducing the SupportRequestSimplifyAggref hook. This update empowers the planner to consult support functions specifically for aggregate nodes, opening the door for dynamic query cleanup and custom mathematical shortcuts.
• Current Beta Status: PostgreSQL 19 is currently in beta. While the core engine introduces foundational support, the ecosystem is already experimenting with extensions. However, a minor friction point remains: native Data Definition Language (DDL) syntax to cleanly attach support functions to built-in aggregates (CREATE AGGREGATE ... SUPPORT) has not yet landed in the core codebase, requiring developers to leverage system catalog workarounds while a patch is actively debated on pgsql-hackers.

Supporting Data: Benchmarking the Elimination of Redundant Sorts

To evaluate the real-world impact of this new architecture, consider a common symptom of automated query generation: redundant sorting.

In complex software deployments where SQL is dynamically generated by ORMs or reporting frameworks, developers occasionally encounter queries featuring unnecessary ordering clauses inside aggregate functions, such as:

SELECT sum(x ORDER BY x) FROM table;

Mathematically, the order of values has absolutely no bearing on a summation. Yet, legacy versions of PostgreSQL—bound by strict semantic rules—would dutifully spin up a Sort node in the execution plan, arranging the dataset before passing it to the aggregate step.

Benchmark Analysis

To measure the performance penalty of this behavior, database engineers executed comparative benchmarks against a table containing 10 million rows of randomized numeric data:

-- Query WITH the redundant sort
SELECT sum(x ORDER BY x) FROM
  (SELECT (random()*1E6)::numeric(16,2) AS x
     FROM generate_series(1,1E7))
OFFSET 1E7;
-- Execution Time: 5716.916 ms (approx. 5.7 seconds)

-- Query WITHOUT the sort
SELECT sum(x) FROM
  (SELECT (random()*1E6)::numeric(16,2) AS x
     FROM generate_series(1,1E7))
OFFSET 1E7;
-- Execution Time: 3664.739 ms (approx. 3.6 seconds)

The data reveals that roughly one-third of the total query time is wasted purely on an unnecessary sorting operation. By writing a custom prosupport function that intercepts SUM() expressions and strips away the useless ORDER BY clause, the execution plan drops the Sort node entirely. Because this transformation occurs once at planning time, its overhead is negligible, and the optimized plan can be safely cached and reused across subsequent executions.

Implementing the Support Function

A support function in PostgreSQL is written in C and adheres to a specific SQL signature:

supportfn(internal) RETURNS internal;

When the planner encounters an aggregate node, it passes a pointer to a SupportRequestSimplifyAggref request structure. The support function inspects the node, verifies that it meets safe optimization criteria (such as confirming the data type is an integer or exact decimal and ensuring no conflicting DISTINCT clauses are present), copies the node, strips the ORDER BY array, and returns the modified aggregate node.

Datum
sum_agg_support(PG_FUNCTION_ARGS)

    Node       *rawreq = (Node *) PG_GETARG_POINTER(0);

    if (IsA(rawreq, SupportRequestSimplifyAggref))
    
        SupportRequestSimplifyAggref *req;
        Aggref     *aggref;
        Aggref     *newagg;
        ListCell   *lc;

        req = (SupportRequestSimplifyAggref *) rawreq;
        aggref = req->aggref;

        // Ensure we are working with a normal aggregate
        if (aggref->aggorder == NIL 
    PG_RETURN_POINTER(NULL);

Official Responses and Ecosystem Reactions

The introduction of SupportRequestSimplifyAggref has generated considerable enthusiasm within the PostgreSQL developer community, though it has also sparked technical debates regarding usability and syntax completeness.

The DDL Gap

While the underlying C infrastructure to support custom aggregate optimization is fully functional in PostgreSQL 19 core, community developers pointed out a notable administrative hurdle: there is currently no native DDL command (such as CREATE AGGREGATE ... SUPPORT) to link a support function to a built-in catalog aggregate like sum(numeric). Attempting to do so via standard SQL results in an error:

ALTER FUNCTION pg_catalog.sum(numeric) SUPPORT sum_agg_support;
ERROR:  "pg_catalog.sum" is an aggregate function

To bypass this limitation during the beta phase, extension maintainers have had to manually update system catalogs (pg_proc) and establish formal dependencies within pg_depend to ensure safe teardown and prevent dangling pointers:

UPDATE pg_catalog.pg_proc
   SET prosupport = 'sum_agg_support'::regproc
WHERE oid = 'pg_catalog.sum(numeric)'::regprocedure;

Ongoing Discussions on pgsql-hackers

Recognizing this friction, community contributors have proposed patches on the pgsql-hackers mailing list to formally integrate native support-function assignment into CREATE AGGREGATE and ALTER AGGREGATE. Discussions center on establishing robust dependency semantics so that database administrators can manage optimized aggregates cleanly without relying on low-level catalog modifications.

Core maintainers have welcomed the feedback, viewing the current limitation as an implementation stepping stone rather than a permanent design constraint.

Implications: The Future of Query Customization

The arrival of aggregate support hooks in PostgreSQL 19 marks a fundamental transformation in how developers can interact with the database engine. By bridging the gap between static core logic and extensible planning-time optimization, PostgreSQL is empowering developers to solve performance bottlenecks that were previously intractable without forking the database code.

Key Takeaways and Future Horizons:

1. Zero-Touch Application Optimization: Legacy applications and auto-generated ORM queries that produce clumsy, redundant SQL syntax can now be dynamically cleaned up at the planning stage, eliminating performance drains without requiring application-level code refactoring.
2. Advanced Numeric Tuning: Beyond stripping redundant sorts, advanced extensions (such as the experimental pg_numeric_agg_support repository on GitHub) demonstrate that developers can substitute highly specialized, ultra-fast versions of SUM() when input precision and scale are statically known.
3. Extensibility Beyond Core Limitations: Developers are no longer entirely constrained by the generic assumptions built into standard SQL aggregate functions. They can tailor database math to match the strict domain constraints of enterprise workloads, such as financial ledger tracking and high-frequency ERP operations.
4. Maturation of the Feature Set: As the PostgreSQL 19 development cycle finalizes and pending DDL patches land in core, the process of attaching custom support functions will transition from catalog manipulation to straightforward, production-ready SQL commands.

Ultimately, PostgreSQL 19 gives database administrators and extension authors a powerful new scalpel. By enabling intelligent, custom-tailored transformations of aggregate functions right at the planner level, PostgreSQL continues to solidify its reputation as one of the most extensible and high-performing relational databases in the enterprise landscape.