3

What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?

For example, consider a column like that used in the Postgres documentation:

CREATE TYPE inventory_item AS (
name            text,
supplier_id     integer,
price           numeric
);

What are the tradeoffs involved between this approach vs a jsonb column mirroring this structure?

For example, I suspect that the composite type won't require storing the key names for every record, whereas the jsonb type would require this.

  • 2
    IMHO don't use json for relational data. – McNets Jan 17 at 20:08
4

It's not really a question of performance,

  • Composite Types are strongly typed meaning you have to define their shape.
  • JSON is weakly typed.

Moreover, JSON has a use case, but with a composite type you almost never want to use them because it's almost always better normalizing them into their own relations. There is an exception to that though -- when you're going to create a whole library around a set of data then composite types can make everything and nicer. For example stdaddr in PostGIS is one such example. That type represents a physical address and multiple things can take that type (like the PostGIS geocoder).

So you can do

CREATE TABLE foo ( foo_id int, address postgis.stdaddr );

now you can get the geocoded information relatively easily and you only have to pass around one value rather than 15.

1

Using a composite type Pros

  • less disc space
  • faster select/insert/update

Using a composite type Cons

  • it's structured (ie not flexible). You can not make up fields on the fly.
  • longer coding time (especially when you use ORM)

There is one more option for storing structured data which is using another table :). You can test yourself by creating 2 tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.