Pascal Cohen wrote: > But to sum up we would like to collect statistics (write mostly tables, > high volume generation, data not critical) on an application usage on a > read mostly DB with the least impact on this DB perfs. ANn we would also > like to be able to archive outside the DB, the old collected data.
[Just throwing ideas around - there's probably a much better and already well established to do all this]: Does the stats data need to be handled within the same transactional scope as the real app data? In other words, do you care if a stats entry is generated for a transaction that then rolls back? If you're OK with that, then another possible area to investigate is the use of IPC messaging to a separate stats collector daemon outside of, and running asynchronously with, PostgreSQL. Many of the PostgreSQL procedural languages can communicate outside the server with mechanisms like UNIX sockets, IP networking, signals, named pipes, and perhaps even things like shared memory if the Pg backend's use of it doesn't interfere. Alternately, you could write your stats collector client as a C add-in to Pg, which would probably let you minimize it's performance cost in exchange for more development and debugging time, plus a higher risk to server stability. Ideally the part of the logging/stats code running inside the Pg backend would do as little work as possible to record the message for later processing. The external stats collector/processor would asynchronously process messages it receives from all Pg backends and record it in your preferred format (perhaps inserting it into a separate write-optimised Pg database on another host). Come to think of it, having stats recorded for transactions that roll back is probably desirable, rather than any sort of downside. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general