What is the reasoning behind using JSONB in PostgreSQL?

[codeliner]

Question taken from the chat. Asked by @shochdoerfer

Do you guys query the JSON payload a lot? If not the JSON field would be more efficient as no conversion happens.

[codeliner]

/cc @prolic

[shochdoerfer]

Just to give a bit of context: The PostgreSQL guys recommend using JSON when using the field just for storage and JSONB when you want to issue queries against the data. The reasoning behind this is that the conversion from JSON to the binary format of JSONB and back costs time to compute.

In addition to that the JSON fields has 2 "advantages" over JSONB: The json format of the output does not change - e.g. the order of the object keys is maintained and since the JSON specification potentially allows multiple keys by the same name, the JSON field in PostgreSQL does the same.

To be fair I have no idea what "more efficient" really means but with millions of events that get read and parsed maybe the JSON field access might be a bit faster.

[prolic]

Jsonb can also be used with a custom persistance strategy, so I am okay with switching the default to json. Wanna submit a PR? Am 02.01.2017 16:26 schrieb "Stephan Hochdörfer" <[email protected]>:

…

Just to give a bit of context: The PostgreSQL guys recommend using JSON when using the field just for storage and JSONB when you want to issue queries against the data. The reasoning behind this is that the conversion from JSON to the binary format of JSONB and back costs time to compute. In addition to that the JSON fields has 2 "advantages" over JSONB: The json format of the output does not change - e.g. the order of the object keys is maintained and since the JSON specification potentially allows multiple keys by the same name, the JSON field in PostgreSQL does the same. To be fair I have no idea what "more efficient" really means but with millions of events that get read and parsed maybe the JSON field access might be a bit faster. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#27 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAYEvNE6KB6zl9bM-nEEtQxw1lTLzvoxks5rOLSYgaJpZM4LY4Li> .

[shochdoerfer]

Sure can tackle that one as well ;)

Migrate to JSON completely or make it configurable? What would you prefer?

[prolic]

Migrate. As I said it is part of the persistance strategy and you can always create a new one for userland, adding more indexes f.e. and so on. Can we keep jsonb for metadata? This one will be queried a lot, depending on the streaming configuration. Am 02.01.2017 16:45 schrieb "Stephan Hochdörfer" <[email protected]>: Sure can tackle that one as well ;) Migrate to JSON completely or make it configurable? What would you prefer? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#27 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAYEvOkaKVZpfrxG7AABi01iQNNnZKAsks5rOLkKgaJpZM4LY4Li> .

[shochdoerfer]

Sounds legit ;)