CODEWITHSUNDEEP
databasepostgresqldatabase-designrelational-database
Resquiens
Resquiens

Reputation: 363

File storage system for a Postgresql database

I currently have a database which needs to store a lot of files. However, I also store information about specific files. I want to be able to store the files alongside the database which contains this metadata, and I am wondering how best to do this. The files are auto-generated and are in a hierarchical structure which is suited to file systems.

The original idea was to store the path to the root directory of the file system containing all the files, and then reference the files relative to this (e.g. ../file_group_1/file_1). In looking into this, it is difficult to find a way to store the files in this file system without say, running a separate server alongside the database which manages the filesystem.

I have looked into the Large Objects type in Postgresql, but I'm also concerned about the security implications. Is there a better solution to this?

Upvotes: 1

Views: 4607

Answers (2)

JERRY
JERRY

Reputation: 1173

For making best file system. I suggest to use folder and document hierarchy.
Document table will have reference of entity table and parent_doc_id for hierarchy logic. you should use Recursive CTE to get document tree as required.
In file system you can use path with document refrence.
i.e
entity => 1001
Document 1 => 1002
Document 2 => 1003

I suggest to use integer path in file system to avoid duplicate filename overlapping.
for document 1: 1001\1002
for document 2: 1001\1003
Actual file name and path you can store in table for refrence.

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 247063

It is often better to store files outside the database, because the access is faster and the database size remains small. The down side is that your application will have to manage consistency between the database and the file system in the face of crashes, aborted transactions etc.

I'd store the complete path of the file with the metadata; splitting it up doesn't save a lot and will make queries more complicated.

To maintain consistency between the database and the file system, you could always write the file first and never delete files except during special garbage collection runs when you can also identify and remove orphans.

If files are stored in the database, bytea is better than large objects unless the files are very large or need to be read and written in parts.

Upvotes: 3

Related Questions