json documents sound great, especially initially, but end up being a maintenance nightmare
The problem with that - in my experience - is that migrating the structure for thousands (if not millions) of documents is way slower than running a DDL command (as it means reading each document, parsing it, modifying it and writing it back). Many DDL commands are just metadata update to the system catalogs so they are quite fast (e.g. adding a new column with a default value). With documents you wind up with millions of single row updates.
This can be mitigated by doing a "lazy" migration when a document with the old structure is first read. But that makes the code much more complicated.
I often create a "metadata" hstore field on a table and use it for random bits of data I don't want to create an actual field for yet. When I find that the application needs that bit of data and in a certain format, I'll move it into an actual field.
You can even write a small DSL syntax language to make it easier to use for developers, and perhaps an Obviously Reduntant Middleware that sits between them to convert their programming language objects to the DSL. Add some batch support, perhaps transactional locks (using mongo, we want to be webscale after all) and perhaps a small terminal based client and voila, no one should ever need to deal with petty integrity in the db again.
Postgres supports constraints on jsonb as well.
I would generally advocate for no write access outside of the app as well. Certainly for new developers. Get some tests on that batch job.
FWIW I think OP was referring to app code still, but code opting in to skipping app data validations. Rails for example makes this very easy to do, and there are tons and tons of people out there selling this as a performance improvement (this one trick speeds up bulk operations!!!!). There are times where it’s useful, but it’s a very sharp knife that people can easily misuse.
So yeah, anyway, have both db constraints and app validations.
But yeah, layering your safety nets is generally wise. I would include testing and code review in that as well.
Tool of choice is ecto which has excellent support for maintaining jsonb structure.
From quick glance, JSON Schema validation isn't built-in to Postgres, but there are third-party extensions, such as https://github.com/supabase/pg_jsonschema or https://github.com/gavinwahl/postgres-json-schema.
I'm more familiar with MySQL and MariaDB, which offer it built-in: https://dev.mysql.com/doc/refman/8.0/en/json-validation-func... and https://mariadb.com/kb/en/json_schema_valid/
I have yet to come across an actual use case for JSON stores in a production app with an established design. What the hell do you mean you have no idea what the data might hold?? Why are you storing unknown, undefined, undefinable things??? Or perhaps, there actually is a schema i.e. fields we rely on being present, but we were too lazy to define it formally?
Now we have to pick the stack. ELK, Loki + Graphana, Graylog or maybe just dump into MongoDB?
Healthcare. Clinical data is unstructured and constantly changing. Would you build out a table with 2000 columns that changed yearly? What about 5000?
Classified ads, the additional details for a Car are different than a Shirt, but both would be ads. And adding a nearly infinite number of fields or a flexible system in a set of schema or detail tables is significantly worse than unstructured JSON.
Another would be records from different, but related systems. Such as the transaction details for an order payment. Paypal data will be different from your CC processor, or debit transaction, but you can just store it as additional details for a given "payment" record.
Another still would be in healthcare, or any number of other systems where the structures will be different from one system to another depending on data interchange, where in the storage you don't care too much, only in the application layer will it make any necessary difference.
For example, a healthcare document that wasn't built or technically owned by the application storing it.
For example, a web text editor that serializes it's state as json.
Not json, but a web scraper storing html documents.
These have structure, it's only that the structure is built/maintained outside of the application storing it. You could of course transform it but I think it's a bit obvious where that might not be worth the cost/complexity.
That's what's really nice about SQL: you can perform these sorts of schema surgeries and still retain backwards-compatibility using VIEWs.
In other words I’m not really using anything in that JSONB field… at least right now.
We thought it was just a bunch of data documents. But it turned out that to actually use that data in an application it had to have predictability, we had to know certain fields were present and in a fixed format. You know: a schema.
NoSql stores still have schemas.
Well this is not a relational issue is it? It is a data normalization issue
Not saying the approach isn't suitable for some use cases. Just that I'd be really careful that this is one of those use cases next time.
Given the lack of statistics, the query planner loves going for a nested loop rather than hash or merge join where those would appropriate, leading to abysmal performance.
There is an thread[0] on the PostgreSQL mailing list to add at least some statistics on JSONB column, but this has gone nowhere since 2022.
[0]: https://www.postgresql.org/message-id/flat/c9c4bd20-996c-100...
It’s not perfect and requires a fair amount of effort to nail the prompt but when it works it works.
Postgres was awesome and handled this brilliantly, but the lack of schema and typing killed it. We just ended up fighting data quality the whole time. We couldn't assume that any document had all the required fields, or that they were in a format that made sense e.g. the Price column sometimes had currency symbols, and sometimes commas-and-periods in UK/US format and sometimes in Euro format - sorting by Price involved some complicated parsing of all the records first.
We moved back to relational tables.
I won't say I'd never do this again, but I would definitely not just throw JSON documents to a database and expect good things to happen.