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

31–40 of 70 posts

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

#31

Post by Spooky23 »

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

Select * is the problem, not the blobs. Storing blobs should be more efficient that putting them in a filesystem somewhere else (which is effectively just another database) and dealing with the overhead of a bunch of other filesystem operations and losing referential integrity, etc.

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 traditional or object file system. In addition to scaling the database more effectively, this allowed the infrastructure teams to optimize delivery of blob data from a platform POV.

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

#32

Post by mgkimsal »

cakes wrote: post #22
mattnewton wrote: post #19

Earlier quoted context omitted.

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.

mysql has had xml/xpath query functions for a while too, iirc.

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

#33

Post by jb613 »

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…

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

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

#34

Post by vbezhenar »

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…

I don't agree that it's easier to store them in filesystem. You'll have to deal with a lot of potential problems: consistency, backup, transactions, replication, corruption. Database solves those problems automatically and as long as you can store everything there, you better do that. Good databases are not that bad at storing blobs.

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

#35

Post by WorldMaker »

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…

Certainly, so long as your database speaks JSON fluently you can even have your cake and eat (some) of it too. PostgreSQL has good JSON support. Microsoft's SQL Server is "working on it". Then there are loads of JSON friendly document databases out there such as Couch{DB, base}, Cloudant, Mongo, Redis, etc and so forth.

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

#36

Post by kbenson »

Spooky23 wrote: post #31
simtr wrote: post #7

Earlier quoted context omitted.

Select * is the problem, not the blobs. Storing blobs should be more efficient that putting them in a filesystem somewhere else (which is effectively just another database) and dealing with the overhead of a bunch of other filesystem operations and losing referential integrity, etc.

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, compared to a separate replicating file store.

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

#37

Post by jb613 »

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…

> I think it's rarely a good idea to store blobs of any kind in the database.

Stop right there. There are plenty of examples for storing blobs in a datastore - images and videos are 2 prime examples.

I think what you should say is in a SQL database. Google's AppEngine with their datastore makes it dead easy and with high performance to store&retrieve things like images and videos.

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

#38

Post by jb613 »

forinti wrote: post #23

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

> databases outlast the applications built on top of them

sure but this needs to be balanced with performance UNTIL then

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

#40

Post by cyphar »

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…

Never use filenames in a database. There's a bottomless well of security vulnerabilities that spring forth when you decide to "just store a path to the asset".
Post reply on HN