Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

I've been on a project that used postgres' json type as it's main data storage. I wouldn't go down that route again, even now that jsonb is available. My thoughts:

It doesn't really make sense to say that a query won't be relational. Few result sets are ever relational - if they include a JOIN or really any grouping, sorting etc, they are a non-relational projection of your data. That's the whole point of a relational database - relational storage and ad hoc non-relational views of it.

If you don't need to migrate your data when you change the schema (ie: nothing has gone live yet), just truncate the entire database every time you change schema. No need to write migrations and you get to iterate towards a good relational schema.

If you do need to migrate your data when you change the schema, do not under any circumstances choose JSON types. Existing migration tools like Flyway, Alembic, etc only support migrations between relations and you will have to write your own, custom, half-baked and buggy schema migrations. Migrating even relational data is hard and migrating non-relational data is much harder. You won't have the support of postgres' fantastic transactional DDL so it will be hard to do migrations without downtime. Probably the only approach that will work is to migrate data as you touch it.

Even if you decide to go for JSON, make sure you understand the limitations of the JSON type in 9.3 and lower. Equality isn't defined on JSON (you have to coerce to text first) so lots of basic SQL features won't work - for example UNION or EXCEPT. 9.4 has JSONB which is a lot better.



I'm also using the Postgres JSON type in 9.3, but my experience is different, perhaps because I come from the ZODB world and so am not under any illusions that it is a panacea!

I've ended up with a very generalised schema, all content is in JSON. I've not had to change the Postgres schema for over a year and it's easy to upgrade schemas inline while fetching from the app. We did write our own migration tools, but they're only a couple of pages of code.

I don't understand your complaint about lack of transactional DDL when using JSON. It's still there, though probably not as important as most of your upgrades will only touch data, giving you transactional upgrades (should you desire them) in any database.

DB queries of JSON data are definitely slow in 9.3 as the values need to be parsed every query, but the lack of JSON equality has not been a problem. You simply use the double chevron text extraction operators rather than the the json extraction operators in your query, col->foo->>bar rather than col->foo->bar. From there you can cast to any type and join to other tables.

For us, the big advantage of using a generalized schema has been simple support for recording versions of data. With over 30 different content types doing this with a traditional relational schema would have resulted in maybe 90 tables. Our generic schema has six.

I'm really looking forward to 9.4 as it will make ad-hoc queries across JSONB really fast using GIN indexes. I'd love to see the option of creating check / foreign-key constraints on JSON data in the future. Unfortunately it looks like the proposal for this is stalled as the implementation turned out to be rather tricky. http://blog.2ndquadrant.com/postgresql-9-3-development-array...


Thank you for clarifying the relational aspect. This is what I meant to say but I couldn't find a concise way to do so.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: