On Fri, 2 Dec 2005, Luke Lonergan wrote:
Stephen,
On 12/2/05 12:18 PM, "Stephen Frost" <sfrost@xxxxxxxxxxx> wrote:
Just a thought, but couldn't psql be made to use the binary mode of
libpq and do at least some of the conversion on the client side? Or
does binary mode not work with copy (that wouldn't suprise me, but
perhaps copy could be made to support it)?
Yes - I think this idea is implicit in what David suggested, and my response
as well. The problem is that the way the client does conversions can
potentially differ from the way the backend does. Some of the types in
Postgres are machine intrinsic and the encoding conversions use on-machine
libraries, each of which preclude the use of client conversion methods
(without a lot of restructuring). We'd tackled this problem in the past and
concluded that the parse / convert stage really belongs in the backend.
I'll bet this parsing cost varys greatly with the data types used, I'm
also willing to bet that for the data types that hae different encoding on
different systems there could be a intermediate encoding that is far
faster to parse then ASCII text is.
for example, (and I know nothing about the data storage itself so this is
just an example), if the issue was storing numeric values on big endian
and little endian systems (and 32 bit vs 64 bit systems to end up with 4
ways of holding the data) you have a substantial cost in parseing the
ASCII and converting it to a binary value, but the client can't (and
shouldn't) know which endian type and word size the server is. but it
could create a big endian multi-precision encoding that would then be very
cheap for the server to split and flip as nessasary. yes this means more
work is done overall, but it's split between different machines, and the
binary representation of the data will reduce probably your network
traffic as a side effect.
and for things like date which get parsed in multiple ways until one is
found that seems sane, there's a significant amount of work that the
server could avoid.
David Lang
The other thought, of course, is that you could use PITR for your
backups instead of pgdump...
Totally - great idea, if this is actually a backup / restore then PITR plus
filesystem copy (tarball) is hugely faster than dump / restore.
- Luke
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
