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.