Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Stavros.

Asked: May 27, 2020 - 6:21 am UTC

Last updated: May 27, 2020 - 8:09 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

Hi Tom,

This is a general question about the design of the tables in a DAILY use database. The users will uploading and downloading pdf files from a web app daily. What i want to ask is it a good idea to create my tables with BLOB columns and saving the pdf files in the database or should i have a column that it will save only the path where the pdf is located?

Because having all the files saved in the database that means the size of the tables will be increases day by day and also LOB_SEGMENT tables would also be created an thus we will run out of space.

Is it a good practice to save BLOB items in the database when the system is daily?

and Chris said...

an thus we will run out of space.

Wherever you put the PDFs, you still need enough disk capacity to store them!

If you're concerned about storing PDFs in the database because you may run out of space, it sounds like your operations need improving.

Storing in the Database

The biggest advantage is the files are consistent with the rest of the row data. When you insert/update/delete a row, you know the associated file was saved as expected.

When the files are outside the database, someone may (accidentally or maliciously) delete the files without changing the row. This leaves pointers to non-existent files!

Database backups include the files. While this makes the database backups (potentially a lot) bigger, it means you have everything when you recover. Remember you still need to backup the PDFs if you put them on the file system!

It's also easier to secure the files. If the PDFs contain sensitive data, you can use row-level security technologies such as VPD or RAS to restrict access.

Storing on the File System

It's easier to save the PDFs on cheaper storage.

You can access the files directly, without having to go through the database.

Both approaches work, but for me placing them in the database is a simpler solution. Think about how customers will access files after uploading them.

If you do decide to store the files outside the database, I suggest using BFILE. This is a locator for the file on the file system:

https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Data-Types.html#GUID-3D9CC018-1637-45CB-95CF-DE67319D1A54

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library