7

I wonder why there is so much fuss about JSON support in Postgres 9.3. What are advantages of JSON over User-defined types (UDTs)? What are pitfalls in using UDTs? Is access to tables with UDTs inefficient? Is ALTER TYPE ADD attribute slow? How are UDTs physically stored by Postgres?

Please, explain and give links to additional information.

rlib
  • 7,444
  • 3
  • 32
  • 40

4 Answers4

3
  • I think JSON is much more flexible than User-defined types, you can add whatever optional attributes you want, you can nest them, you can put them into lists;
  • JSON is very readable format;
  • JSON is standard object notation in many languages (Javascript, Python) so you can read data from table and use it;
  • You don't have to create new type anytime when you want to process data, you can create JSON, process it, then just forget about it;
Roman Pekar
  • 107,110
  • 28
  • 195
  • 197
3

As Roman Pekar mentioned in one of the previous answers, JSON support offer much more flexibility and it offers the possibility to kinda mimic a NoSQL behavior on a relational one.

Furthermore, it makes it easier in Client-Server applications to store JSON values sent from the client directly from the Database.

One can use 30% of the fields for a client of the application, 30% for another, and so on, not having to define multiple tables or tables with a large set of columns. Thus, one can store large chunks of heterogeneous information into one place.

Last but not least, JSON is a standard and it's supported by many of the big programming languages.

(We are currently using the feature in our project (and have been using it since in Beta); furthermore, this was the main reason we chose Postgres for our application, as we needed a big DB with mainly decoupled information. We tried using NoSQL databases but we needed too many tables to store the information in, and it was costly on "joins". On the other hand, it would have been hard to cope only with a relational DB, so instead of going half-relational half-nonrelational, we chose Postgres's JSON support.)

Raul Rene
  • 10,014
  • 9
  • 53
  • 75
  • 3
    "JSON is a standard and it's supported by many of the big programming languages": UDT can be presented by structures that are supported by "big programming languages". – rlib Sep 23 '13 at 13:50
  • 2
    "Thus, one can store large chunks of heterogeneous information into one place." - UDTs do the same. – rlib Sep 23 '13 at 13:52
  • 1
    "not having to define multiple tables or tables with a large set of columns" - ALTER command on UDTs if change is needed. – rlib Sep 23 '13 at 13:53
2

Less seriously it is a marketing little bit :). More seriously - internal JSON support is final stage of some years work on this topic. There are no large difference between internal types and UDT and lot of internal types (and functionality) starts as UDT or UDF. A move to upstream is relative hard process and concept (and API) is hardly tested and discussed. So internal implementation guarantees significantly higher quality and higher stability (less errors, more stable API) and support. A community says - "it is interesting feature for us, and we would to enhance and support". There are no other differences - (in performance or storage format).

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94
0

Some things about user defined types:

  1. They are fixed format (JSON is schemaless)
  2. You can make assumptions about them.
  3. You have more flexibility in addressing indexing concerns.
  4. A composite type (one form of UDT) can have a JSON attribute.

User defined types allow you to extend SQL quite a bit more reliably around these types than JSON does, but JSON gives you a lot more flexibility. Also, nested types are a lot more flexible in terms of support when done as composite types than as JSON objects in 9.3 (though this may change at some point). You cannot convert, in 9.3, a JSON object to a composite type if the JSON object is nested at all.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182