JSON in Database Schema Design

Question

I'm working on a web application with constantly changing requirements. The relationships between entities in the database have mostly remained the same but the attributes of each entity type changes a lot.

I'm using Java, JPA, and Postgres. Postgres has a JSON type. Is it a good design to remove all non-key and foreign-key attributes and just put them in a JSON attribute?

FooTable Columns Old Design Long id String foo Boolean fooCheck Bar bar /**Foreign key ref*/

FooTable Columns New Design Long id JSON attributes Bar bar /** Foreign key ref */

The advantages I see of the new design are

1. Less overhead converting between POJOs and JSON
2. Don't really have to change schema?
3. Faster to test new things?

A disadvantage I see is migrating out of date JSON. Are there any potential pitfalls I'm missing of this approach?

Answer 1

Found some useful blog posts on the subject

• Unleash the Power of Storing JSON in Postgres
• When To Avoid JSONB In A PostgreSQL Schema

TL;DR It's acceptable to use JSON if the data is isolated. It can be hard to migrate JSON data. JOIN performance is really slow.

Answer 2

Putting the attributes into a JSON field is kind of sweeping the problem under the carpet. Conceptually you are still changing schema if you add, remove or change attributes - you still have to change code relying on these attributes and you have to update all queries which rely on these attributes. You just don't have the database to verify you are complying with the schema, so you have much higher risk of introducing bugs and you will suffer a heavy performance penalty when querying those attributes.

If it is a problem changing the schema of the database as often as you want, you should probably look into streamlining the deployment procedure with migrations or similar. Since you have "constantly changing requirements" it would be valuable to invest time in optimizing the deployment of changes to minimize friction.

That said, there are use cases where a JSON field is appropriate, for example if the data is opaque to the backend application and only used by third-party plug-ins, or if you are just logging data which is not read again by the application. But this does not seem to be the case here.