Streaming Binary Data to bytea Field is Very Slow – PostgreSQL Optimization

byteaoptimizationpostgresql

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 ?

Best Answer

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 sequence s;
CREATE TEMPORARY TABLE buffer(seq int default nextval('s'), chunk bytea);

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

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

TRUNCATE (or DROP) TABLE 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.

Related Question