1

I am trying to insert all the data at once using the following:

userModel.setServiceData(gson.toJson(sortedResponse).getBytes());

This is giving Out of Memory for the huge data.

So I am trying to add data in chunks to the postgres database using something like:

byte[] chunkedMessage = {};

for (Iterator<Message> iterator = sortedResponse.iterator();  iterator.hasNext();) {
    Message message = (Message) iterator.next();
    chunkedMessage = gson.toJson(message).getBytes();
    userModel.setServiceData(chunkedMessage);
}

Obviously, this will end up storing the last chunk but not all chunks collectively.

I am using hibernate so thinking if there is any easy way with hibernate query or may be as easy as something in the above code.

sjain
  • 23,126
  • 28
  • 107
  • 185
  • So you have a JSON column in your PostgreSQL database and you want to insert a record with such large content inside this column that the JVM crashes? – Adam Siemion Dec 23 '15 at 10:50
  • I have a `bytea` column and when I get the response from remote API I am storing that response into this column for tracking purposes. This response can be huge which can cause OOM if stored at once. – sjain Dec 23 '15 at 10:52
  • This solution can never scale. You get OOM error for a single user? Good luck with two or more. – duffymo Dec 23 '15 at 11:19

1 Answers1

0

Do not use arrays (byte[]), as it needs to allocate the memory for the complete data you want to insert into the database and switch to streams. Change the type of userModel.serviceData from byte[] to java.sql.Blob, which allows to create a new Blob instance from an InputStream or Reader:

Blob blob = Hibernate.getLobCreator(session).createBlob(stream, size);
Adam Siemion
  • 15,569
  • 7
  • 58
  • 92
  • I will look into this solution. However, I used `bytea` after reading this: http://stackoverflow.com/questions/4386030/how-to-use-blob-datatype-in-postgres – sjain Dec 23 '15 at 15:11
  • By the way, what is the datatype to store BLOB in postgres? – sjain Dec 23 '15 at 15:14