Hacker News Reader

Stuffing data into JSONB columns always makes me feel uncomfortable. Not necessarily for performance/efficiency reasons. You also loose the strong schema that SQL gives you, you don't get to use constraints. You might as well use Mongo, no?

How can you be sure that the data stuffed into JSONB fits a particular schema, and that future changes are backwards compatible with rows added long ago?

View on HN

JSONB can have constraints. I think with an extension you can do full JSON Schema validation, too.

Yes, I watched a video[0] about using CHECKs and pg_jsonschema to do this in the past. However this only checks for conformance at insert/update time. As time goes on you'll inevitably need to evolve your structures but you won't be able to have any assurances to whether past data conforms to the new structure.

The way this article suggests using JSONB would also be problematic because you're stuffing potentially varying structures into one column. You could technically create one massive jsonschema that uses oneOf to validate that the event conforms to one of your structures, but I think it would be horrible for performance.

[0]: https://www.youtube.com/watch?v=F6X60ln2VNc

Reply on news.ycombinator.com