Re: GSoC proposal for pgAdmin 4 bytea support

Hi

On Mon, Apr 1, 2019 at 3:12 AM Haoran Yu <haleyyew(at)gmail(dot)com> wrote:

> Dear PostgreSQL community,
>
> I have submitted a proposal for the project pgAdmin 4 bytea support. The
> project discusses storing media content (images, audio, video) as bytea.
> However, I have a quick question. What does bytea data look like typically
> when storing media content? What I had in mind is, media contents that uses
> MIME type, which are rendered as part of HTML. For example, the following
> is rendered as a red dot:
>
> 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAUA
> AAAFCAYAAACNbyblAAAAHElEQVQI12P4//8/w38GIAXDIBKE0DHxgljNBAAO
> 9TXL0Y4OHwAAAABJRU5ErkJggg==’
>
> This string is decoded to bytea, and I stored it in a bytea column.
>
> What are some other examples of using bytea to store media content, not
> necessarily using the MIME type? Is there a way to detect the type of these
> media (audio, image) stored in bytea?
>

When I have stored small media items in bytea columns in the past, I just
stored the data. I vaguely recall I did store the mime type in another
column, but that may not be the case in all scenarios (e.g. when a system
is designed to store only PNGs). I think you should assume it's raw data
only, and try to determine the file type by examining the data;

e.g

PNG files have an 8 byte signature:
http://www.libpng.org/pub/png/spec/1.2/PNG-Structure.html
MPEG files have identifying information in the frame header that you may be
able to use: http://mpgedit.org/mpgedit/mpeg_format/MP3Format.html
JPEG images have identifying markers:
https://en.wikipedia.org/wiki/JPEG_File_Interchange_Format

etc.

> Another question I had is, I read that there are performance-related
> issues for storing media in bytea. Are there practical ways to store bytea
> data that does not face performance-related issues? For example, storing
> large media content using multiple bytea parts, and reassembling them
> together once retrieved from the database?
>

Not that I'm aware of. For larger objects, most people store them
externally (which of course loses ACID properties). There are certainly
applications for storing smaller objects directly in the database though -
and some folks have done work in the past with index types and
operators/functions for finding and comparing images for example, so there
are also benefits other than ACID to storing data in this way.

BTW; for pgAdmin related GSoC questions, you'd do better to ask on
pgadmin-hackers(at)postgresql(dot)org(dot)

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company