0

At my project, I alrealdy have tables wich have columns with json(nested) data, but they're not JSON data type. For some json attributes, I have a column at the same table, so that I can have some performance while searching, ordering, etc.. Just to use like relational data. For better understanding, here is an example of what I actually have:

lb_reg_marcacao_especial

        Column        |            Type             | Modifiers
----------------------+-----------------------------+-----------
 id                   | integer                     | not null
 json                 | character varying           | not null
 nm_marcacao          | character varying           |
 dt_inclusao          | timestamp without time zone |
Indexes:
    "lb_reg_marcacao_especial_pkey" PRIMARY KEY, btree (id_reg)
    "lb_reg_marcacao_especial_nm_marcacao_key" UNIQUE CONSTRAINT, btree (nm_marcacao)

while json columns will have data like:

'{
  "dt_inclusao": "02/12/2013 11:05:27",
  "nm_user_inclusao": "Some name",
  "nested": {
    "nm_marcacao": "marc1",
    "dt_ultima_alteracao": ""
  },
  "nm_user_ultima_alteracao": "",
  "ds_marcacao": "marc1",
  "st_marcacao": true
}'

Some of these json data have ~100 keys and about 3 level of nesting. As you can see, data is replicated, since I have some keys in json and in table's columns (nm_marcacao and dt_inclusao).

So, I'm thinking about changing the json column data type to JSON and removing the other columns. What about you ?

user1538560
  • 429
  • 1
  • 6
  • 16
  • I don't think there will be a performance difference between `varchar` and `json` currently. This _will_ change however with the upcoming 9.4 which has a new JSON type which is much more efficient. –  May 08 '14 at 13:25
  • I agree, but now on, I pretend to search directly on json column, an not on normalized column, like I did before. Would be any difference now ? – user1538560 May 08 '14 at 13:29
  • Only the execution plan of the query you are using can tell you that. –  May 08 '14 at 13:34

1 Answers1

1

Switching to JSON instead of text has several benefits:

1) The data will be verified to confirm to actual JSON specs. Your server-side app might already be doing that, but type safety is always nice.

2) You have access to several JSON functions, such as getters of properties at arbitrary paths, conversion to records, and more:

http://www.postgresql.org/docs/9.3/static/functions-json.html http://clarkdave.net/2013/06/what-can-you-do-with-postgresql-and-json/

To do setters, I suggest a simple plv8 function that takes 3 inputs (json object, path-to-property, updated-value) and returns the updated json object back. That can be used directly in update statements.

3) You can actually index certain properties of the JSON already for performance:

How to create index on json field in Postgres 9.3

4) JSON 9.4 will introduce binary storage, along with index engine improvements will drastically increase speed:

http://obartunov.livejournal.com/177247.html

Here is a nice talk on the state of JSON in 9.3:

http://www.slideshare.net/amdunstan/93json-26647827

Here is a performance report of an earlier version (using the original hstore2 work) showing some performance relative to mongodb - this is by the jsonb author:

http://obartunov.livejournal.com/175235.html

and an updated benchmark from someone else:

https://plus.google.com/+ThomBrownUK/posts/1JizRBGPYBq

Community
  • 1
  • 1
qooleot
  • 409
  • 4
  • 9
  • "*such as getters/setters of properties*" - how exactly can you ***set*** a property based on a path? I don't think this is currently possible (without using a function implemented in JavaScript) –  May 08 '14 at 14:22
  • I understand, there are many benefits, that's why I'm thinking of changing it ... But still have to think about searching performance, I haven't found any bench about it yet .. – user1538560 May 08 '14 at 14:37
  • "But still have to think about searching performance" searching performance is more nuanced - if you're searching for a specific property's value at a specific depth, you can create a functional index for it. Are you searching for properties, values, both - at arbitrary depths? – qooleot May 08 '14 at 20:32
  • "how exactly can you set a property based on a path?" You will likely *never* be able to actually set a property without having to override the entire JSON column. Thats a part of postgres where the object is either written or not - there are no partial updates. With that said, write performance in anything but extreme circumstances is still very good. I'll update my answer above to detail setters. – qooleot May 08 '14 at 20:35