Binary Large OBject, datatype

learn more… | top users | synonyms

73
votes
11answers
27k views

Files - in the database or not?

What is the best place for storing binary files that are related to data in your database? Should you: Store in the database with a blob Store on the filesystem with a link in the database Store in ...
23
votes
5answers
60k views

How to insert (file) data into a PostgreSQL bytea column?

This question is not about bytea v. oid v. blobs v. large objects, etc. I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This ...
23
votes
1answer
437 views

Logical reads different when accessing the same LOB data

Here are three simple tests that read the same data, yet report very different logical reads: Setup The following script creates a test table with 100 identical rows, each containing an xml column ...
14
votes
2answers
399 views

LOB_DATA, slow table scans, and some I/O questions

I have a rather big table with one of the columns being an XML data with an average size of XML entry being ~15 kilobytes. All other columns are regular ints, bigints, GUIDs etc. To have some concrete ...
8
votes
2answers
518 views

Migrating from text and image to varchar(max) and varbinary(max)

I have a SQL Server database which contains a number of image and text columns, and I'm studying potential problems that might arise from migrating them to their non-deprecated counterparts varbinary(...
8
votes
1answer
238 views

PostgreSQL bytea vs smallint[]

I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically ...
6
votes
1answer
434 views

MS SQL high tempdb disk I/O during merge replication of BLOBs

Having a merge publication for replicating BLOBs (sql type - image), got very high tempdb disk I/O for my size of data. Publication is download-only and has no filters. High disk I/O is caused by ...
5
votes
1answer
7k views

Export table without BLOB column

I have a big table with BLOB objects and some references from other tables to this one. I prepare dev and test schema for my app by making expdp (data pump) from production, then anonymizing data on ...
4
votes
3answers
13k views

How to export an image column to files in SQL Server?

I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?
4
votes
1answer
116 views

Slow Delete's of LOB data in SQL Server

I have a tables for logging, and a stored procedure to purge old data that has very slow DELETE performance, beyond what makes sense to me. I am looking for how to modify the tables or DELETE ...
4
votes
1answer
3k views

How to determine Oracle LOB storage footprint?

With SECUREFILE storage I can specify whether I want compression (and the level of it) and deduplication, and it's all a trade-off between time and space. Timing is fairly easy to profile but what's ...
4
votes
1answer
211 views

Oracle BLOB store performance

LOBs in Oracle are stored in-row when they are less than 4K in size and out-of-row otherwise. I'm wondering how do SECUREFILE storage options COMPRESS and ENABLE STORAGE IN ROW work? I have a lot of ...
4
votes
1answer
600 views

BLOBs storage in a dedicated table

I have a database that has several BLOB columns scattered across multiple tables. Since I am a developer and am looking at making application code writing easier, I wanted to turn these columns into ...
3
votes
1answer
64 views

Replacing a large object in PostgreSQL

What should one do when one wishes to replace a large object with a new large object. An example would be to update an uploaded file with a new version. One way would presumably be to create a new ...
3
votes
1answer
1k views

Can I do VACUUM FULL to pg_largeobject table?

I have two tables (table1, table2) in a Postgres 9.1 database. Both having oid type. Each table 1 million records. And pg_largeobject table size is around 40GB. I have removed 0.9 million records from ...
3
votes
1answer
264 views

How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work? I know that the database will need to store a ...
3
votes
1answer
475 views

Can I use mysql to serve 100's of millions of small image files?

I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
3
votes
1answer
412 views

MySQL - Where can I find metrics on the performance of Blob vs file system?

I know and understand that there are performance hits in storing blob data in the database, but the blob portion of the data is going to be rarely retrieved/viewed, it is for smaller data (the vast ...
2
votes
3answers
211 views

Why do large images stored in database hurt performance [closed]

I have read on the internet that it is better to keep large images on file systems since storing them in the database hurts performance. My question is why do large images hurt db performance?
2
votes
3answers
1k views

Storing file into oracle db

I'm developing a java web-app for the acquisition of contracts, and that have to receive some file from an external system. For each new contract i recieve some information about the new customer and ...
2
votes
1answer
976 views

Data type for protobuf objects in mysql

I would like to store some protobuf objects in a MySQL database. The objects are created in a Java application. The object is accompanied with a textual type identifier and a timestamp. +------------+...
2
votes
1answer
3k views

Selecting with varbinary(max) criteria (in the where clause)

Basic info Database: SQL Server Express 2008 R2 Client: SQL Server Management Studio Backround (skip if not interested): A project I'm maintaining uses an ORM, which apparently stored my enum ...
2
votes
1answer
122 views

Updating a table which contains an Image datatype column

I have a table which contains a few columns, one of which is an image datatype, this is used to store BLOB data, which could be anything from a jpg to a large PDF. I have a requirement to update one ...
2
votes
1answer
370 views

What will be the best suited datatype to store a text file in database [closed]

I want to store a text file containing logs in a database(SQL Server). That file size can vary from 1 MB to 10 MB. what will best suited data type to store it in to the database. Will it be BLOB or ...
2
votes
1answer
191 views

one table for all blob with a one to one table for metadata which link to one or many tables?

I'm going to start by saying that I'm not a DBA but i got that role in my current project so I'm doing my best. The application must be able to store files(attachments, official documents, etc) The ...
2
votes
1answer
280 views

Oracle. How to copy BLOB, LONG RAW and BFILE column between tables

I have a table name mytable in Oracle 9i. columns of mytable are id number(9,0) C1 LONG RAW, C2 VARCHAR2(256), C3 BLOB, C4 BFILE when I try to do this: create this_other_table as select * from ...
2
votes
2answers
195 views

pg_restore does not restore blobs

I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command: pg_dump --format=c --...
2
votes
1answer
266 views

Easiest method to retrieve the content of BLOBs from a Sybase databse

I would like know the easiest way (no coding) to extract the content of the blobs of a database. For example, I have a table which contains a column whose format is BLOB. The result I would like to ...
2
votes
1answer
142 views

BLObs – Should I Store In Database Or in Directory Structure?

BLObs – Should I Store In Database Or in Directory Structure I have been developing a database to replace an ageing data structure that contains 1000's of files within a directory structure. The ...
2
votes
2answers
670 views

Compress files and directories into zip file and put it into blob field

What is the best way to do the following using SQL Server? Compress all the files, folders and subfolders from a specific location (specified in a parameter in a stored procedure) - the server side ...
2
votes
1answer
2k views

How to copy bytea from Postgres to Oracle

I have a db on Postgres 9.1. The db collected some binary data that are stored in bytea file. I need to copy the data to Oracle 10g. How to do it? The details I have a table plines in Postgres9.1. ...
2
votes
1answer
94 views

Does SQLBackup2Azure delete the blobs as well?

I am not sure if this questions belongs here or in server fault, apologies if it belongs there. I am using MS SQL 2012 on an azure VM, and am planning on utilizing the SQLbackup2Azure tool to store ...
2
votes
0answers
362 views

Empty Space in buffer pool

I was doing some reading and came across this article from Paul Randal: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/ Using the queries from that article, I ...
1
vote
4answers
866 views

What is a Scalable Storage Mechanism for large TTL data collections

We currently have a legacy webservice that stores each xml request/response in Sql Server. The data only needs to persist for 3 days before it is considered expired. Sql Server is not good at deleting ...
1
vote
2answers
623 views

How to reduce database size with BLOB fields

I have a database that's used to store fingerprints. Under my conception this should be a small database but the problem is that the original developer was probably tripping on peyote and decided it ...
1
vote
1answer
82 views

Proposal: MySQL blob handling revision [closed]

Why does MySQL store blobs in the direct table rather than off to the side so that if it needs to read the corresponding stuff it will read the stuff off to the side? Essentially it would create its ...
1
vote
2answers
899 views

MySQL stores BLOB in innodb buffer pool (innodb_buffer_pool_size)?

I have several databases all in INNODB, 50GB are BLOB columns, only 700MB are not BLOB data. Tools as mysqltuner ask me to set innodb_buffer_pool_size = 51GB. Does MySQL store the BLOB in RAM? If yes,...
1
vote
1answer
573 views

Which Database to choose: MySQL or Oracle

I have an application generating time series data (32 channels, 22KHz, 6 secs) once a minute. These have to be stored in a database. At the moment I'm using MySQL with InnoDB tables (file_per_table,...
1
vote
1answer
709 views

Possible reasons for slow BLOB updates in Oracle?

We have an application that transfers .bmp images as BLOBs to an Oracle table. One of the users of the app has complained that it is taking 2-3 seconds to transfer each image. Each image is ~1.2Mb. I'...
1
vote
1answer
450 views

How to determine when REORG with LONGLOBDATA is required (DB2)

REORGCHK only identifies when a reorg is required for space reclamation on normal (non-LOB) data. Is there a way of determining how many pages are wasted due to LONG and LOB data that has been ...
1
vote
1answer
469 views

Export synonyms content to tables

I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database. What ...
1
vote
1answer
183 views

Saving LZW encoded data into a mySQL database

I send LZW compressed data from the client side with JavaScript to the server. The problem is that the data becomes corrupted after saving it to database. So my question is, what collation should I ...
1
vote
1answer
681 views

RBS and Fail Over Cluster with SQL Server 2012

I am trying to install one SQL Server 2012 cluster with two nodes. I will also be enabling Remote Blob Storage (RBS) on server so I was wondering if I need to take something in account while ...
1
vote
1answer
688 views

Column of generic/variable type?

In SQL, I would like to create a table which can contain generic data types. This types can change from every row. The solution I could come up to is: CREATE TABLE TagValue ( tag VARCHAR(64), ...
1
vote
1answer
919 views

import csv data stored in a blob column

Here's what i need to do: Users will upload CSV files into SQL Server 2012 blob column Each night, i would like to take each file and import the data into table I will have 2 web sites using the ...
1
vote
1answer
2k views

Java application unable to access Blob stream in Oracle after upgrade to 11g

This issue below might be an application problem, but I cannot seem to rule out a poor database setup, I was hoping to get some insights into potential database issues. I am maintaining a legacy Java ...
1
vote
1answer
1k views

Length of LOB data (%1) to be replicated exceeds configured maximum %2

We recently hit this error shortly after enabling replication on a table. Length of LOB data (%1) to be replicated exceeds configured maximum %2 I've found solutions to this problem already: http:...
1
vote
1answer
557 views

What is the underlying representation of BLOB data when I export it?

When I export a blob object to an SQL file, I get some thing like this for a blob field: 0x4f3a383a227374644....., Questions I would like to know how the BLOB is actually converted to the above ...
1
vote
0answers
14 views

does insert “logs or traces” into RDBMS (example log4j) makes sense? What are pros and cons?

Our developers are inserting Java logs into a table. This table can grow very much (for example debug logging enabled) and I need to to truncate table. Our database is for OLTP. > desc ...
1
vote
1answer
57 views

MySQL – BLObs Hitting Performance

One of my tables contains seven BLObs. Six of these refer to raw data and are typically 1.0 – 1.5Mb each. The seventh is the result of processing this data and can be nearly equal in size to the sum ...