I am trying to stream binary data into a bytea field. The process is quite simple:

loop until the end of the incoming stream
  UPDATE myTable SET data = data || $chunk WHERE id = myId
  • $chunk is a binding for the current stream chunk
  • The row (myId) already exists with NULL data
  • each chunk is about 64k
  • the final data is around 4MB

Everything is working properly expect that concatenating data become more and more slow as chunks are stored.

Since I need to keep my SQL quite portable, I cannot use "PostgreSQL Large Objects"

Is it possible to optimize this process ?

  • maybe by pre-allocating with with an empty data, and then use overlay() to place each chunk ?
  • 1
    Can't you assemble the chunks outside the database? I think Postgres has to rewrite the entire data for each chunk in this case, so you're asking it to do a large amount of unnecessary work. Feb 13, 2018 at 9:42
  • Unfortunately, due to memory limit reason, it is not possible to assemble chunks outside the database. Feb 13, 2018 at 13:46

1 Answer 1


Updating a row in a multiversion model like postgres means creating a second copy of that row with the new contents. Physically, there is no in-place update: UPDATE is similar to DELETE + INSERT the new contents.

So in the above loop, the first chunk, instead of being written once, is written N times, the second chunk is written N-1 times, the third N-2 times and so on.

To make it worse, all these writes have to go to the WAL files for journaling too, and all the intermediate versions of the rows will need be picked up by the autovacuum process to be eventually discarded.

Assuming that these chunks cannot be assembled on the client and must be streamed, it might help to do something like this:

CREATE TEMPORARY TABLE buffer(seq int default nextval('s'), chunk bytea);

-- buffer in temporary storage
   INSERT INTO buffer(chunk) VALUES ($chunk)

-- assemble in final storage
INSERT INTO permanent_table(data)
   SELECT string_agg(chunk,''::bytea order by seq) FROM buffer;


At least the chunks will be written only twice (once in buffer storage and once in final durable storage), and only once in the journal, as the temporary table won't be WAL-logged.

  • Assuming that we are okay with Postgres crashing meaning that we lose the streamed chunks up to that point, would making the buffer table additionally UNLOGGED be more performant? Feb 17 at 9:23
  • @MarioIshac: no because temporary tables are already unlogged by design. Feb 19 at 11:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.