Use bytearray for blobs or not?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Use bytearray for blobs or not?

Thomas Güttler
Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.

It works.

In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG issue.

What do you think?

Which alternatives could be useful?

Regards,
   Thomas




--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Reply | Threaded
Open this post in threaded view
|

Re: Use bytearray for blobs or not?

Achilleas Mantzios
On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:

> Some months ago I wrote a little application with Python+Django which stores
> blob data in bytearrays.
>
> It works.
>
> In the future there will be a lot more traffic, and I am unsure
> if this is really a good solution. I know this is more FUD than
> concrete PG issue.
>
> What do you think?

Performance (at least for JDBC) is known to be better with blobs.
However, with bytea life is just easier for many reasons (backups, logical replication, other types of replication, sys management, etc).

>
> Which alternatives could be useful?
>
> Regards,
>   Thomas
>
>
>
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt


Reply | Threaded
Open this post in threaded view
|

Re: Use bytearray for blobs or not?

Thomas Güttler


Am 04.01.19 um 12:48 schrieb Achilleas Mantzios:

> On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
>> Some months ago I wrote a little application with Python+Django which stores
>> blob data in bytearrays.
>>
>> It works.
>>
>> In the future there will be a lot more traffic, and I am unsure
>> if this is really a good solution. I know this is more FUD than
>> concrete PG issue.
>>
>> What do you think?
>
> Performance (at least for JDBC) is known to be better with blobs.
> However, with bytea life is just easier for many reasons (backups, logical replication, other types of replication, sys
> management, etc).


I could switch to a s3 like storage server, too. Up to now, this would only be
some lines of code. I could store the s3 IDs in postgres.




--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

lup
Reply | Threaded
Open this post in threaded view
|

Re: Use bytearray for blobs or not?

lup
In reply to this post by Achilleas Mantzios

On 1/4/19 4:48 AM, Achilleas Mantzios wrote:

> On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
>> Some months ago I wrote a little application with Python+Django which
>> stores
>> blob data in bytearrays.
>>
>> It works.
>>
>> In the future there will be a lot more traffic, and I am unsure
>> if this is really a good solution. I know this is more FUD than
>> concrete PG issue.
>>
>> What do you think?
>
> Performance (at least for JDBC) is known to be better with blobs.

Do you have any details on which part is slower with bytea? Original
insert, read back to client, general (p)sql access.  I'm moving towards
bytea but still have time to change my mind if I cannot afford the
performance hit.

> However, with bytea life is just easier for many reasons (backups,
> logical replication, other types of replication, sys management, etc).
Yes, and in my case I even get a cheap compression from the original file.

>
>>
>> Which alternatives could be useful?
>>
>> Regards,
>>   Thomas
>>
>>
>>
>>
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Use bytearray for blobs or not?

Achilleas Mantzios
On 4/1/19 6:02 μ.μ., Rob Sargent wrote:

>
> On 1/4/19 4:48 AM, Achilleas Mantzios wrote:
>> On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:
>>> Some months ago I wrote a little application with Python+Django which stores
>>> blob data in bytearrays.
>>>
>>> It works.
>>>
>>> In the future there will be a lot more traffic, and I am unsure
>>> if this is really a good solution. I know this is more FUD than
>>> concrete PG issue.
>>>
>>> What do you think?
>>
>> Performance (at least for JDBC) is known to be better with blobs.
>
> Do you have any details on which part is slower with bytea? Original insert, read back to client, general (p)sql access.  I'm moving towards bytea but still have time to change my mind if I cannot
> afford the performance hit.
No since I never used them (blobs) in production. Basically the java driver does not support streaming. So for very large data (e.g. videos, movies, etc) people use blobs.

>
>> However, with bytea life is just easier for many reasons (backups, logical replication, other types of replication, sys management, etc).
> Yes, and in my case I even get a cheap compression from the original file.
>>
>>>
>>> Which alternatives could be useful?
>>>
>>> Regards,
>>>   Thomas
>>>
>>>
>>>
>>>
>>
>>
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt


Reply | Threaded
Open this post in threaded view
|

Re: Use bytearray for blobs or not?

Thomas Güttler
In reply to this post by Thomas Güttler
I came across this article, and now I think blob in s3 in s3-object-id in PostgreSQL
are the best solution for me:

https://wiki.postgresql.org/wiki/BinaryFilesInDB

I hope the wiki page is still valid.

Regards,
   Thomas


Am 04.01.19 um 12:41 schrieb Thomas Güttler:

> Some months ago I wrote a little application with Python+Django which stores
> blob data in bytearrays.
>
> It works.
>
> In the future there will be a lot more traffic, and I am unsure
> if this is really a good solution. I know this is more FUD than
> concrete PG issue.
>
> What do you think?
>
> Which alternatives could be useful?
>
> Regards,
>    Thomas
>
>
>
>

--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines