От: Mischa Sandberg
Тема: Re: COPY vs INSERT
Дата: ,
Msg-id: 1115248292.427956a468a55@webmail.telus.net
(см: обсуждение, исходный текст)
Ответ на: Re: COPY vs INSERT  ("David Roussel")
Ответы: Re: COPY vs INSERT  (Christopher Petrilli)
Re: COPY vs INSERT  (Kris Jurka)
Список: pgsql-performance

Скрыть дерево обсуждения

 (Steven Rosenstein, )
 Re:  (Tom Lane, )
  Re: COPY vs INSERT  (Mischa Sandberg, )
   Re: COPY vs INSERT  ("David Roussel", )
    Re: COPY vs INSERT  (Mischa Sandberg, )
     Re: COPY vs INSERT  (Christopher Petrilli, )
      Re: COPY vs INSERT  (Keith Worthington, )
     Re: COPY vs INSERT  (Kris Jurka, )
      Re: COPY vs INSERT  (Mischa Sandberg, )
       Re: COPY vs INSERT  (Kris Jurka, )
    Re: COPY vs INSERT  (John A Meinel, )
    Re: COPY vs INSERT  (Christopher Kings-Lynne, )
     Re: COPY vs INSERT  (Tom Lane, )
      Re: COPY vs INSERT  ("Jim C. Nasby", )
       Re: COPY vs INSERT  (Dennis Bjorklund, )
        Re: COPY vs INSERT  ("Jim C. Nasby", )
       Re: COPY vs INSERT  (Harald Fuchs, )
       Re: COPY vs INSERT  (Bruno Wolff III, )
        Re: COPY vs INSERT  (Tom Lane, )
  Re:  (Mike Rylander, )

Quoting David Roussel <>:

> > COPY invokes all the same logic as INSERT on the server side
> > (rowexclusive locking, transaction log, updating indexes, rules).
> > The difference is that all the rows are inserted as a single
> > transaction. This reduces the number of fsync's on the xlog,
> > which may be a limiting factor for you. You'll want to crank
> > WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though.
> > One of my streams has 6K records; I run with WB=1000, CS=128.
>
> So what's the difference between a COPY and a batch of INSERT
> statements.  Also, surely, fsyncs only occur at the end of a
> transaction, no need to fsync before a commit has been issued,
> right?

Sorry, I was comparing granularities the other way araound. As far as
xlog is concerned, a COPY is ALWAYS one big txn, no matter how many
putline commands you use to feed the copy. With inserts, you can choose
whether to commit every row, every nth row, etc.

Copy makes better use of the TCP connection for transmission. COPY uses
the TCP connection like a one-way pipe. INSERT is like an RPC: the
sender has to wait until the insert's return status roundtrips.
--
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.



В списке pgsql-performance по дате сообщения:

От: Mischa Sandberg
Дата:
Сообщение: Re: COPY vs INSERT
От: Kris Jurka
Дата:
Сообщение: Re: COPY vs INSERT