Results 1 to 8 of 8

Thread: BLOBS in Database: Good or Bad practice?

  1. #1

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,560

    BLOBS in Database: Good or Bad practice?

    Hi Folks,

    this subject came up in a discussion i had with someone last week:

    First off: Please no advice like "put the file (Image, Video, PDF, whatever) in a Folder on the Disk, and save the path to it in the DB"
    I know that technique, but it doesn't help the discussion/experience exchange.

    The discussion was:
    Do you have the BLOB-Field in the same table as the rest ("1-Table-Design"), or do you create a separate table in a 1:1-Relation (ForeignKey=PrimaryKey) ("2-Table-Design")?

    For all intents and purposes, the Blob-Field is not mandatory, meaning in the second scenario ("2-Table-Design") it would be either "one-to-one" or "one-to-none" relation
    Declaring a ForeignKey as the PrimaryKey is also easy (ForeignKey with ON DELETE/UPDATE, but WITH Unique- and WITHOUT AutoIncrement-Attribute).

    I was actually leaning to the 2-Table-Design for the following reasons:
    1) I can fire off a "SELECT * FROM tbl_master WHERE SomeField=SomeValue" (And no: We're not discussing a SELECT * either , and the BLOB would not get pulled through the connection
    2) Sharding, i could actually shard the BLOB-Table to a different harddisk
    3) Fragmentation (For lack of a better word)?!? This one i'm not sure about, since it goes into the specs of Database-Technology i'm not familiar with (Paging, allocating Memory/Diskspace etc.).
    My Gut-feeling tells me, that "Fragmentation" would be worse in the 1-Table-Design

    Next:
    How high would the penalty be in the 2-Table-Design, if (for whatever reasons) you have to use a JOIN on both tables?
    I'm pretty sure, perfomance in the 1-Table-Design would be miles ahead


    As a last: In the 1-Table-Design, does it matter, where your BLOB-Column is? As in: the ordinal order?
    My gut-feeling also tells me, the best place for a BLOB-Column would be as the last column in a table, but again: not something i'm familiar with


    Thoughts? Experience?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  2. #2
    Hyperactive Member
    Join Date
    Jun 2019
    Posts
    326

    Re: BLOBS in Database: Good or Bad practice?

    We use both designs and both are useful in different use cases. And as always - it depends what information will be stored in blobs, how often will be retrieved, backup strategies, etc.

    In the past some databases (don't remember exact details now) were limited to single BLOB per tables so multi-table design was the only choice. Also it was required to put the BLOB as last field in the table. But that was past and now things are different.

    For example two-table design I used recently was for users avatars. The second table contains multiple BLOBs with images resized for different resolutions. This way the proper sized for the current display resolution is retrieved from the second table without messing with main table fields by adding multiple fields for different avatar resolutions. Also local caching of all users avatars is performed so no need to retrieve avatar for each record again and again. It is much faster to join in memory using dictionaries compared to transferring multiple BLOBs over the wire.

    So it depends :-)

  3. #3

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,560

    Re: BLOBS in Database: Good or Bad practice?

    Thx peter.

    Was your 2-Table-Design a classic 1:m (1 row per resolution) or a 1:0/1 relation (in case of 1:0/1 multiple BLOB-Columns for the diff. resolutions)?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4
    Hyperactive Member
    Join Date
    Jun 2019
    Posts
    326

    Re: BLOBS in Database: Good or Bad practice?

    Multiple BLOB columns for 512x512, 256x256, 128x128, 64x64, 32x32 resolutions. Adding new resolution is just adding new column + adding few lines in code (select/case) to support it. There is a simple tool that processes all records and can regenerate resolutions from the original file, which is kept on disk and referenced in db as path to file string.

  5. #5
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,434

    Re: BLOBS in Database: Good or Bad practice?

    Same here... done it both ways, and it's use depends on the needs. In the case of the one table use, we were storing icons along with some action information for a plugin type of system, so the images were pretty small and compressible, and more importantly all the same size, so there wasn't too much risk. In the two table setup... we were storing some high res images of various sizes. In this instance there were storage issues, but that was mostly due to a business decision by the client, not by the design of the database or the system itself - they were storing thousands of 8"x11.5" hi-res glossy images... and it very quickly ate through their storage allotment on the servers - a couple terabytes worth in a few months. I know this sounds like an argument against BLOBs, but like I said, it was more due to a decision by the client, and we tried to warn them that what they were doing wasn't going to be optimal (the images were used for marketing, and when printed, were reduced to approximately 1.5"x2" size ... so there was no need for the oversized, highres, hig-def images... but did they listen noooooo)... about a year after I rolled off the project and they went live, I got a call from the DBA assigned to them (we host their data) asking about their table use, and what this particular table was for, and why in the world it was so huge.... so I told him the history of it... after some explitives.... they had to move them to dedicated servers and upped their monthly hosting fees...

    If you're planning on using select * (brf) ... then use the two table design.
    In regards to performance with joins and what nots... indexes will be your friend in this case.
    Fragmentation - Will the data be written to often? Or mostly read? That will play a big part in fragmentation. Also the size. If everything is consistently the same size, fragmentation is less of an issue, especially if the row, including the image can fit on a page.
    If you're using SQL Server, something you may want to look at (depending on which version) is FILESTREAM objects (I forget which version they were introduced at) ... while BLOBs can be anything since they are just basically an array of bytes, FILESTREAM objects are files. Like BLOBs, they are stored outside of the table, with a pointer. Unlike a BLOB, they are stored as a file, and thus can be sent & received as a stream directly. You still select the field like you normally would, but rather than pulling the data over the wire into byte array and then converting it into what ever it was, you simply connect it to an appropriate stream object and start reading it.

    Bottom line: if the images are small enough, of a consistent size, and don't take too much room, there shouldn't be a problem using a one-table system. However, if the images are of the larger variety, or of a varying sizes, or there will be lots of edits to them, and since you plan to use select *, then go with the two table setup and apply indexes. Also consider looking into FILESTREAM if you're using SQL Server.

    This article may also be of some help
    https://www.pluralsight.com/blog/tut...ith-sql-server

    my two centavos.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,560

    Re: BLOBS in Database: Good or Bad practice?

    tg,
    thx for your insight.

    Yeah, i know the feeling about SELECT * and i'm not a friend of it, either. I just mentioned it for completeness' sake, if someone else finds this thread.

    So Bottom line from you:
    Blobs "relatively" small in size and consistent in size (!) use a 1-Table-Design (avoiding SELECT * of course),
    if varying in size (or even varying in type (mp4, pdf, jpg etc.)) go for the 2-Table-Design
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  7. #7
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,480

    Re: BLOBS in Database: Good or Bad practice?

    One other thing to remember in SQL Server blob storage is not in line (meaning not on the row) so it required extra IO anyway.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  8. #8

    Thread Starter
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,560

    Re: BLOBS in Database: Good or Bad practice?

    Thx Gary.

    My question is not aimed at a particular DBMS, but still good to know.
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width