Are "column names" optimized in PostgreSQL JSONB jsonb_agg(*)?

Question

Using this SELECT:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t;

If some_table had 1000 rows, are the "column names", which are repeated for each row in "content", sent over the network 1000 times? Or is that somehow optimized away? If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.

• eagle275: Depends - usually the json-object carries only the data. Except its from serialization ... But if the column names are contained .. the chances are high that it's every time the case

Answer 1

It is not optimized. Each column name, as a JSON key, is sent in full, multiple times. You could try connecting over SSL and turning on compression, but for security reasons that is disabled by default in most systems, and is getting increasingly difficult to turn on.

If the column names are really reapeated once per row, we would spend more bandwith for them, then for the values.

If that is the highest cost you have to pay for gratuitous use of JSON, then consider yourself lucky.

• Sebastien Diot: It only looks "gratutious" because I haven't explained the motivation, as it's not really relevant to this question. Still, you made me laugh. :)