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

61–70 of 70 posts

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

#61

Post by phaedrus »

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…

When my previous employer forced my team to implement storage of structured data as a serialized BLOB (on top of a system which used to store the data the "right" way), I turned in my resignation.

Background: we had been storing other, similar data in a structured way for years, so we had a system set up to do it right. I'm not sure what the rationale was for switching, but it was declared by fiat over the protests of a team of five experienced .NET developers and an experienced lead DBA. We began experiencing problems from it before we were even a month into the project, such as serialization output not agreeing between client apps (.NET serialization is NOT designed to be a shared archive format!!), implementation requiring breaking the separation of concerns between layers of our application, etc. And for what? When asked what we would do when the format changes, management cheerfully replied "oh we'll just write + run a conversion EXE to update the data in bulk. Why, we do that all the time in [other engineering team who cowboy-codes everything and operates with a level of technical debt that makes it suck to work on that codebase]."

Of course this wasn't the only reason I was resigning, but it made the decision easier!

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

#62

Post by phaedrus »

LyndsySimon wrote: post #50
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 relatio…

It seems the article was focused purely on the performance implications of the decision, and nothing about the maintenance and architectural impact.

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

#63

Post by joe_the_user »

kbenson wrote: post #52
joe_the_user wrote: post #44

Earlier quoted context omitted.

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

Every decision is a trade-off between up-front work and later eventual work. Given "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.", just a few of the possible solutions might include: - Move to a fully relational schema: - - Pros: You can leverage the power of the database to efficiently index, search, and aggregate any specific column wi…

Broadly, software is about tradeoffs.

But the relational model is essentially so good an approach that if you are building something that's close to a relational model but not fully relational, you're almost certainly creating more present and future potential problems than if you moved to fully relational model (edit: and that's not saying other approaches are bad if you have a problem a ways away from the relational domain).

Neither of us know the particulars of gp's problem and I so couldn't say if you're alternatives are better tradeoffs. However, I would wager if someone wound-up adding several fields and tables to an existing blob-filled database, the result would haunt it's creator on many lonely nights at the office.

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

#64

Post by tigershark »

mattmanser wrote: post #29
tigershark wrote: post #21

Earlier quoted context omitted.

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.

I never mentioned config files. The article explicitly states: "A good example of this optional nature of data is user preferences – you only really need to store the settings that differ from the default values." User settings is a good candidate for a configuration object in the configuration store that I built. The primary id will be the user id, the secondary id the machine name (if the system needs to support different configurations on different machines) and the rest will be a CLOB containing the JSON serialized user configuration.

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

#65

Post by tigershark »

jb613 wrote: post #33
tigershark wrote: post #21

Earlier quoted context omitted.

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…

> and a value that is the configuration object serialized in JSON It all depends on the data, but if it's not simple, then serialized JSON values would generally incur performance hit for search operations. Breaking out the data into separate columns could be better indexed.

No, there is no whatsoever performance hit because the identifiers are in separate columns in the same table, and they can be indexed normally. There will be a performance hit if for some future requirement all the ids column are exhausted, they start using the generic key with complex serialized objects AND they want the generic key to be searchable. At that point they can simply add another id column if it is really necessary.

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

#66

Post by Zikes »

andy_ppp wrote: post #28
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.

Queryable and even indexable! Hardly a trade-off at all, really.

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

#67

Post by spacemanmatt »

LyndsySimon wrote: post #50
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 relatio…

HAHA, I tend to agree that 'never' is the only good answer.

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

#68

Post by Spooky23 »

kbenson wrote: post #36
Spooky23 wrote: post #31

Earlier quoted context omitted.

How do you figure that? In addition to making database performance less predictable and introducing all of the problems that BLOBs bring, you lose most of the benefits of the database in the process. File systems are about storing files. Databases are about intelligently organizing data for retrieval and reliably delivering atomic transactions. Any system that I've seen scale up well separated blob data to a traditio…

There are a few cases where it makes sense to store files in the database, but the constraints are fairly specific. The one time I did it to god effect was when all the files were fairly small (<40k), and one of the defining features of the system needed to be it's resilience. We were able to fold the file storage into the normal master/slave replication setup we were doing, which was a big reduction in complexity, c…

Sounds like you scoped it well. I see that as a similar use case to putting crypto keys or user pictures in LDAP.

Often folks doing this try to re-invent a content management system like FileNet in the DB.

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

#69

Post by kbenson »

Spooky23 wrote: post #68
kbenson wrote: post #36

Earlier quoted context omitted.

There are a few cases where it makes sense to store files in the database, but the constraints are fairly specific. The one time I did it to god effect was when all the files were fairly small (<40k), and one of the defining features of the system needed to be it's resilience. We were able to fold the file storage into the normal master/slave replication setup we were doing, which was a big reduction in complexity, c…

Sounds like you scoped it well. I see that as a similar use case to putting crypto keys or user pictures in LDAP. Often folks doing this try to re-invent a content management system like FileNet in the DB.

In our case, it was for storage of electronically signed documents. Really it was an HTML template (the same displayed to them) with the inputs replaced with the values they presented, converted to PDF, and attached to the account. A few pages of PDFs like that doesn't take much room, and ensuring there isn't a mixup with files and accounts when it's for regulatory compliance makes it well worth any downsides.

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

#70

Post by merb »

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…

actually we save history data as json inside postgresql and article data as json and we have a price table that adds a history as a postgresql trigger. that's actually not blob data but its a kind of serialization. However we access the data regulary. The Price History is exposed to the user so it needs to work. Our system needs to work even against older versions of the table.
Post reply on HN