When should you store serialized objects in the database? (2010) • Hacker News Board

Re: When should you store serialized objects in the database? (2010)

#21

Post by tigershark » Wed, Jun 01, 2016, 3:06 PM UTC

exabrial wrote: post #9

Bad ideas from 5, 10, er 20, er 30 years ago are stil bad ideas. I know the HN police will cite me for no citation, so I'd say it comes with experience. The law changed at one point, and we were legally bound to be able to locate a customers record by a piece of data in a blob. The only way to fix the problem was to dump the massive (1tb+) table and reinsert them into a real schema. The engineering effort to do this…

If you use an hammer to kill a mosquito obviously it's a bad idea. The hammer is useful to put a nail in the wall. In my current job I introduced a configuration store based on serialization. Obviously, instead of storing everything in a blob, I created a table with some generic string id columns and a generic key that is an object serialized in JSON and a value that is the configuration object serialized in JSON. In this way I can have the best of both worlds, a generic configuration store that is also indexed on the string ids and searchable on the generic key.

Re: When should you store serialized objects in the database? (2010)

#22

Post by cakes » Wed, Jun 01, 2016, 3:09 PM UTC

mattnewton wrote: post #19

ams6110 wrote: post #14

Earlier quoted context omitted.

it's always preferable to store a json encoded representation That's just the format du jour. Ten years ago it would have been "store an XML encoded representation" and ten years before that it would have been some delimited representation. Tomorrow it may be yaml or something even more hideous. Blobs in the DB can make sense in some situations but they should really be blobs: images, or other binary/raw data. But be…

But there are database tools emerging to deal with JSON, like Postgres (and Mongodb.) I haven't used Mongo in production, but postgres's json support is fantastic and materially better than anything I have seen with XML. Sometimes you really do need to store nearly schemaless data and operate on it for some time.

As reference there are/were tools in something like MSSQL for dealing with XML.

Re: When should you store serialized objects in the database? (2010)

#23

Post by forinti » Wed, Jun 01, 2016, 3:09 PM UTC

In my experience, databases outlast the applications built on top of them, so it makes no sense to cut corners on the data modelling.

Except, of course, if the data only exists to support the application (some sort of buffer, cache, or session storage).

Re: When should you store serialized objects in the database? (2010)

#24

Post by distances » Wed, Jun 01, 2016, 3:09 PM UTC

bvinc wrote: post #16

TheSoftwareGuy wrote: post #6

For anybody using sqlite, they have good documentation about this very question: https://www.sqlite.org/intern-v-extern-blob.html

Is this really the same thing? This page compares storing blobs in sqlite vs in a separate file. I think a better sqlite page about the concept of serializing things in your database is the fact that sqlite has json support. https://www.sqlite.org/json1.html

That's with a loadable JSON1 extension, which one won't find e.g. in Android.

Though checking now for this, someone has packaged a later version of SQLite with this extension [1]. I wonder if there is any possible performance advantage when using a system provided SQLite vs. one installed with the application?

[1] https://github.com/requery/sqlite-android

Re: When should you store serialized objects in the database? (2010)

#25

Post by blowski » Wed, Jun 01, 2016, 3:12 PM UTC

I was working on a massive CRUD project - hundreds of end-user customisable textarea fields. Despite this sounding like a project perfect for a NoSQL database, the in-house team that would be maintaining it were MySQL experts, and didn't want to support MongoDB or anything like that.

So, yep, we stored everything as serialized objects in the database. We had a separate table for 'change events', and whenever someone changed the contents of one of the textareas we stored it in that table. A worker would eventually update the the serialized object, but in the meantime, we would load the serialized object and apply all of the changes that had happened since it was last updated. Basically, an Event Sourcing pattern.

So it was either that, or the EAV route, or the 'end user altering the database' route. The latter two options sounded even worse. Our solution worked out pretty well. Admittedly, it only had a few hundred concurrent users, and even the biggest document was never more than 100K.

So, it can work, but YMMV.

Re: When should you store serialized objects in the database? (2010)

#26

Post by distances » Wed, Jun 01, 2016, 3:16 PM UTC

distances wrote: post #24

bvinc wrote: post #16

Earlier quoted context omitted.

Is this really the same thing? This page compares storing blobs in sqlite vs in a separate file. I think a better sqlite page about the concept of serializing things in your database is the fact that sqlite has json support. https://www.sqlite.org/json1.html

That's with a loadable JSON1 extension, which one won't find e.g. in Android. Though checking now for this, someone has packaged a later version of SQLite with this extension [1]. I wonder if there is any possible performance advantage when using a system provided SQLite vs. one installed with the application? [1] https://github.com/requery/sqlite-android

Note to self: should have read the whole project description, there's a performance chapter answering this exact question.

Re: When should you store serialized objects in the database? (2010)

#27

Post by bvinc » Wed, Jun 01, 2016, 3:17 PM UTC

What about this reason? What if your program is pretty much entirely used from a JSON REST service? What if these JSON objects need to also be sent between machines? What if they need to be exportable to files sometimes?

Now imagine the same program also has an internal database where these JSON objects can be imported and used. Does it make sense that, when actually in use, these objects are relational and split between 10 complicated tables? Why should someone bother writing complex import/export conversion functions, maintaining them in the future, and having worse performance. Wouldn't it be much simpler, maintainable, and faster to just plop the JSON in the database?

Re: When should you store serialized objects in the database? (2010)

#28

Post by andy_ppp » Wed, Jun 01, 2016, 3:18 PM UTC

Zikes wrote: post #15

PostgreSQL and jsonb: https://www.postgresql.org/docs/current/static/datatype-json...

Yes, maybe use a database that allows you the best of both worlds, a serialised blob that happens to be queryable and generally really high performance.

Re: When should you store serialized objects in the database? (2010)

#29

Post by mattmanser » Wed, Jun 01, 2016, 3:21 PM UTC

tigershark wrote: post #21

exabrial wrote: post #9

Bad ideas from 5, 10, er 20, er 30 years ago are stil bad ideas. I know the HN police will cite me for no citation, so I'd say it comes with experience. The law changed at one point, and we were legally bound to be able to locate a customers record by a piece of data in a blob. The only way to fix the problem was to dump the massive (1tb+) table and reinsert them into a real schema. The engineering effort to do this…

If you use an hammer to kill a mosquito obviously it's a bad idea. The hammer is useful to put a nail in the wall. In my current job I introduced a configuration store based on serialization. Obviously, instead of storing everything in a blob, I created a table with some generic string id columns and a generic key that is an object serialized in JSON and a value that is the configuration object serialized in JSON. In…

The article is not talking about config files, which is one of the few valid reasons to do this, as with a config file you're almost always going to just want the whole thing once at initialization.

And even then, only if there's loads of config values. If you've only got 5 or 10, that solution is bad.

The article is implicitly talking about business objects.

Re: When should you store serialized objects in the database? (2010)

#30

Post by joesmo » Wed, Jun 01, 2016, 3:38 PM UTC

Proper databases have JSON or other serialized field types. Even mysql 5.7 has some support for this. There is no reason you should hit a limit on one of your tables at a few hundred thousand records because you're an idiot and stored a ton of serialized data in mysql. It happens all the time though.