On Oct 25, 2010, at 2:46 PM, Steve Wong wrote: > 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?
There is no way to know without testing whether your hardware, OS, database schema, and database load can meet your demands. However, there is no technical reason why PostgreSQL could not meet your timing goals- MVCC does not inherently introduce delays, however the PostgreSQL implementation requires a cleanup process which can introduce latency. If you find that your current architecture is not up to the task, consider using LISTEN/NOTIFY with a payload (new in 9.0), which we are using for a similar "live-update" system. Cheers, M -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
