4 ms·

IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these

IMHO Blobs usually turn into a mess though. Some VP will ask "what weekday do users comment the most". With blobs this is drama. You end up parsing all these blobs and creating relational tables anyway. Sure you could do metrics separately -- but good luck predicting what stats you will want in the future. Need an admin portal? With blobs you are coding all these custom admin pages. With mysql, I love when new people join and they can see and play with actual tables - slap in a good sql viewer and BAM you now have an admin portal. VP wants stats? give them mysql access and slack any sql they want: done .

willvarfar 5y ago
I often put json blobs into MySQL tables. Modern MySQL has support for accessing the fields in the json from normal sql. You can also create computed columns that deref the json, and even index it. Other rdbms often have even better json support.
- CRConrad 5y ago
  Those don't sound like "blobs" to me -- that stands for "Binary Large OBject"; JSON is text, not binary, isn't it?
jeremyjh 5y ago
I haven't used mysql this way but we do lots of reporting on jsonb data using the provided json functions of postgres. It is very easy to extract a (nested) key, or extract a set of key/values to a table value that you join to just as if it were a separate table.
kroolik 5y ago
> Some VP will ask "what weekday do users comment the most". This is why people invented read models. Because the data model that works for the app and makes it easy to work with there might not necessarily work for analytics. Generate analytics data from you app model so that both world can move independently. > Need an admin portal? With blobs you are coding all these custom admin pages. Admin portal with direct access to database columns? No thanks! IMHO, admin portal should hook into you business logic so that all the constraints are validated. Unless your app is just a CRUD.
- makapuf 5y ago
  or logic is in db, with validation triggers and constraints constraints, but that is not always the case.
  - out_of_protocol 5y ago
    Nah, database can and should check data consistency but not the business logic (otherwise you whole app is just sql triggers and stuff like that). Also, business logic tend to change over time, but existing items do not
williamdclt 5y ago
jsonb isn't a blob, JSON attributes are queryable and indexable like any column (in Postgres at least). And as somebody else says, read models exist for analytics use-cases. I wouldn't (at some scale) use the same DB for analytics as for production use anyway, it would require crazy indexes on the production DB and you'd have to take analytics into account for any schema change. Not to mention that anyway, quite often the prod DB schema requires domain knowledge to query and interpret correctly: read models can be built to abstract this domain knowledge.
nicoburns 5y ago
With Postgres’s JSONB type you can query and index the unstructured JSON data just like regular columns. You generally want to avoid storing arrays of data in JSONB as much as possible, but unstructured objects are just as easy to query as regular columns with only a small performance hit.

willvarfar 5y ago

I often put json blobs into MySQL tables. Modern MySQL has support for accessing the fields in the json from normal sql. You can also create computed columns that deref the json, and even index it. Other rdbms often have even better json support.

CRConrad 5y ago
Those don't sound like "blobs" to me -- that stands for "Binary Large OBject"; JSON is text, not binary, isn't it?

jeremyjh 5y ago

I haven't used mysql this way but we do lots of reporting on jsonb data using the provided json functions of postgres. It is very easy to extract a (nested) key, or extract a set of key/values to a table value that you join to just as if it were a separate table.

kroolik 5y ago

> Some VP will ask "what weekday do users comment the most". This is why people invented read models. Because the data model that works for the app and makes it easy to work with there might not necessarily work for analytics. Generate analytics data from you app model so that both world can move independently. > Need an admin portal? With blobs you are coding all these custom admin pages. Admin portal with direct access to database columns? No thanks! IMHO, admin portal should hook into you business logic so that all the constraints are validated. Unless your app is just a CRUD.

makapuf 5y ago
or logic is in db, with validation triggers and constraints constraints, but that is not always the case.
- out_of_protocol 5y ago
  Nah, database can and should check data consistency but not the business logic (otherwise you whole app is just sql triggers and stuff like that). Also, business logic tend to change over time, but existing items do not

williamdclt 5y ago

jsonb isn't a blob, JSON attributes are queryable and indexable like any column (in Postgres at least). And as somebody else says, read models exist for analytics use-cases. I wouldn't (at some scale) use the same DB for analytics as for production use anyway, it would require crazy indexes on the production DB and you'd have to take analytics into account for any schema change. Not to mention that anyway, quite often the prod DB schema requires domain knowledge to query and interpret correctly: read models can be built to abstract this domain knowledge.

nicoburns 5y ago

With Postgres’s JSONB type you can query and index the unstructured JSON data just like regular columns. You generally want to avoid storing arrays of data in JSONB as much as possible, but unstructured objects are just as easy to query as regular columns with only a small performance hit.