Originally planned to store files directly in the database, but as it turned out this is not the best idea. I read that only links to files should be stored in the database. From this several questions follow: 1) What type of field is it more expedient to use? 2) How to associate files with a database, and accordingly save the link and tighten the file?

  • one
    There is no fundamental ban on storing the contents of files directly in the database (by the way, where did you read that this is impossible / wrong?) The appropriateness of this approach depends on many circumstances, including file sizes and the nature of the content. Specify which files are involved in your case. - cridnirk
  • @cridnirk High Resolution Raster - Abraham
  • In this case, the storage in the form of external files is fully justified. - cridnirk
  • @cridnirk when receiving the content of such a file, there always appears an extra RTT + dirty trick from the side of the proxying language, if it doesn’t have a thread processing point (the entire file has to be loaded into the RAM). Therefore, this is a no-no approach, which is disabled by default and can only be allowed if necessary. - etki

1 answer 1

The database does not usually store a link to the file, but simply its ID, if you need to store the name - in mysql - varchar (255), in postgres - analogs.

since you do not need to store the entire path to the file, you can limit the field to the length of the file name. And if you store just an idi file - well ... you just make a numeric field long enough to store millions or billions of files

Your code accordingly inserts the path to the directory and the ID / file name in the right place.

or, if you need to keep the names of files in secret so that the user doesn’t go through the files or guess the names, store the hash, or to create unique IDs for the session, pass the hash to the script that reads the file and gives it to the browser, with the link from one user will not work for another.

@cridnirk is right to store or not to store files in the database - depends on the project. On the hayload, as a rule, not worth it. For compact small sites, especially if you do not want to deal with the storage of files, paths, but simply throwing the database back and forth is possible. For example, you do not have the time and effort to establish normal backups, etc., but there are several DB servers — you have replicated — and you have ready backup files. (wildly crutch, IMHO, but for all the different solutions)

Not relevant to the question, but supplementing on demand from the comments: Lags that lag behind at different intervals can be made to recover: one replica lags by 15 minutes / and another by 24 hours - gives you some freedom of maneuvers in case of catastrophic delete / drop, etc. .

The presence of the status, created, updated fields allows, as a rule, not to access these replicas, since in the correct architecture, entries are deleted only manually / automatically after a year or more. Instead of deletion, the status is deleted, the updated field is entered, so you can easily find the changed entries. Scripts are not given the right to delete.

  • Replication is by no means a backup! - Fine
  • @ Small sometimes it is used this way, and it works to restore the database, it is not only load balancing. - strangeqargo
  • You can take a backup from a replica, a replica can even be nominally behind the master by a specified amount, a replica can do a lot of things, but it cannot be a backup. Hot stanby for a quick recovery when the primary database drops - yes, backup - no. And if there is so much data on the project that there is no place to backup the database, then obviously the files will not be stored in it, but in something more suitable. - Fine
  • if there is so much data on the project that there is no place to back up the database or files, then this is not a project, but some kind of garbage. - strangeqargo 7:57 pm
  • That's it. Therefore, it is not necessary to write that replication can be replaced by backups. It is impossible. Backups are required to be made regularly and regularly and the possibility of recovering from this backup should be checked. - Fine