Re: binary representation of datatypes

On Wed, Oct 22, 2008 at 3:29 AM, Matthieu Imbert
<matthieu(dot)imbert(at)ens-lyon(dot)fr> wrote:
> Jeroen Vermeulen wrote:
>> Matthieu Imbert wrote:
>>
>>> scenario 1 - parse the textual representation of all results of
>>> requests to the database and convert textual timestamps to a binary
>>> format that i choose among those ones (number of microseconds since
>>> 2000-01-01, or a structure similar to pg_tm (but with
>>> microsecond precision), or a time-format similar to one defined in
>>> rfc1305, or something else)
>>>
>>> or
>>>
>>> scenario 2 - directly use pgsql binary timestamp format. I think the
>>> latter is far more efficient. I'm new to postgresql, but from
>>> what i understand, here are the conversions involved in both scenarios
>>> (hopping that my ascii art won't be garbled by your mail
>>> clients ;-) :
>>>
>>>
>>> scenario 1:
>>> .---------. .----------. .---------. .----------. .--------------. .----------. .---------.
>>> |timestamp| |pgsql | |timestamp| |pgsql | |timestamp | |my | |my |
>>> |storage |->|internal |->|storage |->|network |->|as |->|timestamp |->|timestamp|
>>> |in | |to | |in | |to | |textual | |conversion| |format |
>>> |database | |network | |network | |textual | |representation| |routines | | |
>>> |backend | |conversion| | | |conversion| | | | | | |
>>> | | |function | | | |function | | | | | | |
>>> '---------' '----------' '---------' '----------' '--------------' '----------' '---------'
>>
>> I think this scenario has two boxes too many. Why would the backend
>> convert to network representation before converting to text?
>>
>>
>> Jeroen
>>
>
> You mean that when results are asked in textual representation (the default), data is sent on network directly as text?

yes.

You should know that text/binary conversions rarely play a significant
role in terms of performance. There are exceptions...large bytea
columns, or enormous sets of integers. This is coming from a guy that
co-wrote a library that allows you to pull data directly in binary.

merlin