Re: Making jsonb_agg() faster

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

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

To: jian he <jian.universality@gmail.com>

Cc: pgsql-hackers@lists.postgresql.org

Date: 2025-08-22T19:11:34Z

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

Attachments

v2-0001-Revise-APIs-for-pushJsonbValue-and-associated-rou.patch (text/x-diff) patch v2-0001
v2-0002-Remove-fundamentally-redundant-processing-in-json.patch (text/x-diff) patch v2-0002
v2-0003-Micro-optimize-datatype-conversions-in-datum_to_j.patch (text/x-diff) patch v2-0003

I realized that my patch-of-record in this thread is out of date
in the wake of 2a600a93c, because it had some code catering to the
possibility that timestamp[tz] is pass-by-reference.  While it
still compiled and worked, we don't want to commit it like that.

v2-0001 takes care of that, and also adopts your suggestion in [1]
about not using two calls of pushJsonbValueScalar where one would do.
I also did a bit more micro-optimization in appendKey, appendValue,
appendElement to avoid redundant copying, because perf testing showed
that appendElement is still a hot-spot for jsonb_agg.  Patches 0002
and 0003 are unchanged.

I did not adopt your changes in [2], because they didn't really
seem like improvements to me.

			regards, tom lane

[1] https://www.postgresql.org/message-id/CACJufxGFzbRDnyG-%3DWZ3RgkVUwOZexUobGDrA3n5bj8MxrAZvQ%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CACJufxGHLYtvOAB7uA59C9Lz%2BrDPta5Egiii-B5opsZbw6eDDQ%40mail.gmail.com