How to decide whether to store binary data in Postgres or in files?

Question

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?

Answer 1

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.