Thread: How to get more than 2^32 BLOBs

How to get more than 2^32 BLOBs

From
Donato Marrazzo
Date:
Hi all,

according this page [1], large object are limited to the OID range of 2^32 (since it's a 4 bytes number).
I'm working on a use case were there are many tables with blobs (on average not so large 32KB).
I foresee that in 2-3 years time frame, the limit of overall blobs will be breached: more than 2^32 blobs.

- Is there a way to change the OID limit?
- Should we switch to a bytea implementation?
- Are there any drawback of bytea except the maximum space?

Thank you!

All the best,
Donato

Re: How to get more than 2^32 BLOBs

From
Laurenz Albe
Date:
On Wed, 2020-04-08 at 11:15 +0200, Donato Marrazzo wrote:
> according this page [1], large object are limited to the OID range of 2^32 (since it's a 4 bytes number).
> I'm working on a use case were there are many tables with blobs (on average not so large 32KB).
> I foresee that in 2-3 years time frame, the limit of overall blobs will be breached: more than 2^32 blobs.
> 
> - Is there a way to change the OID limit?
> - Should we switch to a bytea implementation?
> - Are there any drawback of bytea except the maximum space?

Don't use large objects.  They are only useful if
1) you have files larger than 1GB or
2) you need to stream writes

There are no such limitations if you use the "bytea" data type, and it is
much simpler to handle at the same time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: How to get more than 2^32 BLOBs

From
Donato Marrazzo
Date:
Hi Laurenz,

thank you for your reply.
Are you aware of any performance drawback?

Il giorno mer 8 apr 2020 alle ore 12:06 Laurenz Albe <laurenz.albe@cybertec.at> ha scritto:
On Wed, 2020-04-08 at 11:15 +0200, Donato Marrazzo wrote:
> according this page [1], large object are limited to the OID range of 2^32 (since it's a 4 bytes number).
> I'm working on a use case were there are many tables with blobs (on average not so large 32KB).
> I foresee that in 2-3 years time frame, the limit of overall blobs will be breached: more than 2^32 blobs.
>
> - Is there a way to change the OID limit?
> - Should we switch to a bytea implementation?
> - Are there any drawback of bytea except the maximum space?

Don't use large objects.  They are only useful if
1) you have files larger than 1GB or
2) you need to stream writes

There are no such limitations if you use the "bytea" data type, and it is
much simpler to handle at the same time.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com



--
Ing. Donato Marrazzo
Via Pia Nalli, 101
00134 Roma
Cell. 335-7446273

Re: How to get more than 2^32 BLOBs

From
Christophe Courtois
Date:
Hi,

Le 08/04/2020 à 12:12, Donato Marrazzo a écrit :
> Hi Laurenz,
> thank you for your reply.
> Are you aware of any performance drawback?

We had a customer with millions of small Large Objects, in part because
his application forgot to unlink them.

As a consequence, pg_dump was using huge amounts of memory, making a
backup impossible. It was with PG 9.5, I don't think the situation
improved since.

> Il giorno mer 8 apr 2020 alle ore 12:06 Laurenz Albe
> <laurenz.albe@cybertec.at <mailto:laurenz.albe@cybertec.at>> ha scritto:
...
>     > I'm working on a use case were there are many tables with blobs
>     (on average not so large 32KB).
>     > I foresee that in 2-3 years time frame, the limit of overall blobs
>     will be breached: more than 2^32 blobs.
>     > - Is there a way to change the OID limit?
>     > - Should we switch to a bytea implementation?
>     > - Are there any drawback of bytea except the maximum space?

>     Don't use large objects.  They are only useful if
>     1) you have files larger than 1GB or
>     2) you need to stream writes
> 
>     There are no such limitations if you use the "bytea" data type, and
>     it is much simpler to handle at the same time.


+1


-- 
Christophe Courtois
Consultant Dalibo
https://dalibo.com/



Re: How to get more than 2^32 BLOBs

From
Laurenz Albe
Date:
On Wed, 2020-04-08 at 12:12 +0200, Donato Marrazzo wrote:
> 
> Il giorno mer 8 apr 2020 alle ore 12:06 Laurenz Albe <laurenz.albe@cybertec.at> ha scritto:
> > On Wed, 2020-04-08 at 11:15 +0200, Donato Marrazzo wrote:
> > > according this page [1], large object are limited to the OID range of 2^32 (since it's a 4 bytes number).
> > > I'm working on a use case were there are many tables with blobs (on average not so large 32KB).
> > > I foresee that in 2-3 years time frame, the limit of overall blobs will be breached: more than 2^32 blobs.
> > > 
> > > - Is there a way to change the OID limit?
> > > - Should we switch to a bytea implementation?
> > > - Are there any drawback of bytea except the maximum space?
> > 
> > Don't use large objects.  They are only useful if
> > 1) you have files larger than 1GB or
> > 2) you need to stream writes
> > 
> > There are no such limitations if you use the "bytea" data type, and it is
> > much simpler to handle at the same time.
> 
> Are you aware of any performance drawback?

No; in fact, "bytea" performs better.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: How to get more than 2^32 BLOBs

From
Donato Marrazzo
Date:

Hi Laurenz and Christophe,

thank you very much for your great advises.