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
JMLessard wrote:
> What about updates where the bytea do not changed. Does a new copy of the
> bytea will be made in the toast table or new row will point to the original
> bytea?
> > https://www.postgresql.org/docs/current/storage-toast.html says:
> > The TOAST management code is triggered only
Thanks to Daniel Verite, nice answer, really helpful :)
It summarizes what I have read in the doc and blogs.
What about updates where the bytea do not changed. Does a new copy of the bytea
will be made in the toast table or new row will point to the original bytea?
> https://www.postgresql.org/do
Jean-Marc Lessard wrote:
> Another area where LOB hurts is the storage. LOB are broken and stored in 2K
> pieces.
> Due to the block header, only three 2k pieces fit in an 8k block wasting 25%
> of space (in fact pgstattuple reports ~ 20%).
Yes. bytea stored as TOAST is sliced into pieces
On 11/14/2018 11:14 AM, Jean-Marc Lessard wrote:
Adrien Nayrat wrote:
> With 17 million LO, it could eat lot of memory ;)
Yes it does.
I did several tests and here are my observations.
First memory settings are:
shared_buffers = 3GB
work_mem = 32Mb
maintenance_work_mem = 1GB
effective_cache_siz
Jean-Marc Lessard writes:
> Would you recommend bytea over LOB considering that the max LOB size is well
> bellow 1GB?
Yes, probably. The reason that pg_dump has trouble with lots of small
BLOBs is the 9.0-era decision to treat BLOBs as independent objects
having their own owners, privilege att
Adrien Nayrat wrote:
> With 17 million LO, it could eat lot of memory ;)
Yes it does.
I did several tests and here are my observations.
First memory settings are:
shared_buffers = 3GB
work_mem = 32Mb
maintenance_work_mem = 1GB
effective_cache_size = 9MB
bytea_output = 'escape'
The largest LO is
Hello,
On 11/10/18 12:49 AM, Jean-Marc Lessard wrote:
> The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of the
> space.
>
If I understand, you have 17 million Large Object?
I do not recall exactly and maybe I am wrong. But it seems pg_dump has to
allocate memory for each obje
On 11/10/18 2:46 PM, Ron wrote:
On 11/09/2018 05:49 PM, Jean-Marc Lessard wrote:
I am running PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by
gcc.exe (Rev5, Built by MSYS2 project) 4.9.2, 64-bit
on win2012 with 12Gb RAM
The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of
On 11/09/2018 05:49 PM, Jean-Marc Lessard wrote:
I am running PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by gcc.exe
(Rev5, Built by MSYS2 project) 4.9.2, 64-bit
on win2012 with 12Gb RAM
The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of the
space.
The pg_dump consum
I am running PostgreSQL 9.6.5 on x86_64-pc-mingw64, compiled by gcc.exe (Rev5,
Built by MSYS2 project) 4.9.2, 64-bit
on win2012 with 12Gb RAM
The dumped table is 0.5TB, 17 million rows and LOB uses about 99% of the space.
The pg_dump consumes the entire system memory and swap, then terminates with
On 07/04/2018 12:31 AM, David Rowley wrote:
On 4 July 2018 at 14:43, Andy Colson wrote:
I moved a physical box to a VM, and set its memory to 1Gig. Everything
runs fine except one backup:
/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
g_dump: Dumping the contents of table
On 4 July 2018 at 14:43, Andy Colson wrote:
> I moved a physical box to a VM, and set its memory to 1Gig. Everything
> runs fine except one backup:
>
>
> /pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
>
> g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() fail
On Tue, 3 Jul 2018 21:43:38 -0500, Andy Colson
wrote:
>Hi All,
>
>I moved a physical box to a VM, and set its memory to 1Gig. Everything
>runs fine except one backup:
>
>
>/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
>
>g_dump: Dumping the contents of table "ofrrds" failed:
On 07/03/2018 08:28 PM, Andy Colson wrote:
On 07/03/2018 10:21 PM, Adrian Klaver wrote:
On 07/03/2018 07:43 PM, Andy Colson wrote:
Hi All,
I moved a physical box to a VM, and set its memory to 1Gig. Everything
runs fine except one backup:
/pub/backup# pg_dump -Fc -U postgres -f wildfire.bac
On 07/03/2018 10:21 PM, Adrian Klaver wrote:
On 07/03/2018 07:43 PM, Andy Colson wrote:
Hi All,
I moved a physical box to a VM, and set its memory to 1Gig. Everything
runs fine except one backup:
/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
g_dump: Dumping the contents
On 07/03/2018 07:43 PM, Andy Colson wrote:
Hi All,
I moved a physical box to a VM, and set its memory to 1Gig. Everything
runs fine except one backup:
/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult()
faile
Hi All,
I moved a physical box to a VM, and set its memory to 1Gig. Everything
runs fine except one backup:
/pub/backup# pg_dump -Fc -U postgres -f wildfire.backup wildfirep
g_dump: Dumping the contents of table "ofrrds" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:
18 matches
Mail list logo
