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

11–20 of 70 posts

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

#11

Post by tylermauthe »

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…

This! Please!

I work at a place that has been trying to undo the damage of having serialized BLOBs for about 3 years. Granted, these are especially nasty things with a custom serialization layer written in Java.

Software companies consist of two assets produced by it's employees: data and algorithms to make the data useful.

Why would you ever lock your data down and make it harder to make cool algorithms to make the data useful?

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

#12

Post by autogn0me »

Agree, storing binaries in database is generally a bad idea. It would be a really miserable idea if it were being done without a sane persistence API. In the Python world, ZODB - http://www.zodb.org/en/latest/ is tightly coupled with the language but works reasonably well in practice. The storage layer is pluggable and https://pypi.python.org/pypi/RelStorage provides storing pickles in RDBMS.

ZODB is arguable a novel approach to persistence using Python. And certainly worth taking some time just to play with it -- the barrier of entry low, e.g. `pip install`. But for each positive there are negatives..

"You got it buddy: the large print giveth, and the small print taketh away"

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

#13

Post by orf »

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.

> and dealing with the overhead of a bunch of other filesystem operations and losing referential integrity, etc.

Yeah, better to instead deal with the overhead of the database combined with the overhead of the filesystem! Referential integrity is also very easy to deal with.

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

#14

Post by ams6110 »

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 beyond a certain size it's almost always easier to manage things like these in a filesystem and just store a pointer (filename) in the DB.

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

#16

Post by bvinc »

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

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

#17

Post by kbenson »

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 sort of like you decided to fix a bunch of problems at the same time...

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

#18

Post by jbyers »

(2010)

Uber moved to a similar architecture in 2014-2015, ~5 years after article and the original Friendfeed post. Being able to operate MySQL predictably at scale is extremely valuable to high-growth companies, enough to tilt in the favor of unconventional schema choices versus less proven NoSQL alternatives.

https://eng.uber.com/schemaless-part-one/

https://eng.uber.com/schemaless-part-two/

https://eng.uber.com/schemaless-part-three/

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

#19

Post by mattnewton »

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…

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.

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

#20

Post by prashnts »

autogn0me wrote: post #12

Agree, storing binaries in database is generally a bad idea. It would be a really miserable idea if it were being done without a sane persistence API. In the Python world, ZODB - http://www.zodb.org/en/latest/ is tightly coupled with the language but works reasonably well in practice. The storage layer is pluggable and https://pypi.python.org/pypi/RelStorage provides storing pickles in RDBMS. ZODB is arguable a novel…

From my personal experience, I completely agree.

For an academic project, I was calculating ~2mln RNA-RNA interactions from their sequences. Since this calculation stays a requirement for all further calculations, being the naïve kid I was, I started pickle'ing the results.

To feel like the cool kid, I wanted to involve a database somehow -- so after trying out a bunch of options, I finally settled for ZODB. As the project scaled up, soon the ZODB started being a big pain, because as I recall, it only allows a limited number of connections even in the read operations.

Lesson learned, though, it now resides as a lookup table in a PgSQL instance.

Post reply on HN