To: "Miha D. Puc" <miha.puc@eba.si>
cc: pgsql-interfaces@postgresql.org
Subject: Re: Bytea network traffic: binary vs text result format 
In-reply-to: <466299A7.20403@eba.si> 
References: <466299A7.20403@eba.si>
Comments: In-reply-to "Miha D. Puc" <miha.puc@eba.si>
	message dated "Sun, 03 Jun 2007 12:36:23 +0200."
Date: Tue, 05 Jun 2007 10:57:31 +1000
From: Andrew McNamara <andrewm@object-craft.com.au>
Message-Id: <20070605005731.F0D606007A7@longblack.object-craft.com.au>

>There was some debate recently about using text or binary format.
>There's people who would like to use it but have trouble converting
>binary encoded results into native types and there's people that say
>there's not much performance difference.
>
>I'd like to stress that performance is very different over slow
>network.  The biggest difference is for byte where the text format
>performance is about 3.5 times worse at inserts and updates and about
>2.9 times worse at selects . Here's the reasoning:

You're referring to the worst case of the text format - handling pure
binary data - and yes, in this case, up to 4 times as many bytes can
flow over the network. If the network is your constraining factor, then
this will be significant. But in many other cases (fast local network),
the disks are the limiting factor, or other data types dominate, and in
those cases, the text format can actually be smaller.

>So there is a need (people asking) and reason (performance) to use
>binary format. But there's a huge drawback - the conversions. It's easy
>for varchar, not too bad for basic types (int, float, bool), effort is
>needed for timestamp, date, time and numeric is a pain.
>
>So with all the above there should be a utility for conversion between
>binary format and native types and/or string format in libpq.

And there's the rub... the "basic" types you mention all have standard C
representations, but there is no standard C type for timestamp, date, time
and numeric, so what would libpq convert to? Any chosen format will only
suite a subset of users, and will result in double conversions for others
who are constrained by the existing types used within their application.

I'm not sure what the answer is - certainly documenting the wire format
would be a good first step.

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/