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

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Is it possible to make a streaming replication faster using COPY instead of lots of INSERTS?
Date: 2011-11-30 14:32:04
Message-ID: CAL_0b1u213XJczQ872_xrVBRLwPsFdcuzaCY2wu5K_Yz3myFVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

An application server does a lot of small inserts on several tables
(kind of users activity logging) in my database. These inserts are
creating a lot of (if not the most of) the traffic on the server. The
server has a hot standby replica working through a very slow channel
between them.

When the amount of this inserts increases the master does not manage
to send such a big stream of changes (because of the low bandwidth) to
the replica in time and the lag between them grows up dramatically.

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?

PostgreSQL version is 9.0.4 on both servers. If you need more
specifics about the configuration just let me know what exactly.

Thank you very much in advance.

--
Sergey Konoplev

Blog: http://gray-hemp.blogspot.com
LinkedIn: http://ru.linkedin.com/in/grayhemp
JID/GTalk: gray(dot)ru(at)gmail(dot)com Skype: gray-hemp

Responses

Browse pgsql-general by date

  From Date Subject
Next Message BK 2011-11-30 16:23:35 Re: Strange problem with turning WAL archiving on
Previous Message Samba 2011-11-30 14:12:07 How to restore the table space tar files created by pg_basebackup?