Thread: slow insert speeds with bytea

On 12/1/19 9:59 AM, Alex O'Ree wrote:
> Is there anything I can to increase insert speeds for bytea? Currently 
> running postgres 9.6.15
> 
> I have a few tables without a bytea and a few with bytea. There is a 
> large performance difference with inserts between the two. I'm inserting 
> a byte[] that's usually less than 1MB on content. The content itself is 
> actually just utf8 string data.
> 
> For the non-bytea table, inserts can be as high as 40k rows/sec, whereas 
> the bytea table is closer to 4k/sec or less.
> 
> If this is just a limitation of postgres, then that's fine but the 
> performance delta is so significant that i feel like i'm missing something

It would help to have more information:

1) The schema of the table e.g. the output of \d in psql.

2) The actual INSERT query.

3) An EXPLAIN ANALYZE of the INSERT query.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 01/12/2019 17:59, Alex O'Ree wrote:
> Is there anything I can to increase insert speeds for bytea? Currently 
> running postgres 9.6.15
>
> I have a few tables without a bytea and a few with bytea. There is a 
> large performance difference with inserts between the two. I'm 
> inserting a byte[] that's usually less than 1MB on content. The 
> content itself is actually just utf8 string data.
>
> For the non-bytea table, inserts can be as high as 40k rows/sec, 
> whereas the bytea table is closer to 4k/sec or less.

4k 1MB rows/sec would be 4GB a second. It would need to be a fairly 
decent IO system to manage that speed, let alone ten times that.

What's the typical row size of the tables with bytea fields vs the other 
tables?

What are your IO stats during the two sorts of insert?

Cheers,
   Steve