I need to store user files in PostgreSQL but I can’t find information on this in the documentation. How do I upload files there? And how do I arrange access to them? In theory, each user can have files, respectively, should there be a table with paths? Right? How is this generally traditionally solved in the case of PostgreSQL?

  • one
    And why should the files be stored in the database? - Mike
  • @Mike and how to? I have no such condition what exactly in a DB, it seemed to me logical. And how do they usually do it? I'm just the first time I come up with database work, so forgive me for stupid questions, if that. - Pavel
  • 2
    In the database, you can store for example file paths, and the files themselves are simply on disk, it is usually easier. And there is no one absolutely correct way. there may already be nuances - Mike
  • @Mike In the database, you can store for example file paths, and the files themselves simply do this on a disk ? - Pavel
  • one
    Well, it depends on what happens to them. If you, for example, have a web application and files to download, then the nginx file will give the user much faster and with less overhead than a script that will drag data from the database and give to the user - Mike

2 answers 2

You can not find information in the documentation on how to store files in the database, because it is wrong.

You can store, but why?

A DBMS is just an abstraction over a set of files (tables, indexes, etc.). You make a query to a table, the DBMS moves the pointer in some file, puts some kind of locks to select a specific entry, writes this to the cache .. And now it becomes clear that the database is an extra layer for accessing files.

Files — when used for its intended purpose (to give data to a client) is always faster than storing in a database, because it requires fewer procedures to read it.

If you store files in a database, you fill in the cache that should be used for other requests. Over time, the database grows, processes that give files clog up a free pool, load and space increases, you need sharding and other unnecessary procedures.

To implement file storage, store the server address (if there are several of them) and the full path to the file on the server in a table and send the files via nginx.

PS Sometimes there is a rationale for storing files in the database, but unfortunately, this is a small percentage of common options.

    First of all, I completely agree that storing files in a database is a very specific task. In 99% of this thought only leads to complications. Save paths, names, metainfo and other in db, and the files themselves on FS, S3 or cloud. However, this is not the answer to your question.

    • How do I upload files there? lo_import
    • And how do I arrange access to them? lo_export
    • Should there be a table with paths? pg_largeobject
    • How is this generally traditionally solved in the case of PostgreSQL? read the documentation

    also see how to do this with psql and think again - do you need it?