Binary Large OBject, datatype
Binary Large Object, is a data type that is used to store binary data (files, images etc).
Questions tagged [blob]
166 questions
174
votes
13 answers
Should binary files be stored in the database?
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 the filesystem but rename to a hash of the…
Jack Douglas
- 40,517
- 16
- 106
- 178
58
votes
6 answers
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 can, presumably, be done by one of the PL/…
SabreWolfy
- 949
- 1
- 7
- 16
33
votes
3 answers
Why is it recommended to store BLOBs in separate SQL Server tables?
This highly-upvoted SO answer recommends to put images in separate tables, even if there is only a 1:1 relationship with another table:
If you decide to put your pictures into a SQL Server table, I would strongly recommend using a separate table…
Heinzi
- 3,210
- 2
- 32
- 43
26
votes
1 answer
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 with enough data to ensure it is stored off row. In…
Paul White
- 94,921
- 30
- 437
- 687
19
votes
2 answers
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 numbers, let's say the table has a million rows…
Alex Shelemin
- 555
- 4
- 11
17
votes
6 answers
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?
Jonas
- 33,945
- 27
- 62
- 64
13
votes
1 answer
How to copy blob (binary data) from one DB to DB using a script?
We are trying to copy a blob (binary data) from one Database to another Database using a simple script. This is the steps we do.
Select the value using MSSQL Studio Client.
Copy it into the clipboard.
Paste it into anINSERT script using…
Nishant
- 899
- 2
- 13
- 20
13
votes
1 answer
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 each table, and executed the below…
RBB
- 815
- 2
- 15
- 37
12
votes
4 answers
How can I store a pdf in PostgreSQL
I have to store .pdf files in a table.
I have a table, state, with columns:
id_state,
name,
pdffile (bytea)
I want to store the pdf files in the pdffile column.
How can I do this?
Giuliocas
- 121
- 1
- 1
- 3
10
votes
1 answer
SQL Server LOB variables and memory usage
When I use a variable of a large object (LOB) data type in SQL Server, is the whole thing kept in memory at all times? Even if it is 2GB in size?
Paul White
- 94,921
- 30
- 437
- 687
9
votes
4 answers
Row size too large (> 8126)
I'm facing the following problem.
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In
current row format, BLOB prefix of 768 bytes is stored inline.
It appears just…
Hellon Canella Machado
- 193
- 1
- 1
- 4
9
votes
2 answers
In what cases are BLOB and TEXT stored in-line on InnoDB?
I've googled this and can't find it, you can see Rick James mention it here.
... BLOB and TEXT are not always stored separately
The answer on What is the difference between MySQL VARCHAR and TEXT data types? says,
InnoDB is similar for VARCHAR,…
Evan Carroll
- 65,432
- 50
- 254
- 507
9
votes
1 answer
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 a few seconds each. Each epoch's record holds…
beldaz
- 1,740
- 3
- 16
- 26
8
votes
1 answer
Best way to shrink a DB after nulling data from varbinary(max)?
We have a database with a large amount of data stored in a field of type varbinary(max). At some point we can purge that data for most rows, but not all. Our plan is to make that field nullable, and simply null out the data when it is no longer…
TTT
- 245
- 1
- 7
8
votes
1 answer
Slow DELETEs 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 statements to perform reasonably well on LOB data.…
Abacus
- 255
- 3
- 8