V
V
Victor2016-02-07 06:41:14
PostgreSQL
Victor, 2016-02-07 06:41:14

What are the advantages of the JSON(b) format in PostgreSQL?

I would like to hear from PostgreSQL experts, what are the significant advantages of working with the JSON(b) format, apart from the fact that the structure of JSON(b) records can be different?
Compared to a standard table divided into fields and the same indexes .
I know that JSON does not store information about the data type, plus this is a space saving?
We take PostgreSQL version >= 9.4 for consideration
1. For example, I need to work with a log table of 1-10 billion records.
How much space does JSON(b) save compared to a regular table? (specify as a percentage)
2. Is the performance of SELECT,INSERT,UPDATE,DELETE different or not?
If yes, please describe in detail how and why.
3.What are the pros and cons of storing in JSON(b) compared to a simple table?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
L
lega, 2016-02-07
@lega

How much space to save

- In json goes extra. space consumption for storing key names, structure and syntax, (numeric) values ​​in string representation.
+ Savings can be due to the fact that there is no reserve for lines of a fixed size.
+ Also, savings can be due to rare fields that are not needed in every row of the table.
Also, json can be compressed well, but whether psql supports it ...
The result depends on the type of data (and storage method).
In theory, it should be lower. in addition to highlighting a row in the table, you need to allocate space for this json + its validation.
+ You can store complex data structures
+ Allows you to make transparent migrations (project development without migrations)
- Requires more space than simple columns (numbers/dates)

S
sim3x, 2016-02-07
@sim3x

json you only need if your log structure is constantly changing
If you have a finite number of log types, then it is better to convert them to your standard before saving

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question