Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

percona.com

Post reply on HN
Print view View original

41–50 of 70 posts

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

#41

Post by garethrees »

It makes sense to store serialized data structures in the database when these conditions apply:

1. There are no use cases that would require you to SELECT on the fields in the serialized data structures.

2. You anticipate that the data structures are going to change frequently during development, so that turning them into relations is going to involve a lot of schema migrations.

Basically you give up the possibility of being able to SELECT on some of the data in return for being able to change its format rapidly and cheaply.

I worked on a project recently where this was helpful -- when I designed the database schema I didn't know the details of many of the data structures that were going to have to be stored there. From the use cases I could deduce the set of fields that would need to be SELECTed on, but the other fields were ill-defined. By storing them as blobs (actually as JSONB fields, since this was PostgreSQL) I could safely defer the decision about how to design these parts of the database, without incurring lots of schema migrations along the way.

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

#42

Post by fauria »

"If the application really is schema-less and has a lot of optional parameters that do not appear in every record, serializing the data in one column can be a better idea than having many extra columns that are NULL."

Why not just use a document oriented database instead? Seems like a good use case for MongoDB for example: https://www.mongodb.com/compare/mongodb-mysql

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

#43

Post by orf »

ams6110 wrote: post #14
orf wrote: post #4

I think it's rarely a good idea to store blobs of any kind in the database. I've seen systems that store pretty large files as blobs (even base64 encoded ones once), then do 'select *' on the table and wonder why their query performance is so terrible. Use a filesystem, that's what it's for. For stuff like this then I would say it's always preferable to store a json encoded representation rather than a format like pi…

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…

> 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.

That's called progress. We can't store it in some future language, who wants to store it as XML, so let's use the best of what we have.

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

#44

Post by joe_the_user »

kbenson wrote: post #17
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…

There are so many ways to accomplish this other than what you did. I assume there was a reason for your choice, but it would have been so much easier if you could just create a separate table that matched the same primary key as the customer record, and contained a single other field, the data required to be searchable. Easy to join and search, easy to insert and update. > reinsert them into a real schema That sounds…

There are so many ways to accomplish this other than what you did. [...] That sounds sort of like you decided to fix a bunch of problems at the same time

A real database with real schema involves a series of guaranteed logical relations. Each violation of these logical relations tends to result in a different kind of problem (if you have a relation requiring pairing cars and drivers, you could have the problem of cars without drivers and drivers without cars. The potential logical problems multiply as the effective schema grows, without you have explicit schema or not).

So basically the move of using a real schema fixes a wide variety of real and potential problems compared to ad-hoc solutions. There are many ad-hoc solutions but since these aren't guaranteeing logical relations, such solutions tend to have holes the appear later.

So the gp may have been forced to use a real schema based on the multiplication of problems or they may have just done it because it was the right thing.

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

#45

Post by Friedduck »

We serialized some XML as a backup to the data we were extracting (and properly modeling), given that the vendor was prone to changing the schema without proper notification, and that there were some data elements we weren't using at that time.

We also built the necessary tools to extract/re-process records easily, and the architecture worked well for us. As our needs or the schema changed we could easily accommodate those changes without undue effort.

It doesn't directly address the question, and I'm not sure that I'd use the same solution if the volume were predicted to be significantly higher, but in our case it worked beautifully. (Happily our volume was predictably within a known range, for reasons I won't go into.)

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

#46

Post by vec »

garethrees wrote: post #41

It makes sense to store serialized data structures in the database when these conditions apply: 1. There are no use cases that would require you to SELECT on the fields in the serialized data structures. 2. You anticipate that the data structures are going to change frequently during development, so that turning them into relations is going to involve a lot of schema migrations. Basically you give up the possibility…

Be very careful with this logic. I'm in the middle of cleaning up a project where the original developer did this calculus and stored a lot of metadata as JSON strings in text fields. Now, three years out, we have new reporting requirements that need to be able to filter on some of that metadata.

Experience has taught me that it's next to impossible to know what types of queries will be needed for the entire lifetime of a project. If you absolutely must store data like this, please leave a clear migration path for moving some or all of it into well structured tables when (not if) it becomes necessary.

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

#47

Post by wesd »

fauria wrote: post #42

"If the application really is schema-less and has a lot of optional parameters that do not appear in every record, serializing the data in one column can be a better idea than having many extra columns that are NULL." Why not just use a document oriented database instead? Seems like a good use case for MongoDB for example: https://www.mongodb.com/compare/mongodb-mysql

Also the assumption is that you don't need to report on the data. If you need to report on the data then you might need to create index on those columns for performance which you can't do on a blob.

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

#48

Post by rwultsch »

I was on the DBA team at FB and I spent the better part of a year working on the deployment system for online schema change. It was a pain. Other companies have done quite a bit of work on this as well (Shift from Square, etc...).

Later on I joined Pinterest as their first MySQL DBA. They had copied the sharding system from FB, but instead of having a bunch of columns, they just stored a JSON blob. This saved them from learning how to perform schema change until I joined the company. This is a pretty incredible feature.

We have a new feature under development (which will be open sourced as part of Percona MySQL) which will allow column level compression with an optional predefined dictionary. During testing, this resulted in a 30% additional reduction in spaced consumed versus InnoDB page compression AND doubles our peak QPS at lower latency. This would not work well with many individuals columns, but kicks ass for JSON blobs.

http://www.slideshare.net/denshikarasu/less-is-more-novel-ap... (slides 37, 40, 41, 42)

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

#49

Post by PretzelFisch »

bvinc wrote: post #27

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 bet…

So, my question after storing xml in a database and using their xml features to build indexes is this. Are you looking for a database or a search engine? what will you gain from a database if you are not using it's features, over saving to disk and building a query index?

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

#50

Post by LyndsySimon »

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…

> Bad ideas from 5, 10, er 20, er 30 years ago are stil bad ideas.

I agree 100%.

I read that title and expected the post to begin with "Never. You should never serialize objects into a single field." I was disappointed.

If you need schemaless storage, use a schemaless DB. I don't understand what's so difficult about that. I wouldn't try to shove unstructured data into PostgreSQL any more than I'd try to shove relational data into MongoDB.

Post reply on HN