11

I need to store binary data files in a PostgreSQL database which runs on an Ubuntu server. Initially there will be a few dozen files of approximately 250kb in size each. However, the number of files will increase over time. I may at times need to extract data from the files for other downstream analyses.

I've done some research regarding the age-old question of storing binary data as BLOBs or references. Both obviously have their pros and cons. Are there any specific issues relating to PostgreSQL that I should be aware of? Is one method or the other preferable if I want to extract data from the files, either via a PostgreSQL function or via an external Python program?

If I were to store the data files directly in the database, would it be better to store them in a separate table with a foreign key referencing the "main" table, rather than in the table containing all the other fields?

I have read the question and answers here; a comment there suggests that storing binary files by reference (in the filesystem) on Linux is better. My questions here relate to PostgreSQL specifically and to extracting data from the files for various analyses.

Update: Similar question.

Improve this question
3
  • With PostgreSQl it is possible to set up a rule that automatically deletes the file in the filesystem when the record containing the reference is deleted.
    – j.p.
    Jan 31, 2011 at 11:25
  • I'm sure there was more than one answer to this question. What happened to it? Is there a way to see it if the poster deleted it? What about the comments on it?
    – SabreWolfy
    Feb 11, 2011 at 16:14
  • Yes, I deleted it, as the performance problems with bytea I wrote about can be avoided. The comments can be summarized by "Everything is fine with bytea, you should just make sure that you don't escape non-printable characters in the database and then un-escape them again in your application. As araqnid commented, you should instead use hex escaping that is supported by libpq."
    – j.p.
    Feb 16, 2011 at 15:52

1 Answer 1

Reset to default
9

I think you should store the data in the database as a normal bytea column. That way you get all the advantages of a database, and you can process the data using database functions (and even PL/Python, if you want that). Larger data items will automatically be stored out of line, so there would be no reason for you to introduce another reference indirection.

The main reasons for storing large binary objects outside the database would be if they are too large to be able to store and retrieve them in a satisfactory time, if they bloat the database beyond practicality, or if you need to access the files as files from a separate application. None of that applies there, as far as I can tell.

Improve this answer
2
  • Thanks for the details. Your point about accessing the files from a separate application led me to realize that I may in future want to allow users to download the binary file to use locally on their machine. Could that be done if the file is stored in the database?
    – SabreWolfy
    Jan 23, 2011 at 21:31
  • Sure. You will need to write a small bit of code to arrange for that (fetching the file data from the database, arranging HTTP download, say), but that is not a blocker.
    – Peter Eisentraut
    Jan 24, 2011 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.