0

Let's have many rows in the SQL database where every record has an image property that holds the path to file in the filesystem. Many database libraries have the ability to set-up hooks that are called right after the row is removed.

Which one of the following patterns is better?

  1. Call hook for every removed row (even when all rows are removed in bulk action) and delete an associated file in that hook function (and fail maybe fail if the file was not removed)

  2. Do not use hooks and instead delete files inside business logic inside function what calls database query (maybe do it inside a transaction to fail if the file was not removed)

Almost every documentation for database libraries warns that using a hook for bulk actions will cause performance issues but I think those performance issues will occur even when you do loop inside eg. deleteAll method.

4
  • 2
    Do the images need to be deleted immediately or could you have a process run periodically and remove any that are not referenced? Would take the delete out of process. Mar 4, 2021 at 17:16
  • @ScottMildenberger Oh you are right. It is not very important if they are deleted immediately or few seconds/minutes/hours after. I will go with some periodic removal or delayed removal :)
    – Baterka
    Mar 4, 2021 at 18:21
  • You might very well need to have such a cleanup process anyway to deal with failed transactions, or other exceptional conditions, that may, due to other errors (possibly programming errors) leave junk behind.
    – davidbak
    Mar 5, 2021 at 1:04
  • Microsoft SQL Server has a feature for this FILESTREAM - and then, built on that - FileTable. Don't know about other databases, but you may find they've got something similar. It keeps the files in a file system for use with normal network file APIs (e.g., available via file shares) but manages them within the database.
    – davidbak
    Mar 5, 2021 at 1:12

2 Answers 2

2

Neither. Better would be not to use file system directly, but to allow database to handle the files as binary fields, or so called BLOBs.

  1. Transactions

File systems are not transactional. If you use transactions in your logic and some transaction fails, you can be sure that database has rolled back any changes you have done and you have exactly the state that you had before transactions. If you want to handle files separately, via hooks or via service logic, it can be very hard to provide transaction support, e.g. to implement roll back. For instance, at one step within transaction you delete a record and delete a file in the file system, then in several steps later the transaction fails. For rollback you would need to restore the files deleted within this transaction. This can be a hard task. If you store binary data in BLOB fields, you get transaction support for free, and will always have a consistent state.

  1. Backups

Database provide usually functionality to create backups and to restore the state from particular backup image. If you need backups, in case of file system you will need to implement your own tools to create and to restore backups so that database contents is consistent with files in file system. If you use BLOBs, you get it for free, database makes sure that you have consistent state at any time.

  1. Permissions

You can define different user roles with different permissions, e.g. one role allows user to read and modify data, other role allows only to read data, third role allows to read data but only from several tables, not from all available tables in database. If you use file system, it will be hard to implement such permission system. It will be hard to make sure that only users that have particular role have access to the files. If you use BLOBs, you get permission system for free.

2
  • This might be a better approach for some use cases. And might not be for other use cases. A lot has to do with resource availability for your database server - storage, performance, administration, etc. Also application access patterns to the file data - maybe it is better for the apps using the data to not go through the database to get the file contents. There isn't a single "right" way.
    – davidbak
    Mar 5, 2021 at 1:05
  • @davidbak: Sure. I pointed to some factors that can be important to make a decision. If transactions, consistence, backups, permissions are not important, other approach may be better.
    – mentallurg
    Mar 5, 2021 at 4:07
1

I strongly agree with Mentallurg's assessment – if you can do it.

If for whatever reason you need to stick with files, then I can actually think of several possibilities:

  • A database hook deletes the file.
  • Business logic deletes the file.
  • A periodic "job" is run, e.g. by crontab, which trolls through the image directory and queries for each filename to see if it is referenced anymore. If not, it deletes the file. "Lazy deletes."

And, since it probably really doesn't matter if a disk file is still hanging around ... (you're not running out of disk space, are you?) ... maybe this third, "lazy" strategy has merit. "Yes, the file ought to be cleaned up, but maybe it really doesn't matter exactly when?"

1
  • If the business logic is a long-running service it could actually launch the cleanup itself. This also makes it easier to code as you have access to all settings and structures.
    – jaskij
    Mar 5, 2021 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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