Re: Is it possible to make a streaming replication faster using COPY instead of lots of INSERTS?

On Nov 30, 2011, at 18:44, Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> wrote:

> On 11/30/2011 10:32 PM, Sergey Konoplev wrote:
>> Would it be more compact from the point of view of streaming
>> replication if we make the application accumulate changes and do one
>> COPY instead of lots of INSERTS say once a minute? And if it will be
>> so how to estimate the effect approximately?
> Streaming replication works on a rather lower level than that. It records information about transaction starts, rollbacks and commits, and records disk block changes. It does not record SQL statements. It's not using INSERT, so you can't switch to COPY. Streaming replication basically just copies the WAL data, and WAL data is not all that compact.

I think a better way to phrase the question is whether these three types of constructs affect different results on the replication side:

Insert into tbl values(...); [times 50]
insert into tbl values (...), (...), (...), ...; [ once with 50 values ]
Copy [ with 50 input rows provided ]

I would presume the first one is badly performing but no idea whether the multi-value version of insert would be outperformed by an equivalent Copy command (both on the main query and during replication)

Though, does auto-commit affect the results in the first case; I.e., without auto-commit do the first two results replicate equivalently?

>
David J