PostgreSQL Discussion - blob storage

Author

blob storage

Travis Harris

2005-04-26, 8:23 pm

I would like to use P* to store files. These files will probably
range from 500K to 2 MB in size and there will be thousands upon
thousands of them. I was wondering how P* stores blobs, if it is all
in one file, or if each blob is sored in it's own file. The reason
being, I know that windows has a 2 GB limit on files, and if they are
not stored as their own files, I'll hit my limit FAST... and it'll do
me no good... If this is going to be a problem, does anyone have any
suggestions?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

Scott Marlowe

2005-04-26, 8:23 pm

On Tue, 2005-04-26 at 15:30, Travis Harris wrote:
> I would like to use P* to store files. These files will probably
> range from 500K to 2 MB in size and there will be thousands upon
> thousands of them. I was wondering how P* stores blobs, if it is all
> in one file, or if each blob is sored in it's own file. The reason
> being, I know that windows has a 2 GB limit on files, and if they are
> not stored as their own files, I'll hit my limit FAST... and it'll do
> me no good... If this is going to be a problem, does anyone have any
> suggestions?

If you store them as large objects, they will each get their own file.

However, you can also store them as rows in a bytea field, and
postgresql will split the table every 1 gig or so automagically.

lo is generally faster but less "database like" and more like a file
system interface while bytea tends to have more overhead due to escaping
/ encoding needed to be done before storage and upon retrieval.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Joshua D. Drake

2005-04-26, 8:23 pm

Travis Harris wrote:
> I would like to use P* to store files. These files will probably
> range from 500K to 2 MB in size and there will be thousands upon
> thousands of them. I was wondering how P* stores blobs,

Either as bytea or a large object.

if it is all
> in one file, or if each blob is sored in it's own file. The reason
> being, I know that windows has a 2 GB limit on files,

PostgreSQL automatically splits its files into 1GB segments also I think
the 2GB limit is only on FAT32 not NTFS but I am not sure.

and if they are
> not stored as their own files, I'll hit my limit FAST... and it'll do
> me no good... If this is going to be a problem, does anyone have any
> suggestions?

bytea works well for small files as long as you are not going to do a :

select * from foo

where foo is going to return 500,000 records all with 500k files. If you
are going to do something like that use large objects intead.

Sincerely,
Joshua D. Drake
Command Prompt, Inc.

>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)

--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedication Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Rich Shepard

2005-04-26, 8:23 pm

On Tue, 26 Apr 2005, Travis Harris wrote:

> I would like to use P* to store files. These files will probably range
> from 500K to 2 MB in size and there will be thousands upon thousands of
> them. I was wondering how P* stores blobs, if it is all in one file, or if
> each blob is sored in it's own file. The reason being, I know that windows
> has a 2 GB limit on files, and if they are not stored as their own files,
> I'll hit my limit FAST... and it'll do me no good... If this is going to be
> a problem, does anyone have any suggestions?

Travis,

I'll assume that "P*" is IM for postgres, eh?

PostgreSQL-7.x on the 2.2.x kernel series has a limit of 2G. However, on
the 2.4.x kernels the limit is 2T; postgres-8.x on the 2.4.x kernels can
store 4T per table.

If that pinches your need, I'd love to be selling you storage solutions.
:-)

Rich

--
Dr. Richard B. Shepard, President
Applied Ecosystem Services, Inc. (TM)
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Alvaro Herrera

2005-04-26, 8:23 pm

On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe wrote:
> On Tue, 2005-04-26 at 15:30, Travis Harris wrote:
>
> If you store them as large objects, they will each get their own file.

Huh, no, they won't. They will be stored in the pg_largeobject table.
It's been quite a while since they are not stored in separate files;
though they keep the POSIX-filesystem-like semantics.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl> )
Syntax error: function hell() needs an argument.
Please choose what hell you want to involve.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Scott Marlowe

2005-04-26, 8:23 pm

On Tue, 2005-04-26 at 16:42, Alvaro Herrera wrote:
> On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe wrote:
>
> Huh, no, they won't. They will be stored in the pg_largeobject table.
> It's been quite a while since they are not stored in separate files;
> though they keep the POSIX-filesystem-like semantics.

Oh, I guess it's been a few years since I last played with large
objects. Sorry for the misinformation.

I guess you can tell I prefer bytea nowadays... :)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Tom Lane

2005-04-26, 8:23 pm

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe wrote:
[color=darkred]
> Huh, no, they won't. They will be stored in the pg_largeobject table.
> It's been quite a while since they are not stored in separate files;
> though they keep the POSIX-filesystem-like semantics.

But in any case, the platform limit on file size is irrelevant because
we split tables into 1GB-size files. The effective limit is 16TB IIRC
(see the FAQ for the correct number).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links