Re: storing large files in database - performance

My apologies: I said I ran "this query" but failed to include the query. It was merely this:

SELECT "indexFile"."_id", "indexFile"."contents"
FROM "mySchema"."indexFiles" AS "indexFile"
WHERE "indexFile"."_id" = '591c609bb56d0849404e4720';

Eric

-----Original Message-----
From: Eric Hill [mailto:Eric(dot)Hill(at)jmp(dot)com]
Sent: Thursday, May 18, 2017 8:35 AM
To: Merlin Moncure <mmoncure(at)gmail(dot)com>; Thomas Kellerer <spam_eater(at)gmx(dot)net>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: storing large files in database - performance

I would be thrilled to get 76 MB per second, and it is comforting to know that we have that as a rough upper bound on performance. I've got work to do to figure out how to approach that upper bound from Node.js.

In the meantime, I've been looking at performance on the read side. For that, I can bypass all my Node.js layers and just run a query from pgAdmin 4. I ran this query, where indexFile.contents for the row in question is 25MB in size. The query itself took 4 seconds in pgAdmin 4. Better than the 12 seconds I'm getting in Node.js, but still on the order of 6MB per second, not 76. Do you suppose pgAdmin 4 and I are doing similarly inefficient things in querying bytea values?

Thanks,

Eric