Thanks Tom Lane for your answer
Same issue for pg_dump and LOB was also reported in https://postgrespro.com/list/thread-id/2211186
Concerning the 25% waste of space we experienced with LOB:
We are using LOB because we save jpeg2000 images into the DB.
We display them as thumbnails in a 6x10 multi-viewer (60 images displayed at a time) that the user can scroll to next ones.
When retrieving images into the multi-viewer, we only read the first 30% of the jpeg2000.
Even if jpeg2000 images are relatively small (about 20MB), retrieving thousands of images partially greatly speedup the display.
src/include/storage/large_object.h:
/*
* Each "page" (tuple) of a large object can hold this much data
*
* We could set this as high as BLCKSZ less some overhead, but it seems
* better to make it a smaller value, so that not as much space is used
* up when a page-tuple is updated. Note that the value is deliberately
* chosen large enough to trigger the tuple toaster, so that we will
* attempt to compress page tuples in-line. (But they won't be moved off
* unless the user creates a toast-table for pg_largeobject...)
*
* Also, it seems to be a smart move to make the page size be a power of 2,
* since clients will often be written to send data in power-of-2 blocks.
* This avoids unnecessary tuple updates caused by partial-page writes.
*
* NB: Changing LOBLKSIZE requires an initdb.
*/
#define LOBLKSIZE (BLCKSZ / 4)
Here is my understanding, please correct me if I am wrong.
If the data is compressible, each 2k (2048) uncompressed pieces should be compressed to pieces smaller than 2000 bytes, so that 4 rows and more can fit in 8k data block.
In our case as jpeg2000 images are already compressed, the 2K pieces remain 2048 bytes after LO compression and only 3 pieces can fit in a block.
Can we change the LOBLKSIZE to 2000 bytes to fit 4 rows in a block as follow?
#define LOBLKSIZE 2000
Must the LOBLKSIZE be a power-of-2?
Is there any optimization expecting a power-of-2 value?
Thank you.
Same issue for pg_dump and LOB was also reported in https://postgrespro.com/list/thread-id/2211186
Concerning the 25% waste of space we experienced with LOB:
We are using LOB because we save jpeg2000 images into the DB.
We display them as thumbnails in a 6x10 multi-viewer (60 images displayed at a time) that the user can scroll to next ones.
When retrieving images into the multi-viewer, we only read the first 30% of the jpeg2000.
Even if jpeg2000 images are relatively small (about 20MB), retrieving thousands of images partially greatly speedup the display.
src/include/storage/large_object.h:
/*
* Each "page" (tuple) of a large object can hold this much data
*
* We could set this as high as BLCKSZ less some overhead, but it seems
* better to make it a smaller value, so that not as much space is used
* up when a page-tuple is updated. Note that the value is deliberately
* chosen large enough to trigger the tuple toaster, so that we will
* attempt to compress page tuples in-line. (But they won't be moved off
* unless the user creates a toast-table for pg_largeobject...)
*
* Also, it seems to be a smart move to make the page size be a power of 2,
* since clients will often be written to send data in power-of-2 blocks.
* This avoids unnecessary tuple updates caused by partial-page writes.
*
* NB: Changing LOBLKSIZE requires an initdb.
*/
#define LOBLKSIZE (BLCKSZ / 4)
Here is my understanding, please correct me if I am wrong.
If the data is compressible, each 2k (2048) uncompressed pieces should be compressed to pieces smaller than 2000 bytes, so that 4 rows and more can fit in 8k data block.
In our case as jpeg2000 images are already compressed, the 2K pieces remain 2048 bytes after LO compression and only 3 pieces can fit in a block.
Can we change the LOBLKSIZE to 2000 bytes to fit 4 rows in a block as follow?
#define LOBLKSIZE 2000
Must the LOBLKSIZE be a power-of-2?
Is there any optimization expecting a power-of-2 value?
Thank you.
Jean-Marc Lessard
Administrateur de base de données / Database Administrator
Ultra Electronics Forensic Technology Inc.
T +1 514 489 4247 x4164
www.ultra-forensictechnology.com
