Subject: Re: Avoid detoast overhead when possible

From: Andy Fan <[email-masked]>
Date: Wed, 30 Oct 2024 07:59:16 +0800

[email-masked] writes:

Hello Nikita,

>> There's a view from the other angle - detoast just attributes that are needed
>> (partial detoast), with optimized storage mechanics for JSONb.
>
> Very glad to know that,  looking forward your design & patch!

This is interesting, do you mind to provide a high level design of
this. I'm not pushing to do this, but you asked me how I think about
your proposal[1], so I think a high level design is a must to for these 
question. A first set of questions includes:

1. How to optimize the JSONb storage?
2. How to be compatible with older version?
3. How to integreate with existing ExprExecutionEngine? looks your
proposal needs to know which "part" of jsonb fields are needed.
4. Is there any optimization if user want to get a full JSONB, like
SELECT jb_col FROM t?

[1]
https://www.postgresql.org/message-id/CAN-LCVO3GZAKVTKNwwcezoc%3D9Lq%3DkU2via-BM3MXVdOq4tD9RQ%40mail.gmail.com

-- 
Best Regards
Andy Fan

---

From: Nikita Malakhov <[email-masked]>
Date: Wed, 30 Oct 2024 14:22:42 +0300

Hi!

Sorry for the long delay, other tasks required my attention and this project
was postponed, but we have some recent requests from clients and this
work has to be revived.

1) In short the idea behind the Json improvements is to introduce iterators
for Json objects to extract these objects value by value, and modification
of in-memory and on-disk representations to hold partially-detoasted Json
objects (tree nodes with need-detoast state) for in-memory, and to store
key-value map in on-disk representation to extract only required values.

2) Backward compatibility would not be a problem because in on-disk
representation we could distinguish old version from new, there are enough
service bits for that. But servers not updated with this patch could not
parse new data, of course.

3) Yes, it definitely has to. These changes are very complex and invasive,
that's the reason I haven't finished the patch.

4) It doesn't seem worthy at first glance. Do you have any ideas on this?

Thank you for your interest!

-- 
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/

---

From: Andy Fan <[email-masked]>
Date: Thu, 31 Oct 2024 09:11:14 +0800

Nikita Malakhov <[email-masked]> writes:

> for Json objects to extract these objects value by value,

OK here.

> and modification

I have troubles to follow here. Could you explain this more for the
below example, What will be done differently from current in the below
example: 

CREATE TABLE t(jbcol jsonb);

INSERT INTO t values ('{"a": 1, "b": 2}'); // new storage format?

SELECT jbcol->'a',  jbcol->'b' FROM t;   // How does ExprInterp.c work
with this? 

> representation we could distinguish old version from new.

OK, so you indeed change the storage format, if so, How is the new
format looks like? It would be an important factor to discuss. 

> there are enough service bits for that.

OK, good news.

> But servers not updated with this patch could not parse new data, of
> course.

This looks too for me. 

> that's the reason I haven't finished the patch.

This is the area I am really worried about. I think you can have some
high-level design document for review first. It would be great that some
more experienced people could have a look at, however I know it is hard
becuase of their bindwidth. You need that document anyway.

Without the details, other people is hard to understand the idea in
your mind. 

> 4) It doesn't seem worthy at first glance. Do you have any ideas on

I think the more important ones are (a) what the new storage looks like,
(b) how does it works with the ExprExecutionEnginner. Without that
infromation, I think it is too soon to talk about this.

-- 
Best Regards
Andy Fan