DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type;
	b=TAit+q2xOAB/vOZBRZwDhzBBVq/ptiDJ0sJKC+/pPS7MKB79jdLfhx3yObmcl4tXYYN6kly6HKE+Mgd2iwzJRNz4BbWm7i8doY9wzF/NRn1dr+oGTF20FUUZ6X14EszCWQNHrwJ5uNnG/zFbyuKDifPLrH8p18iSPp5FDoYiN98=;
Message-ID: <203952.56702.qm@web111712.mail.gq1.yahoo.com>
Date: Mon, 25 Oct 2010 11:46:23 -0700 (PDT)
From: Steve Wong <powerpchead@yahoo.com>
Subject: MVCC and Implications for (Near) Real-Time Application
To: pgsql-performance@postgresql.org
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-199831977-1288032383=:56702"

--0-199831977-1288032383=:56702
Content-Type: text/plain; charset=us-ascii

Hi experts,

I have a (near) real-time application in which inserts into the database needs 
to be visible to queries from other threads with minimal delay. The inserts are 
triggered by real-time events and are therefore asynchronous (i.e. many 
performance tips I read related to batch inserts or copy do not apply here, 
since these events cannot be predicted or batched), and the inserted data need 
to be available within a couple of seconds to other threads (for example, an 
inserted row that only appears to other query threads 5 seconds or more after 
the insert is not acceptable). The delay should be under 2 seconds maximum, 
sub-1 second would be great.

My questions are: (1) Does the MVCC architecture introduce significant delays 
between insert by a thread and visibility by other threads (I am unclear about 
how multiple versions are "collapsed" or reconciled, as well as how different 
query threads are seeing which version)? (2) Are there any available benchmarks 
that can measure this delay? (3) What are relevant config parameters that will 
reduce this delay?

Thanks for your patience with my ignorance of MVCC (still learning more about 
it),
Steve


--0-199831977-1288032383=:56702
Content-Type: text/html; charset=us-ascii

<html><head><style type="text/css"><!-- DIV {margin:0px;} --></style></head><body><div style="font-family:'times new roman', 'new york', times, serif;font-size:12pt"><div>Hi experts,</div><div><br></div><div>I have a (near) real-time application in which inserts into the database needs to be visible to queries from other threads with minimal delay. The inserts are triggered by real-time events and are therefore asynchronous (i.e. many performance tips I read related to batch inserts or copy do not apply here, since these events cannot be predicted or batched), and the inserted data need to be available within a couple of seconds to other threads (for example, an inserted row that only appears to other query threads 5 seconds or more after the insert is not acceptable). The delay should be under 2 seconds maximum, sub-1 second would be great.</div><div><br></div><div>My questions are: (1) Does the MVCC architecture introduce significant delays between
 insert by a thread and visibility by other threads (I am unclear about how multiple versions are "collapsed" or reconciled, as well as how different query threads are seeing which version)? (2) Are there any available benchmarks that can measure this delay? (3) What are relevant config parameters that will reduce this delay?</div><div><br></div><div>Thanks for your patience with my ignorance of MVCC (still learning more about it),</div><div>Steve</div><div style="position:fixed"></div>


</div><br>


      </body></html>
--0-199831977-1288032383=:56702--