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

51–60 of 70 posts

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

#51

Post by longshorej »

PretzelFisch wrote: post #49
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?

Replication is a big advantage that's hard to replicate to the same degree with ZFS or rsync.

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

#52

Post by kbenson »

joe_the_user wrote: post #44
kbenson wrote: post #17

Earlier quoted context omitted.

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 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 without too much trouble.

- - Cons: Schema change may be expensive. Requires processing all data into a new structure. Requires massive changes to large swaths of your data handling routines. Must be done all at once, or you need to run both systems in parallel for a while during conversion. In this case, took 9+ months.

- Add a field on that table:

- - Pros: Simple. Requires very little change in processing routines. Efficient. Can rely on DB typing to require data be present for a record.

- - Cons: Depending on database may require excessive downtime while schema is updated.

- Add a table that tracks this field and links to the main record.

- - Pros: Simple. Requires relatively little change in processing routines. Efficient. Zero downtime. Can fill old records with background process.

- - Cons: Hard to enforce that the data exists for every record. Can be mitigated with report generated for records without this link.

Now, given those choices, I would say the correct choice in any particular situation depends quite a bit on external constraints. Do you have 9+ months and the free developer time to essentially redesign what may be large swaths of your back-end? Do you have assurance (tests, language features, etc) that you won't increase enough bugs to negate the benefits of moving to a fully relational schema? Is there a looming deadline on when the works needs to be completed by? Is downtime not really an option, and/or is building a parallel data store for the migration not feasible? How useful is normalizing the data expected to be in the short, medium and long term?

Like I said, I assume they had a reason for the full relational migration. I was just pointing out that there are easier solutions that in some cases fit business needs better than that. If your company goes under because your large migration project stole focus and manpower from other needed projects when there were simpler solutions available, then you made the wrong decision, period.

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

#53

Post by garethrees »

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

I understand your concern. Perhaps if I give an example, it will be clear why I followed the approach I did.

The project is an industrial control system. Different kinds of item are processed by the system. Each kind of item needs image acquisition parameters, for example field of view. There's no use case that needs to select kinds of item whose image capture parameters specify a particular field of view, and it's clear that such a use case is very unlikely to arise — the image acquisition parameters are part of the internal specification of the system, not something that any end user is interested in.

At database design time it wasn't settled which model of camera was going to be used, and so it was far from settled which parameters were going to be needed. Using a JSONB field for the image acquisition parameters meant that I could leave the specification of these parameters to the camera programmer, without having to incur a series of database migrations as the correct set of parameters were worked out.

Even in your project, where new reporting requirements eventually arose, it doesn't mean the original decision was necessarily a bad one. Sometimes it makes sense to incur technical debt in order to bring a product to market in a timely fashion.

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

#54

Post by unlinker »

cyphar wrote: post #40
ams6110 wrote: post #14

Earlier quoted context omitted.

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

Of course, you don't take for granted what the client says. You generate your own file name.

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

#55

Post by collyw »

orf wrote: post #43
ams6110 wrote: post #14

Earlier quoted context omitted.

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.

JSON is receiving pretty much the same hype that XML did 15 years ago. I never got really understood the hype about it then and I don't get it with JSON now. I find XML better for some things and JSON better for others. Neither is perfect.

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

#57

Post by collyw »

unlinker wrote: post #56
collyw wrote: post #3

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

Can't you open a Python shell and unpickle it?

Gave me an error the last time I tried to that.

Plus even if it did work, it involves logging into the server, activating my python virtualenv, pulling the data out via the python / Django shell an unpickling and printing it. As opposed to running a query on my local machine connecting to the database. When you are debugging a problem and just want to get an overview of what is happening, that is a hell of a lot of hassle.

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

#58

Post by Arwill »

This is the consequence of the chosen programming language not being adapted to work with a relational database. The language is made to work with objects, and the access to DB is clumsy, trough library functions, which are deeply encapsulated, and the language and the DB are two different worlds. In SAP's ABAP language relational database access is integrated into the language. In SAP, when you create a database table, the structure of that table will be automatically available to any program as a structure datatype. So if you change a table definition, you will also change the data type used by the programs. Doing table changes is supported by a database tool that will automatically copy records from the old table to the new one if necessary. Its easy to find all references to a DB table, and recompile the sources. Its actually done automatically when both program and DB structure changes are deployed. Whatever change the developer does in the development system, that change will be automatically adjusted in the productive system on deployment. This makes any table structure change pretty easy, the development environment takes care of that. Wherever SAP applications use blobs to store data (for example HR payroll), those are the worst to develop with. Doing a non-simple change on a TB big table would surely cause disruption in a SAP system too, but other techniques are available for those cases.

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

#59

Post by bvinc »

PretzelFisch wrote: post #49
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?

What you would gain is the ease, simplicity, and stability of using your favorite sql engine. You get ACID transactions and the ability to add and remove from large lists using low memory, for free.

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

#60

Post by rhinoceraptor »

You can get away with it in Postgres. The app I work on stores phone numbers in a JSONB array in the following format:

    [{
      "tags": ["cell"],
      "number": "1231231234"
    }]
Here's a snippet demonstrating how you can do a lateral left join on the column to find the number tagged 'cell' in tags array:

    select * from mytable t
    left join lateral (
      select phone->'number' as cell_phone from
      jsonb_array_elements(t.phone_numbers) phone
      where phone->'tags' @> '["cell"]'
    ) p on true;
Post reply on HN