Thread: blob storage
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?
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.
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/
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
On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe wrote: > 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. 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.
On Tue, 2005-04-26 at 16:42, Alvaro Herrera wrote: > On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe wrote: > > 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. > > 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... :)
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> On Tue, Apr 26, 2005 at 03:41:28PM -0500, Scott Marlowe 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.
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