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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
