Saving Long Binary (Raw) Strings

Question

We collect the raw binary string, which is variable in size (from 100 to 800 thousand), and we would like to keep these separate lines. They do not need to be indexed (duh), and there will be no queries in the contents of the field.

The number of these inserts will be very large (they are intended for archival purposes), say, 10,000 per day. What is the best field type for blob strings? Should it be text or blob or something else?

Answer 1

As for PostgreSQL , enter text out of the question. It is slower, uses more space and more error prone than bytea for this purpose.
There are basically 3 approaches:

• Use bytea type (basically the pg equivalent of the blob code type)

• use " large objects "

• store blobs as files in the file system and save only the file name in the database.

Each has its own advantages and disadvantages.

• quite easy to handle, but it needs more disk space. Some decoding and coding is required, which makes it also slow. Backups are growing fast!

• a little inconvenient in processing, but you have your own infrastructure for manipulating blobs - if you need it. And you can easily make separate backups.

• is the fastest way and uses the least disk space. But it does not provide the referential integrity that you get when you store in the database.

I have a number of implementations for image files: save a small sketch in a byte field for referential integrity and quick reference. Save the original image as a file in the file system. Of course, you need to think about when and how to delete obsolete files, how to back up external files, etc.

Answer 2

You should probably use blob in most databases. text columns will often be normalized or transformed according to text encoding or locale; and therefore, the actual bytes may not be saved as expected. In almost all cases, this is the only difference between the text and blob columns.

Answer 3

We have a custom VARBINARY column for storing up to 300 MB files.