On 3/23/16 4:14 AM, Moreno Andreo wrote:
> The main goal is to be *quick*. A doctor with a patient on the other
> side of his desk does not want to wait, say, 30 seconds for a clinical
> record to open.
> Let me explain what is the main problem (actually there are 2 problems).
> 1. I'm handling health data, and sometines they store large images (say
> an hi-res image of an x-ray). When their team mates (spread all over the
> city, not in the same building) ask for that bitmap (that is, 20
> megabytes), surely it can't be cached (images are loaded only if
> requested by user) and searching a 35k rows, 22 GB table for the
> matching image should not be that fast, even with proper indexing
> (patient record number)
Why wouldn't that be fast? Unless the TOAST table for that particular
table is pretty fragmented, pulling up thumbnails should be very fast.
I'd expect it to be the cost of reading a few pages sequentially.
If you're mixing all your blobs together, then you might end up with a
problem. It might be worth partitioning the blob table based on the size
of what you're storing.
> 2. When I load patient list, their photo must be loaded as well, because
> when I click on the table row, a small preview is shown (including a
> small thumbnail of the patient's photo). Obviously I can't load all
> thumbs while loading the whole patient list (the list can be up to
> 4-5000 records and photo size is about 4-500kBytes, so it would be an
> enormous piece of data to be downloaded.
I would think a thumbnail would be 30-40k or less, not 500k. It sounds
like part of the problem is you should keep the thumbnails separate from
the high-res file. But really you should probably do that for
everything... I suspect there's parts of the UI when you want to display
a fairly low-res version of something like an xray, only pulling the raw
image if someone actually needs it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com