From: Andrew McNamara <andrewm@object-craft.com.au>
To: Miha D. Puc <miha.puc@eba.si>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: Bytea network traffic: binary vs text result format
Date: Tue, 05 Jun 2007 10:57:31 +1000
Message-ID: <20070605005731.F0D606007A7@longblack.object-craft.com.au> (raw)
In-Reply-To: <466299A7.20403@eba.si>
References: <466299A7.20403@eba.si>
>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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: andrewm@object-craft.com.au, miha.puc@eba.si
Subject: Re: Bytea network traffic: binary vs text result format
In-Reply-To: <20070605005731.F0D606007A7@longblack.object-craft.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox