When should you store serialized objects in the database? (2010)
1–10 of 70 posts
Re: When should you store serialized objects in the database? (2010)
#2Link about FriendFeed (mentioned in the blog):
http://web.archive.org/web/20100314211658/http://bret.appspo...
Re: When should you store serialized objects in the database? (2010)
#3I am working on a system where the data is serialized using Python's pickle and stored in the database. Absolute nightmare for debugging as its basically unreadable.
Re: When should you store serialized objects in the database? (2010)
#4I 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 pickle (python's object serialization format). If you don't and some clever chap works out a way to write input to that field then you've got an easy RCE. Plus it's easier to debug JSON, and databases like PG have a native data type for it.
Re: When should you store serialized objects in the database? (2010)
#5I 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…
Plus using something language agnostic allows you to employ polyglot programming. If your data is all pickled (or Java serialized, or really anything else that's "native"), good luck using anything besides the original language.
Re: When should you store serialized objects in the database? (2010)
#6For anybody using sqlite, they have good documentation about this very question: https://www.sqlite.org/intern-v-extern-blob.html
Re: When should you store serialized objects in the database? (2010)
#7I 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.
Re: When should you store serialized objects in the database? (2010)
#8[deleted]
Re: When should you store serialized objects in the database? (2010)
#9Bad 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 took 9 months to get it right, because other people changed the way blobs were written out over the course of years.
Being clever doesnt pay, again.
Re: When should you store serialized objects in the database? (2010)
#10I 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.
It's easier to manage storage for file system objects than in-database objects. Things like performance (potentially on a per-file basis using symlinks), cost (likewise), out of band access (e.g. serving statically directly from web server and not bottlenecking on a DB connection), fragmentation, free space recovery on deletion, etc.