9

I'm developing a Postgres-backed system, in which many binary files will be stored.

I have at least 2 choices:

  1. Store them in Postgres.
  2. Store them as files.

What criteria do I need to consider to make the best possible decision?

Glory to Russia
  • 17,289
  • 56
  • 182
  • 325
  • 1
    You would store them in the database if storing them is required to be transactional. Or if access to them should be managed with the same privileges as the rest of the data. You would store them in the file system if external tools (image resizer, web server, ...) have to access those file of they are bigger than 1GB. –  Feb 06 '16 at 10:20
  • The files are songs. A song is uploaded once and can be listened to (but not downloaded) many times. – Glory to Russia Feb 06 '16 at 10:22
  • Let's assume that once uploaded, the songs will stay in the database forever. – Glory to Russia Feb 06 '16 at 10:23
  • With files, how do your SQL client access the contents through a SQL connection? – Daniel Vérité Feb 06 '16 at 18:27
  • Also, see [Files - in the database or not?](http://dba.stackexchange.com/questions/2445) on DBA.se with its 10+ answers. – Daniel Vérité Feb 06 '16 at 18:30

1 Answers1

9

I would consider the following:

  • Performance. Storing binary files in the file system generally performs better, both for reading and writing.
  • Security. Access to the files in the file system is controlled by the operating system, however if you store the files in the database, PostgreSQL access rules apply.
  • Backup consistence. If you store the data separately (file system and database) is very difficult to have a consistent backup. File system backup and database backup are likely to be out of sync.
  • Transactional properties. File system is not transactional, but obviously PostgreSQL is.
Jesús López
  • 8,338
  • 7
  • 40
  • 66
  • Storing the files in the file system is not necessarily faster. See e.g. here: http://research.microsoft.com/apps/pubs/default.aspx?id=64525 (it's for SQL Server, but I wouldn't be surprised if something similar was true for other DBMS as well) –  Feb 06 '16 at 10:33
  • What about scalability? If you have lots of files to store, what is preferable? – Adi Levin Feb 06 '16 at 10:34
  • @a_horse_with_no_name. I really doubt SQL Server performs better than file system regarding binary files. – Jesús López Feb 06 '16 at 10:41
  • It does (with certain file sizes). Read the document from the link I provided. –  Feb 06 '16 at 10:42
  • @Adi Levin. Feel free to edit the answer to extend it – Jesús López Feb 06 '16 at 10:42
  • @JesúsLópez, I am hoping to get an answer from you. I'm not sure what's the correct answer on scalability. – Adi Levin Feb 06 '16 at 10:45
  • @Adi Levin. I'm also not sure which one scales better. To have more capacity to store more files you just need to throw more disks on both file system and database. – Jesús López Feb 06 '16 at 10:48
  • That's what I thought. And scaling Postgres is more complicated than this, isn't it? I mean, at some point, vertical scaling won't work any more, and you have to do sharding... – Adi Levin Feb 06 '16 at 10:49