Re: Making jsonb_agg() faster

Merlin Moncure <mmoncure@gmail.com>

From: Merlin Moncure <mmoncure@gmail.com>

To: Tom Lane <tgl@sss.pgh.pa.us>

Cc: pgsql-hackers@lists.postgresql.org

Date: 2025-07-22T21:02:27Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix jsonb_object_agg crash after eliminating null-valued pairs.
- ef5f559b95e2 19 (unreleased) landed
Micro-optimize datatype conversions in datum_to_jsonb_internal.
- 005a2907dc30 19 (unreleased) landed
Remove fundamentally-redundant processing in jsonb_agg() et al.
- b61aa76e4585 19 (unreleased) landed
Revise APIs for pushJsonbValue() and associated routines.
- 0986e95161ce 19 (unreleased) landed
Add a macro for the declared typlen of type timetz.
- 3628af42107d 19 (unreleased) landed

On Tue, Jul 22, 2025 at 11:43 AM Merlin Moncure <mmoncure@gmail.com> wrote:

> On Tue, Jul 22, 2025 at 10:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>>
>> Thoughts?
>>
>
> Really excited about this -- I'll do some testing.  Performance of
> serialization (generating json output from non json data) is the main
> reason I still recommend json...jsonb is better in most other cases, but
> serialization performance is extremely important.
>

Performance improvement is pretty dramatic, down from 300%+ to ~ 40% --
it's pretty reliable 40% on all output bound cases FWICT.

merlin

>