Re: binary representation of datatypes

On Wed, Oct 22, 2008 at 3:29 AM, Matthieu Imbert
<matthieu.imbert@ens-lyon.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