I put together a quick test, copying one of my largest tables (around 1.7GB), from my staging system back to my workstation (not over the dedicated GB connection, but through our intranet). Without the binary qualifier on the COPY command it took 11 minutes and 33 seconds. With the binary option it took 2 minutes and 52 seconds. That's a significant savings in replication time, roughly 400% if my math is right. When I next test a slony copy, I'll compare results using the dedicated link to eliminate any random latencies on our intranet.

Doug

-----Original Message-----
From: slony1-general-bounces at lists.slony.info [mailto:slony1-general-bounces at lists.slony.info] On Behalf Of Knight, Doug
Sent: Thursday, October 02, 2008 1:18 PM
To: slony1-general at lists.slony.info
Subject: RE: [Slony1-general] Binary COPY in slony?

Our overall database size is roughly 100GB right now, and as we add data types could easily double or triple. Using the same dedicated 1GB Ethernet connection I use for DRBD and heartbeat between two HA-clustered servers, I can complete a full slony copy in under 3 hours on a live system. We just completed our first complete copy of a live system yesterday, and we're analyzing the results (looking for data gaps during switchover, finding ways to minimize or eliminate data gaps, performance of individual table copies, etc). Most of our binary data is in a lot of time partitioned tables, each roughly 1.7GB in size. Each table could take up to 121 seconds to copy. If I could bring that number down it would shorten the overall time to do the initial sync. As a test I could do some manual copies of these tables, and see if the binary mode helps. Its not a critical item right now, so I have to fit the testing into my current tasks. When I do, I'll post results.

DOug

-----Original Message-----
From: Filip Rembiałkowski [mailto:plk.zuber at gmail.com] 
Sent: Thursday, October 02, 2008 12:09 PM
To: Knight, Doug
Cc: slony1-general at lists.slony.info
Subject: Re: [Slony1-general] Binary COPY in slony?

2008/10/2 Knight, Doug <dknight at wsi.com>:
> Thanks! Would I need it on both the "copy to stdout" and "copy from stdin"?

I think all information is in the source and the docs :)
http://www.postgresql.org/docs/8.3/static/sql-copy.html
http://www.postgresql.org/docs/8.3/static/libpq-copy.html

It's possible to write a patch, but please can you tell me what
performance gain you expect?

You wrote "We have some very large binary tables that might benefit
from the performance of the binary COPY."

did you try to measure the possible speedup? can you share your results?


-- 
Filip Rembiałkowski
_______________________________________________
Slony1-general mailing list
Slony1-general at lists.slony.info
http://lists.slony.info/mailman/listinfo/slony1-general