How is JSON stored in PG ?

From: David Gauthier <davegauthierpg(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: How is JSON stored in PG ?
Date: 2021-11-16 15:54:24
Message-ID: CAMBRECD14zp50Ex3E8L2MtZPHGAsSVX3zDo57=e_ov7fM8ONSA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi:

psql (11.5, server 11.3) on linux

I'm considering using JSON as a datatype for something I'm working on. The
reasons are...

1) the 'metadata' (if you want to call it that) in JSON is very flexible.
Doesn't require an alter table or anything like that to change.
2) The customers for this data is probably going to be python code. IOW,
they can sort things out in their code after reading the whole JSON
file/record.
3) Nice array of built-in functions for this datatype.
4) There does appear to be the ability to formulate query predicates on the
JSON content (just in case)

But #4 has me a bit worried. I'm wondering how PG stores this data given
that its content can be specified in a query predicate. Does PG just store
the content in traditional PG tables ? If so, I can do that myself. If
not, is there a penalty to be paid at query time if PG needs to get the
JSON data, then dismantle into a temp table (or something like that) to
query.

Pros and Cons for using JSON ?

Thanks for any help !

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joel Rabinovitch 2021-11-16 16:09:05 Performance question about using autosave=always and cleanupSavepoints=true
Previous Message Laurenz Albe 2021-11-16 15:42:46 Re: historical log of data records