Обсуждение: Use bytearray for blobs or not?
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
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
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
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 >> >> >> >> > >
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 largedata (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, sysmanagement, 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
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