Postgresql – Migrating BLOB in Oracle into PostgreSQL

blobmigrationoraclepostgresqlpostgresql-9.4

I use the data type BLOB in an Oracle table to store images and document files. Now for Postgres(9.4.5), I have two options, i.e., BYTEA or OID.

With consideration of passing the params (SAVING) from the Java side as follows:

DiskFileItemDeepy file  =  myFile;  
InputStream is = null;
long fileSize = 0;
if (file != null && file.getFileSize() > 0){ 
  is = file.getInputStream(); 
  fileSize = file.getFileSize(); 
  call.setBinaryStream(1, (InputStream)is,  (long)fileSize);
}
...
call.execute(); 

//When retrieve the data use:

java.sql.Blob blob = (Blob) resultSet.getBlob(tableColumnName);

For the purpose mentioned above, which Postgres data type is a better candidate for replacement of the BLOB, is it BYTEA or OID?

Best Answer

From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres.

bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB value). Which means that this is kept in the memory on the JVM while reading it. This is the same as with Oracle's BLOB type. From a JDBC point of view those two data types behave nearly the same.

The OID (or better: "large objects") isn't a real "data type". It is merely a pointer to the data stored in the file system. The advantage of using large objects is that you can properly stream the content from the server to the client, you don't need to load the entire binary data into the client's memory.

However dealing with large objects is much more complicated than dealing with bytea columns. You also need to manually clean up deleted large objects if you delete the corresponding row from the base table (something you do not need to do with bytea). Reading and writing of large objects is also much more complicated then dealing with bytea values.

As you are currently using a BLOB column, you apparently do have the memory on the client side to process the data, in that case to make transition as smooth as possible, I highly recommend to use bytea in Postgres.

Related Question