pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBLOBs vs BYTEA
4+ messages / 4 participants
[nested] [flat]
* BLOBs vs BYTEA
@ 2005-02-01 02:26 Sam Adams <samadams@myfastmail.com>
2005-02-01 03:51 ` Re: BLOBs vs BYTEA Dennis Sacks <dennis@illusions.com>
0 siblings, 1 reply; 4+ messages in thread
From: Sam Adams @ 2005-02-01 02:26 UTC (permalink / raw)
To: pgsql-sql
Hi,
Apologies if I've already sent a post asking this but I'm not sure if it
actually went through last time as it doesn't seem to be in my sent
items box.
Anyway, I was wondering which would be a better way to store a large
amount of files each a few megabytes in size. There could be hundreds of
thousands of files altogether. If stored as BYTEAs this would put them
all in a single table. Would this effect performance considerablely? I
assume if there were thousands then it would. But if the data is stored
as BLOBs then aren't they store inside the database just in another
table? Wouldn't this also be undesirable. Would it be better to store
them normally on the file system and just provide the path and file name
in the database. Obviously this wont provide any security or backup but
would it make sense to do it this way with such a large amount of data?
Thanks.
-Sam
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BLOBs vs BYTEA
2005-02-01 02:26 BLOBs vs BYTEA Sam Adams <samadams@myfastmail.com>
@ 2005-02-01 03:51 ` Dennis Sacks <dennis@illusions.com>
2005-02-01 08:04 ` Re: BLOBs vs BYTEA Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dennis Sacks @ 2005-02-01 03:51 UTC (permalink / raw)
To: Sam Adams <samadams@myfastmail.com>; +Cc: pgsql-sql
Sam Adams wrote:
>Anyway, I was wondering which would be a better way to store a large
>amount of files each a few megabytes in size. There could be hundreds of
>thousands of files altogether. If stored as BYTEAs this would put them
>all in a single table. Would this effect performance considerablely? I
>assume if there were thousands then it would. But if the data is stored
>as BLOBs then aren't they store inside the database just in another
>table? Wouldn't this also be undesirable. Would it be better to store
>them normally on the file system and just provide the path and file name
>in the database. Obviously this wont provide any security or backup but
>would it make sense to do it this way with such a large amount of data?
>
>
This is one of those age old debates. I've done both, and I'll tell you
that in my experience it is less of a headache to store the files in the
filesystem (which is what filesystems are designed for) and store the
metadata in the database.
Dennis Sacks
dennis@illusions.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BLOBs vs BYTEA
2005-02-01 02:26 BLOBs vs BYTEA Sam Adams <samadams@myfastmail.com>
2005-02-01 03:51 ` Re: BLOBs vs BYTEA Dennis Sacks <dennis@illusions.com>
@ 2005-02-01 08:04 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-02-01 15:01 ` Re: BLOBs vs BYTEA Karl Denninger <karl@denninger.net>
0 siblings, 1 reply; 4+ messages in thread
From: Achilleus Mantzios @ 2005-02-01 08:04 UTC (permalink / raw)
To: Dennis Sacks <dennis@illusions.com>; +Cc: Sam Adams <samadams@myfastmail.com>; pgsql-sql
O Dennis Sacks Ýãñáøå óôéò Jan 31, 2005 :
> Sam Adams wrote:
>
> >Anyway, I was wondering which would be a better way to store a large
> >amount of files each a few megabytes in size. There could be hundreds of
> >thousands of files altogether. If stored as BYTEAs this would put them
> >all in a single table. Would this effect performance considerablely? I
> >assume if there were thousands then it would. But if the data is stored
> >as BLOBs then aren't they store inside the database just in another
> >table? Wouldn't this also be undesirable. Would it be better to store
> >them normally on the file system and just provide the path and file name
> >in the database. Obviously this wont provide any security or backup but
> >would it make sense to do it this way with such a large amount of data?
> >
> >
> This is one of those age old debates. I've done both, and I'll tell you
> that in my experience it is less of a headache to store the files in the
> filesystem (which is what filesystems are designed for) and store the
> metadata in the database.
I think if you are doing replication or write in java,
you'll be much happier with bytea.
>
> Dennis Sacks
> dennis@illusions.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
-Achilleus
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BLOBs vs BYTEA
2005-02-01 02:26 BLOBs vs BYTEA Sam Adams <samadams@myfastmail.com>
2005-02-01 03:51 ` Re: BLOBs vs BYTEA Dennis Sacks <dennis@illusions.com>
2005-02-01 08:04 ` Re: BLOBs vs BYTEA Achilleus Mantzios <achill@matrix.gatewaynet.com>
@ 2005-02-01 15:01 ` Karl Denninger <karl@denninger.net>
0 siblings, 0 replies; 4+ messages in thread
From: Karl Denninger @ 2005-02-01 15:01 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; Dennis Sacks <dennis@illusions.com>; +Cc: Sam Adams <samadams@myfastmail.com>; pgsql-sql
On Tue, Feb 01, 2005 at 10:04:45AM +0200, Achilleus Mantzios wrote:
> O Dennis Sacks Ýãñáøå óôéò Jan 31, 2005 :
>
> > Sam Adams wrote:
> >
> > >Anyway, I was wondering which would be a better way to store a large
> > >amount of files each a few megabytes in size. There could be hundreds of
> > >thousands of files altogether. If stored as BYTEAs this would put them
> > >all in a single table. Would this effect performance considerablely? I
> > >assume if there were thousands then it would. But if the data is stored
> > >as BLOBs then aren't they store inside the database just in another
> > >table? Wouldn't this also be undesirable. Would it be better to store
> > >them normally on the file system and just provide the path and file name
> > >in the database. Obviously this wont provide any security or backup but
> > >would it make sense to do it this way with such a large amount of data?
> > >
> > >
> > This is one of those age old debates. I've done both, and I'll tell you
> > that in my experience it is less of a headache to store the files in the
> > filesystem (which is what filesystems are designed for) and store the
> > metadata in the database.
>
> I think if you are doing replication or write in java,
> you'll be much happier with bytea.
I store objects that are referred to in the database in the database.
This requires some more work for binary objects in the application code,
but not much.
And, it insures that integrity is maintained. Otherwise you need a
"cleanup" routine to cover the possibility of a file missing metadata, or
metadata missing a file, along with all the replication/backup issues.
No thanks on those for me.
--
--
Karl Denninger (karl@denninger.net) Internet Consultant & Kids Rights Activist
http://www.denninger.net My home on the net - links to everything I do!
http://scubaforum.org Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.com Musings Of A Sentient Mind
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-02-01 15:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-01 02:26 BLOBs vs BYTEA Sam Adams <samadams@myfastmail.com>
2005-02-01 03:51 ` Dennis Sacks <dennis@illusions.com>
2005-02-01 08:04 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2005-02-01 15:01 ` Karl Denninger <karl@denninger.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox