Menu
I want to store a serialized object somewhere and after looking around, realize there are a couple of ways to do this.
I'm looking at storing the file somewhere in the file system and storing the file location as entries in a table.
Assuming the files are around 100-500Kb, is this a suitable way of doing so? Or using bytea or blob a better solution?
996
PostgreSQL does not have the BLOB data type. However, you can use the bytea data type for storing the binary string. We will create a new table named company_files to store the binary string. We will store the content of a file in the file_data column.
Blob (Binary large object) is an Oracle data type that is used to store binary data like contents of a file or information like audio, video, and images. PostgreSQL does not have the Blob data type directly, but we can work with it using the methods below.
PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.
Large Objects using BLOB/CLOB In Postgres, these data types are stored in a single system table called 'pg_largeobject' which has to be accessed via identifiers of data type OID which are stored with the table using BLOB/CLOB data. The catalog pg_largeobject holds the data making up “large objects”.
Small objects (which 500Kb) qualifies as will work fine as bytea. The main limitation on the size is that you will need to materialize the whole field in memory first at the server and then at the client (there are ways aorund it, but they are limited). For half a megabyte, that shouldn't be an issue, but if you start storing much larger objects it may need some consideration.
Storing it in the filesystem and storing the location (or storing it named by a surrogate primary key from a SERIAL sequence) is a good way to deal with larger files, and you can of course apply it here as well. The downsides are that you loose transactional integrity (you can't be sure that both the filesystem and the database was updated, so you need to implement some kind of verification tool to run regularly to compare these), and probably more importantly you can't get consistent backups (what if the file was backed up and not the db record? or the other way around) There are methods to deal with this as well, of course, but it rapidly becomes much more complex than keeping the data stored in the table.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
