How to restrict image size to save in PostgreSQL database

lincoln

I have a simple question, it is regarding the handling of images, it is advisable to save the images in the database or on the computer.
in this moment I am saving the images in a table, but I have noticed that the size increases enormously.

and on the other hand if there would be a way to restrict the size of the image to save it in the database, I am using PostQgresql.

here I upload the image

void NuevaEstMonitoreoDialog::on_btnFoto1_clicked()
{
  QString fileName=QFileDialog::getOpenFileName(this,"upload an image.",QDir::currentPath(),
                                                  "Imagenes (*.jpg *.jpeg *.png *.bmp)");
  if(fileName.isEmpty())
    return;
  QFileInfo info(fileName);
  if((info.size()/1024)>2048){
    QMessageBox::critical(this,qApp->applicationName(),
                          "The file you are trying to save is very large.\n"
                          "You can save files with a maximum size of 2 MB." );
    return;
  }


  QFile file(fileName);
  if(!file.open(QFile::ReadOnly)){
    QMessageBox::critical(this,qApp->applicationName(),
                          "Error opening file.\n"+file.errorString());
    return;
  }
  ui->txtPath1->setText(fileName);
  imagen_1=file.readAll();
  file.close();
  file.flush();
 

}

ChrisW67

The image stored in the database should only be a small amount larger than the image on disk. In PostgreSql that is four bytes + file size plus the natural overheads of storing any data in a DB.

The decision comes down to exactly what you need and a lot of trade-offs. For a store of tens of images the answers will be different than for a store of a million. For a store that changes frequently the answer might be different than for one that is slowly-growing or infrequently changed.

Maintaining an image store in database ensures that metadata in the database that refers to it is always captured in a backup of the database. Storing the image on-disk with metadata in-database opens the possibility of database backup and image store backup not aligning.

Maintaining an image store in database can:

Allow use of database replication to ensures all sites are in-sync
Allow use of the database security mechanisms
Make the database file sizes harder to deal with on an day-to-day basis.

Storing the image on-disk allows other processes direct access, which is not possible if the image is in the database. For example, when you are also going to serve the image through a web server.

Storing many thousands of files on-disk can impose OS limitations that require addressing. The file uploaded may have a file name that is invalid locally, two images from different sources can have the same name, or the file system may impose a size or performance limit on directory sizes.

https://wiki.postgresql.org/wiki/BinaryFilesInDB

lincoln

Thanks for the answer, I have decided to save only the paths of the images in the database and the images in a folder on the hard disk, it seems better to me that way.

Kent-Dorfman

I generally store blobs as regular files and register them by ID or filepath in postgresql. I'm not totally happy with how postgresql stores blobs. Of course this limits access to those blobs to local connections or cases where the filesystem is shared.