PostgreSQL JSON – Benefits of Storing Columns in JSON Instead of Traditional Tables

jsonpostgresql

Are there any benefits in using JSON(B) over traditional table structures?

Imagine having a table structure like this:

 create table table1 (
        t_id int,
        first_name varchar(20),
        last_name varchar(20),
        age int
    )

What if you stored the same columns inside a JSON(B) field like this:

{
    "first_name":"name",
    "last_name":"name",
    "age":2
}

and have a table like this:

create table table2 (
    t_id int,
    attribute jsonb
)

Correct me if I'm wrong, but since both variants are causing a row to be completely rewritten if there have been any updates or deletes on that row, then both variants are identical in that regard.

Best Answer

Large complex models can be stored (read and write) in database with low cost. You are avoiding expensive joins and other db operations.

Out of the box serialization/deserialization in your app, you can avoid ORM completely.

You get flexibility to change model withouth changing database schema.

On the other hand you are losing db mechanisms like normalization, referential integrity, data type checking ...

Querying json data is not impossible, but it's slower than querying relational data.

Use case scenario is data that has been exclusively manipulated by your app.

Related Question